CODEWITHSUNDEEP
javaexcelapache-poiheap-memory
Developer
Developer

Reputation: 1019

Excel POI 3.5 WorkBook Java Heap Space Exception?

I am using the latest POI 3.5 for Excel reading . I have Excel MS office 2007 installed and for that poi is providing XSSF for executing the data.

For 15000 lines of data it is executing properly, but when exceeding the limit till 30000 or 100000 or 200000, it is prone to a Java heap space Exception.

Code is below :

UATinput = new FileInputStream(UATFilePath);

uatBufferedInputStream = new BufferedInputStream(UATinput);

UATworkbook = new XSSFWorkbook(uatBufferedInputStream);

I am getting the Exception in the last line for Java heap size. I have increased the size using -Xms256m -Xmx1536m, but still for more data it is giving the Java heap space Exception.

Can anybody help me out for this Exception for the XSSFWorbook?

Upvotes: 4

Views: 10471

Answers (9)

Kiran
Kiran

Reputation: 1

You can use SXSSF, A low-memory footprint SXSSF API built on top of XSSF. "http://poi.apache.org/spreadsheet/how-to.html#sxssf"

Upvotes: 0

catch32
catch32

Reputation: 18612

The JVM runs with fixed available memory. Once this memory is exceed you will receive "java.lang.OutOfMemoryError". The JVM tries to make an intelligent choice about the available memory at startup (see Java settings for details) but you can overwrite the default with the following settings.

To turn performance you can use certain parameters in the JVM. Xms1024m - Set the minimum available memory for the JVM to 1024 Megabyte Xmx1800m - Set the maximum available memory for the JVM to 1800 Megabyte. The Java application cannot use more heap memory then defined via this parameter.

If you start your Java program from the command line use for example the following setting: java -Xmx1024m YourProgram.

Upvotes: 0

jRam90
jRam90

Reputation: 193

You should really look forward to process the XML data grid behind XLSX technology. You will be liberated from the heap space problems. Here is the tutorial: Check both links below.

http://poi.apache.org/spreadsheet/how-to.html

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/examples/FromHowTo.java

Some basic knowledge of parsing and the use of the SAX-XML project is required.

Upvotes: 0

liya
liya

Reputation: 792

Try this one: -Xms256m -Xmx512m.

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48366

As others have said, your best bet is to switch over the the Event API

One thing that'll make a small difference though is to not wrap your file in an input stream! XSSF will happily accept a File as the input, and that's a lower memory footprint than an InputStream. That's because POI needs random access to the contents, and with an input stream the only way to do that is to buffer the whole contents into memory. With a File, it can just seek around. Using a File rather than an InputStream will save you a little over the size of the file worth of memory.

If you can, you should pass a File. If memory is tight, write your InputStream to a file and use that!

Upvotes: 0

Jai
Jai

Reputation: 11

Its true guys, after using the UserEventModel, my performance was awesome. Please write to me, if you guys have any issues. [email protected]

Upvotes: 1

TheSteve0
TheSteve0

Reputation: 3526

The other thing to watch in your own code is how many objects you are "new"ing. If you are creating a lot of objects as you read through cells, it could exhaust the heap as well. Make sure you are being careful with the number of objects you create.

Upvotes: 0

Mihir Mathuria
Mihir Mathuria

Reputation: 6549

Instead of reading the entire file in memory try using the eventusermodel api

This is a very memory efficient way to read large files. It works on the principle of SAX parser (as opposed to DOM) in the sense that it will call callback methods when particular data structures are encountered. It might get a little tricky as it expects you to know the nitty-gritty of the underlying data

Here you can find a good tutorial on this topic

Hope this helps!

Upvotes: 6

pgras
pgras

Reputation: 12780

If you use XSSFWorkbook, POI has to create a memory model containing your whole Excel file, thus a huge memory consumption. Maybe you could use the Event API which isn't as simple as the user API but allows lower memory consumption.

By the way you could also set a bigger value for -Xmx...

Upvotes: 0

Related Questions