`
hua2142452
  • 浏览: 66208 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论

Java利用xml将大批量数据导出到excel的一个方法

    博客分类:
  • java
阅读更多
笔者在Java开发中常常会遇到将数据库数据导出到Excel的要求,比如在我的一个项目中,客户要求所有查询结果都可以导出到Excel,对于数据量不大的(几万条),这比较容易实现,但对于数据量比较大的(几十万及以上,具体要看导出内容的字段个数和长短),则在数据库查询和生成excel文件上都会有麻烦。

以我在实际项目中的一个表为例,Oracle 10g数据库,这个表中有40多个字段,内容是姓名、年龄、性别、地址、生日、职业、工作单位等个人信息,要求导出20多个字段。导出为HTML的表格的话,即数据放在<table><tr><td>中,1万条数据大约需要7.5-8M。

对于数据库查询,可以通过以下二个方法解决:

一、采用分页查询。根据数据库服务器性能和数据库操作频繁程度,决定每页大小,比如每页5000到10000行,每次查询出一页,然后生成excel文件。这种方法的缺点是需要多次查询数据库,而数据库分页查询因需要使用临时表,所以比较耗性能。

二、采用中间表。一次性将查询结果放到中间表中,可采用“select ...into...”,再从中间表中一部分一部分地取出来生成excel。每取一部分,就可以删除它,或者标记为“已导出”。这种方法需要操作中间表,过程麻烦一些,但效率好。

下面是生成excel的几种方法(Office 2000以上实现):

一、采用POI或者JXL组件生成EXCEL格式的文件。这种方式可以很好地控制内容的格式,缺点是这二种方式都是将EXCEL文件整个加载到内存中进行处理的,向excel追加数据时,也要先全部加载原文件,当数据量大时,生成的EXCEL文件会很大,可能会因占内存太多而导致WEB服务器内存溢出。

二、利用Excel文件支持HTML格式的功能,即系将html文件后缀名改为.xls,就可以用excel打开。这是最简单的方法,在Jsp中只要将http头改一下,让客户端浏览器下载生成的.xls文件,而不是让浏览器打开html文件就可以了。Jsp文件代码如下:
   1. <%@ page language="java" contentType="text/html; charset=GBK" %>  
   2. <%  
   3.  String fileName = "export_file_name.xls";//下载对话框的保存文件名  
   4.  response.setHeader("Content-disposition", "attachment; filename="+ fileName);  
   5. %>  
   6. <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">  
   7. <head>  
   8. <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=GBK">  
   9. <title>个人信息导出清单</title>  
  10. </head>  
  11. <table>  
  12.   <tr>  
  13.     <td>姓名</td><td>年龄</td><td>性别</td><td>身份证号</td><td>......</td>  
  14.   </tr>  
  15.   <tr>  
  16.     <td>张三</td><td>25</td><td>男</td><td x:str="'1234567890"></td><td>......</td>  
  17.   </tr>  
  18.   <tr>  
  19.     <td>小美</td><td>23</td><td>女</td><td x:str="'0987654321"></td><td>......</td>  
  20.   </tr>  
  21.   <% /*利用Jsp输出更多行*/ %>  
  22. </table>  
  23. </html>  

<%@ page language="java" contentType="text/html; charset=GBK" %> 
<% String fileName = "export_file_name.xls";//下载对话框的保存文件名 response.setHeader("Content-disposition", "attachment; filename="+ fileName); %> 
<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=GBK"> 
<title>个人信息导出清单</title> 
</head> 
<table> 
    <tr>
      <td>姓名</td>
      <td>年龄</td>
      <td>性别< /td>
      <td>身份证号</td>
      <td>......</td> 
   </tr> 
   <tr> 
      <td>张三</td>
      <td>25</td>
      <td>男</td>
      <td x:str="'1234567890"></td>
      <td>......</td> 
   </tr> 
   <tr> 
      <td>小美</td>
      <td>23</td>
      <td>女< /td>
      <td x:str="'0987654321"></td>
      <td>......</td> 
   </tr> 
<% /*利用Jsp输出更多行*/ %> 
</table> 
</html>


注意上面的<html>标记,加上了microsoft office的命名空间,主要是处理excel格式问题,比如身份号“440957198402125468”,在excel中会认为是数字型,转换为 “4.41E+17”这样的科学计数法。所以要控制格式,而格式转换最简单的就是在字符串前加一个单引号“'”,在excel中就不会自动转换了,对于时间格式也一样。这里加单引号,是通过在表格中的单元格中加属性x:str来实现的:<td x:str="'数字或时间字符串内容"></td>,加了这个属性后,单元格中不需要再加内容了,这样正好减少了生的的xls文件的源码。

采用这种方法,用jsp来生成xls文件并下载时,jsp页面一次性将内容加载到内存中,如果数据量大,生成的文件太大,可能会使web服务器内存溢出面抛出OutOfMemory异常。可以换一种方法,生成文件时先写到服务器文件系统中,写文件采用流的方式不断追加,写完文件后再下载。

不过这种方式还有另外一个问题,就是只能生成一个工作表,而EXCEL一个工作表(sheet)最多只能有65535行数据,当数据内容多时,需要建更多的sheet,笔者目前还没找到在html中加多个sheet的方法。

三、生成符合EXCEL的XML规范的XML文件。类似第二种方法,生成后缀名为.xls,内容为xml格式的文本,其中的xml必须完全符合excel xml规范(Excel xml schema/DTD,相关内容在http://msdn.microsoft.com/en-us/library/aa140062(office.10).aspx),否则excel不能打开。采用DataOutStream流的形式,先将xml格式的内容写到服务器文件系统中,再下载。如果数据量大,可分多个sheet。xml格式如下:

<?xml version="1.0" encoding="GBK" ?> 
   <?mso-application progid="Excel.Sheet"?> 
   <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
    xmlns:o="urn:schemas-microsoft-com:office:office" 
    xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
    xmlns:html="http://www.w3.org/TR/REC-html40"> 
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> 
     <Version>11.9999</Version> 
    </DocumentProperties> 
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> 
     <WindowHeight>10005</WindowHeight> 
     <WindowWidth>10005</WindowWidth> 
     <WindowTopX>120</WindowTopX> 
     <WindowTopY>135</WindowTopY> 
     <ActiveSheet>1</ActiveSheet> 
     <ProtectStructure>False</ProtectStructure> 
     <ProtectWindows>False</ProtectWindows> 
    </ExcelWorkbook> 
    <Styles> 
     <Style ss:ID="Default" ss:Name="Normal"> 
      <Alignment ss:Vertical="Center"/> 
      <Borders/> 
      <Font ss:FontName="??ì?" x:CharSet="134" ss:Size="12"/> 
      <Interior/> 
      <NumberFormat/> 
      <Protection/> 
     </Style> 
     <Style ss:ID="s23"> 
      <Font ss:FontName="??ì?" x:CharSet="134" ss:Size="12" ss:Color="#0000FF" 
       ss:Bold="1"/> 
      <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/> 
     </Style> 
    </Styles> 
    <Worksheet ss:Name="Sheet1"> 
     <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" 
      x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="16.5"> 
      <Row ss:AutoFitHeight="0"> 
       <Cell><Data ss:Type="Number">1</Data></Cell> 
       <Cell><Data ss:Type="String">aaa</Data></Cell> 
      </Row> 
      <Row ss:AutoFitHeight="0"> 
       <Cell><Data ss:Type="Number">2</Data></Cell> 
       <Cell><Data ss:Type="String">bbb</Data></Cell> 
      </Row> 
      <Row ss:AutoFitHeight="0"> 
       <Cell><Data ss:Type="Number">3</Data></Cell> 
       <Cell><Data ss:Type="String">ccc</Data></Cell> 
      </Row> 
     </Table> 
     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 
      <Unsynced/> 
      <ProtectObjects>False</ProtectObjects> 
      <ProtectScenarios>False</ProtectScenarios> 
     </WorksheetOptions> 
    </Worksheet> 
    <Worksheet ss:Name="Sheet2"> 
     <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4" x:FullColumns="1" 
      x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"> 
      <Column ss:AutoFitWidth="0" ss:Width="73.5"/> 
      <Column ss:AutoFitWidth="0" ss:Width="119.25"/> 
      <Column ss:AutoFitWidth="0" ss:Width="89.25"/> 
      <Column ss:AutoFitWidth="0" ss:Width="141"/> 
      <Row> 
       <Cell ss:StyleID="s23"><Data ss:Type="String">aaaa</Data></Cell> 
       <Cell ss:StyleID="s23"><Data ss:Type="String">bbbb</Data></Cell> 
       <Cell ss:StyleID="s23"><Data ss:Type="String">cccc</Data></Cell> 
       <Cell ss:StyleID="s23"><Data ss:Type="String">dddd</Data></Cell> 
      </Row> 
      <Row> 
       <Cell><Data ss:Type="String">aaaa</Data></Cell> 
       <Cell><Data ss:Type="String">bbbb</Data></Cell> 
       <Cell><Data ss:Type="String">cccc</Data></Cell> 
       <Cell><Data ss:Type="String">dddd</Data></Cell> 
      </Row> 
      <Row> 
       <Cell><Data ss:Type="String">aaaa</Data></Cell> 
       <Cell><Data ss:Type="String">bbbb</Data></Cell> 
       <Cell><Data ss:Type="String">cccc</Data></Cell> 
       <Cell><Data ss:Type="String">dddd</Data></Cell> 
      </Row> 
      <Row> 
       <Cell><Data ss:Type="String">aaaa</Data></Cell> 
       <Cell><Data ss:Type="String">bbbb</Data></Cell> 
       <Cell><Data ss:Type="String">cccc</Data></Cell> 
       <Cell><Data ss:Type="String">dddd</Data></Cell> 
      </Row> 
     </Table> 
     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 
      <Print> 
       <ValidPrinterInfo/> 
       <PaperSizeIndex>9</PaperSizeIndex> 
       <HorizontalResolution>600</HorizontalResolution> 
       <VerticalResolution>600</VerticalResolution> 
      </Print> 
      <Selected/> 
      <ProtectObjects>False</ProtectObjects> 
      <ProtectScenarios>False</ProtectScenarios> 
     </WorksheetOptions> 
   </Worksheet> 
   <Worksheet ss:Name="Sheet3"> 
    <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" 
     x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="16.5"/> 
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 
     <Unsynced/> 
     <ProtectObjects>False</ProtectObjects> 
     <ProtectScenarios>False</ProtectScenarios> 
    </WorksheetOptions> 
   </Worksheet> 
  </Workbook>

注意:xml头、命名空间必须正确,Workbook为根标记,Worksheet为一个工作表(sheet),Table为工作表中的表,Row 为表中一行,Cell为单元格,Data为单元格中的数据,可以有诸如String、Number等不同格式。Styles类似Html的style,定义之后可以在后面的行、单元格中引用。其他标记和属性,请到查microsoft csdn文档。输出时,不断追加Worksheet、Table、Row、Cell到目标文件中即可。

采用这种方式,由于是采用流输出,不用一次加载到内存中,所以比POI和JXL效率好,而又能生成多个sheet,所以比第二种方法好,可应用来导出大批量数据。

以上几种方式,可根据实际情况采用,另外还有其他xml方式,比如结合xslt、MIME映射等,但是更加复杂,笔者尚未作研究。

分享到:
评论
1 楼 bluseli 2011-09-16  
请问,能否给出源码。非常感谢,我邮箱是yanhuodig@sina.com

相关推荐

Global site tag (gtag.js) - Google Analytics