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);
[download]

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;
[download]

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

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

Alexander

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++;
}
[download]

2018-04-11 Athanasius added code tags

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,

Jey

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++;
}
[download]

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;
[download]

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