大数据量导出Excel的方案

talangniao

浏览: 114804 次
来自: ...

最近访客更多访客>>

ggyi2003

jeffnono

junzai1225

bocoepms

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Excel JDBC HTML SQL 浏览器

来源： http://www.iteye.com/topic/422117
范例
http://devbbs.doit.com.cn/thread-72-1-1.html

看过很多关于Excel导出时出现内存溢出的情况，也有很多解决方案。现提供如下解决方案，如有不妥，请指正： 
    该项目使用B/S架构，由于POI、JXL在导出excel大数据量情况下会产生大量对象最终导致内存溢出。其实Excel可以另存为html文件，保存为html后的文件内容如下： Html代码 
<html xmlns="urn:schemas-microsoft-comfficeffice"  
xmlns:x="urn:schemas-microsoft-comffice:excel"  
xmlns="http://www.w3.org/TR/REC-html40">  
  
<head>  
<meta http-equiv=Content-Type content="text/html; charset=gb2312">  
<meta name=ProgId content=Excel.Sheet>  
<meta name=Generator content="Microsoft Excel 11">  
……样式信息……   
<body link=blue vlink=purple>  
<table x:str border=0 cellpadding=0 cellspacing=0 width=620 style='border-collapse:   
collapse;table-layout:fixed;width:466pt'>  
<col width=129 style='mso-width-source:userset;mso-width-alt:4128;width:97pt'>  
<col class=xl25 width=72 span=2 style='width:54pt'>  
<col class=xl25 width=63 style='mso-width-source:userset;mso-width-alt:2016;   
width:47pt'>  
<col class=xl25 width=118 style='mso-width-source:userset;mso-width-alt:3776;   
width:89pt'>  
<col width=166 style='mso-width-source:userset;mso-width-alt:5312;width:125pt'>  
<tr height=19 style='height:14.25pt'>  
  <td height=19 class=xl24 width=129 style='height:14.25pt;width:97pt'>字段1</td>  
  <td class=xl24 width=72 style='width:54pt'>字段2</td>  
  <td class=xl24 width=72 style='width:54pt'>字段3</td>  
  <td class=xl24 width=63 style='width:47pt'>字段4</td>  
  <td class=xl24 width=118 style='width:89pt'>字段5</td>  
  <td width=166 style='width:125pt'></td>  
</tr>  
……数据……   
<![if supportMisalignedColumns]>  
<tr height=0 style='display:none'>  
  <td width=129 style='width:97pt'></td>  
  <td width=72 style='width:54pt'></td>  
  <td width=72 style='width:54pt'></td>  
  <td width=63 style='width:47pt'></td>  
  <td width=118 style='width:89pt'></td>  
  <td width=166 style='width:125pt'></td>  
</tr>  
<![endif]>  
</table>  
</body>  
</html>  

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">


<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
……样式信息……
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 width=620 style='border-collapse:
collapse;table-layout:fixed;width:466pt'>
<col width=129 style='mso-width-source:userset;mso-width-alt:4128;width:97pt'>
<col class=xl25 width=72 span=2 style='width:54pt'>
<col class=xl25 width=63 style='mso-width-source:userset;mso-width-alt:2016;
width:47pt'>
<col class=xl25 width=118 style='mso-width-source:userset;mso-width-alt:3776;
width:89pt'>
<col width=166 style='mso-width-source:userset;mso-width-alt:5312;width:125pt'>
<tr height=19 style='height:14.25pt'>
  <td height=19 class=xl24 width=129 style='height:14.25pt;width:97pt'>字段1</td>
  <td class=xl24 width=72 style='width:54pt'>字段2</td>
  <td class=xl24 width=72 style='width:54pt'>字段3</td>
  <td class=xl24 width=63 style='width:47pt'>字段4</td>
  <td class=xl24 width=118 style='width:89pt'>字段5</td>
  <td width=166 style='width:125pt'></td>
</tr>
……数据……
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
  <td width=129 style='width:97pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=63 style='width:47pt'></td>
  <td width=118 style='width:89pt'></td>
  <td width=166 style='width:125pt'></td>
</tr>
<![endif]>
</table>
</body>
</html>

    至此，可通过数据生成如上格式的HTML文本信息则避开大量对象的建立，如果将该HTML直接以application/excel返回浏览器时则Excel文件会比普通大一点，可以通过配置过滤器对该HTML进行压缩即可，如下: 
Java代码 
       
response.reset();    
response.setContentType("application/zip;charset=GBK");   
String s = "查询-" + new java.sql.Date(System.currentTimeMillis()).toString().replaceAll("-","") + ".xls";   
String filename = s + ".zip";   
response.addHeader("Content-Disposition", "inline;filename=" + filename);  

        
response.reset(); 
response.setContentType("application/zip;charset=GBK");
String s = "查询-" + new java.sql.Date(System.currentTimeMillis()).toString().replaceAll("-","") + ".xls";
String filename = s + ".zip";
response.addHeader("Content-Disposition", "inline;filename=" + filename);

    以上只是一种导出Excel大数据量的解决方案。请大家给予意见！谢谢！

sjpsega 写道
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()一下，及时把资源释放。

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()一下，及时把资源释放。

分享到：