Excel POI 3.5 WorkBook Java Heap Space Exception?

Question

I am using the latest version of POI 3.5 to read Excel. I have Excel MS Office 2007 installed, and for this poi provides XSSF for data execution.

For 15,000 rows of data, it runs correctly, but if the limit is exceeded to 30,000 or 100,000 or 200,000, it is subject to a Java heap space Exception.

Code below:

UATinput = new FileInputStream(UATFilePath); uatBufferedInputStream = new BufferedInputStream(UATinput); UATworkbook = new XSSFWorkbook(uatBufferedInputStream); 

I get an exception in the last line for Java heap size. I increased the size with -Xms256m -Xmx1536m , but still it provides a Java heap space Exception to get additional data.

Can someone help me on this exception for XSSFWorbook?

Answer 1

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

This is a very efficient way to read large files. It works on the principle of the SAX analyzer (as opposed to the DOM) in the sense that it will call callback methods when specific data structures are encountered. This may seem a bit complicated, as it expects you to learn about master data.

Here you can find a good tutorial on this topic.

Hope this helps!

Answer 2

His real guys, after using UserEventModel, my work was amazing. Please email me if you have any problems. [e-mail]

Answer 3

Try the following: -Xms256m -Xmx512m .

Answer 4

If you are using an XSSFWorkbook, the POI should create a memory model containing your entire Excel file, and therefore a huge amount of memory. Perhaps you can use the Event API , which is not as simple as the user API, but can reduce memory consumption.

By the way, you can also set a larger value for -Xmx ...

Answer 5

Another thing you can see in your own code is how many objects you are "new." If you create a lot of objects while reading cells, this can also bring out a bunch. Make sure you are careful about the number of objects to create.

Answer 6

As others have said, it's best to switch the Event API

One thing that makes a little difference is not to wrap your file in the input stream! XSSF will gladly accept the file as input and reduce the memory footprint than InputStream. This is because the POI requires random access to the content, and with the input stream, the only way to do this is to combine all the contents into memory. With a file, it can just search. Using a file rather than an InputStream will save you a bit of memory file size.

If you can, you must transfer the file. If the memory is tight, write an InputStream to a file and use it!

Answer 7

You should really expect to process the XML data grid behind the XLSX technology. You will be freed from problems with a lot of space. Here is a tutorial: Check out 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 using the SAX-XML project is required.

Answer 8

JVM works with fixed available memory. As soon as this memory exceeds you, you will get "java.lang.OutOfMemoryError". The JVM tries to make a reasonable choice of available memory at startup (see Java settings for details), but you can overwrite the default value with the following settings.

To improve performance, you can use certain parameters in the JVM. Xms1024m - Set the minimum available memory for the JVM to 1024 megabytes Xmx1800m - Set the maximum available memory for the JVM to 1800 megabytes. A Java application cannot use more heap memory and is then defined using this parameter.

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

Answer 9

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