Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have the following example code: 
use warnings;
use strict;

use Spreadsheet::WriteExcel;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");

my $worksheet;

add_ws();

sub add_ws {
    $worksheet = $workbook->add_worksheet('Test');
}

[download]
This worksheet doesn't get added unless I use it outside of the subroutine like so: 
use warnings;
use strict;

use Spreadsheet::WriteExcel;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");
my $worksheet = $workbook->add_worksheet('Test');

[download]
Any idea why or am I totally missing something?

Thanks

2007-01-06 Retitled by planetscape, as per Monastery guidelines
Original title: 'subroutine mystery?'

Replies are listed 'Best First'.
Re: Help adding a worksheet in a subroutine with Spreadsheet::WriteExcel
by kyle (Abbot) on Jan 05, 2007 at 18:50 UTC

    The documentation for Spreadsheet::WriteExcel says:

    In addition, close() may be required to prevent perl's garbage collector from disposing of the Workbook, Worksheet and Format objects in the wrong order. Situations where this can occur are:

    • If my() was not used to declare the scope of a workbook variable created using new().
    • If the new(), add_worksheet() or add_format() methods are called in subroutines.

    The reason for this is that Spreadsheet::WriteExcel relies on Perl's DESTROY mechanism to trigger destructor methods in a specific sequence. This may not happen in cases where the Workbook, Worksheet and Format variables are not lexically scoped or where they have different lexical scopes.

[reply]
[d/l]
[select]
Re: Help adding a worksheet in a subroutine with Spreadsheet::WriteExcel
by ferreira (Chaplain) on Jan 05, 2007 at 18:54 UTC

    You may have said that you posted too early and that calling close() is the answer to your problem, but it is indeed a mistery documented in the entry for close() in Spreadsheet::WriteExcel.

    In addition, close() may be required to prevent perl's garbage collector from disposing of the Workbook, Worksheet and Format objects in the wrong order. Situations where this can occur are:
    • If my() was not used to declare the scope of a workbook variable created using new().
    • If the new(), add_worksheet() or add_format() methods are called in subroutines.

    This is basically a bug, I think. The implementation probably use weak references or something similar which allows objects to be disposed without a discipline needed for correctly handling composite objects. The invoking of close() is the documented workaround.

[reply]
[d/l]
[select]
Re: Help adding a worksheet in a subroutine with Spreadsheet::WriteExcel
by ww (Archbishop) on Jan 05, 2007 at 18:26 UTC
    That looks like a classic case of overwriting the newly written worksheet each time thru the loop.
[reply]
      I think I used a bad example...I posted this question too soon. The reason it isn't getting added in the sub is because I never close the workbook (e.g. $workbook->close();)

      Do that and it works fine

[reply]
        Thanks so much, You solved my problem..even I was missing close function and struggling to add worksheets.
[reply]

Back to Seekers of Perl Wisdom