筆者在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檔案代碼如下：
<%@ 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到目標檔案中即可。
ss:ExpandedRowCount="3" 特別注意，WPS中不用管，Office中必須和當前sheet中的行數相同。從1開始數，可以超出。
採用這種方式，由於是採用流輸出，不用一次載入到記憶體中，所以比POI和JXL效率好，而又能產生多個sheet，所以比第二種方法好，可應用來匯出大批量資料。
以上幾種方式，可根據實際情況採用，另外還有其他xml方式，比如結合xslt、MIME映射等，但是更加複雜，筆者尚未作研究。
轉自：http://blog.csdn.net/yrsheng/article/details/4100393
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
