4

I’m looking for a – simple – solution to output big excel files. Input data comes from the database and I’d like to output the Excel file directly to disk to keep memory consumption as low as possible. I had a look to things like Apache POI or jxls but found no way to solve my problem.

And as additional information I need to generate .xls files for pre 2007 Excel, not the new .xlsx xml format. I also know I could generate CSV files but I’d prefer to generate plain Excel…

Any ideas ?

I realize my question isn't so clear, I really want to be able to write the excel file without having to keep the whole in memory...

Improve this question
2
  • stackoverflow.com/questions/1842244/… Commented Jan 15, 2010 at 12:00
  • 1
    I saw this question, even if the title is quite similar I'm only interested in writing excel files, no need to read them. And I'm specifically looking for a way not to hold the whole excel file in memory... Commented Jan 15, 2010 at 12:12

3 Answers 3

Reset to default
6

The only way to do this efficiently is to use character-based CSV or XML (XLSX) format, because they can be written to the output line by line so that you can per saldo have only one line at once in the memory all the time. The binary-based XLS format must first be populated completely in memory before it can be written to the output and this is of course memory hogging in case of large amount of records.

I would recommend using CSV for this as it may be more efficient than XML, plus you have the advantage that the any decent database server has export capabilities for that, so that you don't need to program/include anything new in Java. I don't know which DB you're using, but if it were for example MySQL, then you could have used LOAD DATA INFILE for this.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your answer, I think I will go for CSV till my company migrates to a newer Office version and then continue with xlsx. You know about a library to generate xlsx line by line ?
2

No idea for generating a real XSL file. But you can directly write a HTML file, or a zip stream containing an OPenDocument spreadsheet (I guess MSExcel can read this later format)

Improve this answer

1 Comment

HTML is an extremely bad idea. Don't do that. Excel will messup it on edit/save and the latest versions will complain about unsupported file format. Use XLS/XLSX/CSV only.
2

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.