23

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?

Danilo Piazzalunga
  • 7,590
  • 5
  • 49
  • 75
  • 1
    It's not uncommon to run out of memory way before you think you should. Just because the file is 9 MB on disk doesn't mean it won't take up a lot more in memory. XML files are especially bad, I've seen JAX use up gigabytes during (un)marshalling. – Kajetan Abt May 20 '11 at 09:33
  • http://stackoverflow.com/questions/88235/dealing-with-java-lang-outofmemoryerror-permgen-space-error – Diogo Moreira Jun 21 '13 at 11:15

6 Answers6

27

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

Randomness
  • 610
  • 6
  • 13
  • 2
    Don't use a string, use a File object directly - see [the POI quickstart guide](http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream) for details of how to do it – Gagravarr Jan 13 '13 at 22:43
  • It will use less memory to use a File over an InputStream, but that memory isn't the PermGen Space, is it? – Eric Apr 24 '18 at 04:43
  • It takes forever to process a large File than to process an inputstream for some reason. This leads to request timeouts. So, we're just trading outofmemory error with timeout error. – saran3h Mar 22 '21 at 14:20
10

I think default heap size is 128M

You can increase this using the -Xmx argument to java

e.g.

java -Xmx512m myapp.java
Joel
  • 29,538
  • 35
  • 110
  • 138
  • And if you run out of stack due to recursion, -Xms will help. – Kajetan Abt May 20 '11 at 09:24
  • What if i have strict JVM restrictions by the IT-Administrator? – kiltek Jun 16 '16 at 08:45
  • My first thought was, @kiltek, to give you the advice to find another job, but perhaps that's not an option ;-) The admin is most commonly a techy so it should be able to change his mind with technical arguments that you do need the memory for your specific purpose. If the above doesn't work out for you, you'll have to search another solution. That's our job as devs, isn't it? :-) – Wivani Jul 01 '16 at 12:08
3

I think a 32 bit JVM has a maximum of 2GB memory.This might be out of date though. If I understood correctly, you set the -Xmx on Eclipse launcher. If you want to increase the memory for the program you run from Eclipse, you should define -Xmx in the "Run->Run configurations..."(select your class and open the Arguments tab put it in the VM arguments area) menu, and NOT on Eclipse startup

Edit: details you asked for. in Eclipse 3.4

1) Run->Run Configurations...

2) if your class is not listed in the list on the left in the "Java Application" subtree, click on "New Launch configuration" in the upper left corner

2b) on the right, "Main" tab make sure the project and the class are the right ones

3)select the "Arguments" tab on the right. this one has two text areas. one is for the program arguments that get in to the args[] array supplied to your main method. the other one is for the VM arguments. put into the one with the VM arguments(lower one iirc) the following: -Xmx2048m

I think that 1024m should more than enough for what you need though!

4)Click Apply, then Click Run

In case it still runs out of heap space you can still change the number to a higher value, for instance you can put -Xmx4g which adds up from 2 Gigabyte to 4 Gigabytes

Ramin
  • 891
  • 2
  • 10
  • 16
3

As suggested by others the first thing to do is to increase the heap size.

If that does not help or you expect larger files you should also take a look at this answer: Processing large xlsx file in Java

It gives a few good starting points on what can be done if the standard "read everything" mode is not working for you.

Community
  • 1
  • 1
Turismo
  • 2,064
  • 2
  • 16
  • 23
1

When writing big files:

What you need is SXSSF (Streaming Usermodel API).

SXSSF (package: org.apache.poi.xssf.streaming) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited.

There is an example on that page too: you need to substitute XSSFWorkbook with SXSSFWorkbook.

Copied this answer from vadchen, from another question. It worked for me. I just needed to replace XSSFWorkbook for SXSSFWorkbook in the code, as he said.

viniciussss
  • 4,404
  • 2
  • 25
  • 42
1

Increase the heap size as such:

-Xms256m -Xmx512m -XX:PermSize=64M -XX:MaxPermSize=1000M

These values should be provided to the VM.

Take a look at the link to, this should help you out

http://www.javabeat.net/tips/192-javalangoutofmemoryerror-java-heap-space.html

harishtps
  • 1,439
  • 7
  • 20
  • 35