CODEWITHSUNDEEP
javaexcelapache-poi
Renjith
Renjith

Reputation: 1154

outOfmemory exception while writing big excel file using SXSSF

I am using SXSSF to write 1 Million(worst case scenario) records.

Following is the way i have coded it. I have to write the records to an already existing excel template. This template is available in the class path. I will copy this template to a common location. Load this file using XSSFWorkBook. SXSSFWorkbook is initialized with XSSFWorkBook and window size(-1) as parameter.

when ever the record count reaches multiple of 100, i will flush the sheet.

Pseudo Code:
if(count % 100 == 0){
    ((SXSSFSheet)sheet).flush(100); //keeps only 100 rows in memory
}

But while executing this, heap memory is gradually increasing and it ends up in Outofmemory exception. Maximum heap memory size of my machine is about 1.5 GB. Heap memory might be big in actual server. But i am worried as the application has multiple threads they all creates big excel files.

Is it an expected behaviour? ie. is it expected to go beyond 1.5 GB while writing record of size ~ 1 Million? But as given in POI doc, SXSSF is not supposed to go out of memory as it flushes rows to the disk.

Upvotes: 2

Views: 1770

Answers (1)

Renjith
Renjith

Reputation: 1154

This is resolved now :). It has nothing to do with SXSSFWorkbook. One particular column in the report (Represents error in report) needs to be RED in color. So I applied Style to that column. Instead of creating the Style outside the FOR loop and applying it for all cells that comes under the column, i was creating a style each time i write error. Out of 1 million records, if 1/2 a million are having errors my code was generating 1/2 a million CellStyles. That was eating my heap memory.

Following link helped me to resolve it. http://poi.apache.org/faq.html#faq-N100EF

Upvotes: 2

Related Questions