Spreadsheet::[Parse|Write]Excel - accessing the contents of a cell

Discussion in 'Perl Misc' started by Justin C, Jul 6, 2006.

  1. Justin C

    Justin C Guest

    I've been trying to follow the examples given in the
    Spreadsheet::parseExcel module and appear to have become stuck. I have a
    spreadsheet, most of which I need to copy to a new spreadsheet...
    A6 to J6084 (well, that last number changes each time the sheet is
    updated but I can extract that info from the sheet).

    What I have so far is:

    ---- START ----
    use strict ;
    use warnings ;
    use Spreadsheet::parseExcel ;
    use Spreadsheet::WriteExcel ;

    my $orig_file = "/home/data/common/alan/excel/CATALOGU/A_TO_Z.XLS" ;
    my $new_file = "/var/www/download/files/complete_listing.xls" ;

    my $newbook = Spreadsheet::WriteExcel->new($new_file) ;
    my $newsheet = $newbook->add_worksheet() ;

    my $oldbook = new Spreadsheet::parseExcel::Workbook->Parse($orig_file) ;
    my $oldsheet = $oldbook->Worksheet('Sheet1') ;

    my $lastrow = $oldsheet->{MaxRow} ;
    print $lastrow, "\n" ;
    my $srow = 6 ; #start row - after all the header/front page stuff.
    my $lcoumn = 9 ;#last col. - leave off the supplier detail.

    my $r = $srow ;

    while ( $r <= $lastrow ) {
    my $c = 0 ;
    while ( $c < 10 ) {
    my $nsr = $r - $srow ; # new sheet row number
    my $cell = $oldsheet->{Cells}[$r][$c] ;
    $newsheet->write($nsr , $c , $cell->Value) if ( $cell->Value ) ;
    $newsheet->write($nsr , $c , $cell->Format) if ( $cell->Format ) ;
    printf "Row: %d Col: %d Nsr: %d\n", $r, $c, $nsr ;
    $c++ ;
    }
    $r++ ;
    }

    ---- END ----

    The new xls file gets created, but it's empty. The while loops iterate
    over the rows and columns in the original OK - well, according to the
    second print statement above it's all incrementing properly.

    If anyone can tell me why either the cell value and formatting aren't
    being read, or they aren't being written, I'll be very grateful.


    Justin.

    --
    Justin C, by the sea.
     
    Justin C, Jul 6, 2006
    #1
    1. Advertising

  2. Justin C <> wrote in
    news::

    > The new xls file gets created, but it's empty.


    What happens if you add an explicit:

    $newbook->close;

    at the end of the program?

    --
    A. Sinan Unur <>
    (remove .invalid and reverse each component for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
     
    A. Sinan Unur, Jul 6, 2006
    #2
    1. Advertising

  3. Justin C

    Justin C Guest

    On 2006-07-07, Jim Gibson <> wrote:
    > In article <>,
    > Justin C <> wrote:
    >
    >> I've been trying to follow the examples given in the
    >> Spreadsheet::parseExcel module and appear to have become stuck. I have a
    >> spreadsheet, most of which I need to copy to a new spreadsheet...
    >> A6 to J6084 (well, that last number changes each time the sheet is
    >> updated but I can extract that info from the sheet).
    >>
    >> What I have so far is:
    >>
    >> ---- START ----

    >
    > [start of program snipped]
    >
    >> while ( $r <= $lastrow ) {
    >> my $c = 0 ;
    >> while ( $c < 10 ) {

    >
    > Perl has 'for' loops, you know.


    Yes, should I have used a for loop instead? Why is it better?


    >> my $nsr = $r - $srow ; # new sheet row number
    >> my $cell = $oldsheet->{Cells}[$r][$c] ;
    >> $newsheet->write($nsr , $c , $cell->Value) if ( $cell->Value ) ;

    >
    > I would delete the if part of this statement. You don't know if


    My concern was, if the cell contained nothing then, $cell->Value would be
    undef and I'd either get something unwanted in the new worksheet or the
    program wouldn't run.


    >> $newsheet->write($nsr , $c , $cell->Format) if ( $cell->Format ) ;

    >
    > This line is not correct. The format instance should be the fourth
    > (optional) argument of write.


    I knew that! I just wasn't paying attention :)


    > Here is a program that copies a spreasheet and sets the color of the
    > copied cells. Maybe you can start with this. It uses the Cell() method
    > instead of Cells() (might be more efficient) and the add_format()
    > method of the Workbook. I leave the offset methods of moving cells to
    > you:


    OK, let me try it and see what happens.

    Parse: 0.2603
    Write: 2.17
    Can't call method "Value" on an undefined value at bin/web_new/a2z2.pl line 22.

    Maybe the spreadsheets you've used this on have no empty cells? I think
    I'm going to compare our two attempts, read the documentation to see
    what yours is doing, and then have another go.

    Thanks for the reply.


    Justin.

    --
    Justin C, by the sea.
     
    Justin C, Jul 9, 2006
    #3
  4. Justin C

    Andrew Guest

    Justin C wrote:
    > On 2006-07-07, Jim Gibson <> wrote:
    > > In article <>,
    > > Justin C <> wrote:
    > >
    > >> I've been trying to follow the examples given in the
    > >> Spreadsheet::parseExcel module and appear to have become stuck. I have a
    > >> spreadsheet, most of which I need to copy to a new spreadsheet...
    > >> A6 to J6084 (well, that last number changes each time the sheet is
    > >> updated but I can extract that info from the sheet).
    > >>
    > >> What I have so far is:
    > >>
    > >> ---- START ----

    > >
    > > [start of program snipped]
    > >
    > >> while ( $r <= $lastrow ) {
    > >> my $c = 0 ;
    > >> while ( $c < 10 ) {

    > >
    > > Perl has 'for' loops, you know.

    >
    > Yes, should I have used a for loop instead? Why is it better?
    >
    >
    > >> my $nsr = $r - $srow ; # new sheet row number
    > >> my $cell = $oldsheet->{Cells}[$r][$c] ;
    > >> $newsheet->write($nsr , $c , $cell->Value) if ( $cell->Value ) ;

    > >
    > > I would delete the if part of this statement. You don't know if

    >
    > My concern was, if the cell contained nothing then, $cell->Value would be
    > undef and I'd either get something unwanted in the new worksheet or the
    > program wouldn't run.
    >
    >
    > >> $newsheet->write($nsr , $c , $cell->Format) if ( $cell->Format ) ;

    > >
    > > This line is not correct. The format instance should be the fourth
    > > (optional) argument of write.

    >
    > I knew that! I just wasn't paying attention :)
    >
    >
    > > Here is a program that copies a spreasheet and sets the color of the
    > > copied cells. Maybe you can start with this. It uses the Cell() method
    > > instead of Cells() (might be more efficient) and the add_format()
    > > method of the Workbook. I leave the offset methods of moving cells to
    > > you:

    >
    > OK, let me try it and see what happens.
    >
    > Parse: 0.2603
    > Write: 2.17
    > Can't call method "Value" on an undefined value at bin/web_new/a2z2.pl line 22.
    >
    > Maybe the spreadsheets you've used this on have no empty cells? I think
    > I'm going to compare our two attempts, read the documentation to see
    > what yours is doing, and then have another go.
    >


    Also, (this might be completely unrelated to your particular case, but
    it does concern Spreadsheet::parseExcel -- last time I checked, a
    couple of months ago), if you are generating or resaving your Excel
    sheets with OpenOffice (in *.xls format), you may end up with some
    strange hidden "defects"(?) that undermine ParseExcel.pm, making it
    produce unexpected results. IIRC, in particular, this has/had to do
    with blank/undefined cells, but there could be other problems as well.
    I gave up trying to fix it and circumvented it by (re)saving the
    spreadsheet in MS Excel.

    Andrew
     
    Andrew, Jul 10, 2006
    #4
  5. Justin C

    Justin C Guest

    On 2006-07-10, Andrew <> wrote:
    > Also, (this might be completely unrelated to your particular case, but
    > it does concern Spreadsheet::parseExcel -- last time I checked, a
    > couple of months ago), if you are generating or resaving your Excel
    > sheets with OpenOffice (in *.xls format), you may end up with some
    > strange hidden "defects"(?) that undermine ParseExcel.pm, making it
    > produce unexpected results. IIRC, in particular, this has/had to do
    > with blank/undefined cells, but there could be other problems as well.
    > I gave up trying to fix it and circumvented it by (re)saving the
    > spreadsheet in MS Excel.


    Fortunately (?) these are virgin Excel sheets. OO has only been near
    them to copy them.


    Justin.

    --
    Justin C by the sea.
     
    Justin C, Jul 10, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. paul cannon
    Replies:
    0
    Views:
    301
    paul cannon
    Mar 15, 2005
  2. lovelymmx
    Replies:
    1
    Views:
    113
    Guanliang Liu
    May 3, 2007
  3. Pam
    Replies:
    6
    Views:
    848
    Matt Garrish
    Sep 25, 2006
  4. Justin C

    Spreadsheet::Parse & Write Excel

    Justin C, Oct 30, 2007, in forum: Perl Misc
    Replies:
    1
    Views:
    190
    Justin C
    Oct 31, 2007
  5. Replies:
    1
    Views:
    289
    J. Gleixner
    Jan 14, 2008
Loading...

Share This Page