大数据量导出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 的电子表格 

如果是用poi或者jxl之类的，大量数据肯定会内存溢出。因为，它们都是一次在内存生成整个excel对象。poi最新版本倒是支持excel2007写临时文件，但是2003不能用。有一种做法就是不以excel的形式导出，如：Excel另存为XML格式，以此XML为模板，将需要导出的内容拼接为XML字符串按顺序写到一个后缀为xls的文件中。也能用Excel打开的，但是这种情况适用于导出的是数据列表，如果有公式、图表之类的就不适用了。这种方式导出几十W都没问题。简而言之就是拼接符合Excel格式的XML字符串写到一个你想写到的地方，直接response中写到用户浏览器也可以啊。你随便找个简单的Excel另存为XML，就能理解我说的了。
POI或者JXL的话把服务器内存占满，导死服务器是肯定会发生的，跟数据条数还有字段个数都有关系。如果不对导出请求做并发限制，那结果会更严重。如果要跟踪内存占用情况的话用jprofile监控下内存，做下代码级优化，但是效果不会改善多少的。

导出的格式类似为纯文本，能实现大数据量的存储，并能实现分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       能                            能

四、将excel转html 

因每个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。

五、这里有另一个解决方案，这里讨论简单的解决方案

大数据量导出Excel的方案，顾名思义是要导出大量的数据，
大量数据导出本身就带有风险，因为很大的数据量比如*G的文件根本不可能用
excel来打开，就算打开了也是无法查看的，
我们做一个简单的计算
excel 07之前每个标签最多有256*65536的数据量，好像最多也是256个标签，
也就是说最多可以存储65536*65536个字段的数据量，也差不多能满足一般的数据的存储。。(有点矛盾)
但是存了这么多数据的电子表格能打开吗？。。。
下面说说导出这么大量数据的解决方案，在另一个文章里提到的方式就不用再提了。

1、用csv导出

写一个导出数据的程序，把需要的数据导出，并把输出结果输出到csv文件，每个字段之间做好分隔符，
这样可以直接打开就是excel来打开的。
如果非要存到excel里，还有一种办法，
新建一个excel文件，选择一个标签， 然后在菜单栏里选择 数据->导入外部数据->外部数据
弹出文件选择框，选择csv文件，弹出 【文件导入向导】 选择 【分隔符号】下一步，
然后接下来的对话框里选择自己定义好的分隔符 比如【;】【###】等，选择后下面的
数据预览里就可以看到数据分割后的结果，选择完成，然后选择一个标签中的某个单元格就可以。

2、使用toad工具

toad支持主流的数据库，像oracle，mysql，mssql等，下载对应的软件安装。
新建一个查询窗口，写好sql语句后执行。
在查询结果里选中需要导出的数据，右键选中export 之类的菜单，具体步骤就不用细说了，
可以直接导出为excel，还有其他的一些文件格式。有时间再总结一下，本人电脑没装toad工具
所以不能一步一步的说怎么导出，但是确实可行的方法，而且我自己也测试过，而且导出时很灵活，
设定分隔符什么的，如果需要格式化数据，，那就在sql语句里直接格式化，都格式化成字符串格式，
这样导出时处理起来比较方便，比如oracle德 to_char，concat 之类的函数。反正需要根据自己的需要
灵活的应用就好了。

3、其他数据浏览工具

比如oracle的plsql ，写一个sql语句，得出结果之后把所有数据选中，直接复制，ctrl+c也可以。
新建一个excel文档，直接粘贴，一般都是已经分割好的，一个单元格一个字段的数据，excel可以识别一般的
分隔符，比如 ; \t 等分隔符，如果不行 就按照1中的办法，
就是繁琐一些，但是很好用。
最近总结出来的就是这些，以后有更好的办法就直接分享。