大数据量导出Excel的方案

试共同条件：
数据总数为110011条，每条数据条数为19个字段。
电脑配置为：P4 2.67GHz，1G内存。

一、POI、JXL、FastExcel比较
POI、JXL、FastExcel均为java第三方开源导出Excel的开源项目。

导出方案一：一次性全部导出到一个Excel文件中。
实际情况均报OutOfMemery错误，以下数据为报OutOfMemery数据时，数据到的最大数据数目，如表1所示：
表1：报OutOfMemery错误时所能处理的数据量

	FastExecl	POI	JXL
10000数据/sheet	37465	28996	42270
5000数据/sheet	39096	31487	46270
3000数据/sheet	39000	32493	47860

小结：
多分sheet能一定程度上减少内存的使用，但是均因为程序中创建的Cell（即为Excel中的一个单元格）无法释放，消耗大量内存，导致OutOfMemery错误；JXL表现最好，创建Cell内存使用较少。

导出方案二：先分多个Excel文件将数据全部导出，然后对多个Excel文件进行合并。
首先，测试将全部数据导出所用的时间，如表2所示，数据均测试三次取平均。
表2：导出全部数据所用时间

 

	FastExecl	POI	JXL
10000数据/文件	68s	33s	30s
5000数据/文件	68s	32s	33s
3000数据/文件	59s	33s	39s

小结：
均成功导出Excel文件，原因是导出一个Excel文件，释放所占用的创建Cell的内存。
FastExecl表现最差，POI表现稳定，JXL随着数据的增大，速度一定程度上增快。

然后，进行整合，由于将多Excel合并成一个Excel文件的功能只有POI所有，故使用POI测试，结果如表3所示。
注：数据量大合并还会报OutOfMemery错误，故合并总数据量以5万为准。
表3：合并5万数据所用时间

	时间
10000数据/文件	11s
5000数据/文件	11s
3000数据/文件	11s

小结：
使用POI对文件进行合并速度较快，但有数据量的限制。


总结：方案二比较可行，但是数据量有限制，为5万条。


二、导出XML 的电子表格
导出的格式类似为纯文本，能实现大数据量的存储，并能实现分Sheet查看，且能添加简单的样式，符合项目要求。经实际测试Excel2003和Excel2007均能识别并正常打开查看。使用时间测试如表4所示，数据均测试3次取平均。
表4：生成全部数据所用时间

	时间
10000数据/sheet	28.0秒
20000数据/sheet	30.1秒
30000数据/sheet	28.1秒
40000数据/sheet	26.5秒
50000数据/shee	28.2秒
55000数据/sheet	26.8秒
59000数据/sheet	30.1秒
59500数据/sheet	发生假死机现象
60000数据/sheet	发生假死机现象

但是导出的数据为XML不是纯正的Excel文件，如使用Excel文件的xls后缀保存，打开文件会弹出警告，但不影响阅读。
且经实际测试，在Access2007和Access2003中可通过导入外部数据的方式，将导出的XML导入进Access数据库。

三、总结
项目要求是大数据量导出Excel文件，POI、JXL、FastExcel不能完全满足要求；使用XML 的电子表格导出实现了大数据量导出，但是格式为XML不是纯正的Excel文件，为曲线救国。两种导出形式的比较，如表5所示。
表5：合并5万数据所用时间

	POI、JXL、FastExcel	XML 的电子表格
导出数据格式	为纯Execl文件	为XML文件
导出数据量	小	较大
能否分Sheet	能	能
能否添加样式	能	能
能否添加图片	POI 能	不能
导出数据能否导入Access	能	能

 

PS。我提高JVM的大小几乎没效果，不知道是怎么回事……

评论

13 楼 asialee 2009-07-09  

我在项目中其实导出excel的功能大多是导成html,然后以excel方式打开，确实在打开的时候有警告，但不影响阅读，没办法，excel2003每个sheet只能65535，但是用这种方法实际上是假导出excel，那么肯定不能实现多sheet，幸亏当时客户说数据不会超过50000，但我在程序中还是做了判断，大于60000的时候分成两个批次进行导出。

12 楼 lnaigg 2009-07-09  

如果不优化内存，大数据量excel导出肯定outofmemory。

其实可以不把导出这一块放在主程序中，使用单独的jvm运行导出功能。


导出为html，然后后缀改一下，这种方式是最好的解决方式，研究得深点，图片也可以加上去。

11 楼 wl95421 2009-07-09  

给个建议，如果没有图片或者公式的话
可以考虑导出为HTML，只要把扩展名改一下，Excel是可以打开它的。

10 楼 whaosoft 2009-07-09  

就是我朋友以前做金融项目也没 要所有数据都打到一个excel上~~ 那么多数据什么都死了!~

9 楼 myworkfirst 2009-07-09  

Jason_zhu 写道

phenom 写道

CVS用EXCEL打开只能显示65536行数据,EXCEL也是

excel2003最多65535行，excel2007最多1048576行。

   这个问题比较好解决，多创建几个sheet就行了。

8 楼 Jason_zhu 2009-07-09  

phenom 写道

CVS用EXCEL打开只能显示65536行数据,EXCEL也是

excel2003最多65535行，excel2007最多1048576行。

7 楼 luoli-17 2009-07-09  

扯淡！！！！！

6 楼 myworkfirst 2009-07-09  

   POI包可以导入图片到execl,我以前导出过。

   另，导出方法，有技巧，lz是怎么导的嘛。光贴个数据，不给过程，怎么知道你的做法好呢。

5 楼 phenom 2009-07-09  

CVS用EXCEL打开只能显示65536行数据,EXCEL也是

4 楼 sjpsega 2009-07-08  

seaven 写道

用的什么方法呢？

其实就是按XML的格式拼字符串……

3 楼 sjpsega 2009-07-08  

base64

myyate 写道

XML文件格式的电子表格可以使用base64来添加图片。

谢谢提醒，不过这个项目中没要求，我也没怎么试验。
但是我在XML文件中插入图片，然后保存，再打开，图片看不到...

2 楼 myyate 2009-07-08  

XML文件格式的电子表格可以使用base64来添加图片。

1 楼 seaven 2009-07-08  

用的什么方法呢？