如何导出大数据量的Excel文件

tdqing

浏览: 470196 次
性别:
来自: 青岛

最近访客更多访客>>

yiluyouni_zxx

tom2139779

tayoru

tanzqgz

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Java

Excel EXT Ruby Office F#

一般导出Excel可以选择POI或者JXL，poi比较方便但是处理大数据量效果不佳，jxl可以支持较大数据量，但是超过5W条也会报OOM错误。

那么如果有上百万条的数据怎么到处Excel呢？

其实Excel可以保存成HTML格式的文档（包括图片），我们可以研究html文件格式，然后用io方式往里面输出数据就可以了。

html格式如下:

<html xmlns:v="urn:schemas-microsoft-com:vml"
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">
<link rel=File-List href="Book1.files/filelist.xml">
<link rel=Edit-Time-Data href="Book1.files/editdata.mso">
<link rel=OLE-Object-Data href="Book1.files/oledata.mso">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
x\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:Author>www.jujumao.com</o:Author>
  <o:LastAuthor>www.jujumao.com</o:LastAuthor>
  <o:Created>2010-05-24T10:00:21Z</o:Created>
  <o:LastSaved>2010-05-25T01:00:34Z</o:LastSaved>
  <o:Company>微软</o:Company>
  <o:Version>11.5606</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
@page
	{margin:1.0in .75in 1.0in .75in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;}
tr
	{mso-height-source:auto;
	mso-ruby-visibility:none;}
col
	{mso-width-source:auto;
	mso-ruby-visibility:none;}
br
	{mso-data-placement:same-cell;}
.style0
	{mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	white-space:nowrap;
	mso-rotate:0;
	mso-background-source:auto;
	mso-pattern:auto;
	color:windowtext;
	font-size:12.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	border:none;
	mso-protection:locked visible;
	mso-style-name:常规;
	mso-style-id:0;}
td
	{mso-style-parent:style0;
	padding:0px;
	mso-ignore:padding;
	color:windowtext;
	font-size:12.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border:none;
	mso-background-source:auto;
	mso-pattern:auto;
	mso-protection:locked visible;
	white-space:nowrap;
	mso-rotate:0;}
.xl24
	{mso-style-parent:style0;
	white-space:normal;}
ruby
	{ruby-align:left;}
