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

2008-03-16

70437
0
ASP.NET
2008-03-17

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

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

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

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

excel.aspx

01 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel.aspx.cs" Inherits="excel" %>
02
03 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
04 <html xmlns="http://www.w3.org/1999/xhtml">
05 <head id="Head1" runat="server">
06     <title>未命名頁面</title>
07 </head>
08 <body>
09     <form id="form1" runat="server">
10         <div>
11             <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
12                 <Columns>
13                     <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
14                         SortExpression="id" />
15                     <asp:BoundField DataField="gender" HeaderText="gender" SortExpression="gender" />
16                 </Columns>
17             </asp:GridView>
18             <asp:Button ID="ExportExcel" runat="server" Text="匯出Excel檔/儲存Excel檔" OnClick="ExportExcel_Click"
19                 Width="200px" /><br />
20             <asp:Button ID="ExportWord" runat="server" Text="匯出Word檔/儲存Word檔" OnClick="ExportWord_Click"
21                 Width="200px" /><br />
22             <asp:Button ID="ExportText" runat="server" Text="匯出txt檔/儲存txt檔" Width="200px" OnClick="ExportText_Click" />
23             <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
24                 SelectCommand="SELECT * FROM [user]"></asp:SqlDataSource>
25         </div>
26     </form>
27 </body>
28 </html>
29

excel.aspx.cs

001

using System;
002

using System.Data;
003

using System.Configuration;
004

using System.Collections;
005

using System.Web;
006

using System.Web.Security;
007

using System.Web.UI;
008

using System.Web.UI.WebControls;
009

using System.Web.UI.WebControls.WebParts;
010

using System.Web.UI.HtmlControls;
011

012

public partial class excel : System.Web.UI.Page
013

{
014

protected void Page_Load(object sender, EventArgs e)
015

{
016

017

}
018

protected void ExportExcel_Click(object sender, EventArgs e)
019

{
020

//匯出excel檔
021

Response.Clear();
022

Response.AddHeader("content-disposition", "attachment;filename=test.xls");//excel檔名
023

Response.ContentType = "application/vnd.ms-excel";
024

Response.Charset = "";
025

System.IO.StringWriter sw = new System.IO.StringWriter();
026

System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
027

DataGrid dg = new DataGrid();
028

dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
029

dg.DataBind();
030

dg.RenderControl(htw);
031

Response.Write(sw.ToString());
032

Response.End();
033

034 //儲存excel檔
035

//System.IO.StringWriter sw = new System.IO.StringWriter();
036

//System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
037

//DataGrid dg = new DataGrid();
038

//dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
039

//dg.DataBind();
040

//dg.RenderControl(htw);
041

//System.IO.File.WriteAllText(@"c:\test.xls", sw.ToString());
042

}
043

protected void ExportWord_Click(object sender, EventArgs e)
044

{
045

//匯出word檔
046

Response.Clear();
047

Response.AddHeader("content-disposition", "attachment;filename=test.doc");//word檔名
048

Response.ContentType = "application/vnd.ms-word";
049

Response.Charset = "";
050

System.IO.StringWriter sw = new System.IO.StringWriter();
051

System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
052

DataGrid dg = new DataGrid();
053

dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
054

dg.DataBind();
055

dg.RenderControl(htw);
056

Response.Write(sw.ToString());
057

Response.End();
058

059 //儲存word檔
060

//System.IO.StringWriter sw = new System.IO.StringWriter();
061

//System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
062

//DataGrid dg = new DataGrid();
063

//dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
064

//dg.DataBind();
065

//dg.RenderControl(htw);
066

//System.IO.File.WriteAllText(@"c:\test.doc", sw.ToString());
067

}
068

protected void ExportText_Click(object sender, EventArgs e)
069

{
070

//匯出txt檔
071

Response.Clear();
072

Response.AddHeader("content-disposition", "attachment;filename=test.txt");//txt檔名
073

Response.ContentType = "application/vnd.ms-word";
074

Response.Charset = "";
075

System.Text.StringBuilder sb = new System.Text.StringBuilder();
076

DataTable dt = new DataTable();
077

dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
078

//欄位名
079

for (int i = 0; i < dt.Columns.Count; i++)
080

{
081

sb.Append(dt.Columns[i].ColumnName);
082

sb.Append(",");
083

}
084

sb.Append(Environment.NewLine);
085

//資料
086

for (int i = 0; i < dt.Rows.Count; i++)
087

{
088

for (int j = 0; j < dt.Columns.Count; j++)
089

{
090

sb.Append(dt.Rows[i][j].ToString());
091

sb.Append(",");
092

}
093

sb.Append(Environment.NewLine);
094

}
095

Response.Write(sb.ToString());
096

Response.End();
097

098 //儲存txt檔
099

//System.Text.StringBuilder sb = new System.Text.StringBuilder();
100

//DataTable dt = new DataTable();
101

//dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
102

////欄位名
103

//for (int i = 0; i < dt.Columns.Count; i++)
104

//{
105

// sb.Append(dt.Columns[i].ColumnName);
106

// sb.Append(",");
107

//}
108

//sb.Append(Environment.NewLine);
109

////資料
110

//for (int i = 0; i < dt.Rows.Count; i++)
111

//{
112

// for (int j = 0; j < dt.Columns.Count; j++)
113

// {
114

// sb.Append(dt.Rows[i][j].ToString());
115

// sb.Append(",");
116

// }
117

// sb.Append(Environment.NewLine);
118

//}
119

//System.IO.File.WriteAllText(@"c:\test.txt", sb.ToString());
120

}
121

}

執行結果：

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

回首頁