How to write 3 Million rows to an Excel File Using Apache POI

0
votes

I have more than 2 Million rows for a search criteria in my db which i have to Export into a xlsx file. I am using apache POI SXSSF to achieve that.

I am getting the data chunk by chunk from DB with 130+ columns and writing it into WorkSheet by creating multiple sheets, each sheet has 400K rows. But i am facing the issue when it reaches 1.1M data, my code is not responding after that also it is not throwing any error as well.

I have 2 confusions based on this issue.

  1. Can we handle more than 1 M data on a single WorkBook.
  2. Can Excel handle more than 1M records on multiple sheets.
1
javaexcelapache-poiexport-to-excelsxssf
The answer to both of your questions is yes. I had *.xlsx workbooks in GB size already (of course not created by apache poi) and while one sheet can only have 2^20 = 1,048,576 rows, multiple sheets having up to 1,048,576 rows each are possible in one workbook. Your problem clearly comes from too low usable RAM memory while executing. But on the other side: More than 2 million data rows in one Excel workbook sounds useless to me. Who shall be able working with those huge amount of data in Excel? – Axel Richter
But Axel SXSSF shouldn't create RAM issue since i am not keeping more data on heap memory. The idea of having that amount of data is for using it as a dump file for another system. – John Thomas
If you have a way to handle huge data effectively, i mean solutions other than SXSSF then please provide. – John Thomas
If SXSSF is used correctly it should not having heap space issues, right. But is it used correctly? And it creates temporary files, so it also could have issues in file system usage. And using Excel files as dump files is a bad idea in my opinion. Do using XML files for that. – Axel Richter

1 Answers

4
votes

It would be disastrous to write 2 million rows in an excel sheet. If you have a requirement to write to some file then choose to write in CSV files as it's much lighter than excel.
And about the max number of rows allowed--~65k in 2003, 1,048,576 in 2007+ you can also use API SpreadsheetVersion.EXCEL97.getMaxRows() and SpreadsheetVersion.EXCEL2007.getMaxRows()