Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Spreadsheet::[Parse|Write]Excel - accessing the contents of a cell

Reply
Thread Tools

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

 
 
Justin C
Guest
Posts: n/a
 
      07-06-2006
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.

--
Justin C, by the sea.
 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      07-06-2006
Justin C <(E-Mail Removed)> wrote in
news:(E-Mail Removed) ue.com:

> 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 <(E-Mail Removed)>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://augustmail.com/~tadmc/clpmisc...uidelines.html

 
Reply With Quote
 
 
 
 
Justin C
Guest
Posts: n/a
 
      07-09-2006
On 2006-07-07, Jim Gibson <(E-Mail Removed)> wrote:
> In article <(E-Mail Removed) om>,
> Justin C <(E-Mail Removed)> wrote:
>
>> 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.


>> $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.
 
Reply With Quote
 
Andrew
Guest
Posts: n/a
 
      07-10-2006
Justin C wrote:
> On 2006-07-07, Jim Gibson <(E-Mail Removed)> wrote:
> > In article <(E-Mail Removed) om>,
> > Justin C <(E-Mail Removed)> wrote:
> >
> >> 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.
>
>
> >> $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: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

 
Reply With Quote
 
Justin C
Guest
Posts: n/a
 
      07-10-2006
On 2006-07-10, Andrew <(E-Mail Removed)> wrote:
> 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.

--
Justin C by the sea.
 
Reply With Quote
 
 
 
Reply

Thread Tools

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding contents on yaml file without overwriting actual contents Kamarulnizam Rahim Ruby 4 01-28-2011 09:10 AM
Re: Accessing the contents of a 'cell' object from Python Jeff Epler Python 1 03-16-2005 10:01 AM
Re: Accessing the contents of a 'cell' object from Python paul cannon Python 0 03-16-2005 12:12 AM
Accessing the contents of a 'cell' object from Python paul cannon Python 0 03-15-2005 10:08 PM
Can I restrict both attribute contents and element contents in schema Don Adams XML 1 03-05-2004 12:48 PM



Advertisments