Write huge rows in Excel file (.xls format older version) in chunks without loading complete file in memory

Question

I'm working on a feature to export data into Excel file (.xls (Older version) format support).

I have to append chunks into .xls file, without loading file into memory. The file should be in .xls format. chunks size can very in each steps and there are millions of rows . divided in chunks. If I load file in memory, there can be a situation of memory-overflow. Can someone can suggest any free or paid api or solution in java which can help.

Answer 1

Update - User wants to append, my suggestion is not able to read workbooks. Leaving it here for any reference.

Use a SXSSFWorkbook which is part of the Streaming API of POI. It makes it possible to keep only a specific limit of rows in memory while writing. It is a lot faster than using a normal Workbook.

This is for the newer format though. You should know that using the old .xls format, you are more limited on row and column count. Check this for more.

SXSSFWorkbook workbook = new SXSSFWorkbook(10); // 10 is the row access window. It keeps only the last 10 rows in memory, then they are removed and the next ten are loaded

For more take a look at this, in the SXSSF (Streaming Usermodel API) part.

P.S: Do not forget to workbook.dispose().