Excel export solution for large data volumes

Test common conditions:

The total number of data entries is 110011, and the number of each data entry is 19 fields.
Computer Configuration: P4 2.67 GHz, 1 GB memory.

I. Comparison of poi, jxl, and fastexcel
Poi, jxl, and fastexcel are all open-source projects of Java third-party open-source export excel.

Export solution 1: export all data to an Excel file at a time.
In actual cases, the outofmemery error is reported. The following data shows the maximum number of data records when outofmemery data is reported, as shown in table 1:
Table 1: Data volume that can be processed when an outofmemery error is reported
Fastexecl poi jxl
10000 data/sheet 37465 28996 42270
5000 data/sheet 39096 31487 46270
3000 data/sheet 39000 32493 47860
Summary:
Multi-score sheet can reduce the memory usage to a certain extent, but all cells created in the Program (that is, one cell in Excel) cannot be released, consuming a large amount of memory, leading to outofmemery errors; jxl has the best performance. It uses less memory to create cells.

Export solution 2: first export all the data in multiple Excel files, and then Merge multiple Excel files.
First, test the time used to export all the data, as shown in table 2. The data is tested three times and averaged.
Table 2: time used to export all data
Fastexecl poi jxl
10000 data/file 68 s 33 s 30 s
5000 data/file 68 s 32 s 33 s
3000 data/file 59 s 33 s 39 s
Summary:
The Excel file is exported successfully because an Excel file is exported to release the memory occupied by the cell creation.
Fastexecl has the worst performance, and poi is stable. jxl increases the speed to a certain extent as data increases.

Then, perform integration. Because the function of merging multiple Excel files into one Excel file is only available in poi, use poi test. The result is shown in table 3.
Note: An outofmemery error is returned when a large data volume is merged. Therefore, the total data size of the merge operation is 50 thousand.
Table 3: time used to merge 50 thousand data
Time
10000 data/file 11 S
5000 data/file 11 S
3000 data/file 11 S
Summary:
Using poi to merge files is faster, but there is a limit on the amount of data.

Conclusion: solution 2 is feasible, but the data size is limited to 50 thousand.

Ii. Export XML workbooks
The exported format is similar to plain text. This allows you to store large amounts of data, View data in sheet, and add simple styles to meet project requirements. The actual test results show that both excel2003 and excel2007 can be identified and opened normally. The time test is shown in table 4. The data is tested three times on average.
Table 4: time used to generate all data
Time
10000 data/sheet 28.0 seconds
20000 data/sheet 30.1 seconds
30000 data/sheet 28.1 seconds
40000 data/sheet 26.5 seconds
50000 data/sheet 28.2 seconds
55000 data/sheet 26.8 seconds
59000 data/sheet 30.1 seconds
59500 data/sheet false crashes
60000 data/sheet false crashes

However, the exported data is not a pure Excel file. If you save the exported data with the XLS Suffix of the Excel file, a warning is displayed when you open the file, but reading is not affected.
According to the actual test, you can import the exported XML into the ACCESS database by importing external data in access2007 and access2003.

Iii. Summary
The project requirement is to export a large amount of data to an Excel file, and poi, jxl, and fastexcel cannot fully meet the requirements. xml workbooks can be used to export a large amount of data, however, XML is not a pure Excel file, saving the country from the curve. Compare the two export formats, as shown in table 5.
Table 5: time used to merge 50 thousand of data
Poi, jxl, fastexcel XML workbooks
Export data in XML format
Export a small amount of data
Whether sheet can be split
Can style be added?
Can I add images to poi?
Whether the exported data can be imported into access

PS. I can hardly improve the JVM size. I don't know what's going on ......

Reference: http://hi.baidu.com/danghj/item/31699b75ba72402bd7a89c95