Exporting GridView Data to Excel
My current project required a way for exporting data to Excel. I could of course write a separate export method for every data set but in my opinion it would be a pointless waste of time. I would also like to utilise the existing functionality as much as possible – as it is, the lists going to Excel are displayed to the user in the browser.
Solution
The solution – the simplest and least painful at that – is to render GridView into HTML, and to present the resulting HTML to Excel that can also read the HTML format.
/// <summary>
/// Export button was pushed.
/// </summary>
protected void btnExpExcel_Click(object sender,
ImageClickEventArgs e)
{
// Let's hide all unwanted stuffing
this.gdvList.AllowPaging = false;
this.gdvList.AllowSorting = false;
this.gdvList.EditIndex = -1;
// Let's bind data to GridView
this.BindList();
// Let's output HTML of GridView
Response.Clear();
Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition",
"attachment;filename=contacts.xls");
StringWriter swriter = new StringWriter();
HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
HtmlForm frm = new HtmlForm();
this.gdvList.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(this.gdvList);
frm.RenderControl(hwriter);
Response.Write(swriter.ToString());
Response.End();
}
There is one more thing to be done before we can start the export. The page (not the user control) hosting GridView must not handle event validation. To ensure that, let's add the following definition to the header, into the Page section of the declaration: EnableEventValidation="false"
Extension Possibilities
This functionality can also be extended to other controllers, e.g. DataList, Repeater and other controllers that can be associated with data. One could create a separate class for exporting, and even extend it to other formats besides Excel.