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?
-
1It'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– Kajetan Abt2011-05-20 09:33:07 +00:00Commented May 20, 2011 at 9:33
-
stackoverflow.com/questions/88235/…Diogo Moreira– Diogo Moreira2013-06-21 11:15:31 +00:00Commented Jun 21, 2013 at 11:15
Add a comment
|
6 Answers
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
3 Comments
Gagravarr
Don't use a string, use a File object directly - see the POI quickstart guide for details of how to do it
Eric
It will use less memory to use a File over an InputStream, but that memory isn't the PermGen Space, is it?
saran3h
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.
I think default heap size is 128M
You can increase this using the -Xmx argument to java
e.g.
java -Xmx512m myapp.java
3 Comments
Kajetan Abt
And if you run out of stack due to recursion, -Xms will help.
kiltek
What if i have strict JVM restrictions by the IT-Administrator?
Wivani
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? :-)
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.
Comments
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
Comments
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
Comments
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.
