Hello All, We are going to start new batch from next week. message/call or mail us for more details.

ASP.Net 2.0: Export GridView to Excel


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

<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>
    &nbsp;
    <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>
&nbsp;

<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: 
  • Step 1: Implement the Export functionality as described above.
  • Step 2: Remove the code to override the VerifyRenderingInServerForm method.
  • Step 3: Modify the code for the ExportGridView function as below. The code highlighted in green creates and HtmlForm on the fly, before exporting the gridview, adds the gridview to this new form and renders the form (instead of rendering the gridview in our original implementation) 
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);


          // Create a form to contain the grid
          HtmlForm frm = new HtmlForm();

          GridView1.Parent.Controls.Add(frm);

          frm.Attributes["runat"] = "server";

          frm.Controls.Add(GridView1);


          frm.RenderControl(htw);

          //GridView1.RenderControl(htw);
          Response.Write(sw.ToString());

          Response.End();

}

This implementation has the advantage that it can be setup as re-usable code in a separate library, without having to override the base class method each time.

7 comments:

  1. Its great pankaj, will you please provide me .net training me ? ? ? pleas plz reply me

    ReplyDelete
  2. http://dotnetquery4u.blogspot.in/2015/10/dot-net-framework-interview-questions.html

    ReplyDelete
  3. dot net framework latest interview questions
    http://dotnetquery4u.blogspot.in/2015/10/dot-net-framework-interview-questions.html

    ReplyDelete
  4. Really it is an amazing article I had ever read. I hope it will help a lot for all. Can you more read now visit Dot Net Certification Training Institute in Delhi

    ReplyDelete
  5. Great List Thank you for sharing such an impressive and useful post

    Dot Net Online Training Hyderabad

    ReplyDelete
  6. Hi,
    Thanks for sharing the info about .NET Plz keep sharing on...
    Thank you...

    ReplyDelete