http://qs321.pair.com?node_id=1212635


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;
poj

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.

        Jey