ASP.NET C# – Exporting List<T> to Excel

ASP.NET C# – Exporting List<T> to Excel

There has been a number of time I have output the results of a database query or the analysis of some function onto the the screen when my customer will ask if they can download the data into Excel. Spreadsheets are an easy way for customers (even the non-technical ones) to manipulate results, carry out further analysis or just to get a format or graph for a report.

There are a number of way to export to Excel and this is one of the quickest and easiest methods that I have found. Essentially you create a DataGrid in memory, bind the data and then stream it out. Using a DataGrid takes away the drudgery of parsing the data to format it.

In this example I am passing data as a List<T> (use your own T list type) but you can use a DataSet or anything that will bind with the DataGrid.

public void ExportToExcel(List<T> myList)
{
  string fileName = "MyFilename.xls";
  
  DataGrid dg = new DataGrid();
  dg.AllowPaging = false;
  dg.DataSource = myList;
  dg.DataBind();
  
  System.Web.HttpContext.Current.Response.Clear();
  System.Web.HttpContext.Current.Response.Buffer = true;
  System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
  System.Web.HttpContext.Current.Response.Charset = "";
  System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition",
    "attachment; filename=" + fileName);
  
  System.Web.HttpContext.Current.Response.ContentType = 
    "application/vnd.ms-excel";
  System.IO.StringWriter stringWriter = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter htmlTextWriter = 
    new System.Web.UI.HtmlTextWriter(stringWriter);
  dg.RenderControl(htmlTextWriter);
  System.Web.HttpContext.Current.Response.Write(stringWriter.ToString());
  System.Web.HttpContext.Current.Response.End();
} 

It’s as easy as that. You’ll get the familiar box to download the file or open directly in your default spreadsheet.

[misi ad=”Adsense”]