JAVA - Apache POI OutOfMemoryError while writing Excel File

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 File开发者_运维问答OutputStream(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.

---

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.

---

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

---

Assign more memory to the heap when running your program:

$ java -Xms256m -Xmx1024m NameOfYourClass

---

I've been there more than once.

Are you running this running on top of an application server?

What I've done in the past as was mentioned by Pablo, is to increase the heap space, but make sure that it is being increased for the application server that you are running on.

I have also had to really optimize the code when doing this.

Since you are outputting to a .xlsx file, XML takes quite a bit of memory. Not sure if it would work for you in this situation or not, but if you can create a normal .xls do that and than convert it at the end into a .xlsx file (using Apache POI of course).

---

Use SXSSFWorkbook instead of XSSFWorkbook, this is used for streaming User model Api

Source: https://coderanch.com/t/612234/Write-Huge-Excel-file-Xlsx

Hopefully this will help you.