Spreadsheet::XLSX date problem

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am using this module to convert xlsx to csv

I am using statements

----

foreach my $sheet (@{$excel->{Worksheet}}) {
    $sheet->{MaxRow} ||= $sheet->{MinRow};
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        $sheet->{MaxCol} ||= $sheet->{MinCol};
        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}-1) {
            my $cell = $sheet->{Cells}[$row][$col];
            if (defined $cell)
            {
                print OUTFILE $cell->{Val};
[download]

----

I am getting trouble with dates (xlsx format is YYYYMMDD)

it is converting it into some integers.

Please advise.

(for eg: it converted date 20091118 to 40135)

Comment on Spreadsheet::XLSX date problem Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::XLSX date problem by moritz (Cardinal) on Jan 08, 2010 at 17:52 UTC
`$ perl -MDate::Simple=date -wle 'print date("2009-11-18") - 40135' 1899-12-30` [download] So it seems to count the days since a date close to 1900-01-01. Maybe that helps you to turn the integers back into dates. I know that Spreadsheet::ParseExcel has the ChkType method which would tell you if a given piece of data is a date, no idea if Spreadsheet::XLSX provides similar information... Perl 6 - links to (nearly) everything that is Perl 6.	[reply] [d/l]
Re: Spreadsheet::XLSX date problem by ww (Archbishop) on Jan 08, 2010 at 22:01 UTC
From Excel\|help: `Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.... You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.` `Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.` The 1904 version is the default, IIRC, for Excel on Apple's OSen (or most of them).	[reply]
Re^2: Spreadsheet::XLSX date problem by Anonymous Monk on Jul 31, 2011 at 18:57 UTC
This is good general information on Microsoft Excel date formats, but it's worthless for this particular problem. The scant documentation provided by the author of Spreadsheet::XLSX is the issue here. The PerlMonks definitely failed on this one.	[reply]