CODEWITHSUNDEEP
javaexcelapache-poiexport-to-excelsxssf
John
John

Reputation: 708

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

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.

Upvotes: 0

Views: 5272

Answers (1)

Uzair
Uzair

Reputation: 469

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()

Upvotes: 4

Related Questions