by
Nicole
Thursday, July 22, 2010 8:17 AM
Exporting Data Tables To Microsoft Excel from ASP.NET Pages
The Microsoft Excel export method involving a gridview control is fairly well known. Take a gridview that does not have paging and sorting enabled, pass it into the method below, and up comes an Excel file:
-
/// <summary>
-
/// This method takes in the specified gridview and creates a Microsoft Excel sheet from it.
-
/// Only use this method for a gridview in which paging or sorting is not in effect.
-
/// </summary>
-
/// <param name="gv"></param>
-
/// <param name="filename"></param>
-
public static void ExportToExcel(GridView gv, string filename)
-
{
-
HttpContext cont = HttpContext.Current;
-
cont.Response.Clear();
-
-
string header = "attachment; filename=" + filename + ".xls";
-
cont.Response.AppendHeader("content-disposition", header);
-
-
cont.Response.ContentType = "application/vnd.ms-excel";
-
-
StringWriter sw = new StringWriter();
-
Html32TextWriter htw =new Html32TextWriter(sw);
-
-
HtmlForm form = new HtmlForm();
-
gv.Parent.Controls.Add(form);
-
-
// Add the new grid view to the new form
-
form.Controls.Add(gv);
-
-
//Render the new form to the HTML text writer
-
form.RenderControl(htw);
-
-
cont.Response.Write(sw.ToString());
-
cont.Response.End();
-
cont.Response.Clear();
-
-
// Clean up
-
form.Dispose();
-
gv.Dispose();
-
htw.Dispose();
-
sw.Dispose();
-
}
In reality, all Microsoft Excel does is read the rendered HTML of the gridview control passed into the HTTP response output stream from the method. But gridview HTML is notoriously inefficient, with unnecessary nested tables. Also, if you're not creating the gridview to display on the page, you have to programmatically create the gridview, add the columns (unless you use auto generate columns, but have no control over the column names displayed), bind the gridview, and pass it into the method. I have done this countless times on reports pages and always thought how great it would just to pass in a data table instead of the hassle of a programmatically generated gridview.
So, I did what a developer does when he/she gets frustrated - found a work around! In this case, it was writing a simple method that took in a data table and then appends the necessary HTML to create a table out of its rows. It's the same concept as the method above but with less overhead and cleaner HTML.
I thought I'd share my method. Note that this only works with Microsoft Excel 2003. It seems that Excel 2007 got rid of ability to read HTML tables. Work around? Try EPPLus using Office Open XML.
-
public static void ExportToExcel(DataTable dt, string fileName)
-
{
-
if (dt != null)
-
{
-
HttpContext cont = HttpContext.Current;
-
cont.Response.Clear();
-
-
string header = "attachment; filename=" + fileName + ".xls";
-
cont.Response.AppendHeader("content-disposition", header);
-
cont.Response.ContentType = "application/vnd.ms-excel";
-
-
StringBuilder sb = new StringBuilder();
-
-
// Render out an HTML table to pass into Excel
-
sb.Append("<table cellpadding=2 cellspacing=2>");
-
-
// Get all column names
-
sb.Append("<tr>");
-
-
foreach (DataColumn dc in dt.Columns)
-
{
-
sb.Append("<td>");
-
-
sb.Append("<b>" + dc.ColumnName.Replace("_", " ") + "</b>");
-
-
sb.Append("</td>");
-
}
-
-
sb.Append("</tr>");
-
-
// Handle table rows and cells
-
for (int i = 0; i < dt.Rows.Count; i++)
-
{
-
sb.Append("<tr>");
-
-
foreach (DataColumn dc in dt.Columns)
-
{
-
sb.Append("<td>");
-
-
sb.Append(dt.Rows[i][dc].ToString());
-
-
sb.Append("</td>");
-
}
-
-
sb.Append("</tr>");
-
}
-
-
// Close the table tag
-
sb.Append("</table>");
-
-
dt.Dispose();
-
-
// Pass the table HTML string into the response output stream to be passed to Excel
-
cont.Response.Write(sb.ToString());
-
cont.Response.End();
-
cont.Response.Clear();
-
}
-
else
-
{
-
throw new Exception("The data table cannot be null.");
-
}
-
}