MyException - 我的异常网
当前位置:我的异常网» 软件架构设计 » 大数据量导出Excel的议案

大数据量导出Excel的议案

www.MyException.Cn  网友分享于:2015-08-26  浏览:96次
大数据量导出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的大小几乎没效果,不知道是怎么回事……

49 楼 yangyi 2009-07-28  
guooscar 写道
jacklondon 写道
guooscar 写道
kusix 写道
这么大的数据量,导出来是给人看的么?

如果根本就没人看,为什么一定要导成EXCEL呢?

遇到这种问题,我认为应当努力在需求层面解决问题,引导客户。


记得上次在sun网站上和老外讨论这个问题,人家一致认为这个需求很ridiculous

----为什么一定要导成EXCEL呢?
因为 Excel 市场分布很广;因为 Excel 的数据排序、汇总功能对于非 IT 人员来说不可或缺;因为 Excel 数据格式被很多应用程序认可。
这种事情还要问么?


说你不可理喻还真是。。。。
给你3亿数据你给我排个序?
你给我汇个总?

海量的数据只有存储的意义,没有展示的意义。如果用excel展示,需要在展示之前进行预处理,直接显示汇总后的结果。在生成的binary的excel中加入预处理脚本
50 楼 harbey 2009-07-31  
或者在数据库里写存储过程,按照时间(比如说一个月)产生一个excel文件,放入指定的文件目录(跨机器可以用ftp传递到web服务器),通过java程序来读取该目录下的所有excel文件,在web页面提供下载链接;

3亿!内存!OutOfMemory!
51 楼 guooscar 2009-08-01  
jacklondon 写道
guooscar 写道
jacklondon 写道
guooscar 写道
kusix 写道
这么大的数据量,导出来是给人看的么?

如果根本就没人看,为什么一定要导成EXCEL呢?

遇到这种问题,我认为应当努力在需求层面解决问题,引导客户。


记得上次在sun网站上和老外讨论这个问题,人家一致认为这个需求很ridiculous

----为什么一定要导成EXCEL呢?
因为 Excel 市场分布很广;因为 Excel 的数据排序、汇总功能对于非 IT 人员来说不可或缺;因为 Excel 数据格式被很多应用程序认可。
这种事情还要问么?


说你不可理喻还真是。。。。
给你3亿数据你给我排个序?
你给我汇个总?

楼主说的数据量为 5万, 你冒出个“3亿数据”,搞什么名堂?
5万 数据量进行排序汇总很正常的。


您真逗. 3亿数据 不是冒出,是我们就做过这个, 如果您就5w数据的话,您爱导什么导什么,
可是事实是客户数据有3亿,人家还要用excel导出,结果就是每个excel 6w行,导了 3亿/6w 个excel.
您觉得客户拿到这些excel有用么?自己还搞个外部排序?
52 楼 guooscar 2009-08-03  
<div class="quote_title">guooscar 写道</div>
<div class="quote_div">
<br>还是您老真逗。您3亿数据,不用excel导出,随您;楼主<span style="color: #ff0000;"> 5</span>万数据,用excel导出,您也不用反对。对不对?</div>
<p><br><br>麻烦从第一页开始看<br><span style="color: #ff0000;">大数据量</span>导出Excel的方案<br>试共同条件:<br>数据总数为<span style="color: #ff0000;">110011</span>条,每条数据条数为<span style="color: #ff0000;">19</span>个字段。<br>电脑配置为:P4 2.67GHz,1G内存。</p>
<p> </p>
<p> </p>
<p> </p>
53 楼 zhy20045923 2009-08-03  
Jason_zhu 写道
phenom 写道
CVS用EXCEL打开只能显示65536行数据,EXCEL也是

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

首先,为什么不采用分批次导出呢?
其次,excel可以每隔一定数据新建一个sheet?
54 楼 Skyecho 2009-08-04  
学习 哈哈
55 楼 yangpanwww 2009-08-07  
  


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