rt
	{color:windowtext;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	mso-char-type:none;
	display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>2</x:ActiveRow>//默认选中的行
       <x:ActiveCol>2</x:ActiveCol>//默认选中的列
















      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>9225</x:WindowHeight>
  <x:WindowWidth>17100</x:WindowWidth>
  <x:WindowTopX>120</x:WindowTopX>
  <x:WindowTopY>105</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext="edit" spidmax="1027"/>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext="edit">
  <o:idmap v:ext="edit" data="1"/>
 </o:shapelayout></xml><![endif]-->
</head>

<body link=blue vlink=purple>

//正文内容
<table x:str border=0 cellpadding=0 cellspacing=0 width=912 style='border-collapse:
 collapse;table-layout:fixed;width:684pt'>
 <col width=72 style='width:54pt'>
 <col width=237 style='mso-width-source:userset;mso-width-alt:7584;width:178pt'>
 <col width=215 style='mso-width-source:userset;mso-width-alt:6880;width:161pt'>
 <col width=28 style='mso-width-source:userset;mso-width-alt:896;width:21pt'>
 <col width=72 span=5 style='width:54pt'>
 <tr height=76 style='height:57.0pt'><!--数据列-->
  <td height=76 width=72 style='height:57.0pt;width:54pt'>萨地方</td>
  <td class=xl24 width=237 style='width:178pt'>阿送大幅大赛sadf<br>
    dsafsafds<br>
    &lt;&gt;&lt;''&quot;&gt;<br>
    afdsa----</td>
  <td align=right width=215 style='width:161pt' x:num>1</td>
  <td width=28 style='width:21pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
 </tr>
















 <tr height=19 style='height:14.25pt'>
  <td height=19 align=right style='height:14.25pt' x:num>2</td>
  <td align=right x:num>2</td>
  <td align=right x:num>2</td>
  <td colspan=6 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=57 style='height:42.75pt;mso-xlrowspan:3'>
  <td height=57 colspan=9 style='height:42.75pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
  <td colspan=7 rowspan=14 height=266 width=603 style='mso-ignore:colspan-rowspan;
  height:199.5pt;width:452pt'><!--[if gte vml 1]><v:shapetype id="_x0000_t75"
   coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe"
   filled="f" stroked="f">
   <v:stroke joinstyle="miter"/>
   <v:formulas>
    <v:f eqn="if lineDrawn pixelLineWidth 0"/>
    <v:f eqn="sum @0 1 0"/>
    <v:f eqn="sum 0 0 @1"/>
    <v:f eqn="prod @2 1 2"/>
    <v:f eqn="prod @3 21600 pixelWidth"/>
    <v:f eqn="prod @3 21600 pixelHeight"/>
    <v:f eqn="sum @0 0 1"/>
    <v:f eqn="prod @6 1 2"/>
    <v:f eqn="prod @7 21600 pixelWidth"/>
    <v:f eqn="sum @8 21600 0"/>
    <v:f eqn="prod @7 21600 pixelHeight"/>
    <v:f eqn="sum @10 21600 0"/>
   </v:formulas>
   <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/>
   <o:lock v:ext="edit" aspectratio="t"/>
  </v:shapetype><v:shape id="_x0000_s1026" type="#_x0000_t75" style='position:absolute;
   margin-left:68.25pt;margin-top:7.5pt;width:347.25pt;height:186pt;z-index:1'
   fillcolor="windowText [64]" strokecolor="window [65]" strokeweight="3e-5mm"
   o:insetmode="auto">
   <v:fill color2="window [65]"/>
   <v:imagedata src="Book1.files/image001.png" o:title=""/>
   <x:ClientData ObjectType="Pict">
    <x:MoveWithCells/>
    <x:SizeWithCells/>
    <x:Locked>False</x:Locked>
    <x:AutoFill>False</x:AutoFill>
    <x:AutoLine>False</x:AutoLine>
    <x:CF>Bitmap</x:CF>
   </x:ClientData>
  </v:shape><![endif]--><![if !vml]><span style='mso-ignore:vglayout'>
  <table cellpadding=0 cellspacing=0>
   <tr>
    <td width=91 height=10></td>
   </tr>
   <tr>
    <td></td>
    <td><img width=463 height=248 src="Book1.files/image002.jpg" v:shapes="_x0000_s1026"></td>
    <td width=49></td>
   </tr>
   <tr>
    <td height=8></td>
   </tr>
  </table>
  </span><![endif]><!--[if !mso & vml]><span style='width:452.25pt;height:199.5pt'></span><![endif]--></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=19 style='height:14.25pt'>
  <td height=19 colspan=2 style='height:14.25pt;mso-ignore:colspan'></td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=72 style='width:54pt'></td>
  <td width=237 style='width:178pt'></td>
  <td width=215 style='width:161pt'></td>
  <td width=28 style='width:21pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
 </tr>
 <![endif]>
</table>

</body>

</html>

在项目中,我们可以先定制出一个excel模板并填充一两条数据(比如包含头、体、尾),在输出的时候,我们只需要把正文内容（文件体）那一部分用JAVA的IO输出就可以了，如果数据量很大，可以每隔一段时间就flush一下。

至此，可通过数据生成如上格式的HTML文本信息则避开大量对象的建立，如果将该HTML直接以application/excel返回浏览器时则Excel文件会比普通大一点，可以通过配置过滤器对该HTML进行压缩即可，如下:

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文件，如a.xls。填上两条伪数据。然后另存为网页，即htm格式，如a.htm。
然后，用记事本打开htm格式的a.htm，这样excel文件格式代码就暴露在你面前。
剩下的事，就是把a.htm源代码的伪数据部分，替成数据库里的数据，然后把替换后的整个a.htm源代码，用java的io写成一个后缀为xls的文件。就打完收工了。
注意：为了不给内存增加压力，要把a.htm源代码分成三部分：头（伪数据部分前的代码） + 伪数据部分 + 尾（伪数据部分后的代码）。
先把头写到文件，并flush。然后是伪数据部分 ,替一条数据库里的记录就写到文件里，并flush。最后把尾写到文件，并flush。

重点就是要经常flush，不然数据大就OOM了;另外如果使用了list取数据,在使用完以后要及时list.clear().

不同版本下Excel数据量极限:

excel2000的工作表中最大只能有65536行数据，每一行最大只能有256列
2003下
行:65536
列:IV即230
2007下
行:1048576
列:XFD，26进制，16384列.

(在excel中atrl+shift+右箭头 atrl+shift+下箭头即可查看)

分享到：

用start命令启动tomcat | Oracle中如何使用DBLink连接不同的实例

2010-05-25 09:07
浏览 2442
评论(5)
分类:编程语言
查看更多

5 楼 tdqing 2010-09-01

jdllove86 写道

但是如果数据在5W甚至更多文件会特别大，我导出的文件30多列。5W大约50-60M,打开需要很长时间，大约5分钟以上吧！而且特别容易把机器的内存全吃光了，不懂LZ有什么好办法没？

这里说的是导出，至于打开慢，就和offie有关了。
ps：剑走偏锋，你可以设置一下response的head，弹出下载窗口而不是直接打开，这样就不会显得应用反应慢了。

4 楼 jdllove86 2010-08-31

3 楼 jdllove86 2010-08-31

很好，是我想要的。

2 楼 newwpp 2010-06-01

很专业。长见识

1 楼 welody 2010-05-28

田哥，太强大了。

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论