perl + excel + reading date values

kshkid · 02-12-2008, 12:50 PM

Hi All,

I have a peculiar problem ( I think its a peculiar problem )

Am using parse, write, save parser perl modules

to read, write, update an excel file

Everything seems to work perfectly except for the following one.

I need to copy date values from sheet 1, doing some extraction and copy the values to sheet 2.

When I copy and display the date value which is of the form "12/1/2008"
I get a number instead of a date value.

Any idea what this number is and how to convert that back to a number so that I could copy it to another sheet ?

Thanks.
-kshkid

Tinkster · 02-12-2008, 02:45 PM

Which CPAN module are you using for the Excel magic, what locale are you using, what
does that "number" look like?

Cheers,
Tink

kshkid · 02-12-2008, 09:35 PM

Following are the modules that am using

use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;

locale is
LANG=en_US.UTF-8

numbers look something like 32416 (of that sort)

Thanks for the reply!

billymayday · 02-12-2008, 09:57 PM

1 should be 1/1/1900, so 32416 is 30/9/1988

Is that what you mean?

Edit - I don't think you want to convert the number at all - it's simply a matter of formatting it in Excel (if it's formatted as a date, Excel will display 1 as 1/1/1900)

kshkid · 02-13-2008, 06:38 AM

Thank you very much for the reply

But that is not the case.

In sheet1 the data is available as "09/10/2001"
if this date value is copied from sheet1 ( through a perl code ) and written to sheet2 it writes that as a number something like 32416 and not as the date value "09/10/2001"

formatting are good and they are the same in both the sheets

This is really confusing !

chrism01 · 02-13-2008, 05:35 PM

Please show us a minimal version of the code that does this

kshkid · 02-14-2008, 01:13 PM

Sure.

Here is the snippet.

At first this seem to be quite straightforward like any other data but only for the date values I encounter these kind of problems

The code does very simple function of copying from 1 sheet to another sheet

Thanks for your time

Code:

my $oExcel = new Spreadsheet::ParseExcel::SaveParser;
  die "Unable to open file " . $PROCESSED_FILE . " <$!>\n" unless defined $oExcel;
  my $oBook = $oExcel->Parse($PROCESSED_FILE);
  die "Unable to parse file " . $PROCESSED_FILE . " <$!>\n" unless defined $oBook;
  $oBook->AddWorksheet(+OUTPUTSHEET_NAME);

  my $obj = Spreadsheet::ParseExcel::Workbook->Parse($PROCESSED_FILE);
  die "Unable to open file " . $PROCESSED_FILE . " <$!>\n" unless defined $obj;

  my $row_num = 0;
  foreach my $sheet (@{$obj->{Worksheet}}) {
    next if( $sheet->{MaxRow} == -1 || $sheet->{MaxCol} == -1 );

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

      $sheet->{MaxCol} ||= $sheet->{MinCol};

      my $col_num = 0;
      foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {

        my $data = $sheet->{Cells}[$row][$col]->{Val};

        $oBook->AddCell(2, $row_num, $col_num++, $data);
      }
      $row_num++ if( $row != 0 );
    }
  }

  $oExcel->SaveAs($oBook, $PROCESSED_FILE);