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 (http://stackoverflow.com/questions/3167029/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

How about allowing your app to use more memory (like -Xmx500m for 500 MB)?

Answer 2

Assign more memory to the heap when running your program:

$ java -Xms256m -Xmx1024m NameOfYourClass