Writing Excel data directly to OutputStream (memory usage)

Question

Im looking for is a simple solution to output large excel files. Input data comes from the database, and Id - to output the Excel file directly to disk, to minimize memory consumption. I looked at things like Apache POI or jxls, but could not find a way to solve my problem.

And as additional information I need to generate .xls files for pre 2007 Excel, and not the new .xlsx xml format. I also know that I can generate CSV files, but Id prefers to generate simple Excel ...

Any ideas?

I understand that my question is not so clear, I really want to write an excel file without storing everything in my memory ...

Answer 1

The only way to do this efficiently is to use character-based CSV or XML (XLSX) format, because they can be written to output line by line so that you can only store one line at a time in memory all the time. First, the binary XLS format must be completely filled in memory before it can be written to the output, and this, of course, is a memory delay in the case of a large number of records.

I would recommend using CSV for this, as it can be more efficient than XML, plus you have the advantage that there is an export option on any decent database server, so you don’t need / enable anything new in java. I do not know which database you are using, but if it were, for example, MySQL, then you could use LOAD DATA INFILE for this.

Answer 2

No idea to create a real XSL file. But you can directly write an HTML file or a zip stream containing an OPenDocument table (I think MSExcel can read this later format)

Answer 3

JExcelAPI is often recommended as a more memory-efficient Apache POI alternative.