Heap error while writing xls file with Apache POI

问题:

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)

回答1:

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();
  }
}

回答2:

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.