大数据量导出Excel的方案 java 大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数据/sheet    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       能                            能

原文链接:http://devbbs.doit.com.cn/thread-46-1-1.html

tidus2005 写道biguan 写道我同事最近要把1000万条记录从数据库导到excel里,在我的帮助下解决了。
呵呵。原创的。
因每个excel最多放5万条,所以他把这1000万条记录记录放到了200个excel文件里。用时40分钟。
采用基本的jdbc技术+io流。
1.先进一个excel文件。填上要的表头和两条记录。然后另存为网页a.html。
2.用记事本打开网页a.html,就看到源代码。把源代码分成三部分:头+记录行+尾。
3.用jdbc访问数据库,循环遍历,每5万条,用io流写文件,格式为"xxx.xls”。
a.html的头代码+记录行代码(已经被5万条替换)+尾代码。

没有看太懂你的意思, 用html是干什么?

用java的io写txt格式的文件,大家都会吧?

其实,也可以用java的io写xls格式的文件的。关键是你得按一定的excel文件格式写,才能保证生成的是excel文件。

这个格式怎样得到呢?方法是这样:

你先建一个excel文件,如a.xls。填上两条伪数据。然后另存为网页,即htm格式,如a.htm。

然后,用记事本打开htm格式的a.htm,这样excel文件格式代码就暴露在你面前。

剩下的事,呵呵,就是把a.htm源代码的伪数据部分,替成数据库里的数据,然后把替换后的整个a.htm源代码,用java的io写成一个后缀为xls的文件。就打完收工了。

注意:为了不给内存增加压力,要把a.htm源代码分成三部分:头(伪数据部分 前的代码) + 伪数据部分 + 尾(伪数据部分 后的代码)。
先把 头 写到文件,并flush。然后是 伪数据部分 ,替一条数据库里的记录就写到文件里,并flush。最后把 尾 写到文件,并flush。

等我实现了第二的方法,把代码贴出来。

还有另一个文章 供参考http://hi.baidu.com/jjpx/blog/item/b647acadb2e9db064b36d6ae.html

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>