The file and data format(s) in Excel are complex. So you're going to have to (a) use Win32::OLE or an equivalent to let your program tell Excel (or OpenOffice or other application that can read/write Excel files) to do the work, (b) parse the file, edit the data, and write the new/updated file, or (c) write the data in a simpler format, edit the data, write the file in an Excel-compatible form.
I've had the same problem with large spreadsheets myself, and used the trick in Spreadsheet::ParseExcel under the heading "Reducing the memory usage of Spreadsheet::ParseExcel" by extracting just the data I needed and discarding the rest, and it worked well enough. Recently a client upgraded Excel and now uses .xlsx files, so I've had to parse some of the data out myself (spelunking through the XML). (Not as simple as it should be, by the way.)
Fortunately, I've always had control of the formatting, and never had to deal with embedded objects (such as charts) so I haven't been bitten by the "just add a few lines to an existing workbook" problem--instead I just sucked out the data and generated a brand new workbook.
Hmmm ... I just thought of another route: (d) store your data in a database (e.g. SQLite) or flat file, and write some VBA code to update the data on demand. So the user could simply open the workbook and run the "update workbook" function for you. It's non-perly and would require a bit of work to get set up. But then the users could pull updates whenever they wished, and you would need only keep the database up-to-date. In fact, I like this one enough that I may do it for the client I normally do spreadsheet work for.
...roboticus
When your only tool is a hammer, all problems look like your thumb.
In reply to Re^11: How to use win32::OLE with multiple versions of Microsoft Office?
by roboticus
in thread How to use win32::OLE with multiple versions of Microsoft Office?
by frida
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |