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.
poj#!/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 (Acolyte) on Apr 11, 2018 at 06:56 UTC | |
by poj (Abbot) on Apr 11, 2018 at 10:36 UTC | |
by jsuresh (Acolyte) on Apr 11, 2018 at 13:24 UTC | |
by poj (Abbot) on Apr 11, 2018 at 13:42 UTC | |
by jsuresh (Acolyte) on Apr 26, 2018 at 16:43 UTC | |
|
In Section
Seekers of Perl Wisdom