Re^6: Setting format in Excel using perl

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

poj

Comment on Re^6: Setting format in Excel using perl Select or Download Code

Replies are listed 'Best First'.
Re^7: Setting format in Excel using perl by jsuresh (Acolyte) on Apr 26, 2018 at 16:43 UTC
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]
Re^8: Setting format in Excel using perl by poj (Abbot) on Apr 26, 2018 at 17:23 UTC
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 [download] poj	[reply] [d/l]


XP is just a number
	PerlMonks