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

[Justin C]

I've been trying to follow the examples given in the
Spreadsheet:arseExcel 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:arseExcel ;
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:arseExcel::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.

 

[A. Sinan Unur]

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 <[email protected]>
(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

 

[Justin C]

I've been trying to follow the examples given in the
Spreadsheet:arseExcel 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.

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.

 

[Andrew]

I've been trying to follow the examples given in the
Spreadsheet:arseExcel 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.

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:arseExcel -- 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

 

[Justin C]

Also, (this might be completely unrelated to your particular case, but
it does concern Spreadsheet:arseExcel -- 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.