Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: Setting format in Excel using perl

by jsuresh (Novice)
on Apr 10, 2018 at 07:55 UTC ( #1212625=note: print w/replies, xml ) Need Help??


in reply to Setting format in Excel using perl

Hi,

I'm trying to fetch the data from database using the query and then print it into excel. so,for that, we need to set format for the field dynamically. I've tried all the cases but it is not working.I'm sharing the part of the code.

my $row2=1; while(my @field=$sth2->fetchrow_array){ my $col2=0; my $arr_ref=\@field; # print "$field[19]\n";--> This is my field and this is + where I need to set the custom format. $worksheet2->write_row($row2,0,$arr_ref); $row2++; }
Please look through it and support.

2018-04-11 Athanasius added code tags

Replies are listed 'Best First'.
Re^2: Setting format in Excel using perl
by poj (Abbot) on Apr 10, 2018 at 10:01 UTC

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2020-11-30 14:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?