Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^5: Setting format in Excel using perl

by jsuresh (Novice)
on Apr 11, 2018 at 13:24 UTC ( #1212681=note: print w/replies, xml ) Need Help??


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

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

Jey

  • Comment on Re^5: Setting format in Excel using perl

Replies are listed 'Best First'.
Re^6: Setting format in Excel using perl
by poj (Abbot) on Apr 11, 2018 at 13:42 UTC

    In that case try the write_date_time solution Tux suggested here

    #!/usr/bin/perl use strict; use Spreadsheet::WriteExcel; use DBI; my $dbh = get_dbh(); # connect as req my $workbook = Spreadsheet::WriteExcel->new( 'perl.xls' ); 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 ISO8601 yyyy-mm-ddThh:mm:ss.ss my $date = $aref->[$datecol]; $date =~ s/ /T/; print "$date\n"; $aref->[$datecol] = ''; $worksheet->write_row($row2,0,$aref); $worksheet->write_date_time($row2,$datecol,$date); ++$row2; } $workbook->close;
    poj
      Hi All,

      Now the custom format has been applied but what I exactly need is, when we export data and print in excel,in the date field when I give filter option, it is just showing the dates but What i need is It should come Year wise and under the years, it should be month wise.

      Please provide support and Thank you so much for the help which let me set custom formats.

      Thanks

      Jey

        You need to provide a Short, Self-Contained, Correct Example that demonstrates the problem like this

        #!/usr/bin/perl use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new( 'test1.xls' ); my $worksheet = $workbook->add_worksheet(); # set column format my $date_format = $workbook->add_format(num_format=>'m/d/yyyy h:mm'); $worksheet->set_column(0,0,15,$date_format);#width 15 $worksheet->write(0,0,'Date'); my $row2 = 1; while (my $date = <DATA>){ chomp($date); # convert to ISO8601 yyyy-mm-ddThh:mm:ss.ss $date =~ s/ /T/; $worksheet->write_date_time($row2,0,$date); ++$row2; } $worksheet->autofilter(0,0,$row2,0); $workbook->close; __DATA__ 2017-01-08 00:00 2018-04-01 11:00 2010-09-09 13:00 2013-07-09 19:00
        poj

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (7)
As of 2020-11-28 17:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?