Re^10: How to use win32::OLE with multiple versions of Microsoft Office?

I did looked them. But one requires to parse them before editing. And for large files in this case, memory goes out of bound.

Also to add, I used cell handler to minimize the usage but for for excel files which contains tables, cell handler skips the last row.

So, I'm sort of stuck here.

Comment on Re^10: How to use win32::OLE with multiple versions of Microsoft Office?

Replies are listed 'Best First'.
Re^11: How to use win32::OLE with multiple versions of Microsoft Office? by roboticus (Chancellor) on Jul 23, 2014 at 11:33 UTC
perl_new_b: 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.	[reply]

Replies are listed 'Best First'.

Re^11: How to use win32::OLE with multiple versions of Microsoft Office?
by roboticus (Chancellor) on Jul 23, 2014 at 11:33 UTC

perl_new_b:

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.

[reply]