in reply to How to modify existing excel file (spreadsheet)

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

  • Comment on Re: How to modify existing excel file (spreadsheet)

Replies are listed 'Best First'.
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.
      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

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