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

Unable to put array as a comment on excel sheet cell .

P: 16
Hi,

I m facing a problem to add an array as a comment to a MS excel cell using Spreadsheet :: WriteExcel .

I m using the below code

Expand|Select|Wrap|Line Numbers
  1. use Spreadsheet::WriteExcel;
  2. $workbook = Spreadsheet::WriteExcel->new('perl.xls');
  3. $worksheet = $workbook->add_worksheet();
  4. print "\n \n Enter a path value to take its contns \n";
  5.     $pth=<STDIN>;
  6.     chomp $pth;
  7.     $pth =~ tr#\\#/#;
  8.     open(MF, "$pth" ) || die "$!";
  9.     @contents = <MF>;
  10.     close(MF);
  11.         $worksheet->write_comment(10, 2, @contents);
  12.     $workbook->close();
  13.  

above script reads the contents of the specified text file and takes it in to an array @contents and writes it to the secified cell (ie is 10 th row 2nd column ) using write_comment method .

But executing this i got the following output .

Uneven number of additional arguments at s4.pl line 11.

also the last line $workbook->close(); is not executed after that .


Could anyone help me how to add an array or a filehandle in a MS excel cell comment .
Oct 20 '09 #1
Share this Question
Share on Google+
3 Replies


nithinpes
Expert 100+
P: 410
The write_comment method accepts the comment string as a single argument. When you pass an array, each element of the array will be considered as an argument and this violates syntax of the method.
You can read entire chunk of the file as a single string and pass it as comment.
Expand|Select|Wrap|Line Numbers
  1. use Spreadsheet::WriteExcel; 
  2. $workbook = Spreadsheet::WriteExcel->new('perl.xls'); 
  3. $worksheet = $workbook->add_worksheet(); 
  4. print "\n \n Enter a path value to take its contns \n"; 
  5.     $pth=<STDIN>; 
  6.     chomp $pth; 
  7.     $pth =~ tr#\\#/#; 
  8.     $/= "" ;   # undefine input record separator to read entire file
  9.     open(MF, "$pth" ) || die "$!"; 
  10.     $contents = <MF>; # entire file is read into variable
  11.     close(MF); 
  12.         $worksheet->write_comment(10, 2, $contents); 
  13.     $workbook->close(); 
  14.  
Oct 21 '09 #2

P: 16
Yaa it worked peoperly ....!! thnx a lot.. for ur help
Oct 21 '09 #3

P: 1
My Perl Script is generating excel and updating it daily by extracting contents from server logs, which are writtent o various sheets and colums,
I have used Spreadsheet::WriteExcel and Spreadsheet::ParseExcel . I am able to comment also, But when the file is saved again with older data,
simply by
Expand|Select|Wrap|Line Numbers
  1. my $workbook;
  2.     {
  3.         local $^W = 0;
  4.         # Rewrite the file or save as a new file
  5.         $workbook = $existingReport->SaveAs($FILE);
  6.     }
  7.  
The older comments on other cells are removed.
In short, The new comments are still written along with all data except the comments from cells which were written in last run. thank you in advance. Please let me know if detail are needed. Thanks again :)
Oct 21 '11 #4

Post your reply

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