Creating Excel file using Perl

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

Replies are listed 'Best First'.
Re: Creating Excel file using Perl by tangent (Parson) on Oct 20, 2015 at 15:07 UTC
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]	[reply] [d/l] [select]
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).
Re: Creating Excel file using Perl by shadowsong (Pilgrim) on Oct 20, 2015 at 14:57 UTC
Hello Rahul Try changing `my $dateformat = $workbook->add_format(num_format => ~'mm/dd/yyyy~')` [download] To `my $dateformat = $workbook->add_format(num_format => 'mm/dd/yy')` [download] It might be easier for us to investigate and provide further assistance if you posted more of your code..	[reply] [d/l] [select]
Re^2: Creating Excel file using Perl by Rahul Saxena (Initiate) on Oct 21, 2015 at 04:11 UTC
Hello shadowsong, I have tried what you suggested, but format in excel for date values is still showing "custom". how can I display date values in 'date' format in excel. Please help me.	[reply]