Recent Post to CUFP

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

Replies are listed 'Best First'.
Re: Recent Post to CUFP by trs80 (Priest) on Jan 23, 2002 at 11:03 UTC
THIS APPLIES TO THE DEFAULT PPM FROM ACTIVESTATE, newer versions allow for cell notation. The ActiveState version is .27 and the latest CPAN version is .34. I recommend upgrading, see end of node for instructions. The `write` method is expecting a row and column not a cell name like Excel. So the `write` method call becomes: `$worksheet->write('3' , '0', '=Results.xls!D4' ); # row4 columnA` [download] The loop will have to be changed to used the row, column vs. the cell name. The documentation also notes that functions are not supported, but I am not sure if that effects this code or not since you are merely creating the functions and not running them. I opened the spreadsheet and clicked on the A4 cell and it gave a file not found since I don't have a Result.xls so I think it will work. Hope this helps. This a fairly generic how to install any module on Win32 without using PPM as long as the module has no XS code. You can get the latest version installed and the code will work as shown (i think) if you have .34 by doing the following on Win32: Download the Spreadsheet::Excel .34 tar.gz form CPAN Extract the tar.gz Go to the directory created In a console (dos window) `ppm install Parse::RecDescent` (dependency) perl Makefile.PL nmake nmake test nmake install There is a link to nmake here. Make sure you put nmake in your path. I would recommend perl -MCPAN -e "install Spreadsheet::WriteExcel", but there is more setup involved and it seems the tar.gz is faulty for Spreadsheet::Write. I had a problem in both the Perl Tar module and WinZip.	[reply] [d/l] [select]
Re: Re: Recent Post to CUFP by jmcnamara (Monsignor) on Jan 23, 2002 at 14:17 UTC
That's a good reply. Just a few additional points. You can install the latest version of Spreadsheet::WriteExcel via PPM as follows: `C:\> ppm PPM> set repository tmp http://homepage.eircom.net/~jmcnamara/perl PPM> install Spreadsheet-WriteExcel PPM> quit C:\>` [download] Occasionally this won't work and you will have to do the following: `PPM>install http://homepage.eircom.net/~jmcnamara/perl/Spreadsheet-WriteExcel.ppd` it seems the tar.gz is faulty for Spreadsheet::Write. I had a problem in both the Perl Tar module and WinZip. That's strange. I've never had problems installing the module via `perl -MCPAN -e shell`. Also, I can untar the distro using Unix `tar` or WinZip without problems. -- John.	[reply] [d/l] [select]
Re: Re: Recent Post to CUFP by Gerard (Pilgrim) on Jan 23, 2002 at 12:09 UTC
Thanks for your advice. As you assumed, as I wrote the code at work, I referred to the most recent documentation from cpan and when I installed it at home I installed the older version from active state, unbeknown to my less observant self. Thanks heaps for your great advice. Gerard	[reply]
Re: Recent Post to CUFP by Albannach (Monsignor) on Jan 23, 2002 at 11:31 UTC
As trs80 notes, Spreadsheet::WriteExcel is still a work in progress, and though there has been a great deal of progress, even the very latest version (0.34 as I type) does not support references to sheets outside the current workbook. As you cannot modify existing workbooks with this module, you can't even add a new empty sheet to your existing Results.xls file, and insert the summary formulas you want in there with references to the relevant existing sheet names. While you could try Win32::OLE if you are on a Windows platform with Excel installed, you might have an easier time of it if you use Spreadsheet::ParseExcel to read the cells you want from the original spreadsheet, then just create a new workbook with Spreadsheet::WriteExcel, inserting the cells you want to keep, no formulas required. A final note on your actual code: besides the usual `use strict;` and `use warnings;` (or -w as your version allows) admonitions, I'd suggest you use the `write($row, $col, $formula)` method format, as then you don't need to do any string manipulation to create the Excel-style cell names in your loop. Update: I had no trouble at all using WinZip to install the latest tar.gz distribution from CPAN. -- I'd like to be able to assign to an luser	[reply] [d/l] [select]