Heap error while writing xls file with Apache POI

Question

I am using Apache POI to create an excel[.xls] file. Now an excel can have 65535 rows & 256 cols. I am trying to write the java code to write the xls file with 65535x256 cells. I am getting a heap error. The current heap conf is -Xms512m -Xmx1700m. RAM size is 3.5gb.

What is the way out for me ? I am using HSSF* classes to write xls file.

Stack Trace is (I have enabled the -verbose:gc option)

[Full GC [Tenured: 1092288K->1092288K(1092288K), 3.3174494 secs] 1583807K->1583807K(1583808K), [Perm : 3351K->3351K(12288K)], 3.3174977 secs] [Times: user=3.30 sys=0.03, real=3.33 secs] 
[Full GC [Tenured: 1092288K->1092288K(1092288K), 3.3073908 secs] 1583807K->1583807K(1583808K), [Perm : 3351K->3351K(12288K)], 3.3074374 secs] [Times: user=3.30 sys=0.01, real=3.31 secs] 
[Full GC [Tenured: 1092288K->9438K(1092288K), 0.3264828 secs] 1583807K->9438K(1583808K), [Perm : 3351K->3351K(12288K)], 0.3265362 secs] [Times: user=0.31 sys=0.00, real=0.31 secs] 
Exception in thread "main" Heap
 def new generation   total 491520K, used 44939K [0x02990000, 0x23ee0000, 0x23ee0000)
  eden space 436928K,  10% used [0x02990000, 0x05572fa8, 0x1d440000)
  from space 54592K,   0% used [0x1d440000, 0x1d440000, 0x20990000)
  to   space 54592K,   0% used [0x20990000, 0x20990000, 0x23ee0000)
 tenured generation   total 1092288K, used 9438K [0x23ee0000, 0x66990000, 0x66990000)
   the space 1092288K,   0% used [0x23ee0000, 0x24817810, 0x24817a00, 0x66990000)
 compacting perm gen  total 12288K, used 3357K [0x66990000, 0x67590000, 0x6a990000)
   the space 12288K,  27% used [0x66990000, 0x66cd7778, 0x66cd7800, 0x67590000)
No shared spaces configured.
java.lang.OutOfMemoryError: Java heap space
    at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:147)
    at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:125)
    at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:103)
    at com.test.ExcelWriter.createWorkbook(ExcelWriter.java:119)
    at com.test.TestMe2.main(TestMe2.java:38)

Can you post the stack trace? Is it an error for permgen memory? — Pushkar, Aug 22 '11 at 20:09
You are creating a huge excel file. You might consider using csv format instead of using HSSF* classes... it would probably be less memory expensive — VirtualTroll, Aug 22 '11 at 20:21
So does this mean that using apache POI we can write data ONLY of specific rows & columns? or size ? If yes, whats that 'breakpoint'? I need to provide the info to the customer, hence want to know ... — Ashish K Agarwal, Aug 22 '11 at 21:13
POI is a really robust API. I have used POI to create xls with huge number of records and never faced any issue — Pushkar, Aug 23 '11 at 19:58

score 2 · Answer 1 · 2011-08-22T22:43:49.193

try setting both Xms and Xmx to the same value i.e.

-Xms1700m -Xmx1700m

Does this work for you?

public static void main(String[] args) {
  try {
    FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
    HSSFWorkbook workbook = new HSSFWorkbook();
    CreationHelper createHelper = workbook.getCreationHelper();
    HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

    for(int i=0; i<20000; i++) {
      Row row = worksheet.createRow(i);             
      row.createCell(0).setCellValue(createHelper.createRichTextString("row " + i));                
    }               

    fileOut.flush();
    workbook.write(fileOut);
    fileOut.close();            
  } catch (Exception e) {
    e.printStackTrace();
  }
}

same exception? can you post the code? it should be possible to do without holding the whole document in memory, also Amine's point is spot on here, you could write the file in CSV format and open it in excel — , Aug 22 '11 at 21:50

score 1 · Answer 2 · answered Aug 24 '11 at 14:00

1

I continued to get the problems with xls file. Eventually I had to convince the customer for a csv format. Now I am able to export more than 20000 records. Thanks @Amine, @Apache Fan, @eon for your valuable suggestions.

answered Aug 24 '11 at 14:00

Ashish K Agarwal

1,172
3
17
33

Heap error while writing xls file with Apache POI

2 Answers2

Linked