Wednesday, August 15, 2018

Read/Write large excel (xlsx) file

In this article you will see how to write a huge excel file.

Using SXSSFWorkbook you can write large excel file. It is a Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy. This allows to write very large files without running out of memory as only a configurable portion of the rows are kept in memory at any one time.

Note - I have tested it to write 10,48,576 rows which created 40 MB excel file. If you want to write more rows than this then you may opt to write CSV file.

You need to add below dependencies in your pom.xml

Method to write a large excel(xlsx) file
Reading a large excel file is little tricky, you can use OPCPackage. It treats excel file as a xml file and hence can be streamed.

Refrence -

Codes to read a large excel file
You can add this class as it is in your project