ASP.NET: Export a DataTable to Excel

    November 28, 2006

Back in February I wrote a post on how to export DataTables to XML and Excel and I still get a lot of search engine traffic to that post.

People have been asking me to simplify the example and only concentrate on the Excel export, so that’s what I will do now.

All spreadsheet applications (Excel, Calc etc.) understand semicolon separated files natively, so everyone can use this method – you don’t even have to use Excel for it to work.

public static void ExportToSpreadsheet(DataTable table, string name)
   HttpContext context = HttpContext.Current;
   foreach (DataColumn column in table.Columns)
    context.Response.Write(column.ColumnName + ";");
   foreach (DataRow row in table.Rows)
    for (int i = 0; i < table.Columns.Count; i++)     {      context.Response.Write(row[i].ToString().Replace(";", string.Empty) + ";");     }     context.Response.Write(Environment.NewLine);    }    context.Response.ContentType = "text/csv";    context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");    context.Response.End(); }

Then just call this method and pass the DataTable and the filename as parameters.

ExportToSpreadsheet(table, "products");

The method is static so you can use it anywhere in a web application. Put it on a page, a HTTP Handler, add it to the App_Code folder or stick it in a separate assembly. As long as you call it from a web application it will work.


Add to | Digg | Reddit | Furl

Bookmark WebProNews:

Mads Kristensen currently works as a Senior Developer at Traceworks located
in Copenhagen, Denmark. Mads graduated from Copenhagen Technical Academy with a multimedia degree in
2003, but has been a professional developer since 2000. His main focus is on ASP.NET but is responsible for Winforms, Windows- and
web services in his daily work as well. A true .NET developer with great passion for the simple solution.