CODEWITHSUNDEEP
performanceapachememoryapache-poi
user2509885
user2509885

Reputation: 45

poi read existing excel and edit with large data

I using apache poi to read and write excel .I am able to do that without any problem.But i am facing memory issues when writing large excels on existing excel.To Explained in more detail below I have template where column headers are colored some are merged (i am talking about 100 to 150 columns in one sheet) this i have around 10 sheets approx. Now i read data from database and populate the data into this excel. Now for small sets of data i am able to do but for large data sets it throws out of memory. Although i get the data from db in small chunks like 1000 rows of data at once proccess that 1000 and again get another set. Maxiumn rows of data will be 50,000 rows per sheet and minumun rows will be one row in each sheet

I have read about SXSSF but the issue is it does not edit on existing excels.

Please let me know your thoughts on how this can be solved.

Upvotes: 1

Views: 2126

Answers (1)

Gagravarr
Gagravarr

Reputation: 48326

There are a few options available, but none that are a "silver bullet" simple answer. Unfortunately, the Excel file formats do require a fair bit of memory to work with.

  1. Increase the heap size given to Java, so it can hold more in memory at a time
  2. Buy some more memory for your server, then increase the heap size. Memory is pretty cheap these days, and it doesn't take very many hours wage of a decent programmer to cover the cost
  3. Use a different file format - CSV for example is very lightweight on the memory footprint
  4. Use the low level SAX API to read in the .xlsx file, then use SXSSF to write it out again without much buffering. Needs quite a lot of custom coding though, much more work for you than just using HSSF/XSSF
  5. Contribute / sponsor the contribution of memory improvements to Apache POI. POI is open source, freely available, and maintained entirely by volunteers, so if something matters to you you're often best off helping!

Upvotes: 1

Related Questions