LinuxQuestions.org - perl + excel + reading date values

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - perl + excel + reading date values (https://www.linuxquestions.org/questions/programming-9/perl-excel-reading-date-values-620618/)

perl + excel + reading date values

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

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

Cheers,
Tink

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!

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)

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 !

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

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);