`
ansili
  • 浏览: 55039 次
  • 性别: 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文件代码如下:

view plaincopy to clipboardprint?
<%@ 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> 
<%@ 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格式如下:

view plaincopy to clipboardprint?
<?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 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映射等,但是更加复杂,笔者尚未作研究。

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/yrsheng/archive/2009/04/22/4100393.aspx

分享到:
评论

相关推荐

    Java大批量导出数据,格式可以为xml或excel..do

    ### Java大批量导出数据至Excel或XML技术详解 #### 技术背景及应用场景 在企业级应用开发中,经常需要处理大量数据的导出需求,例如报表导出、数据分析等场景。对于这类需求,使用Java进行批量数据导出至Excel或...

    大批量数据导出到XLS

    本主题聚焦于“大批量数据导出到XLS”,这是一个关于如何高效地将大量数据从网页或其他数据源导出到Excel(XLS)文件的过程。XLS是Microsoft Excel的一种文件格式,广泛用于存储和组织表格数据,便于分析和共享。 ...

    以读取XML方式的大批量导入Excel

    这篇博客“以读取XML方式的大批量导入Excel”介绍了一种高效处理Excel数据的方法,特别是当数据量非常大时,传统的API可能无法满足性能需求。XML作为一种结构化的数据格式,能够很好地存储和传输大量数据,因此,...

    java导出大批量(百万以上)数据的excel文件

    通过分批导出数据到多个XML文件,然后再合并为一个压缩包供用户下载,这种方法适合处理大规模数据。 3. **代码实现细节**: - 在Java中,可以使用`DataOutputStream`和`BufferedOutputStream`结合`...

    Excel大批量导入导出解决方案.docx

    本文将详细介绍如何避免这些问题,并提供针对不同Excel文件格式(.xls与.xlsx)的大批量数据导入导出的优化方法。 #### 一次线上问题实例 在一次线上问题中,由于执行了一个大数据量的Excel导出操作,导致服务器...

    excel java中导入导出

    博文链接提供的资源“数据大批量导出(有代码有真相).docx”可能包含具体的示例代码和实践案例,可以作为学习和参考的依据。通过深入研究这些示例,开发者能够更好地掌握如何在Java中高效地进行Excel文件的导入导出。

    sql数据导入到access,和xml等文件中

    2. **直接导入链接**:在Access中可以直接链接到SQL Server数据库,这样Access就像一个前端,直接操作SQL Server的数据,但这种方法需要SQL Server的ODBC驱动支持。 3. **使用VBA脚本**:编写Visual Basic for ...

    报表技术POI&EasyPOI导入导出操作Excel、Word、CSV(讲义、源码、资料).zip

    5. **使用示例**:在Excel的导入导出操作中,EasyPOI可以方便地读取一行数据并映射到Java对象,也可以将Java对象集合一次性写入Excel。对于Word,可以创建模板并替换其中的占位符内容,实现批量生成报告。 6. **...

    利用POI将word转换成html实现在线阅读

    Apache POI是一个强大的开源Java库,它允许开发者读取、写入以及创建Microsoft Office格式的文件,包括Word(.doc)文档。 首先,让我们深入了解Apache POI库。POI项目始于2001年,其目标是提供一个API,让开发者...

    H2数据库讲解(教程)

    "**Java大批量导出数据,格式可以为xml或excel..doc**"会指导你如何利用Java编程接口实现数据的XML或Excel格式导入导出,这对于数据迁移或备份很有帮助。 SQL是操作数据库的语言,"**H2Database_SQL语法.doc**"涵盖...

    内存数据库H2

    《Java大批量导出数据,格式可以为xml或excel》可能讲解了如何使用Java编程语言将数据批量导出为XML或Excel格式,这对于数据交换和分析非常有用。H2提供了API来支持这种操作,使得开发者可以方便地与应用程序集成。 ...

    测试培训教材

    一个好的测试管理工具应该能把以上几个阶段都管理起来。 测试人员每时每刻都在度量别人的工作成果,而测试人员的工作成果又由谁来度量呢?度量的标准和依据是什么呢?软件测试的度量是测试管理必须仔细思考的问题。...

Global site tag (gtag.js) - Google Analytics