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

原创 于 2010-05-25 09:07:28 发布 · 833 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#Excel #EXT #Ruby #Office #F#

本文介绍了一种处理大数据量导出Excel的方法，通过将数据转换为HTML格式，利用Java IO输出来避免内存溢出问题。同时提供了不同Excel版本支持的最大数据量信息。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一般导出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+下箭头即可查看)

 

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值