Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Setting format in Excel using perl

by jsuresh (Novice)
on Apr 06, 2018 at 12:06 UTC ( #1212433=perlquestion: print w/replies, xml ) Need Help??

jsuresh has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have generated an report using perl from DB and In that I'm getting some problem with the Date field in Excel. How do I set a custom format for excel using perl? I need this format to be employed in Excel using perl "m/d/yyyy h:mm" Please support me, It is creating an hectic problem. Thanks

Replies are listed 'Best First'.
Re: Setting format in Excel using perl
by Tux (Canon) on Apr 06, 2018 at 12:15 UTC
    my $wbk = Spreadsheet::WriteExcel->new ($xls); my $wks = $wbk->add_worksheet (); my $fmt = $wbk->add_format (align => "center", num_format => "dd-mm-yy +yy"); $wks->write ($row, $col, $date, $fmt);

    update: After a question in a /msg, I checked what the value of $date must be:

    use Spreadsheet::WriteExcel; use Date::Calc qw( Delta_Days ); my $wbk = Spreadsheet::WriteExcel->new ("test.xls"); my $wks = $wbk->add_worksheet (); my $dtf = $wbk->add_format (align => "center", num_format => "yyyy-mm- +dd"); my $dt = 20180406; # Dates need a special treat ... my ($y, $m, $d) = unpack "A4A2A2" => $dt; $dt = 2 + Delta_Days (1900, 1, 1, $y, $m, $d); $wks->write (2, 1, $dt); $wks->write (2, 3, $dt, $dtf); # THIS WORKS for the formatted value # Alternatively use $wks->write_date_time ("D2", "2018-04-06T16:17", $dtf); $wbk->close;

    And IMHO M/D/YY should hardcoded be forbidden in all cases. Use ISO!

    Enjoy, Have FUN! H.Merijn
Re: Setting format in Excel using perl
by afoken (Canon) on Apr 06, 2018 at 12:08 UTC

    Show the relevant part of your code. Which module do you use to generate the Excel file?


    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

      This is one of the formats I used

      my $format1=$workbook->add_format(); $format1->set_bold(); //query my $sth1 = $dbh->prepare($sql1); $sth1->execute(); # write the data into worksheet my $row1=1; while(my @field=$sth1->fetchrow_array){ my $arr_ref=\@field; $worksheet1->write_row($row1,0,$arr_ref); $row1++; }

      2018-04-11 Athanasius added code tags

Re: Setting format in Excel using perl
by Ratazong (Monsignor) on Apr 11, 2018 at 06:11 UTC

    Hi jsuresh

    As others have shown, it is totally possible to format cells in Excel using perl. However in my experience this is probably not the easiest way.

    My typical use-case seems to be similar to yours: fetch some data from some databases, combine it, and present it in Excel-reports. These reports shall be "beautiful", having suitable column widths, suitable text and number formatting, highlighted headers, often cell-borders ...

    Creating all this in perl takes time, and makes my code very dependant on the output-format. (E.g. if someone wants the comment-field to be 20% wider, I would have to update the code.)

    So I don't start writing to an empty Excel-sheet, but prepare a template. And do all the formatting in this template, using Excel. My script then just writes the data in the corresponding fields/columns, and does not have to care about formatting itself. And finally stores the template under a different filename.

    Maybe this approach is also helpful for your future projects

    Best regards, Rata

      Hi Rata,

      Yeah, Exactly, I will definitely take your approach for my upcoming projects.Thanks for your support.

      Thanks again,


Re: Setting format in Excel using perl
by jsuresh (Novice) on Apr 10, 2018 at 07:55 UTC

    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

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

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1212433]
Approved by marto
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (2)
As of 2021-10-24 07:11 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (89 votes). Check out past polls.