File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Other Open Source Projects and the fly likes Having memory problems creating excel file using POI API. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Having memory problems creating excel file using POI API." Watch "Having memory problems creating excel file using POI API." New topic

Having memory problems creating excel file using POI API.

Rohit Chaudhary

Joined: May 11, 2010
Posts: 7

We have a job in our application which reads data from DB and creates excel sheet out of it using POI.
The problem we are facing is sometimes data is too big (may be >20,000,00) which results in out of memory.
Job logic writes all data into excel(creates multiple sheets since most time data is >65,000 (max rows in excel)) in memory and is written to disk only after it completes writing all data.
Can you guys provide suggestions how to handle this issue?

One solution i can think of is to write 65k records to excel in memory and then flush to disk. Then load the same file and continue with writing next sheet (Not sure whether API supports this way?).
create multiple excel files each having 65k records and merge the files into one single file. Can you guys suggest how we can do this?

Anthony Andras
Ranch Hand

Joined: Nov 09, 2008
Posts: 31

I am not 100% familiar with the Apache POI API, however is it possible to read only a limited number of records at a time, then resume reading? Separating a single file into batches of records should help improve your overall memory consumption.

In other words, you can first read the first 5000 records and perform whatever processing you require. Then read the next 5000 records, etc . . .

Hope this provides some insight.


Lester Burnham

Joined: Oct 14, 2008
Posts: 1337
POI definitely uses quite a bit of memory. That's probably mostly due to the complicated file format (OLE), so I'm not sure if other libraries (like jExcelApi) would do better; but it's worth a try.
It is sorta covered in the JavaRanch Style Guide.
subject: Having memory problems creating excel file using POI API.
Similar Threads
converting access tableto xl sheet using java
Files processing on server
want to get more then 256 columns in XL using JXL API
generate large excel using zip stream
Performance of POI-HSSF