java.lang.OutOfMemoryError: Java heap space while reading excel with Apache POI

java apache-poi
user467871 picture user467871 · May 20, 2011 · Viewed 45.3k times · Source

My file is 9MB and I got this error while loading workbook.

XSSFWorkbook workbook = new XSSFWorkbook(excelFilePath);

this line causes to java.lang.OutOfMemoryError: Java heap space

How can I solve this?

Answer

Randomness picture Randomness · May 20, 2011

First thing to check - are you opening the XSSFWorkbook with an InputStream or a File? Opening it with a File is much lower memory.

Next up, do you want to read or write? If memory is very tight for you, there are alternate options for using XSSF in a sax like way.

For reading, see http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api for details of using XSSF + Sax. As another option, if you just want to get the textual content of the file, then it looks like Apache Tika has an event based text extractor for .xlsx files using POI.

For writing, there has been lots of discussions on the POI dev list recently about improving the "Big Grid Demo". You might want to follow those - http://poi.apache.org/mailinglists.html

Related questions

How can I read numeric strings in Excel cells as string (not numbers)?

I have excel file with such contents: A1: SomeString A2: 2 All fields are set to String format. When I read the file in java using POI, it tells that A2 is in numeric cell format. The problem is that the …

Read More
Apache POI Excel - how to configure columns to be expanded?

I am using Apache POI API to generate excel spreadsheet to output some data. The problem I am facing is when the spreadsheet is created and opened, columns are not expanded so that some long text like Date formatted text …

Read More
How do I set cell value to Date and apply default Excel date format?

I've been using Apache POI for some time to read existing Excel 2003 files programmatically. Now I have a new requirement to create entire .xls files in-memory (still using Apache POI) and then write them to a file at the end. …

Read More