How to modify existing excel file (spreadsheet)

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

Replies are listed 'Best First'.
Re: How to modify existing excel file (spreadsheet) by davidrw (Prior) on Sep 26, 2005 at 14:55 UTC
Look on search.cpan.org for excel, and also be sure to check out the Index of Spreadsheet FAQs node here on pm, as well as Using Win32::OLE and Excel - Tips and Tricks from the Tutorials.	[reply]
Re: How to modify existing excel file (spreadsheet) by Limbic~Region (Chancellor) on Sep 26, 2005 at 15:04 UTC
benlaw, I have a fair amount of experience doing this on a *nix system. This meant not being able to use Win32::Ole. Depending on what it is you want to do, Spreadsheet::ParseExcel and Spreadsheet::WriteExcel should be a good place to start in a non-windows environment. I ran into several advanced problems (graphs and formatting) that required me to modify the original code so if you run into specific problems - be sure to post them here. Cheers - L~R	[reply]
Re^2: How to modify existing excel file (spreadsheet) by ickyb0d (Monk) on Sep 26, 2005 at 17:43 UTC
I also have a decent amount of experience using the Spreadsheet modules. I found a lot of those formatting issues can be avoided by formatting a template of the first (in Excel), loading the template (using the Spreadsheet module) and then simply filling the cells using Spreadsheet module.	[reply]
Re^3: How to modify existing excel file (spreadsheet) by Limbic~Region (Chancellor) on Sep 26, 2005 at 17:51 UTC
ickyb0d, It was certainly not that straight forward for me - perhaps you would care to provide an example. For instance, if a group of cells were merged with a yellow background and centered text, I could not simply use Spreadsheet::ParseExcel to read the Excel generated template file and Spreadsheet::WriteExcel to modify the cell's contents without losing formatting. I did find ways around the problem but if it is as easy as you say it is, please provide an example. Cheers - L~R	[reply]
Re^2: How to modify existing excel file (spreadsheet) by benlaw (Scribe) on Sep 27, 2005 at 14:28 UTC
Limbic~Region Thx . It seems Spreadsheet::ParseExcel::SaveParser workable in my case. But it cannot copy Graph within the worksheet from template(xls file). Is it possible to do it too? Thx. I am learning how to use Win32::Ole, but can it add a 'sheet' at specific page (eg. middle of the 2 sheets) or handle the graph(within worksheet), it seems the tutorial has not talk about that.	[reply]
Re^3: How to modify existing excel file (spreadsheet) by Limbic~Region (Chancellor) on Sep 27, 2005 at 14:38 UTC
benlaw, There is experimental support for charts in Spreadsheet::WriteExcel. The trouble I had was that I needed to hide the data sheet that the chart used so jmcnamara was kind enough to hack it in for me. I needed to use both modules in tandem (along with a bit of source code modification) to achieve everything I needed. In a nutshell, you create a dummy chart with all the formatting you want and bind it to a datasheet with enough rows/cols to meet your real data, extract the binary representation of that chart to a bin file, and then insert the chart into your new spreadsheet and bind it to a datasheet with the real data. It wasn't a big PITA but it does take a little getting used to. Cheers - L~R	[reply]
Re: How to modify existing excel file (spreadsheet) by gargle (Chaplain) on Sep 26, 2005 at 14:46 UTC
Hi, Have you tried to supersearch on perlmonks for win32::ole and/or excell? You'd find exactly what you're looking for. -- if ( 1 ) { $postman->ring() for (1..2); }	[reply]