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


Hi Monks!

I am trying to create Worksheets using Spreadsheet::WriteExcel module

The code is as follows
use Spreadsheet::WriteExcel
my $workBook = Spreadsheet::WriteExcel -> new ( "report.xls" );

foreach $file ( @files ) {
        if ( ! ( ( $file eq "." ) || ( $file eq ".." ) ) )
{

                 #Create excel sheet for each testsuite.
                 my $workSheet;

                 $workSheet = create_excel ( $file );
                 write_excel ( $workSheet );                 
       }
}

[download]

Here the array "@files" contains files in a directory

I am using a function create_excel to create an excel sheet and format the sheet

The function create_excel is as follows
sub create_excel {
        my $name = shift;

        my $worksheet = $workBook->add_worksheet( "$name" );

        #my $format = $workBook->add_format ( border => 2, valign => '
+vcenter', align => 'center', bold => 1, );
        #$format->set_bg_color('47');

        #my $format1 = $workBook->add_format ( border => 2, align => '
+center', bold => 1 );
        #$format1->set_bg_color('47');

        #my $format2 = $workBook->add_format ( border => 2, valign => 
+'vcenter', align => 'center', bold => 1, );
        #$format2->set_bg_color('42');

        #$worksheet->set_column ( "C:C",40 );
        #$worksheet->merge_range ( "C6:C8","TEST",$format );
        #$worksheet->merge_range ( "D6:H6","RESULT",$format );
        #$worksheet->merge_range ( "I6:N6","PAGES",$format );

        return $worksheet;
}

[download]

I am using a function write_excel to write the data in the excel sheet

I am not able to do like this...

I am getting a blank excel sheet. There is no data written in it

Can anyone Help me on this is regard

Thanks in advance

Replies are listed 'Best First'.
Re: Work Sheets in excel in Perl
by jmcnamara (Monsignor) on Sep 29, 2008 at 09:32 UTC
    The problems is that your worksheet objects have a different scope to the parent workbook object and as a result they get garbage collected too early.

    The solution is to place the worksheet reference in the same scope as the workbook or else to call the the workbook close() method to ensure that the destructors are called in the right order: 

        foreach my $file (@files) {
        if ( !( ( $file eq "." ) || ( $file eq ".." ) ) )
        {    
            ...
        }
    }

    $workBook->close();

    [download]
    See the close() section of the Spreadsheet::WriteExcel docs for more information.

    --
    John.

[reply]
[d/l]

      Thanks a lot John!!!!

      It works!!!
[reply]
Re: Work Sheets in excel in Perl
by dragonchild (Archbishop) on Sep 29, 2008 at 14:39 UTC
    I'd recommend taking a look at Excel::Template. It may simplify your work quite dramatically.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: Work Sheets in excel in Perl
by Anonymous Monk on Sep 29, 2008 at 06:26 UTC
[reply]

Back to Seekers of Perl Wisdom