We have a dynamic web project and on a certain request large reports are generated (in .xls format). The data for the reports are fetched from the Data Base and are written to .xls files using poi.But if the number of records exceeds 100000 rows with around 24 columns, the server crashes.
Want to know how to generate large xls files using poi.
Server : WebSphere Application Server v 7.0.0.29, JVM - 512MB - 2048 MB, POI - Implementation-Version: 3.9
Please let me know if any other details are required from my side.
There are several issues with this approach:
.xls format is limited in its row count (65536 rows). I would suggest moving to .xlsx, which supports up to 1000000.
With such large row sets, server memory consumption grows very large if you use the default (Worksheet, Row, ...) model. Without further info, this is a shot in the dark, but it is a likely issue.
Use the streaming model for generating such huge reports. Take a look at http://poi.apache.org/spreadsheet/how-to.html#sxssf, that should get you started.