[ACCEPTED]-API to write huge excel files using java-apache-poi

Try to use SXSSF workbook, thats great thing 2 for huge xls documents, its build document 1 and don't eat RAM at all, becase using nio

All existing Java APIs try to build the 7 whole document in RAM at once. Try to write 6 an XML file which conforms to the new xslx 5 file format instead. To get you started, I 4 suggest to build a small file in the desired 3 form in Excel and save it. Then open it 2 and examine the structure and replace the 1 parts you want.

Wikipedia has a good article about the overall format.

I had to split my files into several excel 8 files in order to overcome the heap space 7 exception. I figured that around 5k rows 6 with 22 columns was about it, so I just 5 made my logic so that every 5k row I would 4 end the file, start a new one and just numerate 3 the files accordingly.

In the cases where 2 I had 20k + rows to be written I would have 1 4+ different files representing the data.

Have a look at the HSSF serializer from the cocoon project.

The 3 HSSF serializer catches SAX events and creates 2 a spreadsheet in the XLS format used by 1 Microsoft Excel

There also is JExcelApi, but its uses more 4 memory. i think you should create .csv file 3 and open it in excel. it allows you to pass 2 a lot of data, but you wont be able to do 1 any "excel magic".

Consider using CSV format. This way you 17 aren't limited by memory anymore --well, maybe 16 only during prepopulating the data for CSV, but 15 this can be done efficiently as well, for 14 example querying subsets of rows from DB 13 using for example LIMIT/OFFSET and immediately write 12 it to file instead of hauling the entire 11 DB table contents into Java's memory before 10 writing any line. The Excel limitation of 9 the amount rows in one "sheet" will 8 increase to about one million.

That said, if 7 the data is actually coming from a DB, then 6 I would highly reconsider if Java is the 5 right tool for this. Most decent DB's have 4 an export-to-CSV function which can do this 3 task undoubtely much more efficient. In 2 case of for example MySQL, you can use the 1 LOAD DATA INFILE command for this.

We developed a java library for this purpose 8 and currently it is available as open source 7 project https://github.com/jbaliuka/x4j-analytic . We use it for operational reporting. We 6 generate huge Excel files, ~200,000 should 5 work without problems, Excel manages to 4 open such files too. Our code uses POI to 3 load template but generated content is streamed 2 directly to file without XML or Object model 1 layer in memory.

Is this memory issue happen when you insert 24 data into cell, or when you perform data 23 computation/generation?

If you are going 22 to load files into an excel that consist 21 of predefined static template format, then 20 better to save a template and reuse multiple 19 time. Normally template cases happen when 18 you are going to generate daily sales report 17 or etc...

Else, every time you need to create 16 new row, border, column etc from scratch.

So 15 far, Apache POI is the only choice I found.

"Clearly, writing 14 ~20k rows(with some 10-20 columns in each 13 row) gives me the dreaded "java.lang.OutOfMemoryError: Java 12 heap space"."

"Enterprise IT"

What YOU CAN 11 DO is- perform batch data insertion. Create 10 a queuetask table, everytime after generate 9 1 page, rest for seconds, then continue 8 second portion. If you are worry about the 7 dynamic data changes during your queue task, you 6 can first get the primary key into the excel 5 (by hiding and lock the column from user 4 view). First run will be insert primary 3 key, then second queue run onwards will 2 read out from notepad and do the task portion 1 by portion.

We did something quite similar, same amount 4 of data, and we had to switch to JExcelapi 3 because POI is so heavy on resources. Try 2 JexcelApi, you won't regret it when you 1 have to manipulate big Excel-files!