Exporting data to Excel from a GridView in ASP.net

Hate or like it, regardless how pretty your GridView looks like, how easy it’s to print, customers just want to export everything to excel to do their own manipulation. So willy-nilly we must find a good solution to handle this. In asp.net, it’s actually very easy to do, but there are a few things we need to be aware of. Let’s have a look at the function first.

private void Export2Excel(GridView gv, string strFileName)
{
 Response.Clear();
 Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
 Response.Charset = "";

 // If you want the option to open the Excel file without saving then
 // 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);
 gv.RenderControl(htmlWrite);

 Response.Write(stringWrite.ToString());

 Response.End();
}

By calling above method in button click event, we can export any GridView to excel.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
    Export2Excel(GridView1, "demo.xls");
}

However there are a few things you need be aware of:

1. If you receive a HttpException regarding to type 'GridView' must be placed inside a form tag with runat=server. You need to overide VerifyRenderingInServerForm Method as below:

public override void VerifyRenderingInServerForm(Control control) { }

2. In Ajax enabled environment. You will very likely to receive an exception error as: Exception is Sys.WebForms.PageRequestParserErrorException .The message received from the server could not be parsed. This is because modified Response object cannot work in Ajax Framework. The workaround is to use asynchronous post back through the trigger in asp:UpdatePanel. Here are the code:

3. We must turn off AllowPaging to false before we are doing the export.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
 GridView1.AllowPaging = false;  
 Export2Excel(GridView1, "Overview.xls");
 GridView1.AllowPaging = true;
}

4. If you see this exception Exception RegisterForEventValidation can only be called during Render().You need to notify ASP.Net that not to validate the event by setting the EnableEventValidation flag to FALSE.

Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="demo.aspx.cs" Inherits="demo" EnableEventValidation = "false"

That has covered all errors I have seen in the past. Please let me know if there are any problems or questions. Another very good post I found on aspsnippet, is definitely worth a read.