#!/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 | [reply] [Watch: Dir/Any] [d/l] [select] |
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
| [reply] [Watch: Dir/Any] |
#!/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 | [reply] [Watch: Dir/Any] [d/l] |