I have a web application that supports download of large result sets (400K+ rows) in Excel format. Limitations that I have with Apache POI is that I have to generate entire excel file before I can stream, this is putting lot of stress on application servers.
Is there a way I can stream the excel file partially few rows at a time? This is forward only operation with single worksheet. I will not modify the cells that are already created
I can do this in CSV but formatting is a "must have" requirement here.
You may want to take a look at the Buffered-streaming SXSSF Howto, which outlines how you can write a XLSX stream by using mostly the same API as XSSF.
This works by creating a "window" into the worksheet, which moves with the cells that are currently written into. I can attest it works even when creating very large streams.
Note that IIRC this uses temporary storage for the unwritten stream and will only write once the workbook is complete.