in reply to Re^2: Spreadsheet::XLSX date format problem
in thread Spreadsheet::XLSX date format problem

Yay! Have fun shooting the sheet. :-}

  • Comment on Re^3: Spreadsheet::XLSX date format problem

Replies are listed 'Best First'.
Re^4: Spreadsheet::XLSX date format problem
by keithneargarder (Initiate) on Aug 12, 2014 at 18:39 UTC
    Having a related issue. I am able to convert the date fine when I know it is a date. My issue is identifying the cell as a date when it has a custom date format. There is not an apparent way for me to know whether the cell is a date value to convert or an actual number as none of the cell attributes tells me it is a date. Excel cell has a custom number/date format of "mm/dd/yyyy" and a value of "07/20/2014". Code below shows my issue: 
    my ( $cell, $col, $k, $row, $sheet );
my $workbook = Spreadsheet::XLSX->new( "test.xlsx" );

foreach $sheet ( @{ $workbook->{Worksheet} } ) {

  $sheet->{MaxRow} ||= 0;    #$sheet->{MinRow};
  $sheet->{MaxCol} ||= 0;    #$sheet->{MinCol};

  next if ( $sheet->{MaxRow} == 0 and $sheet->{MaxCol} == 0 );

  foreach $row ( $sheet->{MinRow} .. $sheet->{MaxRow} ) {

    foreach $col ( $sheet->{MinCol} .. $sheet->{MaxCol} ) {

      $cell = $sheet->{Cells}[$row][$col];

      if ($cell) {
        foreach $k ( keys %{$cell} ) {
          print "cell{$k}=$cell->{$k}\n";
        }
      }
    }
  }
}

Results:
cell{Type}=Numeric
cell{Val}=41840
cell{_Value}=41840
cell{Format}=

    [download]
    In the original Spreadsheet::ParseExcel the $cell{Type} attribute above returns "Date".
[reply]
[d/l]

      I don't know whether or how you could automatically identify it as a date. When outputting dates to Excel in PHP, I store the serial date and apply a date format. I've not used the equivalent in Perl yet (edited to add: and you're doing input, not output) so can't tell you "just do this and this and Bob's your uncle." I'd be interested in knowing what you find out.

[reply]

In Section Seekers of Perl Wisdom