Create large XLS file with low memory consumption

[Sébastien Lorber <lo...@gmail.com>]

Hello,

I've been using POI for some time now at work and we already had many
issues because POI was not able to stream.
For large Excel exports (> 100k rows) we usually get OutOfMemory issues.
As there was no solution to this problem we went back to plain old CSV file
which at least can be streamed.


We noticed the apparition on POI 3.8 of the SXSSF workbook which permits to
stream.
First i'd like to know about that:
Is it possible to make it stream directly to the servlet stream instead of
streaming to a file?
So that the user doesn't need to wait the end of the file generation before
receiving data.



The problem we have at work is that we work for a large car industry which
still uses XLS files on many countries, not XLSX.

Is it possible to stream as well the creation of XLS files?
-> It seems not right?

Is it possible to easily convert an XLS to an XLSX file?
-> It seems possible but should be handled manually (i think) and not sure
it wouldn't consume a lot of memory.



For now our hardware is something like:
- Server1: backoffices
- Server2: preproduction
- Server3 - 9: frontoffice
Each server is quite powerful and running from 20 to 30 VM of 0.5Go to
1Go (one per country)

For now the only solution i see, except switching to CSV, is to have a
separate webapp just for Excel file creation.
But it needs some developpments, and some overhead because of multitenancy,
because each separate country can be considered as a tenant with a separate
database, and we only want one excel webapp per tenant.


Do you have any other idea?
Is POI going to solve this kind of problem in the future?



Thanks!

Re: Create large XLS file with low memory consumption

[Nick Burch <ni...@alfresco.com>]

On Thu, 26 Apr 2012, Sébastien Lorber wrote:
> We noticed the apparition on POI 3.8 of the SXSSF workbook which permits to
> stream.
> First i'd like to know about that:
> Is it possible to make it stream directly to the servlet stream instead of
> streaming to a file?

No. We need to temporarily store bits of the file (PackageParts 
effectively) as we generate the workbook, then assemble it all together 
before the file is ready. Only at that point can you send it to the user


> The problem we have at work is that we work for a large car industry which
> still uses XLS files on many countries, not XLSX.
>
> Is it possible to stream as well the creation of XLS files?

No, the file formats are different. With XLSX you can generate some parts 
independently of others, so we can arrange to keep certain key bits in 
memory and stream the rest. With XLS, there are too many forward and 
backward references between the bits of the file for this to work

Nick