Excel POI 3.5 WorkBook Java Heap Space Exception ?

Question

Hi,

I am using 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 exceeding the limit till 30000 or 100000 or 200000 , it is prone to java heap space exception.

code is below :

enter code here

UATinput = new FileInputStream(UATFilePath);

uatBufferedInputStream = new BufferedInputStream(UATinput);

UATworkbook = new XSSFWorkbook(uatBufferedInputStream);

I am getting 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 java heap space. I have tried so many ways to get out of this exception , but still same exception.

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

Thanks, Mishal Shah

Answer 1

try -Xms256m -Xmx512m

Answer 2

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...

Answer 3

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!

Answer 4

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.

Answer 5

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