• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Having memory problems creating excel file using POI API.

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Grow a forest with seedballs and this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic