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.

Which app server are your using?

Datheng Wang
– Datheng Wang

2018-01-17 11:08:00 +00:00
Commented Jan 17, 2018 at 11:08 — Datheng Wang
– Datheng Wang, Commented Jan 17, 2018 at 11:08

ktsujister · Accepted Answer · 2012-01-25 10:31:27Z

3

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.

answered Jan 25, 2012 at 10:31

ktsujister

5005 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gagravarr Over a year ago

You're much better off using the newer SXSSF rather than the Big Grid Demo, as the former does much more work for you than the latter

IceArdor Over a year ago

The BigGridDemo is useful if you want to modify an existing spreadsheet. Plain SXSSF is a write-only format (starting with either a blank workbook or an opened XSSFWorkbook object. The SAX event-based model is reading only. To have a class that can edit an existing file without loading the entire workbook into memory, you will need to use lower level APIs to write XML files and repackage the workbook.

Konrad Garus · Accepted Answer · 2010-07-06 11:42:41Z

1

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

answered Jul 6, 2010 at 11:42

Konrad Garus

54.2k44 gold badges163 silver badges234 bronze badges

Comments

Pablo Santa Cruz · Accepted Answer · 2010-07-06 11:43:56Z

1

Assign more memory to the heap when running your program:

$ java -Xms256m -Xmx1024m NameOfYourClass

answered Jul 6, 2010 at 11:43

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Comments

user147373 · Accepted Answer · 2011-01-12 04:17:29Z

1

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).

answered Jan 12, 2011 at 4:17

user147373

Comments

Ashish Malhotra · Accepted Answer · 2017-12-19 05:36:43Z

1

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.

answered Dec 19, 2017 at 5:36

Ashish Malhotra

2711 gold badge2 silver badges15 bronze badges

Collectives™ on Stack Overflow

JAVA - Apache POI OutOfMemoryError while writing Excel File

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related