Spreadsheet::WriteExcel, can't write to returned object?

[Justin C]

$deity knows if I've got the terminology right on this. I'm trying to
tidy up an old program that extracts some stuff from one Excel
spreadsheet, munges it a little, and then puts it into another
spreadsheet. My method of tidying is, in this instance, to take all of
the bits and put them into subroutines so that the main program looks a
lot cleaner.

So I *had*:
#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $file = 'delete_me.xls';
my $workbook = Spreadsheet::WriteExcel->new($file);
my $excel = $workbook->add_worksheet();

for my $row ( 0 .. 99 ) {
for my $col ( 0 .. 9 ) {
my $str = $row * $col;
$excel->write_string($row, $col, $str);
}
}

That works as expected. I wanted to 'tidy away' the creation of the file and worksheet (I've also got some formatting coding there too in my real program). So here's what I made:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $file = 'delete_me.xls';
my $excel = create_excel_file($file);

for my $row ( 0 .. 99 ) {
for my $col ( 0 .. 9 ) {
my $str = $row * $col;
$excel->write_string($row, $col, $str);
}
}

sub create_excel_file {
my $of = shift;
my $workbook = Spreadsheet::WriteExcel->new($of);
my $worksheet = $workbook->add_worksheet();
return $worksheet;
}

This creates an empty file, the returned worksheet object doesn't get
anything printed to it, nor do I get any errors or warnings. I tried
returning \$worksheet but then the $exel->write_string line complained
"Can't call method ... on an unblessed reference".

This is probably where we see gaping holes in my Perl understanding. I
think this about where I got to in the Alpaca before I stopped having
free time to spend furthering my Perl education.

I did think of putting the sub contents in a BEGIN block, and lose it at
the end of the program, but I don't think that's very elegant (and I'm
not certain I could make that work either). Is there another way, or do
I have to put up with it how it is?

Justin.

 

[Jim Gibson]

$deity knows if I've got the terminology right on this. I'm trying to
tidy up an old program that extracts some stuff from one Excel
spreadsheet, munges it a little, and then puts it into another
spreadsheet. My method of tidying is, in this instance, to take all of
the bits and put them into subroutines so that the main program looks a
lot cleaner.

So I *had*:
#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $file = 'delete_me.xls';
my $workbook = Spreadsheet::WriteExcel->new($file);
my $excel = $workbook->add_worksheet();

for my $row ( 0 .. 99 ) {
for my $col ( 0 .. 9 ) {
my $str = $row * $col;
$excel->write_string($row, $col, $str);
}
}

That works as expected. I wanted to 'tidy away' the creation of the file and
worksheet (I've also got some formatting coding there too in my real
program). So here's what I made:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $file = 'delete_me.xls';
my $excel = create_excel_file($file);

for my $row ( 0 .. 99 ) {
for my $col ( 0 .. 9 ) {
my $str = $row * $col;
$excel->write_string($row, $col, $str);
}
}

sub create_excel_file {
my $of = shift;
my $workbook = Spreadsheet::WriteExcel->new($of);
my $worksheet = $workbook->add_worksheet();
return $worksheet;
}

This creates an empty file, the returned worksheet object doesn't get
anything printed to it, nor do I get any errors or warnings. I tried
returning \$worksheet but then the $exel->write_string line complained
"Can't call method ... on an unblessed reference".

This is probably where we see gaping holes in my Perl understanding. I
think this about where I got to in the Alpaca before I stopped having
free time to spend furthering my Perl education.

The problem is that the top-level spreadsheet object ($workbook) as
returned by new() is lexically-scoped to the create_excel_file
subroutine and goes out of scope when the subroutine returns. While you
are returning and saving the worksheet object, the spreadsheet file has
probably been closed and written to disk at that point, so further
writes do not get saved.

Solutions are to either 1) return the workbook object as well or 2)
make the workbook object global (file scope).

 

[Justin C]

[snip]

The problem is that the top-level spreadsheet object ($workbook) as
returned by new() is lexically-scoped to the create_excel_file
subroutine and goes out of scope when the subroutine returns. While you
are returning and saving the worksheet object, the spreadsheet file has
probably been closed and written to disk at that point, so further
writes do not get saved.

Solutions are to either 1) return the workbook object as well or 2)
make the workbook object global (file scope).

.... defeating moving it to a sub to tidy up. However, I do wonder about
the logic of creating a subroutine that is used only once. AIUI they're
to re-use code. Hey ho. Maybe I'll, as you suggest, return the workbook
too... which has worked quite nicely.

Thank you for the suggestion.

Justin.

 

[Willem]

Jim Gibson wrote:
) The problem is that the top-level spreadsheet object ($workbook) as
) returned by new() is lexically-scoped to the create_excel_file
) subroutine and goes out of scope when the subroutine returns. While you
) are returning and saving the worksheet object, the spreadsheet file has
) probably been closed and written to disk at that point, so further
) writes do not get saved.
)
) Solutions are to either 1) return the workbook object as well or 2)
) make the workbook object global (file scope).

Or 3) stick a reference to the workbook object into the worksheet object,
so that the reference sticks around until the worksheet goes out of scope.


SaSW, Willem
--
Disclaimer: I am in no way responsible for any of the statements
made in the above text. For all I know I might be
drugged or something..
No I'm not paranoid. You all think I'm paranoid, don't you !
#EOT

 

[Peter J. Holzer]

Jim Gibson wrote:
) The problem is that the top-level spreadsheet object ($workbook) as
) returned by new() is lexically-scoped to the create_excel_file
) subroutine and goes out of scope when the subroutine returns. While you
) are returning and saving the worksheet object, the spreadsheet file has
) probably been closed and written to disk at that point, so further
) writes do not get saved.
)
) Solutions are to either 1) return the workbook object as well or 2)
) make the workbook object global (file scope).

Or 3) stick a reference to the workbook object into the worksheet object,
so that the reference sticks around until the worksheet goes out of scope.

That's a change in the spreadsheet object which should only be done by
the maintainer of that package, not by the user (staying out of the
living room and all that). Furthermore, it is likely to be an
incompatible change: Introducing a cyclic reference breaks garbage
collection, so all existing users of the package would have to
explicitely destroy the object.

hp