`
daigong
  • 浏览: 113876 次
  • 性别: Icon_minigender_1
  • 来自: 沈阳
社区版块
存档分类
最新评论

excel 导出大数据量问题

阅读更多
转:http://blog.chinaunix.net/u3/103458/showart_2033111.html

看过很多关于Excel导出时出现内存溢出的情况,也有很多解决方案。现提供如下解决方案,如有不妥,请指正:
    该项目使用B/S架构,由于POI、JXL在导出excel大数据量情况下会产生大量对象最终导致内存溢出。其实Excel可以另存为html文件,保存为html后的文件内容如下:
Html代码
<html xmlns:o="urn:schemas-microsoft-com:office:office"  
xmlns:x="urn:schemas-microsoft-com:office: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:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office: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大数据量的解决方案。请大家给予意见!谢谢!
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics