Recently I have answered a number of questions regarding an easy way to export data grid 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.
Preface/Notice
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.
The Code
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.
/// <summary>
/// This static class is used to export items to excel format
/// </summary>
public static class ExportHelper
{
/// <summary>
/// This method will export a datagrid to MS Excel format, it will remove all header and footer
/// styles from the grid
/// </summary>
/// <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>
/// <example>
/// <code>
/// ExportHelper.ExportGrid(dgrListing, "MyExport.xls");
/// </code>
/// </example>
/// <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.Clear();
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
ClearControls(oGrid);
//Show grid lines
oGrid.GridLines = GridLines.Both;
//Color header
oGrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
//Render the grid to the writer
oGrid.RenderControl(oHtmlTextWriter);
//Write out the response (file), then end the response
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.End();
}
private static void ClearControls(Control control)
{
//Recursively loop through the controls, calling this method
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
ClearControls(control.Controls[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();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}
}As you can see in this example the actual export code is pretty simple. First we set up the response object by cleaning anything that has already been written to the response, we then set up 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 set up 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 an HTML table it displays very well when opened by Excel.
Example Usage
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 user's 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[0].Visible = false;
//Rebind
BindGrid();
//Export
ExportHelper.ExportGrid(dgrExample, "ExampleExport.xls"); 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.
Considerations
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.
Conclusion
This should provide many people with a great starting point for exporting grid data in a web application. As always your feedback is appreciated!