Apologies for the delay in responding... and thank you both for the responses.
You both (sight unseen even) pointed out the quick win and a very novice mistake I made. I was creating a large number of CellStyle objects. I changed this to a collection object which is initially populated with known styles I need and later referenced to get the appropriate style. The application screams through several million records now in roughly 32 seconds and a maximum of 1.3GB of memory used by the JVM in total. One additional question: I know that SXSSF is intended for writing only. What is the accepted/expected means of appending data into an existing workbook? For my purposes I need to overwrite the contents of a certain number of sheets. My current process: 1. Open the existing workbook as an XSSFWorkbook. 2. Delete the sheets I will be overwriting. 3. Write those changes out to disk and close the objects. 4. Re-open the XSSFWorkbook (are these two steps necessary? I am erring on the side of caution making sure the workbook objects are written and refreshed before doing my mass data load). 5. Open an SXSSFWorkbook passing in the XSSFWorkbook object to the constructor. 6. Run through my database to Excel export process. 7. Write, flush and close objects. Is this, in your view, the proper method of modifying and/or appending data into an existing workbook using SXSSF? -----Original Message----- From: Alex Geller [mailto:[email protected]] Sent: Monday, January 23, 2012 12:40 PM To: [email protected] Subject: Re: SXSSF Writing - Memory issues Hi, I make sure that the cell styles that I register are unique and I wouldn't expect to have more than a few hundred at most. But even if the sheet contains thousands of different colors/font combinations that isn't likely to be the reason that you run out of memory. You can however run into trouble if you use merged regions (Sheet.addMergedRegion()) because these are kept in memory and the numbers can be quite large. If for example you have two cells that are merged per row then you will have as many entries as rows. This issue could be fixed by writing the file containing the merged regions in streaming manner the same way we are writing the data file. If this isn't the issue then I suggest to analyze the heap (e.g. use jhat) to see what classes are at the top of the heap histogram. Regards, Alex -- View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-Writing-Memory-issues-tp5161879p5166819.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
