Re: Creating Excel file using Perl

In addition to what shadowsong points out, the date string that you pass to write_date_time() must be in ISO8601 format ( 'yyyy-mm-ddT' or 'yyyy-mm-ddThh:mm:ss.sss' )

my $format = $workbook->add_format(num_format => 'mm/dd/yy');
$worksheet->write_date_time(0, 1, '2004-05-13', $format); # wrong
$worksheet->write_date_time(1, 1, '2004-05-13T', $format); # right
[download]

Results in:

Row, Col    = (0, 1)
Value       = 2004-05-13

Row, Col    = (1, 1)
Value       = 05/13/04
[download]

Comment on Re: Creating Excel file using Perl Select or Download Code

Replies are listed 'Best First'.
Re^2: Creating Excel file using Perl by Rahul Saxena (Initiate) on Oct 21, 2015 at 04:24 UTC
hello tangent, My only issue is that it is showing "custom" format, why not 'date' format when displayed in excel. I need to use formula on date values after creating excel file as I am not able to apply formula on custom format. Please help.	[reply]
Re^3: Creating Excel file using Perl by locked_user sundialsvc4 (Abbot) on Oct 21, 2015 at 15:07 UTC
If you are “not able to apply a formula to a custom format,” then this immediately suggests to me that Excel no longer considers the contents of that cell to be a number: it is now considered to be a string. And that, therefore, would be the actual root cause of your problem. You must be certain that you are, in fact, posting a date value, and then (if necessary) instructing Excel to format the cell (range) as you desire. Here’s an easy way to check. In Excel, load the spreadsheet and then change the (date) formatting, say of the column. Values that truly are “dates” will be reformatted. Values that are strings, of course, will not. String-valued columns will allow any string value (including those representing non-existent dates like February 30th).