ASP.Net 2.0: Export GridView to Excel
【原文见： http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx】
Author: Dipal Choksi
Translator: SPARON
T-Blog: http://sparon.cnblogs.com
T-MSN: ZhaoKeYong@hotmail.com
本文描述了在ASP.NET 2.0中如何将GridView 导出为 Excel
简介：
在本文中我们将具体介绍如何将ASP.Net 2.0下的GridView导出为Excel.
本文的焦点是Gridview导为Excel功能和它的数据绑定只出口示范功能.
本文代码可以用来导出为Excel的功能但不局限于这个部分，还可以在很多项目中使用。
Step 1: Setup your web page with the Gridview
这里我假定你满足：在一个页面中有个名为GridView1的GridView。在GridView中我们绑定了一个名为ContactPhone的SQL数据库。接下来的代码就是如何将GridView导出为Excel并且不依赖于具体的数据绑定还具有场景自改变能力。
ContactPhone Table Structure:
|
Column Name
|
Type
|
|
ContactID
|
Int (Identity)
|
|
FName
|
Varchar(50)
|
|
LName
|
Varchar(50)
|
|
ContactPhone
|
Varchar(20)
|
Step: The Actual Export
这段代码是直接输出为Excel的，你也可以改变content-disposition和ContentType以输出不同的类型。
1
string attachment = "attachment; filename=Contacts.xls";
2
3Response.ClearContent();
4
5Response.AddHeader("content-disposition", attachment);
6
7Response.ContentType = "application/ms-excel";
8
9StringWriter sw = new StringWriter();
10
11HtmlTextWriter htw = new HtmlTextWriter(sw);
12
13GridView1.RenderControl(htw);
14
15Response.Write(sw.ToString());
16
17Response.End();
18
19
如果你运行以上代码，将返回一个HttpException：
'GridView1'是一个类型为'GridView'的控件，必须为其添加一个runat=server标记.
为避免这个错误，我们添加以下代码：
1public override void VerifyRenderingInServerForm(Control control)
2
3
{
4
5
}
6
Step : Convert the contents
如果GridView中有其它控件，比如Checkboxes，Dropdownlists，我们需要将它转换为其相关的值，以下递归就用于导出Excel前的准备工作,将各类控件转换为其相关值.
1private void PrepareGridViewForExport(Control gv)
2
3{
4
5 LinkButton lb = new LinkButton();
6
7 Literal l = new Literal();
8
9 string name = String.Empty;
10
11 for (int i = 0; i < gv.Controls.Count; i++)
12
13
{
14
15 if (gv.Controls[i].GetType() == typeof(LinkButton))
16
17 {
18
19 l.Text = (gv.Controls[i] as LinkButton).Text;
20
21 gv.Controls.Remove(gv.Controls[i]);
22
23 gv.Controls.AddAt(i, l);
24
25
}
26
27 else if (gv.Controls[i].GetType() == typeof(DropDownList))
28
29 {
30
31 l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
32
33 gv.Controls.Remove(gv.Controls[i]);
34
35 gv.Controls.AddAt(i, l);
36
37 }
38
39 else if (gv.Controls[i].GetType() == typeof(CheckBox))
40
41 {
42
43 l.Text = (gv.Controls[i] as CheckBox).Checked? "True" : "False";
44
45 gv.Controls.Remove(gv.Controls[i]);
46
47 gv.Controls.AddAt(i, l);
48
49 }
50
51 if (gv.Controls[i].HasControls())
52
53 {
54
55 PrepareGridViewForExport(gv.Controls[i]);
56
57 }
58
59}
60
61
Code Listing:
Image: Page Design
Image : Sample in action
Image: Export to Excel button is clicked
Image: GridView contents exported to Excel
ExcelExport.aspx
1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %>
2
3
4
5<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6
7
8
9<html xmlns="http://www.w3.org/1999/xhtml" >
10
11<head runat="server">
12
13<title>Contacts Listing</title>
14
15</head>
16
17<body>
18
19<form id="form1" runat="server">
20
21<div>
22
23<strong><span style="font-size: small; font-family: Arial; text-decoration: underline">
24
25Contacts Listing
26
27 <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br />
28
29<br />
30
31<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"
32
33DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">
34
35<Columns>
36
37<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />
38
39<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />
40
41<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />
42
43<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />
44
45<asp:TemplateField HeaderText="Favorites">
46
47<ItemTemplate>
48
49
50
51 <asp:CheckBox ID="CheckBox1" runat="server" />
52
53</ItemTemplate></asp:TemplateField>
54
55</Columns>
56
57<FooterStyle BackColor="#CCCC99" />
58
59<RowStyle BackColor="#F7F7DE" />
60
61<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
62
63<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
64
65<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
66
67<AlternatingRowStyle BackColor="White" />
68
69</asp:GridView>
70
71
72
73<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"
74
75DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"
76
77ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"
78
79SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"
80
81UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">
82
83<InsertParameters>
84
85<asp:Parameter Name="FName" Type="String" />
86
87<asp:Parameter Name="LName" Type="String" />
88
89<asp:Parameter Name="ContactPhone" Type="String" />
90
91</InsertParameters>
92
93<UpdateParameters>
94
95<asp:Parameter Name="FName" Type="String" />
96
97<asp:Parameter Name="LName" Type="String" />
98
99<asp:Parameter Name="ContactPhone" Type="String" />
100
101<asp:Parameter Name="ContactID" Type="Int32" />
102
103</UpdateParameters>
104
105<DeleteParameters>
106
107<asp:Parameter Name="ContactID" Type="Int32" />
108
109</DeleteParameters>
110
111</asp:SqlDataSource>
112
113
114
115<br />
116
117</div>
118
119</form>
120
121</body>
122
123</html>
124
125
ExcelExport.aspx.cs
1using System;
2
3using System.Data;
4
5using System.Configuration;
6
7using System.Collections;
8
9using System.Web;
10
11using System.Web.Security;
12
13using System.Web.UI;
14
15using System.Web.UI.WebControls;
16
17using System.Web.UI.WebControls.WebParts;
18
19using System.Web.UI.HtmlControls;
20
21using System.Text;
22
23using System.IO;
24
25
26
27public partial class DeleteConfirm : System.Web.UI.Page
28
29{
30
31
32
33 protected void Page_Load(object sender, EventArgs e)
34
35 {
36
37 }
38
39
40
41 protected void Button1_Click(object sender, EventArgs e)
42
43 {
44
45 //Export the GridView to Excel
46
47 PrepareGridViewForExport(GridView1);
48
49 ExportGridView();
50
51 }
52
53
54
55 private void ExportGridView()
56
57 {
58
59 string attachment = "attachment; filename=Contacts.xls";
60
61 Response.ClearContent();
62
63 Response.AddHeader("content-disposition", attachment);
64
65 Response.ContentType = "application/ms-excel";
66
67 StringWriter sw = new StringWriter();
68
69 HtmlTextWriter htw = new HtmlTextWriter(sw);
70
71 GridView1.RenderControl(htw);
72
73 Response.Write(sw.ToString());
74
75 Response.End();
76
77 }
78
79
80
81 public override void VerifyRenderingInServerForm(Control control)
82
83 {
84
85 }
86
87
88
89 private void PrepareGridViewForExport(Control gv)
90
91 {
92
93 LinkButton lb = new LinkButton();
94
95 Literal l = new Literal();
96
97 string name = String.Empty;
98
99 for (int i = 0; i < gv.Controls.Count; i++)
100
101 {
102
103 if (gv.Controls[i].GetType() == typeof(LinkButton))
104
105 {
106
107 l.Text = (gv.Controls[i] as LinkButton).Text;
108
109 gv.Controls.Remove(gv.Controls[i]);
110
111 gv.Controls.AddAt(i, l);
112
113 }
114
115 else if (gv.Controls[i].GetType() == typeof(DropDownList))
116
117 {
118
119 l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
120
121 gv.Controls.Remove(gv.Controls[i]);
122
123 gv.Controls.AddAt(i, l);
124
125 }
126
127 else if (gv.Controls[i].GetType() == typeof(CheckBox))
128
129 {
130
131 l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
132
133 gv.Controls.Remove(gv.Controls[i]);
134
135 gv.Controls.AddAt(i, l);
136
137 }
138
139 if (gv.Controls[i].HasControls())
140
141 {
142
143 PrepareGridViewForExport(gv.Controls[i]);
144
145 }
146
147 }
148
149 }
150
151}
152
153
Implementation Options:
通常情况,在输出函数中开发人员都会面临一个错误,典型的就是"RegisterForEventValidation can only be called during Render();"
访问者通常会在评论中提出一些好的建议.我特别要强调的是开发者需要重写VerifyRenderingInServerForm方法,该方法描述如下:
- Step 1:实现导出功能的上述功能.
- Step 2:重写一个VerifyRenderingInServerForm的空方法.
- Step 3:修改ExportGridView函数,在绿色高亮代码部创建HtmlForm【原句为:The code highlighted in green creates and HtmlForm on the fly,在翻译HtmlForm on the fly时遇到了一些困难,故on the fly未翻译,请各位高手指教】,在导出girdview之前，添加gridview 到新的form并且render它（取代原来的render实现）
1private void ExportGridView()
2
3{
4
5 string attachment = "attachment; filename=Contacts.xls";
6
7 Response.ClearContent();
8
9 Response.AddHeader("content-disposition", attachment);
10
11 Response.ContentType = "application/ms-excel";
12
13 StringWriter sw = new StringWriter();
14
15 HtmlTextWriter htw = new HtmlTextWriter(sw);
16
17
18
19 // Create a form to contain the grid
20
21 HtmlForm frm = new HtmlForm();
22
23 GridView1.Parent.Controls.Add(frm);
24
25 frm.Attributes["runat"] = "server";
26
27 frm.Controls.Add(GridView1);
28
29
30
31 frm.RenderControl(htw);
32
33 //GridView1.RenderControl(htw);
34
35 Response.Write(sw.ToString());
36
37 Response.End();
38
39}
40
41
这样实施有个优势,就是可将其设置为复用代码类库,不用每次去复写基类的方法.
Note to readers:
Thank you for your comments and feedback! Happy coding!!!
ASP.Net 2.0: Export GridView to Excel - Part II
该文中将会在导出Excel 时GridView引入Hyperlink列,以至于需要使用更多的反射来重新设计原来的逻辑.
