1

I need to write a large excel file in java. I need four different types of data in same excel, as I need to hand over that excel to the customer, making 4 different excels don't suit me. So I went with preparing 4 different sheets each for one type of data. Now data I am fetching from the data base is much larger, and can easily exceed 1048576 rows limit per sheet. So I have handled that overflow part too. Now I was testing this file creation for maximum threshold i.e preparing 4 sheets all having 1048576 rows, and I got java heap exception, I increased memory from 256mb to 2gb and I got the new error that is "GC overhead limit exceeded". I further increased memory to 10GB but got the same error. I agree that I am creating many objects, i.e row.createCell() each time to create a cell of excel. Below is the output I got when I checked memory configurations by free -g :

Memort type total used free shared buffers cached Mem: 31 22 8 0 0 17 -/+ buffers/cache: 4 27 Swap: 0 0 0

Completely clueless what to do next. CSV file I guess is not an option for me. Any lead will be heartily appreciated.

tv1902
  • 181
  • 1
  • 3
  • 14
  • might help to collect some garbage on the run (manually) – XtremeBaumer Sep 07 '17 at 10:25
  • share some code as well. – Mohit Tyagi Sep 07 '17 at 10:26
  • what about splitting data into many sheets and merging them later using excel – Akhil S Kamath Sep 07 '17 at 10:27
  • i think invoking java gc manually will not help much. https://stackoverflow.com/questions/1481178/how-to-force-garbage-collection-in-java answers for above question link many explained other method tweaking jvm , writing custom jvm for better gc – Akhil S Kamath Sep 07 '17 at 10:31
  • 2
    Assuming that you are using Apache POI, you should look out for the [“streaming API”](https://poi.apache.org/apidocs/?org/apache/poi/xssf/streaming/SXSSFWorkbook.html), as otherwise, the insane memory consumption when processing xlsx is a well-known issue. – Holger Sep 07 '17 at 11:02
  • 1
    I found the solution. You might want to know it. Actually, in every sheet writer class, I was opening the excel, writing it and closing it again. So I was able to write the first sheet, no matter how much long. But as I was closing the file after it, next sheet writer was not able to load that large file. Now the code is opening file once and closing only when all sheets are ready. Also, I am using SXSSFWorkbook and it is also memory efficient, So thankfully my problem is solved :) – tv1902 Sep 09 '17 at 15:24

0 Answers0