ASP.NET - Export GridView to Excel

Exporting GridView Data to Excel Sheet in ASP.NET is a common task for many Applications. In this article I am explaining problem and solution for this task.

Exporting GridView Data to Excel Sheet in ASP.NET  is a common task for many Applications. But while exporting GridView to Excel Sheet generally we face some problems.
In this article, I am going to explain steps for exporting GridView Data to Excel Sheet and what are the problems we face in this task.

There are two cases  when you export GridView Data-


1. GridView without  control like (Button, LinkButton, ImageButton etc.)
2. GridView with  control like (Button, LinkButton, ImageButton etc.)

Generally we face  problem with second option and the common error is-

Error- RegisterForEventValidation can only be called during Render();

Here I am going to explain both cases-

1. GridView without  control like (Button, LinkButton, ImageButton etc.)

For this task, I have designed GridView like this-



Here is .aspx code-

<asp:GridView ID="GrdEmpData" runat="server" AutoGenerateColumns="false" Width ="50%">
<Columns>
    <asp:TemplateField HeaderText="EmpId" ItemStyle-HorizontalAlign  ="Left"  ItemStyle-Width ="30%">
      <ItemTemplate >
         <asp:Label ID="lblEmpId" runat="server" Text='<% # Eval("EmpId") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField  HeaderText="EmpName" ItemStyle-HorizontalAlign  ="Left"  ItemStyle-Width ="30%">
       <ItemTemplate>
         <asp:Label ID="lblEmpName" runat="server" Text='<% # Eval("EmpName") %>'></asp:Label>
       </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField  HeaderText="DeptId" ItemStyle-HorizontalAlign  ="Left"  ItemStyle-Width ="30%">
      <ItemTemplate>
         <asp:Label ID="lblDeptName" runat="server" Text='<% # Eval("DeptName")%>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export to Excel " onclick="btnExport_Click"/>

I have bound this GridView with DataTable, Here is .cs code for getting data from Database to bind GridView.
This is connection string in web.config file-

<connectionStrings>
<add name="ConString" connectionString="Data Source=ServerName;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=UserName;Password=YourPassword"/>
</connectionStrings>

Now .cs code to get data from database-

protected void Page_Load(object sender, EventArgs e)
    {
         if (!IsPostBack)
        {
           //Binding Data to GridView
            GrdEmpData.DataSource = GetData();
             GrdEmpData.DataBind();
         }
    }

//Function to get data from database
    private DataTable  GetData()
    {
        DataTable dt = null;
        try
        {
            string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            SqlConnection con = new SqlConnection(conString);
            SqlDataAdapter da = new SqlDataAdapter("select * from tab_EmpMaster,tab_DeptMaster where tab_EmpMaster.DeptId=tab_DeptMaster.DeptId", con);
            dt = new DataTable();
             da.Fill(dt);
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
         return dt;
    }

After Executing this code your will get data in GridView control. Now the main task is to export this data to Excel Sheet, Here is code for this-

protected void btnExport_Click(object sender, EventArgs e)
    {
         try
        {
             //Calling function to Export GridView Data to Excel Sheet
            FunExportToExcel(GrdEmpData);  
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
     }

//Function to export GridView data to Excel sheet
    private void FunExportToExcel(GridView GrdView)
    {
         try
        {
             Response.Clear();
            Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
            Response.Charset = "";
            // If you want the option to open the Excel file without saving than
            // comment out the line below
            // Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.xls";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
             GrdView.RenderControl(htmlWrite);
             Response.Write(stringWrite.ToString());
            Response.End();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
     }

If you will use this code then after executing code you will get following error-

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

To solve this error include this code in your .cs file-

public override void VerifyRenderingInServerForm(Control control)
    {
        // Do nothing
    }

After adding this code your GridView Data will be exported to Excel Sheet.

2. GridView with  control like (Button, LinkButton, ImageButton)

For showing  functionality of this case-2 ,  I have added one LinkButton inside GridView Like this-

<asp:TemplateField  HeaderText="Delete" ItemStyle-HorizontalAlign  ="Left"  ItemStyle-Width ="20%">
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CommandName ="delete"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>




Now if you if use same code for  this case , which we have used for case-1, then it will generate following error-

Error- RegisterForEventValidation can only be called during Render();

Note - This error comes because of rendering problem, when you export GridView data to Excel Sheet then controls of GridView first converted to Literal controls. But control like Button, LisnkButton , ImageButton etc. can't be converted to Literal Controls, so it generates error.

To solve this problem I have used following solution-

Before exporting GridView Data to Excel Sheet , hide all the controls like  Button, LinkButton , ImageButton etc.
and after exporting data again show controls.

Here is .cs code for that-

protected void btnExport_Click(object sender, EventArgs e)
    {
        try
        {
             //Calling function to hide controls like Button, LinkButton, ImageButton inside GridView
            HideControls();

            //Calling function to Export GridView Data to Excel Sheet
            FunExportToExcel(GrdEmpData2);

            //Calling function to show controls like Button, LinkButton, ImageButton inside GridView
            ShowControls();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
     }

//Function to hide control like button,linkbutton of GridView
//Here I am hiding LinkButton, you can hide other controls also
    private void HideControls()
    {
         try
        {
            foreach (GridViewRow row in GrdEmpData2.Rows)
            {
                LinkButton lnk = (LinkButton)row.FindControl("lnkDelete");
                 if (lnk != null)
                {
                    lnk.Visible = false;
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
     }

    //Function to show control like button,linkbutton of GridView
    private void ShowControls()
    {
         try
        {
            foreach (GridViewRow row in GrdEmpData2.Rows)
            {
                LinkButton lnk = (LinkButton)row.FindControl("lnkDelete");
                 if (lnk != null)
                {
                    lnk.Visible = true;
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
    }

Now execute this code, you will get result without any error.

If you want to download complete code then , download from here-

Download code-  Here

Thanks

By Jitendra Faye   Popularity  (11064 Views)
Biography - Jitendra Faye
Provides Self Implemented Solution.     My Articles