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

#!/usr/bin/perl

use warnings;

use Spreadsheet::WriteExcel;

use Spreadsheet::ParseExcel;

use Spreadsheet::ParseExcel::SaveParser;

sub create_excel {

$workbook = Spreadsheet::WriteExcel>new('FAISTATS_'."$Month".'.xls');

for ( $i=1;$i<=8;$i++) {

$s$i = $workbook->add_worksheet( "rpzea0".$i."a001" ) ;

} }

&create_excel();

I want to create 8 different worksheets inside the excel sheet FAI_STATS_$Month.xls .I am able to do it when the code is not inside a subroutine but when i place the code inside a subroutine and call it ..it only creates one worksheet that too with the name of the FILe. Please help
  • Comment on Not able to create worksheets inside a subroutine

Replies are listed 'Best First'.
Re: Not able to create worksheets inside a subroutine
by hdb (Monsignor) on Sep 29, 2013 at 15:50 UTC

    One typo in your code might cause the problem: 

    $workbook = Spreadsheet::WriteExcel>new('FAISTATS_'."$Month".'.xls');

    [download]
    should be 
    $workbook = Spreadsheet::WriteExcel->new('FAISTATS_'."$Month".'.xls');

    [download]

    When I corrected this and ran it under use strict; (a few declarations are needed...), it created a file with the desired sheets. 

    use warnings;
use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

my $Month = 9;
my @s;

sub create_excel {
  my $workbook = Spreadsheet::WriteExcel->new('FAISTATS_'."$Month".'.x
+ls');
  
  for ( my$i=1;$i<=8;$i++) {
    $s[$i] = $workbook->add_worksheet( "rpzea0".$i."a001" ) ;
  } 
}

create_excel();

    [download]
[reply]
[d/l]
[select]
      Thanks...i found out what the problem is...i was not declaring wrokbook as type my(local).Can you try running you code after removing my it woudnt work..i dont understand why
[reply]

        No, removing my is not the way to go at all. Always use strict;, clear away all errors and then see what happens.

[reply]
[d/l]
[select]

        Spreadsheet::WriteExcel tries to close your workbook automatically but sometimes has problems "If the new(), add_worksheet() or add_format() methods are called in subroutines." You can read the section on the close() method for more detail on limitations of automatic closing.

[reply]
Re: Not able to create worksheets inside a subroutine
by roboticus (Chancellor) on Sep 29, 2013 at 15:39 UTC

    devbond:

    A couple notes:

    • I don't see anything obviously wrong.
    • I've created workbooks similarly, so it's definitely not a problem in cpan::Spreadsheet::WriteExcel,
    • It's not a good practice to call functions using the ampersand. Just leave off the ampersand and it will be fine.
    • Your "C-style" loop could be more perlish, like: 
      for $i (1 .. 8) { ... }

      [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
      i made the following modifications but still no change in the output :(

      #!/usr/bin/perl

      use warnings;

use Spreadsheet::WriteExcel;

use Spreadsheet::ParseExcel;

use Spreadsheet::ParseExcel::SaveParser;

$Month=Sept;
my @s;
sub create {
$workbook = Spreadsheet::WriteExcel->new('FAISTATS_'.'$Month'.'xls');

for $i(1..8) {

$s[$i] = $workbook->add_worksheet( "rpzea0".$i."a001" ) ;

} }
create();

      [download]
[reply]
[d/l]

        Please add use strict; at the beginning and correct all errors that will be thrown. Also, the single quotes around $Month will prevent that you get the correct month. You also lack a dot in front of the extension xls.

[reply]
[d/l]
[select]
Re: Not able to create worksheets inside a subroutine
by MidLifeXis (Monsignor) on Sep 30, 2013 at 13:32 UTC
[reply]

Back to Seekers of Perl Wisdom