Spreadsheet::WriteExcel Question

[Perl Beginner]

I am using Win32. I have created an excel spreadsheet, formatted the columns and rows, and would like to write to the cells…all of this using Spreadsheet::Wr iteExcel. My issue is, my script is very vast with a lot of subroutines, but I need the excel spreadsheet created in the main subroutine becasue this is where the data is that i want to capture. So if I create and format the spreadsheet within the main subroutine, and as it loops through, a new spreadsheet is being created every time, so that when I move to the next row, it writes to it fine, but essentially it is writing over the previous row making it blank. If I create and format the spreadsheet outside of the main subroutine, then when I get to the part where it writes to the excel spreadsheet, it gives me an error that says, “Can’t call method ‘write’ on an undefined value…” I even tried to put the spreadsheet lines in its own subroutine and have the main subrouting call it, but i still run into the same problem when trying to write to it.

My question is how do I initially create and format the spreadsheet at the beginning and then as the subroutine loops, write to the cells, incrementing the rows in my loop? Any insight would be greatly appreciated.

Here is an example of the script format with the excel creation inside the main subroutine. Basically, the way it's set up, the main subroutine will continue looping until it goes through all of the declared %testcase_corre lation variables:

[HTML]#!/usr/bin/perl

use Spreadsheet::Wr iteExcel;
$report_file_pa th_name = "C:\\results.tx t";

my %testcase_corre lation1 = (
name => "Name1",
test_case => "NM204",
Code No. => "Z-204.5 CHEM",
);

my %testcase_corre lation2 = (
name => "Name2",
test_case => "NM366",
Code No. => "Z-366 CHEM",
);

my %testcase_corre lation3 = (
name => "Name3",
test_case => "NM876",
Code No. => "Z-876 CHEM",
);


my $row_count = 1;

my other declared variables;

execute_main_su broutine (\%testcase_cor relation1, $report_file_pa th_name, 'CHEM');
execute_main_su broutine (\%testcase_cor relation2, $report_file_pa th_name, 'CHEM');
execute_main_su broutine (\%testcase_cor relation3, $report_file_pa th_name, 'CHEM');

sub defined_subrout ine1{
.
.
}
sub defined_subrout ine2{
.
.
}

sub execute_main_su broutine{

my $params = shift;
my %testcase = %$params;
my $report_file_pa th_name = shift;
my $NBC_type = shift;

my $workbook = Spreadsheet::Wr iteExcel->new("c:\\perl. xls");

# Add worksheet
my $sheet1 = $workbook->add_worksheet( "Results Overview");

$sheet1->set_margins_LR (.3);
$sheet1->set_margin_top (.75);
$sheet1->set_margin_bot tom(.75);
$sheet1->center_horizon tally();


# Add Formats
my $format = $workbook->add_format() ;
$format->set_align('lef t');
$format->set_align('top ');
$format->set_border() ;
$format->set_text_wrap( );

my $format_fail = $workbook->add_format() ;
$format_fail->set_align('lef t');
$format_fail->set_align('top ');
$format_fail->set_color('red ');
$format_fail->set_border() ;
$format_fail->set_text_wrap( );

my $format_header = $workbook->add_format() ;
$format_header->set_align('cen ter');
$format_header->set_align('vce nter');
$format_header->set_bold();
$format_header->set_text_wrap( );
$format_header->set_border() ;

# Set the height of the rows
$sheet1->set_row(0, 60,$format_head er );


# Set the width of the columns
$sheet1->set_column('A: A', 15);
$sheet1->set_column('B: B', 25);
$sheet1->set_column('C: C', 15);
$sheet1->set_column('D: D', 41);

$sheet1->write(0, 0, "Test Case -\nMessage Group", undef,$format_h eader );
$sheet1->write(0, 1, "AEP-45\nParagraph\n Tested", undef,$format_h eader);
$sheet1->write(0, 2, "Completion\nSt atus", undef,$format_h eader);
$sheet1->write(0, 3, "Reason for Failure", undef,$format_h eader);

defined_subrout ine1();
.
.
defined_subrout ine2();
.
.
$sheet1->write($row_cou nt, 0, "$testcase{test _case}", $format);
$sheet1->write($row_cou nt, 1, "$testcase{AEP_ 45}", $format);

if (xyz == 0)
{
$sheet1->write($row_cou nt, 2, "PASS", $format);
}
else{
$sheet1->write($row_cou nt, 2, "FAIL", $format_fail);
}

$row_count++;

return 0;
} #end main subroutine[/HTML]

[nithinpes]

If I create and format the spreadsheet outside of the main subroutine, then when I get to the part where it writes to the excel spreadsheet, it gives me an error that says, “Can’t call method ‘write’ on an undefined value…” I even tried to put the spreadsheet lines in its own subroutine and have the main subrouting call it, but i still run into the same problem when trying to write to it.

My question is how do I initially create and format the spreadsheet at the beginning and then as the subroutine loops, write to the cells, incrementing the rows in my loop? Any insight would be greatly appreciated.

This reply is to the emphasised portion of your quote.If you are creating spreadsheet outside the main subroutine, then you need to pass $workbook and $sheet1 as parameters to main subroutine. Otherwise, $workbook and $sheet1 would be undefined inside main subroutine.

