• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

Having memory problems creating excel file using POI API.

Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?

Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.


Posts: 1337
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
ice is for people that are not already cool. Chill with this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!