Two Laptop Bag*
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
Author

Having memory problems creating excel file using POI API.

Rohit Chaudhary
Greenhorn

Joined: May 11, 2010
Posts: 7
Hello,

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?).
OR
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?

Thanks,
Rohit
Anthony Andras
Ranch Hand

Joined: Nov 09, 2008
Posts: 31
Rohit,

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.

Regards,

Anthony
Lester Burnham
Rancher

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.
 
GeeCON Prague 2014
 
subject: Having memory problems creating excel file using POI API.