使用ASP.NET的GridView滙出/儲存資料至Excel,Word,Txt檔

最近剛好有人討論到這個問題...

小弟到網路上找了一下資料...其實還滿多資料的...

小弟做了一個簡單的範例..如何由GridView匯出資料至excel,word,txt

ps.註解的部分屬於儲存檔案的程式碼

excel.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel.aspx.cs" Inherits="excel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>未命名頁面</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                    SortExpression="id" />
                <asp:BoundField DataField="gender" HeaderText="gender" SortExpression="gender" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="ExportExcel" runat="server" Text="匯出Excel檔/儲存Excel檔" OnClick="ExportExcel_Click" Width="200px" /><br />
        <asp:Button ID="ExportWord" runat="server" Text="匯出Word檔/儲存Word檔" OnClick="ExportWord_Click" Width="200px" /><br />
        <asp:Button ID="ExportText" runat="server" Text="匯出txt檔/儲存txt檔" Width="200px" OnClick="ExportText_Click" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
            SelectCommand="SELECT * FROM [user]"></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

excel.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

}
    protected void ExportExcel_Click(object sender, EventArgs e)
    {
        //匯出excel檔
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=test.xls");//excel檔名
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        DataGrid dg = new DataGrid();
        dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        dg.DataBind();
        dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

//儲存excel檔
        //System.IO.StringWriter sw = new System.IO.StringWriter();
        //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        //DataGrid dg = new DataGrid();
        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        //dg.DataBind();
        //dg.RenderControl(htw);
        //System.IO.File.WriteAllText(@"c:\test.xls", sw.ToString());
    }
    protected void ExportWord_Click(object sender, EventArgs e)
    {
        //匯出word檔
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=test.doc");//word檔名
        Response.ContentType = "application/vnd.ms-word";
        Response.Charset = "";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        DataGrid dg = new DataGrid();
        dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        dg.DataBind();
        dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

//儲存word檔
        //System.IO.StringWriter sw = new System.IO.StringWriter();
        //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        //DataGrid dg = new DataGrid();
        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        //dg.DataBind();
        //dg.RenderControl(htw);
        //System.IO.File.WriteAllText(@"c:\test.doc", sw.ToString());
    }
    protected void ExportText_Click(object sender, EventArgs e)
    {
        //匯出txt檔
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=test.txt");//txt檔名
        Response.ContentType = "application/vnd.ms-word";
        Response.Charset = "";
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        DataTable dt = new DataTable();
        dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
        //欄位名
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append(dt.Columns[i].ColumnName);
            sb.Append(",");
        }
        sb.Append(Environment.NewLine);
        //資料
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                sb.Append(dt.Rows[i][j].ToString());
                sb.Append(",");
            }
            sb.Append(Environment.NewLine);
        }
        Response.Write(sb.ToString());
        Response.End();

//儲存txt檔
        //System.Text.StringBuilder sb = new System.Text.StringBuilder();
        //DataTable dt = new DataTable();
        //dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
        ////欄位名
        //for (int i = 0; i < dt.Columns.Count; i++)
        //{
        //    sb.Append(dt.Columns[i].ColumnName);
        //    sb.Append(",");
        //}
        //sb.Append(Environment.NewLine);
        ////資料
        //for (int i = 0; i < dt.Rows.Count; i++)
        //{
        //    for (int j = 0; j < dt.Columns.Count; j++)
        //    {
        //        sb.Append(dt.Rows[i][j].ToString());
        //        sb.Append(",");
        //    }
        //    sb.Append(Environment.NewLine);
        //}
        //System.IO.File.WriteAllText(@"c:\test.txt", sb.ToString());
    }
}

執行結果：