Export GridView to Excel

I thought this sounded like a pretty standard requirement so I figured there would be a nice snippet on msdn or in an msdn publication describing the official Microsoft solution.  I couldn’t find that, or any other approach that I was happy with.  I ran across a few sites that looked promising, but didn’t quite meet my requirements – I had to either override a page level method and do nothing  or else turn off event validation.  It also generally appeared that the people using these solutions were also running into a variety of issues with grids that allow sorting and paging - all of the grids I am working with allow both.

The approach I settled on is very similar to the above links, but without the limitations of having to turn off event validation or override any page methods.  This gives me the freedom to put these methods were I want them (in my case within a static GridViewExportUtil class) without having to worry about the page that hosts the GridView doing anything special.

using System;
using System.Data;
using System.Configuration;
using System.IO;
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;
/// <summary>
///
/// </summary>
public class GridViewExport
{
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="gv"></param>
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExport.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExport.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExport.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridViewExport.PrepareControlForExport(current);
}
}
}
}

 Do save the above class as a class in your web application project then you can use it any where in your application to convert a gridview into excel format. Currently I used the .xls format, i'm doing the .xlxs format also, soon I'll post.

In the code behind where you need to convert a gridview to excel format there you need to do like this

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
// pass the grid that for exporting as  a excel.
GridViewExport.Export("YourExcelFileName.xls", this.YourGridViewName);
}