大数据量导出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的大小几乎没效果，不知道是怎么回事……

评论

33 楼 liujunsong 2009-07-11  

浪费时间的做法居然有这么多种.开眼了.
不要用java,啥问题都没有了.
用C写段程序访问数据库就行了,在DB上写个sp写到文本文件里面也行.

32 楼 harbey 2009-07-11  

1、可以将大量数据导出到.txt文本，数据之间用自定义符号隔离开；
   这样就可以解决excel最大行数的限制以及吃内存的情况；
2、写一个将导出的.txt合并成一个.xls的应用程序；
   可以在客户端进行数据转换，减少了服务器端的压力；
都可以使用多线程来操作；

31 楼 kenken0y 2009-07-11  

jcs7575 写道

如果是存数据 导出CSV格式比较好

保存为csv格式也有很多技巧的
例如：
021直接导出来会变成21，应该导出为 ="021" 或者"=""021"""
引号，逗号，回车如何导出
参见 http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

30 楼 jcs7575 2009-07-11  

如果是存数据 导出CSV格式比较好

29 楼 sjpsega 2009-07-10  

biguan 写道

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。

呵呵，你的方法很好啊，其实我本来也想到直接用io生成XLS文件的，不会我是直接用记事本打开的，结果看到乱码 - -

还有写io你提到了重点，就是要经常flush，不然数据大就OOM了。

我的方法也主要就是拼字符串，还有就是从数据库读取的数据采用分页的形式，用list分次读取出来，结束采用list.clear()一下，及时把资源释放。

28 楼 biguan 2009-07-10  

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。

27 楼 mccxj 2009-07-10  

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是干什么？

意思是把excel保存为html格式的
如果格式要求不复杂，保存为xml，csv都是可以很简单的处理

26 楼 yangyi 2009-07-10  

这基本上是需求错位导致的

25 楼 eclipse2008 2009-07-10  

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万条替换）+尾代码。

java io是王道，以不变应万变，只要了解文件格式就可以了。

24 楼 biguan 2009-07-10  

当时是领导要求要用excel看，还要借助excel自带的统计，来进行一些统计。
所以只好生成excel文件了。
灵感来自于java的io写txt文件。再通过尝试，居然成了，不得不佩服java的强大。

23 楼 tidus2005 2009-07-10  

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是干什么？

22 楼 biguan 2009-07-10  

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



ps:兄弟是新来的，感觉方法不错，请给个鲜花，呵呵。

21 楼 xfan1982 2009-07-09  

直接导出csv，导出6w行很快，
超出6w，可以让用户输入起始行和结束行，分批导出

20 楼 cats_tiger 2009-07-09  

到底用的什么方法也没有说呀。这些我们以前也测试过，关键还是怎样解决OOM的问题。

19 楼 luzl 2009-07-09  

phenom 写道

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

所以导成CSV的报表，如果超过这个行数，客户用Excel也看不了，呵呵，CSV可能传送方便，看得时候怎么看呢？

18 楼 dajian 2009-07-09  

Dead_knight 写道

wl95421 写道

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

这个方案我做过，不会引起内存溢出，但有个问题，就是这个文件会比相同内容的Excel文件大，不过这个可以通过过滤压缩来解决

这个方法好，学习了，

曾经用POI导出10W条记录，也用分sheet方式，凑合着用过一段时间

POI确实挺稳定的

17 楼 wl95421 2009-07-09  

再给你一个建议吧
如果真得有这么夸张的需求
那么干脆做一个VC程序，通过Excel的Com来写数据
然后通过远程来调用
即减轻了服务器的压力
还可以处理大数据量。

16 楼 vlinux 2009-07-09  

我的做法是：
1.用jxl,养成经常write()的习惯
2.每6W条记录分一个sheet
3.JAVA循环的时候要记得不要在循环体中分配太多内存，避免系统回收时占用过高的CPU资源－－本来导出时候已经很消耗CPU了
4.每次导出先创建一个不重复的临时的文件，等文件导好后再重命名为可用的文件，这样可以避免用户导出的时候强行终止所带来的莫名其妙的文件记录，重命名好后入库，通知前台可以下载－－或者可以省略掉重命名这步？

15 楼 keer2345 2009-07-09  

用Access呀，多少行都门问题

14 楼 Dead_knight 2009-07-09  

wl95421 写道

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

这个方案我做过，不会引起内存溢出，但有个问题，就是这个文件会比相同内容的Excel文件大，不过这个可以通过过滤压缩来解决