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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Contacts Listing</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<strong><span style="font-size: small; font-family: Arial; text-decoration: underline">
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">
<Columns>
<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">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate></asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</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">
<InsertParameters>
<asp:Parameter Name="FName" Type="String" />
<asp:Parameter Name="LName" Type="String" />
<asp:Parameter Name="ContactPhone" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="FName" Type="String" />
<asp:Parameter Name="LName" Type="String" />
<asp:Parameter Name="ContactPhone" Type="String" />
<asp:Parameter Name="ContactID" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="ContactID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
<br />
</div>
</form>
</body>
</html>
ExcelExport.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
public partial class DeleteConfirm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//Export the GridView to Excel
PrepareGridViewForExport(GridView1);
ExportGridView();
}
private void ExportGridView()
{
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();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
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]);
}
}
}
}
Implementation Options:
In
quite a few cases, developers face an error in the Export functionality
- typically the error message is "RegisterForEventValidation can only
be called during Render();".
Our
website readers have contributed some good suggestions in the article
comments below. I would particularly like to highlight the suggestion
by Marianna, who provides an alternative implementation to the
VerifyRenderingInServerForm override. This approach is described below: