Export GridView To Excel ASP.NET

In this example i'm explaining how to Create Or Export GridView to Excel In Asp.Net 2.0,3.5 using C# and VB.NET. Place one button on the page for exporting data to ms excel file.

I have used Northwind database to populate GridView.

I have also explained how we can Create PDF From Gridview in one of my previous articles.

Write following code in Click Event of button

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

This is how exported excel sheet will look like. Hope this helps.

Download Sample Code

Tweet

If you like this post than join us or share