2016-03-03
這種方式是把 GridView 內容以HTML方式輸出,賦予檔案型態 ContentType = "application/text",並設定附檔名 .csv,偽裝為 Excel (.csv) 檔案的方法。
優點是 csv 是純文字檔案,不需要特別軟體就可開啟。(不過輸出時候要注意字元編碼)
缺點是 .csv 檔案使用逗號去分隔欄位,原先資料庫欄位中的資料,如果包含1個逗號,.csv 檔案開啟時候會被判斷成2個欄位;如果包含換行字元,會被斷成另一筆資料。
GridViewExportToCSVDemo.aspx 內容
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewExportToCSVDemo.aspx.cs" Inherits="WebApplication3.GridViewExportToCSVDemo" %> <!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:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.;Initial Catalog=TestDB;User ID=sa; Password=P@ssw0rd" SelectCommand="SELECT * FROM [TestTable]"></asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:TemplateField HeaderText="流水編號" > <ItemTemplate> <asp:Label ID="Label1" runat="server"> <%# Container.DataItemIndex + 1 %> </asp:Label> </ItemTemplate> </asp:TemplateField> <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="Button5" runat="server" Text="Button" OnClick="Button5_Click" /> </div> </form> </body> </html> |
GridViewExportToCSVDemo.aspx.cs 內容
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication3 { public partial class GridViewExportToCSVDemo : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button5_Click(object sender, EventArgs e) { GridViewExporttoCSV(GridView1); } protected void GridViewExporttoCSV(GridView gridViewExport) { string fileName = "匯出檔名"; Response.Clear(); Response.Buffer = true; //Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv"); Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv\""); //Response.Charset = "utf-8"; // 失敗 Response.Charset = "Big5"; Response.ContentEncoding = Encoding.GetEncoding(950);//950就是所謂的BIG5 // 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/text"; //Response.Write("<meta http-equiv=Content-Type content=application/text;charset=utf-8>"); gridViewExport.AllowPaging = false; gridViewExport.DataBind(); StringBuilder sb = new StringBuilder(); string celltext = ""; // GridView1 第一欄是 Container.DataItemInde+1 流水號,不會輸出 // 所以下面迴圈改為 k=1 開始 //for (int k = 0; k < gridViewExport.Columns.Count; k++) for (int k = 1; k < gridViewExport.Columns.Count; k++) { //add separator sb.Append(gridViewExport.Columns[k].HeaderText + ','); } //append new line sb.Append("\r\n"); // GridView1 第一欄是 Container.DataItemInde+1 流水號,不會輸出 // 所以下面迴圈改為 k=1 開始 for (int i = 0; i < gridViewExport.Rows.Count; i++) { //for (int k = 0; k < gridViewExport.Columns.Count; k++) for (int k = 1; k < gridViewExport.Columns.Count; k++) { //add separator // sb.Append(gridViewExport.Rows[i].Cells[k].Text + ','); celltext = gridViewExport.Rows[i].Cells[k].Text; if (celltext == " ") { sb.Append("" + ','); } else { sb.Append(celltext + ','); } } //append new line sb.Append("\r\n"); } Response.Output.Write(sb.ToString()); Response.Flush(); Response.End(); // Prevent Empty Gridview data from populating “ ” into textbox // There is no need to write any code, just add HtmlEncode="false" to the Boundfield. // 不存在 //<asp:BoundField DataField="EmployeeName" HeaderText="Name" NullDisplayText=" "/> //無效 } } } |
(完)
沒有留言:
張貼留言