2016-03-03
這種方式是把 GridView 內容以HTML方式輸出,賦予檔案型態 ContentType = "application/vnd.ms-excel",並設定附檔名 .xls,偽裝為 Excel 檔案的方法。
在伺服器上登錄 2007 Office system 檔案格式 MIME 類型
https://technet.microsoft.com/zh-tw/library/ee309278(v=office.12).aspx
優點是電腦不需要安裝 Excel 商業版,也不需要 3rd Party 元件。
缺點是 Excel 2007 或更新版本開啟時候會出現錯誤訊息(如下),Excel 2003則正常,因為新版本 對檔案格式檢查較嚴格
您正在嘗試開啟 'XXXX.xls',其檔案格式與副檔名所指定的格式不同,開啟檔案前,請確認檔案未損毀,且來自信任的來源
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication3.Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" SelectCommand="SELECT * FROM [TestTable]"></asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="no" HeaderText="no" InsertVisible="False" ReadOnly="True" SortExpression="no" /> <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> <asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" /> </Columns> </asp:GridView> <asp:Button ID="Button4" runat="server" Text="Button" OnClick="Button4_Click" /><br /> </div> </form> </body> </html> |
Default.aspx.cs
using System; using System.Web.UI.HtmlControls; using System.IO; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication3 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } // Office 2007 File Format MIME Types for HTTP Content Streaming // http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx protected void Button4_Click(object sender, EventArgs e) { ExportGridViewToExcelXls(GridView1); } private void ExportGridViewToExcelXls(GridView gridViewExport) { //string fileName = "匯出檔名" + DateTime.Now + ".xls"; string fileName = "匯出檔名"; HtmlForm form = new HtmlForm(); Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls\""); // Office 2007 File Format MIME Types for HTTP Content Streaming // http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx Response.ContentType = "application/vnd.ms-excel"; Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>"); StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); form.Attributes["runat"] = "server"; //form.Controls.Add(gridViewExport); Boolean oldAllowPaging = gridViewExport.AllowPaging; Boolean oldAllowSorting = gridViewExport.AllowSorting; gridViewExport.AllowPaging = false; // 不可有分頁,否則只會列出目前分頁的內容 gridViewExport.AllowSorting = false; // 有排序標題列不好看 gridViewExport.DataBind(); // 不重新 DataBiind,標題列依然有排序底線 form.Controls.Add(gridViewExport); this.Controls.Add(form); // form.RenderControl(hw); 若出現錯誤訊息:RegisterForEventValidation 只能在 Render(); 期間呼叫 // 解決:把 gridViewExport 放在 <form runat=server></form> 之間 // 或將 EnableEventValidation = "false" AutoEventWireup="true" 這兩個屬性加到 <%page %> 標籤裡。 // 您正在嘗試開啟 'XXXX.xls',其檔案格式與副檔名所指定的格式不同,開啟檔案前,請確認檔案未損毀,且來自信任的來源 // Office Excel 2007 開啟時才會出現,Office Excel 2003以下的版本卻是沒有這個對話框! // 因為新版本 對檔案格式檢查較嚴格 form.RenderControl(hw); string style = @"<style> .textmode { mso-number-format:\@;}</style>"; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); gridViewExport.AllowPaging = oldAllowPaging; gridViewExport.AllowSorting = oldAllowSorting; } } } |
檢視匯出的 .xls 檔案內容,可以看到其實是 HTML 內容,不是真正的 .xls 內容。
<meta http-equiv=Content-Type content=text/html;charset=utf-8><style> .textmode { mso-number-format:\@;}</style><form method="post" action="./Default.aspx" id="ctl03" runat="server"> <div class="aspNetHidden"> <input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="" /> </div> <div> <table cellspacing="0" rules="all" border="1" id="GridView1" style="border-collapse:collapse;"> <tr> <th scope="col">no</th><th scope="col">myname</th><th scope="col">addr</th> </tr><tr> <td>1</td><td>趙 </td><td> </td> </tr><tr> <td>2</td><td>錢 </td><td> </td> </tr><tr> <td>3</td><td>孫 </td><td> </td> </tr><tr> <td>4</td><td>李 </td><td> </td> </tr><tr> <td>5</td><td>周 </td><td> </td> </tr><tr> <td>6</td><td>吳 </td><td> </td> </tr><tr> <td>7</td><td>鄭 </td><td> </td> </tr><tr> <td>8</td><td>王 </td><td> </td> </tr><tr> <td>9</td><td>陳 </td><td> </td> </tr><tr> <td>10</td><td>劉 </td><td> </td> </tr><tr> <td>11</td><td>林 </td><td> </td> </tr><tr> <td>12</td><td>游 </td><td> </td> </tr><tr> <td>13</td><td>張 </td><td> </td> </tr><tr> <td>14</td><td>周 </td><td> </td> </tr> </table> </div></form> |
(完)
沒有留言:
張貼留言