Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Re^2: Setting format in Excel using perl

by poj (Abbot)
on Apr 10, 2018 at 10:01 UTC ( #1212635=note: print w/replies, xml ) Need Help??

in reply to Re: Setting format in Excel using perl
in thread Setting format in Excel using perl

See TIME AND DATE FUNCTIONS in Excel::Writer::XLSX::Utility. Assuming all cells in column T are dates.

#!/usr/bin/perl use strict; use Excel::Writer::XLSX; use Excel::Writer::XLSX::Utility; use DBI; my $dbh = get_dbh(); # connect as req my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); my $worksheet = $workbook->add_worksheet(); # set column format my $datecol = 19; my $date_format = $workbook->add_format(num_format=>'m/d/yyyy h:mm'); $worksheet->set_column($datecol,$datecol,15,$date_format);#width 15 my $sql = "SELECT * FROM test21"; my $sth = $dbh->prepare($sql); $sth->execute; my $row2 = 1; while (my $aref = $sth->fetchrow_arrayref){ # convert to Excel date my $date = $aref->[$datecol]; $date =~ s/ /T/; # optional print "$date\n"; $aref->[$datecol] = xl_parse_date( $date ); $worksheet->write_row($row2++,0,$aref); } $workbook->close;

Replies are listed 'Best First'.
Re^3: Setting format in Excel using perl
by jsuresh (Novice) on Apr 11, 2018 at 06:56 UTC
    Hi Poj,

    Everything look's perfect but the only thing is my Perl library is not having the Excel::Writer::XLSX::Utilities module.

    I have asked my manager for approving my request to add the module and i will try and will let you know.

    Many Thanks, Jey

        Yes,currently I'm using Spreadsheet::WriteExcel.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1212635]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2020-12-02 18:49 GMT
Find Nodes?
    Voting Booth?
    How often do you use taint mode?

    Results (44 votes). Check out past polls.