ASP.Net2.0:ExportGridViewtoExcel

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

Introduction:

In this article, we will see how to Export an ASP.Net 2.0 GridView to Excel.

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.

The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Step 1: Setup your web page with the Gridview

In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario.

ContactPhone Table Structure:

Column Name

Type

ContactID

Int (Identity)

FName

Varchar(50)

LName

Varchar(50)

ContactPhone

Varchar(20)

Step: The Actual Export

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

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

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

To avoid this error, add the following code:

public override void VerifyRenderingInServerForm(Control control)

{

}

Step : Convert the contents

If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added.

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

Code Listing:

Image: Page Design

Image : Sample in action

Image: Export to Excel button is clicked

Image: GridView contents exported to Excel

ExcelExport.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Contacts Listing</title>

</head>

<body>

<div>

Contacts Listing

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br />

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"

DataSourceID="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">

<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />

<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />

<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />

<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />

<asp:TemplateField HeaderText="Favorites">

<asp:CheckBox ID="CheckBox1" runat="server" />

</ItemTemplate></asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"

DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"

ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"

SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"

UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp