Spreadsheet::XLSX date format problem

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

Hi there Monks!

I am using Spreadsheet::XLSX module to read the data from a .xlsx(spreadsheet) file.

Source file value : 10/23/2013
The actual data once parsed : 41570 ( The values returned from the cell while reading the data from that cell).

It seems to be the number of days since 12/30/1899? Is this a bug with the module?
Is there a way to solve this issue like converting or parsing 41570 days since 12/30/1899 to get 10/23/2013 after I read the date data from the .xlsx file?
Here is the code I am using:

 sub xlsx_get {

   my $file_to_parse = shift;

   my @values;
     
   my $excel = Spreadsheet::XLSX -> new ($file_to_parse) or die "could
+ not read the excel: $@$!";

   foreach my $sheet (@{$excel -> {Worksheet}}) {
        
     $sheet -> {MaxRow} ||= $sheet -> {MinRow};

     # Skip worksheet if it doesn't contain data.
     next if $sheet -> {MinRow} > $sheet -> {MaxRow};
     my $c=0;

     foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
       $c++;
       
       $sheet -> {MaxCol} ||= $sheet -> {MinCol};

       my $name_xlsx    = $sheet->{Cells}[$row][0]->{Val};
       my $date_xlsx = $sheet->{Cells}[$row][1]->{Val};

       # Skip if it doesn't contain data.
       next unless $name_xlsx;   

       # log for test date format
       warn $date_xlsx;
            
       push @values, [ $name_xlsx, $date_xlsx ];
            
     }
 
   }

  return \@values;

} # end sub xlsx_get
[download]

Thanks for looking!

Comment on Spreadsheet::XLSX date format problem Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::XLSX date format problem by Bethany (Scribe) on Jul 11, 2014 at 15:50 UTC
It seems to be the number of days since 12/30/1899? Is this a bug with the module? No bug, that's how Excel stores dates. I've been dealing with this on a PHP-based Website, and naturally there's a Perl module for that. http://search.cpan.org/~aburs/DateTime-Format-Excel-0.31/lib/DateTime/Format/Excel.pm P.S.: To demonstrate that Excel actually stores the date as epoch days, open Excel and type `2014-07-11` (or any date in a format Excel recognizes as being a date) into a cell. Now format the cell as a generic number. `2014-07-11` becomes `41831.00`. Date-time values get stored with the fraction of a day to the right of the decimal point. P.P.S.: If you haven't got Excel, OpenOffice Calc behaves exactly the same way.	[reply] [d/l] [select]
Re^2: Spreadsheet::XLSX date format problem by Anonymous Monk on Jul 11, 2014 at 19:17 UTC
You're right, the module did it, thanks!	[reply]
Re^3: Spreadsheet::XLSX date format problem by Bethany (Scribe) on Jul 11, 2014 at 19:46 UTC
Yay! Have fun shooting the sheet. :-}	[reply]
Re^4: Spreadsheet::XLSX date format problem by keithneargarder (Initiate) on Aug 12, 2014 at 18:39 UTC
Re^5: Spreadsheet::XLSX date format problem by Bethany (Scribe) on Aug 13, 2014 at 00:49 UTC
Re: Spreadsheet::XLSX date format problem by Corion (Patriarch) on Jul 11, 2014 at 14:27 UTC
Don't use `->{Val}`. It contains the unformatted value. Use `->value()` to get the formatted value.	[reply] [d/l] [select]
Re^2: Spreadsheet::XLSX date format problem by Anonymous Monk on Jul 11, 2014 at 14:47 UTC
I tried that an it still gave me the wrong format: `my $date_xlsx = $sheet->{Cells}[$row][1]->value(); print result: 41570` [download]	[reply] [d/l]
Re: Spreadsheet::XLSX date format problem by Anonymous Monk on Jul 11, 2014 at 15:07 UTC
Look at the data ... XSLX is a Zip-compressed XML file, if I recall correctly. Don't try to guess by "looking through a glass, darkly." Go to the actual source and see what is actually there. It's quite likely that what Excel put there is its internal representation of a date, which would be "days since some epoch."	[reply]
Re^2: Spreadsheet::XLSX date format problem by Anonymous Monk on Jul 11, 2014 at 15:28 UTC
That’s exactly what I did: "Source file value : 10/23/2013 The actual data once parsed : 41570 ( The values returned from the cell while reading the data from that cell).Source file value : 10/23/2013 The actual data once parsed : 41570 ( The values returned from the cell while reading the data from that cell)."	[reply]