Recently I have answered a number of questions regarding an easy way to export datagrid contents to excel format. In this blog post I will share a static class that I have created called "ExportHelper" which I use quite often to generate quick Excel exports of datagrid data. I will outline the process and the code below. It should be noted that this solution is NOT a DotNetNuke specific solution, I use this for both DotNetNuke and standard ASP.NET solutions.
First of all please note that this solution is a great utility to help individuals export data to Excel it is NOT a 100% solution, there are specific configurations, multi-row headers for example, that will break this process. However, in general this process works very well to export content with minimal code and can be reused between multiple applications.
For the ease of use I created the export process as a static class with a single static member. This allows me to quickly export a grid with just a few lines of code.
/// This static class is used to export items to excel format
public static class ExportHelper
/// This method will export a datagrid to MS Excel format, it will remove all header and footer
/// styles from the grid
/// <remarks>NOTE: Grid must be bound without paging PRIOR to being passed to this method
/// for exporting to Excel. Failure to do so will result in incorrect results</remarks>
/// <param name="oGrid">The datagrid that we are to export</param>
/// <param name="exportFile">The name of the file that should be shown to the user</param>
/// ExportHelper.ExportGrid(dgrListing, "MyExport.xls");
/// <exception cref="IndexOutOfRangeException">Thrown when more than 65,100 Records exist</exception>
public static void ExportGrid(DataGrid oGrid, string exportFile)
//Clear the response, and set the content type and mark as attachment
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + exportFile + "\"");
//Clear the character set
HttpContext.Current.Response.Charset = "";
//Create a string and Html writer needed for output
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
//Clear the controls from the pased grid
//Show grid lines
oGrid.GridLines = GridLines.Both;
oGrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
//Render the grid to the writer
//Write out the response (file), then end the response
private static void ClearControls(Control control)
//Recursively loop through the controls, calling this method
for (int i = control.Controls.Count - 1; i >= 0; i--)
//If we have a control that is anything other than a table cell
if (!(control is TableCell))
if (control.GetType().GetProperty("SelectedItem") != null)
LiteralControl literal = new LiteralControl();
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
if (control.GetType().GetProperty("Text") != null)
LiteralControl literal = new LiteralControl();
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
As you can see in this example the actual export code is pretty simple. First we setup the respone object by clearning anything that has already been written to the response, we then setup the content type and attachment to let the browser know that we are sending a file. A parameter to the "ExportGrid" method is used to allow a specific file name to be used when sending the file to the user. After this we setup a string and HtmlWriter to take the rendering from the gird, then we clean the controls in the grid, ensuing we only have literals. Lastly we render the grid to the writer then send the resultant code to the user.
This uses a little known feature of Excel which allows it to open HTML documents as Excel documents. Since the DataGrid renders itself as a HTML table it displays very well when opened by Excel.
As noted in the XML Comments for the method it is very important that if you have paging enabled in your datagrid, that you disable it and re-bind your grid PRIOR to calling the export. Since the grid is rendered to an HtmlWriter and not the response stream, changes to the grid configuration will not be saved in ViewState and will not affect the users display. Below is an example of the code I use to export a grid. Please note that the "BindGrid()" method performs standard grid binding.
//Disable paging and viewstate
dgrExample.AllowPaging = false;
//Hide any columns you don't want exported
dgrExample.Columns.Visible = false;
In this example code I disable paging, hide the first column which was an edit column, then bind the grid and export. with 4 simple lines of code I can export any datagrid from my application with ease.
Due to the nature of the operations completed inside the ExportGrid method you may only call the ExportGrid method from inside of a postback to the server. If your application is AJAX enabled, you CANNOT export a grid during an AJAX update as the modifications to the response header is not allowed. If inside DotNetNuke just be sure to register your export button as a postback control using the DotNetNuke.Framework.AJAX.RegisterPostBackControl() method.
This should provide many people a great starting point for exporting grid data in a web application. As always your feedback is appreciated!