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

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

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

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

excel.aspx
view plain | print | copy to clipboard | ?
1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel.aspx.cs" Inherits="excel" %>  
2 
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
4 
5<html xmlns="http://www.w3.org/1999/xhtml" >  
6<head runat="server">  
7    <title>未命名頁面</title>  
8</head>  
9<body>  
10    <form id="form1" runat="server">  
11    <div>  
12        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">  
13            <Columns>  
14                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" 
15                    SortExpression="id" />  
16                <asp:BoundField DataField="gender" HeaderText="gender" SortExpression="gender" />  
17            </Columns>  
18        </asp:GridView>  
19        <asp:Button ID="ExportExcel" runat="server" Text="匯出Excel檔/儲存Excel檔" OnClick="ExportExcel_Click" Width="200px" /><br />  
20        <asp:Button ID="ExportWord" runat="server" Text="匯出Word檔/儲存Word檔" OnClick="ExportWord_Click" Width="200px" /><br />  
21        <asp:Button ID="ExportText" runat="server" Text="匯出txt檔/儲存txt檔" Width="200px" OnClick="ExportText_Click" />  
22        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" 
23            SelectCommand="SELECT * FROM [user]"></asp:SqlDataSource>  
24    </div>  
25    </form>  
26</body>  
27</html> 
<%@ 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
view plain | print | copy to clipboard | ?
1using System;  
2using System.Data;  
3using System.Configuration;  
4using System.Collections;  
5using System.Web;  
6using System.Web.Security;  
7using System.Web.UI;  
8using System.Web.UI.WebControls;  
9using System.Web.UI.WebControls.WebParts;  
10using System.Web.UI.HtmlControls;  
11 
12public partial class excel : System.Web.UI.Page  
13{  
14    protected void Page_Load(object sender, EventArgs e)  
15    {  
16 
17    }  
18    protected void ExportExcel_Click(object sender, EventArgs e)  
19    {  
20        //匯出excel檔  
21        Response.Clear();  
22        Response.AddHeader("content-disposition", "attachment;filename=test.xls");//excel檔名  
23        Response.ContentType = "application/vnd.ms-excel";  
24        Response.Charset = "";  
25        System.IO.StringWriter sw = new System.IO.StringWriter();  
26        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);  
27        DataGrid dg = new DataGrid();  
28        dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
29        dg.DataBind();  
30        dg.RenderControl(htw);  
31        Response.Write(sw.ToString());  
32        Response.End();  
33 
34 
35        //儲存excel檔  
36        //System.IO.StringWriter sw = new System.IO.StringWriter();  
37        //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);  
38        //DataGrid dg = new DataGrid();  
39        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
40        //dg.DataBind();  
41        //dg.RenderControl(htw);  
42        //System.IO.File.WriteAllText(@"c:\test.xls", sw.ToString());  
43    }  
44    protected void ExportWord_Click(object sender, EventArgs e)  
45    {  
46        //匯出word檔  
47        Response.Clear();  
48        Response.AddHeader("content-disposition", "attachment;filename=test.doc");//word檔名  
49        Response.ContentType = "application/vnd.ms-word";  
50        Response.Charset = "";  
51        System.IO.StringWriter sw = new System.IO.StringWriter();  
52        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);  
53        DataGrid dg = new DataGrid();  
54        dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
55        dg.DataBind();  
56        dg.RenderControl(htw);  
57        Response.Write(sw.ToString());  
58        Response.End();  
59 
60 
61        //儲存word檔  
62        //System.IO.StringWriter sw = new System.IO.StringWriter();  
63        //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);  
64        //DataGrid dg = new DataGrid();  
65        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
66        //dg.DataBind();  
67        //dg.RenderControl(htw);  
68        //System.IO.File.WriteAllText(@"c:\test.doc", sw.ToString());  
69    }  
70    protected void ExportText_Click(object sender, EventArgs e)  
71    {  
72        //匯出txt檔  
73        Response.Clear();  
74        Response.AddHeader("content-disposition", "attachment;filename=test.txt");//txt檔名  
75        Response.ContentType = "application/vnd.ms-word";  
76        Response.Charset = "";  
77        System.Text.StringBuilder sb = new System.Text.StringBuilder();  
78        DataTable dt = new DataTable();  
79        dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();  
80        //欄位名  
81        for (int i = 0; i < dt.Columns.Count; i++)  
82        {  
83            sb.Append(dt.Columns[i].ColumnName);  
84            sb.Append(",");  
85        }  
86        sb.Append(Environment.NewLine);  
87        //資料  
88        for (int i = 0; i < dt.Rows.Count; i++)  
89        {  
90            for (int j = 0; j < dt.Columns.Count; j++)  
91            {  
92                sb.Append(dt.Rows[i][j].ToString());  
93                sb.Append(",");  
94            }  
95            sb.Append(Environment.NewLine);  
96        }  
97        Response.Write(sb.ToString());  
98        Response.End();  
99 
100 
101        //儲存txt檔  
102        //System.Text.StringBuilder sb = new System.Text.StringBuilder();  
103        //DataTable dt = new DataTable();  
104        //dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();  
105        ////欄位名  
106        //for (int i = 0; i < dt.Columns.Count; i++)  
107        //{  
108        //    sb.Append(dt.Columns[i].ColumnName);  
109        //    sb.Append(",");  
110        //}  
111        //sb.Append(Environment.NewLine);  
112        ////資料  
113        //for (int i = 0; i < dt.Rows.Count; i++)  
114        //{  
115        //    for (int j = 0; j < dt.Columns.Count; j++)  
116        //    {  
117        //        sb.Append(dt.Rows[i][j].ToString());  
118        //        sb.Append(",");  
119        //    }  
120        //    sb.Append(Environment.NewLine);  
121        //}  
122        //System.IO.File.WriteAllText(@"c:\test.txt", sb.ToString());  
123    }  
124}  
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()); } }

執行結果：


參考來源： http://www.codersource.net/published/view/283/exporting_data_grid_to_excel.aspx