By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,812 Members | 1,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,812 IT Pros & Developers. It's quick & easy.

Spreadsheet::WriteExcel Question

P: 57
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]
Feb 27 '08 #1
Share this Question
Share on Google+
8 Replies


nithinpes
Expert 100+
P: 410
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.

Expand|Select|Wrap|Line Numbers
  1. my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");
  2. my $sheet1 =  $workbook->add_worksheet("Results Overview");
  3. .
  4. .
  5. .
  6. execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  7. execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  8. execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  9. .
  10. .
  11. sub execute_main_subroutine{
  12.  
  13. my $params = shift;
  14. my %testcase = %$params;
  15. my $report_file_path_name = shift;
  16. my $NBC_type = shift;
  17. my ($workbook,$sheet1) = @_;
  18.  
  19.  
  20.  
Feb 28 '08 #2

P: 57
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.

Expand|Select|Wrap|Line Numbers
  1. my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");
  2. my $sheet1 =  $workbook->add_worksheet("Results Overview");
  3. .
  4. .
  5. .
  6. execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  7. execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  8. execute_main_subroutine (\%testcase_correlation3, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  9. .
  10. .
  11. sub execute_main_subroutine{
  12.  
  13. my $params = shift;
  14. my %testcase = %$params;
  15. my $report_file_path_name = shift;
  16. my $NBC_type = shift;
  17. my ($workbook,$sheet1) = @_;
  18.  
  19.  
  20.  
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:

Expand|Select|Wrap|Line Numbers
  1. my ($workbook,$sheet1) = @_;
I separated them and put:

Expand|Select|Wrap|Line Numbers
  1. my $workbook = @_;
  2. 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.
Feb 28 '08 #3

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

Expand|Select|Wrap|Line Numbers
  1. execute_main_subroutine (\%testcase_correlation1, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  2. execute_main_subroutine (\%testcase_correlation2, $report_file_path_name, 'CHEM',$workbook,$sheet1);
  3. 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!
Feb 28 '08 #4

nithinpes
Expert 100+
P: 410
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:

Expand|Select|Wrap|Line Numbers
  1. my ($workbook,$sheet1) = @_;
I separated them and put:

Expand|Select|Wrap|Line Numbers
  1. my $workbook = @_;
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. my $workbook = @_;
  2. 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
Expand|Select|Wrap|Line Numbers
  1. my $workbook = shift; ### equivalent to my $workbook = shift @_;
  2. my $sheet1 = shift;
Feb 29 '08 #5

P: 57
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.
Feb 29 '08 #6

P: 57
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:

Expand|Select|Wrap|Line Numbers
  1. my $workbook = Spreadsheet::WriteExcel->new("c:\\perl.xls");
  2.  
  3. my $sheet1 = $workbook->add_worksheet("Results Overview");
I am using the following line instead of the shift:
Expand|Select|Wrap|Line Numbers
  1. 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!
Feb 29 '08 #7

nithinpes
Expert 100+
P: 410
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!
Mar 2 '08 #8

P: 57
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.
Mar 3 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.