Asp.net GridView 导出到Excel

最新推荐文章于 2021-08-27 21:02:31 发布

最新推荐文章于 2021-08-27 21:02:31 发布 · 111 阅读

文章标签：

#ui #xhtml #数据库

方法1、

(1.)Default.aspx

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

<!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>Untitled Page</title>
</head>
<body runat="server">
<form id="form1" runat="server">
<div runat="server">

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" AllowPaging="True"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
CellPadding="2" ForeColor="Black" GridLines="None">
<FooterStyle BackColor="Tan" />
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
SortExpression="CustomerID" />
<asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
SortExpression="CompanyName" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName" />
<asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle"
SortExpression="ContactTitle" />
<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="Region" HeaderText="Region"
SortExpression="Region" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode"
SortExpression="PostalCode" />
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
</Columns>
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="导出" />
<br />

</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="Select * from Northwind.dbo.customers"></asp:SqlDataSource>
</form>
</body>
</html>

其中：Web.Config中的数据库设置为：

大家可以根据自己的情况进行设置。

(2.) Default.aspx.cs

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

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataBind();//绑定数据
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
System.Web.HttpContext curContext = System.Web.HttpContext.Current;

Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "UTF-8";//设置字符集
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;//设置编码集

Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.AllowPaging = false;//写到Excel的数据不用分页
BindData();
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());//向客户端写数据
Response.End();
GridView1.AllowPaging = true;//恢复分页
BindData();

}
private void BindData()
{
GridView1.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)//这个方法不写的话可能报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内。
{
// Confirms that an HtmlForm control is rendered for
}

}
方法2：不用重写VerifyRenderingInServerForm方法,Button1_Click不一样，其它的一样。

protected void Button1_Click(object sender, EventArgs e)
{

StringBuildersb=newStringBuilder();
StringWritersw=newStringWriter(sb);
HtmlTextWriterhtw=newHtmlTextWriter(sw);

Pagepage=newPage();
HtmlFormform=newHtmlForm();

GridView1.EnableViewState=false;

page.EnableEventValidation=false;

//RealizalasinicializacionesdelainstanciadelaclasePagequerequieranlosdiseñadoresRAD.
page.DesignerInitialize();

page.Controls.Add(form);

GridView1.AllowPaging = false;//写到Excel的数据不用分页
BindData();
form.Controls.Add(GridView1);

page.RenderControl(htw);

Response.Clear();
Response.Buffer=true;
Response.ContentType="application/vnd.ms-excel";
Response.AddHeader("Content-Disposition","attachment;filename=data.xls");
Response.Charset="UTF-8";
Response.ContentEncoding=Encoding.Default;
Response.Write(sb.ToString());
Response.End();