Easy Reporting with GridView Control

Asp.Net GridView control has a RenderControl method that can be used to generated Excel report. The idea is creating a GridView control as report template and render Excel sheet with the control.

Following code gives an example:

GridView1.DataSource = someDataView;

GridView1.DataBind();
GridView1.Visible = true;
PrepareGridViewForExport(GridView1);

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=anyNameString.xls");
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);
GridView1.RenderControl(htmlWrite);

// combine two Tablular data into one Excel sheet.
GridView2.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

A few notes regarding above code snippet:
1. The GridView control instances, such as GridView1 and GridView2 in above code, have to be created from within the page where to product Excel report. In reality, they may not be required to show on the page. In such case, we can create a invisible panel to hold the GridView controls as following:

<asp:Panel runat="server" ID="PanelDetails" Visible="false">
<%-- hidden GridView controls --%>
</asp:Panel>

2. In case that the GridView control is required to show on the page, if we use the same instance to render Excel report, the control may be not quite meet the report needs, because it is customized for the page, e.g. it has buttons or check boxes and so forth, we have to "Prepare" it before rendering a report. In above code, it calls PrepareGridViewForExport method shown below to get rid of all the link buttons.

void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
gv.Controls.Remove(gv.Controls[i]);
}

if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
}

}
}

The problem roots from that we use one GridView control for two different uses. It is cleaner to create a specific reporting GridView instance rather than modifying the instance that is for the page.

3. It is viable to combine multiple GridView control rendering into one Excel sheet. In above code, we saw both GridView1 and GridView2 render into one html string. Excel understands the combined rendering.