LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 02-12-2008, 12:50 PM   #1
kshkid
Member
 
Registered: Dec 2005
Distribution: RHEL3, FC3
Posts: 383

Rep: Reputation: 30
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
 
Old 02-12-2008, 02:45 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,965
Blog Entries: 11

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



Cheers,
Tink
 
Old 02-12-2008, 09:35 PM   #3
kshkid
Member
 
Registered: Dec 2005
Distribution: RHEL3, FC3
Posts: 383

Original Poster
Rep: Reputation: 30
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!
 
Old 02-12-2008, 09:57 PM   #4
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
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)

Last edited by billymayday; 02-12-2008 at 09:59 PM.
 
Old 02-13-2008, 06:38 AM   #5
kshkid
Member
 
Registered: Dec 2005
Distribution: RHEL3, FC3
Posts: 383

Original Poster
Rep: Reputation: 30
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 !
 
Old 02-13-2008, 05:35 PM   #6
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,239

Rep: Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024Reputation: 2024
Please show us a minimal version of the code that does this
 
Old 02-14-2008, 01:13 PM   #7
kshkid
Member
 
Registered: Dec 2005
Distribution: RHEL3, FC3
Posts: 383

Original Poster
Rep: Reputation: 30
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);
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
bash; reading values from a file km4hr Programming 16 07-28-2005 02:07 PM
reading values for a background process shadowman21i Programming 1 12-22-2004 06:47 AM
Reading excel files from c language rajesh_b Programming 4 11-25-2004 06:26 AM
reading bridge values from kernel anuqan Linux - Networking 3 04-14-2004 06:18 PM
Perl of Wisdom needed for reading in iptables values pjcp64 Programming 5 06-02-2003 05:52 AM


All times are GMT -5. The time now is 02:05 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration