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

评论

73 楼 gzh5432467 2013-05-28  

这大数据导出确实是问题，以前几万条导出成word也造成了内存外泄，记得当时是修改了jvm解决了！！！

72 楼 青春的、脚步 2012-12-04  

kimmking 写道

青春的、脚步 写道

最近用大数据导表，觉得poi可以，只是感觉问题不是出在这里  蛋疼。。

可以跟我交流。

谢谢    怎么方便联系到你呢  ？QQ ？

71 楼 kimmking 2012-12-03  

青春的、脚步 写道

最近用大数据导表，觉得poi可以，只是感觉问题不是出在这里  蛋疼。。

可以跟我交流。

70 楼 青春的、脚步 2012-12-03  

楼主方便联系吗 想和你交流些问题

69 楼 青春的、脚步 2012-12-03  

最近用大数据导表，觉得poi可以，只是感觉问题不是出在这里  蛋疼。。

68 楼 对酒当歌，人生几何 2010-07-07  

vlinux 写道

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

请问你这里的第一条，是什么意思？ 我查询了一个list出来，想循环一个list就write一次。结果，除了第一条数据，其他都没写到文件里面。也没有找到合适的api，搞不懂了

67 楼 mycybyb 2010-03-19  

POI也是可以的

66 楼 kimmking 2010-03-19  

lkj107 写道

csv不支持sheet
普通的操作excel的包，超过3万条容易内存溢出
所以没有完美的解决方案
很多人都是使用导出多个文件，最后合并的
如楼上所说的用xml，但是很多单位office还是用的2003版的

我什么时候说csv了，
我说 biff8

65 楼 lkj107 2010-03-19  

csv不支持sheet
普通的操作excel的包，超过3万条容易内存溢出
所以没有完美的解决方案
很多人都是使用导出多个文件，最后合并的
如楼上所说的用xml，但是很多单位office还是用的2003版的

64 楼 kimmking 2010-03-19  

excel2003的话，自己看biff8，格式固定的话，自己写二进制也不难。
excel2007就更简单了，自己写xml，然后zip即可。

63 楼 kimmking 2010-03-19  

ray_linn 写道

最简单得莫过于在excel里写一段vba脚本, VBA脚本采用AJAX的方式去和server端数据通讯，并负责填充表单。

好处：

1. 局域网内服务器端产生数万条数据并通讯并不需要耗费许多时间。

2. 表单的填充在客户端完成，不需要耗费服务器的时间。

vba里容易假死，很别扭的。

62 楼 lightning_11 2010-03-19  

还是用CSV吧，我曾经搜索47个excel，各个xls的数据量大小不一，大的2M小的几十K
然后把找到的数据一起放到csv中，一共44000多条，共用44秒，感觉很好，如果用excel根本不行

61 楼 wbsh583 2010-03-19  

学习了正需要导出了 好好看看能行么我的也是一直在包内存溢出

60 楼 yourgame 2009-10-13  

我现在碰到一个难题

上传xls文件读取内容写入数据库,问题来了.当我的文件比较大,内容比较多,比如2,30个字段,记录行数超过9999行时,上传到服务器后,每次运行到这句的时候直接就没有反映了

File file = new File("c:/xx.xls");
		 
Workbook workbook = Workbook.getWorkbook(file);//执行到这里就无反应

但是记录少于10000就不会有问题,请问打家是如何解决大文件读取的

59 楼 wdzwdz 2009-09-08  

为何不使用csv格式导出数据呢

58 楼 bluemaple 2009-09-02  

wl95421 写道

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

同意这种说法。

57 楼 yidao620c 2009-08-21  

我导过3亿条。结果死机了。

56 楼 jwnest 2009-08-07  

如果是这种需求，考虑方案的时候就不应该局限在Java的Tools里面，因为Java里面那些操作excel的工具就只是一个权益之计，只支持一些最基本的excel应用，并不适合你说的这种场景。
之前项目里面也有这样的场景，大概需要输出的数据有几万条，还需要把导出的数据和一些其他的含有奇怪内容的excel进行合并，输出到一个excel里面，当时测试的时候，所有的Java Tools都没有办法读入那些有奇怪内容的excel，就更谈不上合并excel了。
之后是用Java POI + VBA script的方法来处理的，所有的数据导出到多个临时的excel文件里面，每个文件只包含一个sheet，全部完成后用java调用一个VBA script，合并所有的临时excel文件的sheet到一个最终文件，搞定。
这样的话，只要保证一个sheet的数据量在合理范围内，就不会有outofmemory的问题，并且那个VBA script也是非常简单的，只需要有添加,删除sheet的功能就可以，效率也不错，毕竟是microsoft自己提供的工具。

55 楼 yangpanwww 2009-08-07  

  


LZ   网上,都说FastExcel比较快,,,怎么在你的测试中相反呢?

刚刚也用到这个..先用FastExcel它好了。,,,但是 到底谁更好呢,,,,有时间测下下.....

54 楼 Skyecho 2009-08-04  

学习 哈哈