ASP.NET: Export Grid View to Excel

Exporting contents to excel spreadsheet is a much required functionality for almost every data driven website. Especially in asp.net websites we may want to export contents of gridview to excel sheets more often. It can be done in a simple way by setting the mime type of a response to fit the format of excel sheet and writing the contents of gridview to the response stream. In this post I am going to explain in detail on how to export gridview contents to an excel sheet.

To get started, I am having a gridview filled with data from employee table. The gridview looks like this with its contents and styling,

Now to export the contents of the above gridview to excel sheet, write the code below in "Export to Excel" button's click event.

protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            string filename="GridViewExport_"+DateTime.Now.ToString()+".xls";
            Response.AddHeader("content-disposition",
            "attachment;filename="+filename);
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            GridView1.RenderControl(hw);
            //style to format numbers to string
            string style = @"";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

Now if you run this code and click on "Export To Excel" button, you will get the following error "Control GridView1 must be placed inside a form tag with runat=server".

This error occurs whenever we try to render a control to response. This can be rectified in two ways.

1. Adding gridview to HtmlForm Object Programmatically and render the form. Use the below code to do that,

        protected void Button1_Click(object sender, EventArgs e)
        {
            HtmlForm form = new HtmlForm();
            Response.Clear();
            Response.Buffer = true;
            string filename="GridViewExport_"+DateTime.Now.ToString()+".xls";
 
            Response.AddHeader("content-disposition",
            "attachment;filename="+filename);
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            form.Controls.Add(GridView1);
            this.Controls.Add(form);
            form.RenderControl(hw);
      
            //style to format numbers to string
            string style = @"";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

2. Overriding VerifyRenderingInServerForm Event in the code behind page. This ensures that HtmlForm Control is rendered for the specific ASP.NET control at runtime. Just add the below event handler code beneath the Page_Load event handler in the code behind page.

     public override void VerifyRenderingInServerForm(Control control)
        {

        }

One last thing one should do is add EnableEventValidation="false" property to the page directive or to the web.config file. This avoids one more exception from arising that will say "RegisterEventValidation can only be called during Render()". See below code for reference,

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DatabaseTestDemo.Default"  EnableEventValidation="false"%>

The output excel file will have contents that look like this. Note that the styling that is applied to gridview is also exported along with the content. 

If you wants to add custom styling to the contents of the excel sheet, then you can add it dynamically by adding custom styles to the gridview and render it. For example,

//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
 
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "white");  
 
for (int i = 0; i < GridView1.Rows.Count;i++ )
{
    GridViewRow row = GridView1.Rows[i];
 
    //Change Color back to white
    row.BackColor = System.Drawing.Color.White;
 
    //Apply text style to each Row
    row.Attributes.Add("class", "textmode");
 
    //Apply style to Individual Cells of Alternating Row
    if (i % 2 != 0)
    {
        row.Cells[0].Style.Add("background-color", "#C2D69B");
        row.Cells[1].Style.Add("background-color", "#C2D69B");
        row.Cells[2].Style.Add("background-color", "#C2D69B");
        row.Cells[3].Style.Add("background-color", "#C2D69B");  
    }
}
GridView1.RenderControl(hw);

The output excel sheet that is exported from the above custom styled gridview look like this,

Please leave your comments and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading this!!

Subscribe to GET LATEST ARTICLES!

Most Shared - Last Week