ASP.NET 2.0中将 GridView 导出到 Excel 文件中

下面代码实现将 GridView 导出到 Excel文件中。

值得注意的是VerifyRenderingInServerForm重载方法：

MSDN上的 VerifyRenderingInServerForm 方法的描述：

必须位于 <form runat=server> 标记中的控件可以在呈现之前调用此方法，以便在控件被置于标记外时显示错误信息。发送回或依赖于注册的脚本块的控件应该在 Control.Render 方法的重写中调用此方法。呈现服务器窗体元素的方式不同的页可以重写此方法以在不同的条件下引发异常。

如果回发或使用客户端脚本的服务器控件没有包含在 HtmlForm 服务器控件 (<form runat="server">) 标记中，它们将无法正常工作。这些控件可以在呈现时调用该方法，以在它们没有包含在 HtmlForm 控件中时提供明确的错误信息。

开发自定义服务器控件时，通常在为任何类型的输入标记重写 Render 方法时调用该方法。这在输入控件调用 GetPostBackEventReference 或发出客户端脚本时尤其重要。复合服务器控件不需要作出此调用。

没有这个方法，程序将报错。

C# 代码

<%...@ Page Language="C#" EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

ICollection CreateDataSource( )

...{

System.Data.DataTable dt = new System.Data.DataTable();

System.Data.DataRow dr;

dt.Columns.Add(new System.Data.DataColumn("id", typeof(Int32)));

dt.Columns.Add(new System.Data.DataColumn("PkID", typeof(string)));

dt.Columns.Add(new System.Data.DataColumn("Title", typeof(string)));

for (int i = 0; i < 6; i++)

...{

dr = dt.NewRow();

dr[0] = i;

dr[1] = "123456789123456789123456789";

dr[2] = "<a href='http://dotnet.aspx.cc/'>欢迎光临【孟宪会之精彩世界】</a>";

dt.Rows.Add(dr);

}

System.Data.DataView dv = new System.Data.DataView(dt);

return dv;

}

protected void Page_Load( object sender, EventArgs e )

...{

if (!IsPostBack)

...{

GridView1.BorderWidth = Unit.Pixel(2);

GridView1.BorderColor = System.Drawing.Color.DarkOrange;

GridView1.DataSource = CreateDataSource();

GridView1.DataBind();

}

protected void Button1_Click( object sender, System.EventArgs e )

...{

Response.Clear();

Response.Buffer = true;

Response.Charset = "GB2312";

Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");

// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！

Response.ContentEncoding = System.Text.Encoding.UTF7;

Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.GridView1.RenderControl(oHtmlTextWriter);

Response.Output.Write(oStringWriter.ToString());

Response.Flush();

Response.End();

}

public override void VerifyRenderingInServerForm( Control control )

...{ }

protected void GridView1_RowDataBound( object sender, GridViewRowEventArgs e )

...{

if (e.Row.RowType == DataControlRowType.DataRow)

...{

e.Row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");

}

</script>

<title>将 GridView 导出到 Excel 文件中</title>

</head>

<body>

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound"

AutoGenerateColumns="false">

<asp:BoundField HeaderText="序号" DataField="id" />

<asp:BoundField HeaderText="身份证号" DataField="PkID" />

<asp:BoundField HeaderText="网址" DataField="Title" ReadOnly="true" HtmlEncode="false" />

</Columns>

</asp:GridView>

<asp:Literal ID="HiddenOut" runat="server" />

<asp:Button ID="Button1" runat="server" Text="导出" OnClick="Button1_Click" />

</form>

</body>

</html>

VB.NET 代码

<%...@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Function CreateDataSource() As ICollection

Dim dt As System.Data.DataTable = New System.Data.DataTable

Dim dr As System.Data.DataRow

dt.Columns.Add(New System.Data.DataColumn("id", GetType(Int32)))

dt.Columns.Add(New System.Data.DataColumn("PkID", GetType(String)))

dt.Columns.Add(New System.Data.DataColumn("Title", GetType(String)))

Dim i As Integer = 0

While i < 6

dr = dt.NewRow

dr(0) = i

dr(1) = "123456789123456789123456789"

dr(2) = "<a href='http://dotnet.aspx.cc/'>欢迎光临【孟宪会之精彩世界】</a>"

dt.Rows.Add(dr)

System.Math.Min(System.Threading.Interlocked.Increment(i),i-1)

End While

Dim dv As System.Data.DataView = New System.Data.DataView(dt)

Return dv

End Function

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

If Not IsPostBack Then

GridView1.BorderWidth = Unit.Pixel(2)

GridView1.BorderColor = System.Drawing.Color.DarkOrange

GridView1.DataSource = CreateDataSource

GridView1.DataBind

End If

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Response.Clear

Response.Buffer = True

Response.Charset = "GB2312"

Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls")

Response.ContentEncoding = System.Text.Encoding.UTF7

Response.ContentType = "application/ms-excel"

Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter

Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

Me.GridView1.RenderControl(oHtmlTextWriter)

Response.Output.Write(oStringWriter.ToString)

Response.Flush

Response.End

End Sub

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then

e.Row.Cells(1).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")

End If

End Sub

</script>

<title>将 GridView 导出到 Excel 文件中</title>

</head>

<body>

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound"

AutoGenerateColumns="false">

<asp:BoundField HeaderText="序号" DataField="id" />

<asp:BoundField HeaderText="身份证号" DataField="PkID" />

<asp:BoundField HeaderText="网址" DataField="Title" ReadOnly="true" HtmlEncode="false" />

</Columns>

</asp:GridView>

<asp:Literal ID="HiddenOut" runat="server" />

<asp:Button ID="Button1" runat="server" Text="导出" OnClick="Button1_Click" />

</form>

</body>

</html>