JAVA - Apache POI OutOfMemoryError while writing Excel File

Question

I am writing an Excel File using Apache POI.

I want to write in it all the data of myResultSet

which has the fieldnames(columns) stored in the String[] fieldnames.

I have 70000 rows and 27 columns

My Code:

String xlsFilename = "myXLSX.xlsx";
org.apache.poi.ss.usermodel.Workbook myWorkbook = new XSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet mySheet = myWorkbook.createSheet("myXLSX");
Row currentRow = mySheet.createRow(0);
for (int k = 0; k < fieldNames.length; k++) {
    // Add Cells Of Title Of ResultsTable In Excel File
    currentRow.createCell(k).setCellValue(fieldNames[k]);
}

for (int j = 0; j < countOfResultSetRows; j++) {
    myResultSet.next();
    currentRow = mySheet.createRow(j + 1);
    for (int k = 0; k < fieldNames.length; k++) {
        currentRow.createCell(k).setCellValue(myResultSet.getString(fieldNames[k]));
        System.out.println("Processing Row " + j);
    }
}

FileOutputStream myFileOutputStream = new FileOutputStream(xlsFilename);
myWorkbook.write(myFileOutputStream);
myFileOutputStream.close();

My problem is that while writing the rows the program is getting slower and slower.

When it reaches row 3500 it stops with the Exception:

Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space at java.lang.AbstractStringBuilder.(AbstractStringBuilder.java:45) at java.lang.StringBuffer.(StringBuffer.java:79)

It seems I'm out of memory.

How can I solve this.

Is there a way to store my data to a temporary file every 1000 of them (for example)?

What would you suggest?

I had the same problem using jxl and never solve it either (JAVA - Out Of Memory Error while writing Excel Cells in jxl)

Now I need xlsx files anyway, so I have to use POI.

Answer 1

There seems to be an approach which creates data file in XML format first and then replacing that XML with existing template xlsx file.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

this is not applicable for xls format files though.