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::WriteExcel. 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_correlation variables:

[HTML]#!/usr/bin/perl

use Spreadsheet::WriteExcel;
$report_file_path_name = "C:\\results.txt";

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

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

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


my $row_count = 1;

my other declared variables;

execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM');
execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM');
execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM');

sub defined_subroutine1{
.
.
}
sub defined_subroutine2{
.
.
}

sub execute_main_subroutine{

my $params = shift;
my %testcase = %$params;
my $report_file_path_name = shift;
my $NBC_type = shift;

my $workbook = Spreadsheet::WriteExcel->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_bottom(.75);
$sheet1->center_horizontally();


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

my $format_fail = $workbook->add_format();
$format_fail->set_align('left');
$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('center');
$format_header->set_align('vcenter');
$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_header );


# 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_header );
$sheet1->write(0, 1, "AEP-45\nParagraph\n Tested", undef,$format_header);
$sheet1->write(0, 2, "Completion\nStatus", undef,$format_header);
$sheet1->write(0, 3, "Reason for Failure", undef,$format_header);

defined_subroutine1();
.
.
defined_subroutine2();
.
.
$sheet1->write($row_count, 0, "$testcase{test_case}", $format);
$sheet1->write($row_count, 1, "$testcase{AEP_45}", $format);

if (xyz == 0)
{
$sheet1->write($row_count, 2, "PASS", $format);
}
else{
$sheet1->write($row_count, 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.

my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");
my $sheet1 =  $workbook->add_worksheet("Results Overview");
.
.
.
execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM',$workbook,$sheet1);
.
.
sub execute_main_subroutine{
 
my $params = shift;
my %testcase = %$params;
my $report_file_path_name = shift;
my $NBC_type = shift;
my ($workbook,$sheet1) = @_;
 
 
 

[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.

my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");
my $sheet1 =  $workbook->add_worksheet("Results Overview");
.
.
.
execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM',$workbook,$sheet1);
.
.
sub execute_main_subroutine{
 
my $params = shift;
my %testcase = %$params;
my $report_file_path_name = shift;
my $NBC_type = shift;
my ($workbook,$sheet1) = @_;
 
 
 

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:

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

I separated them and put:

my $workbook = @_;
my $sheet1 = @_;

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:

execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM',$workbook,$sheet1);

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:

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

I separated them and put:

my $workbook = @_;
my $sheet1 = @_;

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:

my $workbook = @_;
my $sheet1 = @_;

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

my $workbook = shift; ### equivalent to my $workbook = shift @_;
my $sheet1 = shift;

[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::WriteExcel 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.