[CODE=text]
my $workbook = Spreadsheet::Wr iteExcel->new("c:\\perl. xls");
my $sheet1 = $workbook->add_worksheet( "Results Overview");
.
.
.
execute_main_su broutine (\%testcase_cor relation1, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation2, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation3, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
.
.
sub execute_main_su broutine{

my $params = shift;
my %testcase = %$params;
my $report_file_pa th_name = shift;
my $NBC_type = shift;
my ($workbook,$she et1) = @_;


[/CODE]

[Perl Beginner]

This reply is to the emphasised portion of your quote.If you are creating spreadsheet outside the main subroutine, then you need to pass $workbook and $sheet1 as parameters to main subroutine. Otherwise, $workbook and $sheet1 would be undefined inside main subroutine.

[CODE=text]
my $workbook = Spreadsheet::Wr iteExcel->new("c:\\perl. xls");
my $sheet1 = $workbook->add_worksheet( "Results Overview");
.
.
.
execute_main_su broutine (\%testcase_cor relation1, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation2, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation3, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
.
.
sub execute_main_su broutine{

my $params = shift;
my %testcase = %$params;
my $report_file_pa th_name = shift;
my $NBC_type = shift;
my ($workbook,$she et1) = @_;


[/CODE]

Thank you for your reply Nithinpes. However, it’s still not working. When I did as you suggested and pass them as parameters, I still get the error, “Can’t call method ‘write’ on an undefined value…”.
So then, instead of putting:

[CODE=perl]my ($workbook,$she et1) = @_;[/CODE]

I separated them and put:

[CODE=perl]my $workbook = @_;
my $sheet1 = @_;[/CODE]

When I did that, then I get a different error that says, “Can’t call method ‘write’ without a package or object reference…”.

I will continue to research this avenue. i think I'm really close, but just missing something. if you have any more suggestions that would be great.

[Perl Beginner]

Well, i was right...i did miss something! i didn't notice that you put $workbook and $sheet1 in the following lines:

[CODE=perl]execute_main_su broutine (\%testcase_cor relation1, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation2, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);
execute_main_su broutine (\%testcase_cor relation3, $report_file_pa th_name, 'CHEM',$workboo k,$sheet1);[/CODE]

When i put them there, the script runs to completion without errors. but now, when i look at the excel spreadsheet, the lines are not populated. so still the 'write' is not working for some reason inside the main subroutine.

But, i'm getting that much closer!

[nithinpes]

Thank you for your reply Nithinpes. However, it’s still not working. When I did as you suggested and pass them as parameters, I still get the error, “Can’t call method ‘write’ on an undefined value…”.
So then, instead of putting:

[CODE=perl]my ($workbook,$she et1) = @_;[/CODE]

I separated them and put:

[CODE=perl]my $workbook = @_;
my $sheet1 = @_;[/CODE]

When I did that, then I get a different error that says, “Can’t call method ‘write’ without a package or object reference…”.

I will continue to research this avenue. i think I'm really close, but just missing something. if you have any more suggestions that would be great.

The way you have assigned is not right:
[CODE=perl]my $workbook = @_;
my $sheet1 = @_;[/CODE]

For both variables, you are assigning entire array containing both $workbook and $sheet1 variables. When I group them together, both variables will be assigned two remaining elements from the array(after shift). If you want to assign them separately, use shift
[CODE=perl]my $workbook = shift; ### equivalent to my $workbook = shift @_;
my $sheet1 = shift;[/CODE]

[Perl Beginner]

I don't know what it is, it's running to completion but it still isn't populating the excel cells. The formating part is working since it's at the beginning of the script, outside of the main subroutine. but inside the subroutine, it just won't 'write' to the cells.

[Perl Beginner]

It's working! everything you said to do was correct. but i had to move all of the excel fomatting lines inside the main subroutine. I don't know why, but once i did that it worked. the only lines outside of the subroutine were:

my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");

my $sheet1 = $workbook->add_worksheet("Results Overview");

I am using the following line instead of the shift:

my ($workbook,$sheet1)= @_;

The only thing now is now i'm getting an excel warning when i open the spreadsheet. it says, 'File Error: data may have been lost'. But when i look at my data, everything is there.

At any rate, thank you for your knowledge!

[nithinpes]

It's working! everything you said to do was correct. but i had to move all of the excel fomatting lines inside the main subroutine. I don't know why, but once i did that it worked. the only lines outside of the subroutine were:

Offcourse, that is what I had done in my initial reply. There were only two lines outside subroutine, one for $workbook and other for $sheet1. When you did formatting outside the subroutine, it did not work because inside subroutine you are using $format and $format_header variables while writing to excel sheet which are undefined. If you were to do so, then you need to pass $format and $format_header also as parameters to subroutine adding complexity to calling subroutine.
Well, I'm not sure why you got that error while opening excel sheet!

[Perl Beginner]

I've been researching the 'File Error: data may have been lost', and it looks as though this is a known issue with Spreadsheet::Wr iteExcel that happens on certain versions of Microsoft Excel. From what i read it's on the excel end not the Perl end. Don't know what the fix is yet.