刚刚也用到这个..先用FastExcel它好了。,,,但是 到底谁更好呢,,,,有时间测下下.....
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自己提供的工具。
57 楼 yidao620c 2009-08-21  
我导过3亿条。结果死机了。
58 楼 bluemaple 2009-09-02  
wl95421 写道
再给你一个建议吧
如果真得有这么夸张的需求
那么干脆做一个VC程序,通过Excel的Com来写数据
然后通过远程来调用
即减轻了服务器的压力
还可以处理大数据量。

同意这种说法。
59 楼 wdzwdz 2009-09-08  
为何不使用csv格式导出数据呢
60 楼 yourgame 2009-10-13  
我现在碰到一个难题

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

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


但是记录少于10000就不会有问题,请问打家是如何解决大文件读取的
61 楼 wbsh583 2010-03-19  
学习了正需要导出了 好好看看能行么我的也是一直在包内存溢出
62 楼 lightning_11 2010-03-19  
还是用CSV吧,我曾经搜索47个excel,各个xls的数据量大小不一,大的2M小的几十K
然后把找到的数据一起放到csv中,一共44000多条,共用44秒,感觉很好,如果用excel根本不行
63 楼 kimmking 2010-03-19  
ray_linn 写道
最简单得莫过于在excel里写一段vba脚本, VBA脚本采用AJAX的方式去和server端数据通讯,并负责填充表单。

好处:

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

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



vba里容易假死,很别扭的。
64 楼 kimmking 2010-03-19  
excel2003的话,自己看biff8,格式固定的话,自己写二进制也不难。
excel2007就更简单了,自己写xml,然后zip即可。
65 楼 lkj107 2010-03-19  
csv不支持sheet
普通的操作excel的包,超过3万条容易内存溢出
所以没有完美的解决方案
很多人都是使用导出多个文件,最后合并的
如楼上所说的用xml,但是很多单位office还是用的2003版的
66 楼 kimmking 2010-03-19  
lkj107 写道
csv不支持sheet
普通的操作excel的包,超过3万条容易内存溢出
所以没有完美的解决方案
很多人都是使用导出多个文件,最后合并的
如楼上所说的用xml,但是很多单位office还是用的2003版的

我什么时候说csv了,
我说 biff8
67 楼 mycybyb 2010-03-19  
POI也是可以的
68 楼 对酒当歌,人生几何 2010-07-07  
vlinux 写道
我的做法是:
1.用jxl,养成经常write()的习惯
2.每6W条记录分一个sheet
3.JAVA循环的时候要记得不要在循环体中分配太多内存,避免系统回收时占用过高的CPU资源--本来导出时候已经很消耗CPU了
4.每次导出先创建一个不重复的临时的文件,等文件导好后再重命名为可用的文件,这样可以避免用户导出的时候强行终止所带来的莫名其妙的文件记录,重命名好后入库,通知前台可以下载--或者可以省略掉重命名这步?

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

文章评论

什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
10个调试和排错的小建议
10个调试和排错的小建议
一个程序员的时间管理
一个程序员的时间管理
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
老程序员的下场
老程序员的下场
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
旅行,写作,编程
旅行,写作,编程
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
那些争议最大的编程观点
那些争议最大的编程观点
漫画:程序员的工作
漫画:程序员的工作
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
Java程序员必看电影
Java程序员必看电影
程序员必看的十大电影
程序员必看的十大电影
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
每天工作4小时的程序员
每天工作4小时的程序员
如何成为一名黑客
如何成为一名黑客
我的丈夫是个程序员
我的丈夫是个程序员
程序员应该关注的一些事儿
程序员应该关注的一些事儿
程序员都该阅读的书
程序员都该阅读的书
鲜为人知的编程真相
鲜为人知的编程真相
编程语言是女人
编程语言是女人
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
 程序员的样子
程序员的样子
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
代码女神横空出世
代码女神横空出世
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
总结2014中国互联网十大段子
总结2014中国互联网十大段子
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员的鄙视链
程序员的鄙视链
为什么程序员都是夜猫子
为什么程序员都是夜猫子
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
中美印日四国程序员比较
中美印日四国程序员比较
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有