.
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
Response.ContentType = "application/excel";
StringWriter sWriter = new StringWriter();
HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
GridView1.RenderControl(hTextWriter);
Response.Write(sWriter.ToString());
Response.End();
But when we try to execute this code on we get this httpexception error.
to get past this error we can write this method in code behind.
public override void VerifyRenderingInServerForm(Control control)
{
}
or we can add a html form and render it after adding gridview in it, i'll be using this.
If paging is enabled or Gridview contains controls like linkbutton, DropDownLists or checkboxes etc then we get this error.
we can fix this error by setting event validation property to false in page directive.
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Hyperlinks or other controls in gridview are not desireable in excel sheet, we should display their display text instead, for this write a method to remove controls and display their respective text property as mentioned below.
private void ChangeControlsToValue(Control gridView)
{
Literal literal = new Literal();
for (int i = 0; i < gridView.Controls.Count; i++)
{
if (gridView.Controls[i].GetType() == typeof(LinkButton))
{
literal.Text = (gridView.Controls[i] as LinkButton).Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(DropDownList))
{
literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(CheckBox))
{
literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
if (gridView.Controls[i].HasControls())
{
ChangeControlsToValue(gridView.Controls[i]);
}
}
}
HTML SOURCE
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
DataSourceID="sqlDataSourceGridView"
AutoGenerateColumns="False"
CssClass="GridViewStyle"
GridLines="None" Width="650px"
ShowHeader="False">
<Columns>
<asp:TemplateField HeaderText="Customer ID" ItemStyle-Width="75px">
<ItemTemplate>
<asp:LinkButton ID="lButton" runat="server" Text='<%#Eval("CustomerID") %>'
PostBackUrl="~/Default.aspx">
</asp:LinkButton>
</ItemTemplate>
<ItemStyle Width="75px"></ItemStyle>
</asp:TemplateField>
<asp:BoundField DataField="CompanyName" HeaderText="Company"
ItemStyle-Width="200px" >
<ItemStyle Width="200px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="ContactName" HeaderText="Name"
ItemStyle-Width="125px">
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="City" HeaderText="city" ItemStyle-Width="125px" >
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="Country" HeaderText="Country"
ItemStyle-Width="125px" >
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
</Columns>
<RowStyle CssClass="RowStyle" />
<PagerStyle CssClass="PagerStyle" />
<SelectedRowStyle CssClass="SelectedRowStyle" />
<HeaderStyle CssClass="HeaderStyle" />
<AlternatingRowStyle CssClass="AltRowStyle" />
</asp:GridView>
<asp:SqlDataSource ID="sqlDataSourceGridView" runat="server"
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>"
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName],
[City], [Country] FROM [Customers]">
</asp:SqlDataSource>
<table align="left" class="style1">
<tr>
<td class="style2">
<asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
RepeatDirection="Horizontal" RepeatLayout="Flow">
<asp:ListItem Value="0">All Pages</asp:ListItem>
</asp:RadioButtonList>
</td>
<td>
<asp:Button ID="btnExportToExcel" runat="server" Text="Export To Excel"
Width="215px" onclick="btnExportToExcel_Click"/>
</td>
</tr>
</table>
C# CODE
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
GridView1.ShowHeader = true;
GridView1.GridLines = GridLines.Both;
GridView1.AllowPaging = false;
GridView1.DataBind();
}
else
{
GridView1.ShowHeader = true;
GridView1.GridLines = GridLines.Both;
GridView1.PagerSettings.Visible = false;
GridView1.DataBind();
}
ChangeControlsToValue(GridView1);
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
Response.ContentType = "application/excel";
StringWriter sWriter = new StringWriter();
HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
HtmlForm hForm = new HtmlForm();
GridView1.Parent.Controls.Add(hForm);
hForm.Attributes["runat"] = "server";
hForm.Controls.Add(GridView1);
hForm.RenderControl(hTextWriter);
// Write below code to add cell border to empty cells in Excel file
// If we don't add this line then empty cells will be shown as blank white space
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");
sBuilder.Append(sWriter + "</body></html>");
Response.Write(sBuilder.ToString());
Response.End();
}
private void ChangeControlsToValue(Control gridView)
{
Literal literal = new Literal();
for (int i = 0; i < gridView.Controls.Count; i++)
{
if (gridView.Controls[i].GetType() == typeof(LinkButton))
{
literal.Text = (gridView.Controls[i] as LinkButton).Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(DropDownList))
{
literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(CheckBox))
{
literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
if (gridView.Controls[i].HasControls())
{
ChangeControlsToValue(gridView.Controls[i]);
}
}
}
VB.NET
Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)
If RadioButtonList1.SelectedIndex = 0 Then
GridView1.ShowHeader = True
GridView1.GridLines = GridLines.Both
GridView1.AllowPaging = False
GridView1.DataBind()
Else
GridView1.ShowHeader = True
GridView1.GridLines = GridLines.Both
GridView1.PagerSettings.Visible = False
GridView1.DataBind()
End If
ChangeControlsToValue(GridView1)
Response.ClearContent()
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls")
Response.ContentType = "application/excel"
Dim sWriter As New StringWriter()
Dim hTextWriter As New HtmlTextWriter(sWriter)
Dim hForm As New HtmlForm()
GridView1.Parent.Controls.Add(hForm)
hForm.Attributes("runat") = "server"
hForm.Controls.Add(GridView1)
hForm.RenderControl(hTextWriter)
Dim sBuilder As New StringBuilder()
sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>")
sBuilder.Append(sWriter & "</body></html>")
Response.Write(sBuilder.ToString())
Response.[End]()
End Sub
Private Sub ChangeControlsToValue(gridView As Control)
Dim literal As New Literal()
For i As Integer = 0 To gridView.Controls.Count - 1
If gridView.Controls(i).[GetType]() = GetType(LinkButton) Then
literal.Text = TryCast(gridView.Controls(i), LinkButton).Text
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
ElseIf gridView.Controls(i).[GetType]() = GetType(DropDownList) Then
literal.Text = TryCast(gridView.Controls(i), DropDownList).SelectedItem.Text
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
ElseIf gridView.Controls(i).[GetType]() = GetType(CheckBox) Then
literal.Text = If(TryCast(gridView.Controls(i), CheckBox).Checked, "True", "False")
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
End If
If gridView.Controls(i).HasControls() Then
ChangeControlsToValue(gridView.Controls(i))
End If
Next
End Sub
Download Sample Code
Other Posts on ASP.NET C# VB.NET
I have used northwind database to populate gridview.
