Preserving metadata when processing Excel file

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

Dear Monks,

For a cgi application on a unix box, I want to open an existing Excel spreadsheet that's sitting on the server, write new data to one of the worksheets, and download the updated file to the user.

We have successfully created and downloaded new Excel workbooks using Spreadsheet::WriteExcel (a most excellent module). But, perusing the Spreadsheet::WriteExcel documentation, we can find no open() or similar command. Worse, we've found articles on other developpers' sites stating flatly that you can't do this with Spreadsheet::WriteExcel - the best you can do is parse an existing file to get cell values, but formatting and formulas are lost. As the whole purpose of the other worksheets in this file is to calculate tables and generate graphical charts based on the first sheet's values, losing formulas is not an option.

So, this is my last shot - does anyone out there have knowledge of how to do this, knowledge that would have escaped the documentation and other developers?

Thanks.

20031224 Edit by BazB: Changed title from 'Spreadsheet::WriteExcel'

Comment on Preserving metadata when processing Excel file

Replies are listed 'Best First'.
Re: Preserving metadata when processing Excel file by maa (Pilgrim) on Dec 24, 2003 at 15:14 UTC
Hi, I can't comment directly on the packages but perhaps your Excel Spreadsheet approach can be modified to make your life a lot easier? Why don't you simply separate the two items? Data (your values) in one Workbook and the formulae/charts in another. Using this approach you can happily (?) use `Spreadsheet::ParseExcel` to read the spreadsheet and `Spreadsheet::WriteExcel` to spit it back out with the new numbers... the linked spreadsheet isn't getting modified and as long as you can ensure that the filename is preserved (i.e. you don't clobber the file as someone opens the other workbook - Excel might not like that) everything should be ok. Alternatively, store your values in a CSV format file(s) and write an Auto_Open macro in your main spreadsheet to import the values into 'named ranges' in your spreadsheet - this saves worrying about "Update Links" messages. Then you have to worry about how 'fresh' the data is. Your approach will depend on how often the data is modified, I guess... HTH - Mark	[reply] [d/l] [select]
Re: Preserving metadata when processing Excel file by jsprat (Curate) on Dec 24, 2003 at 20:27 UTC
Have you looked at Spreadsheet::ParseExcel? HTH	[reply]


Perl Monk, Perl Meditation
	PerlMonks