ASP.NET: Export a DataTable to Excel

By Mads Kristensen · November 28, 2006 · 31 Comments

Get the WebProNews Newsletter:

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; context.Response.Clear(); foreach (DataColumn column in table.Columns) { context.Response.Write(column.ColumnName + ";"); } context.Response.Write(Environment.NewLine); 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.

Tag:

Add to Del.icio.us | 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.

http://www.madskristensen.dk/

Top Rated White Papers and Resources

There are 31 Comments. Add Yours.

Like (0) Dislike (0)
Guest
January 30, 2008 at 1:26 pm

good post!

Reply
- Like (0) Dislike (0)
  Guest
  February 18, 2008 at 10:51 pm
  
  hi,
  
  Do you have a visual basic code for this? Im new at .net and i wana create a class that will be exporting to excel a datatable. Exactly how this code of your do. But only using visual basic codes.
  
  Thanks a lot…
  
  ron
  
  Reply
  - Like (0) Dislike (0)
    Tin77
    February 19, 2008 at 2:25 am
    Should look like this:
    
    Public Shared Sub ExportToSpreadsheet(ByVal table As DataTable, ByVal name As String) Dim context As HttpContext = HttpContext.Current context.Response.Clear() For Each column As DataColumn In table.Columns context.Response.Write(column.ColumnName + ";") Next context.Response.Write(Environment.NewLine) For Each row As DataRow In table.Rows For i As Integer = 0 To table.Columns.Count - 1 context.Response.Write(row(i).ToString().Replace(";", String.Empty) + ";") Next context.Response.Write(Environment.NewLine) Next context.Response.ContentType = "text/csv" context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv") context.Response.[End]() End Sub
    Reply
    - Like (0) Dislike (0)
      Nilesh Kale
      October 24, 2008 at 11:46 pm
      
      This very good for me .bt my basic requiremest is that I want to show excel sheet in proper formated money is in if null 0.00 etc.It is possible 2 get such type of output.Rest is fine at my end.
      
      Thanks.
      
      Reply
    - Like (0) Dislike (0)
      Guest
      February 25, 2009 at 5:03 am
      
      hi, i get all colums of row value in single call with delimiter semicolon. How can i get the values in each cell separately
      
      Reply
Like (0) Dislike (0)
Guest
February 1, 2008 at 2:43 pm

This works really well – way better than all of the other options out there. Thanks!

Reply
Like (0) Dislike (0)
amit
February 21, 2008 at 9:45 am

hw to export more than 200 columns and 15000 rows . it takin more tha 20 mins

Reply
Like (0) Dislike (0)
Ricomyer
July 11, 2008 at 3:51 pm

Works great! Odd to me that you used semi-colons as your delimter instead of surrounding text in quotes and using comma as delimiter. But I was able to easily translate. Are .csv delimters different here in America?

Reply
Like (0) Dislike (0)
Guest
October 14, 2008 at 4:08 pm

What’s a good approach to provide a pre-formatted Excel spreadsheet? My users aren’t really thrilled having to move columns around and format cells themselves.

I once wrote a class that did all that for a desktop app but required a template file to be stored in the assembly and I used Reflection etc…performance nightmare but it did the job. Is there a way to use the above method and still provide nice pre-formatting?

Reply
Like (0) Dislike (0)
Guest
December 24, 2008 at 4:31 am

it convertes the value to an exponential value if the value is like ” 0000000000005 ” , i want to display this value as it is, is it possible ?

Reply
- Like (0) Dislike (0)
  Balaji
  February 24, 2009 at 2:45 am
  
  Its not a bug man. The utility will export the data exactly as it is. But the cells in the excel sheet will get reformatted as per the default formatting for that datatype defined in excel. And setting the cell formatting of Excel is out of the scope of this article and can be done manually .
  
  Reply
Like (0) Dislike (0)
Jeevan
February 4, 2009 at 4:49 am

Thank you very much for the posting. it’s really a useful resource

Reply
Like (0) Dislike (0)
Andre
February 12, 2009 at 6:07 am

I

Reply
Like (0) Dislike (0)
UBK
April 10, 2009 at 8:53 am

Need to use comma (,) in place of semi colon (;) to make it work with excel 2007, else it is displaying the records in a single cell.
Thogh your method is very help to me.

Thanks,
UBK

Reply
Like (0) Dislike (0)
UBK
April 10, 2009 at 8:54 am

Need to use comma (,) in place of semi colon (;) to make it work with excel 2007, else it is displaying the records in a single cell.
Thogh your method is very help to me.

Thanks,
UBK

Reply
Like (0) Dislike (0)
Guest
April 21, 2009 at 4:20 am

i used this code.it works fine but display records in only one cell .i want to display it in different cells
please reply..
thanks

Reply
Like (0) Dislike (0)
Chris
July 6, 2009 at 10:04 am

Unable to cast object of type ‘System.Data.DataColumn’ to type ‘DocumentFormat.OpenXml.Spreadsheet.Column’.

I’m trying to do this in VB. The code is:

Protected Sub ExportToSpreadsheet(ByRef mydata As DataTable, ByVal myPath As String)

For Each DataColumn As Column In mydata.Columns
Response.Write(DataColumn.LocalName & “;”)
Next
Response.Write(Environment.NewLine)

For Each DataRow As Row In mydata.Rows

For i = 0 To mydata.Columns.Count – 1
Response.Write(DataRow(i).ToString().Replace(“;”, String.Empty) + “;”)
Next
Response.Write(Environment.NewLine)
Next
Response.ContentType = “text/csv”
Response.AppendHeader(“Content-Disposition”, “attachment; filename=” + myPath + “.csv”)
Response.End()
End Sub

Reply
- Like (0) Dislike (0)
  Chris
  July 6, 2009 at 10:17 am
  
  Oops. I needed to switch the class items to the following:
  
  For Each Column As DataColumn In mydata.Columns
  
  For Each Row As DataRow In mydata.Rows
  
  also, change the file type to .txt to force Excel 2007 to use the Text Wizrd.
  
  Reply
Like (0) Dislike (0)
raj
August 1, 2009 at 5:13 am

hi you code is working properly but could not able to write each field in seperate column of excel.pls give solution.

Reply
Like (0) Dislike (0)
Jessica Lee
August 4, 2009 at 10:03 pm

Hi,
Thank you very much for the good and useful resource.

Reply
Like (0) Dislike (0)
Jessica Lee
August 5, 2009 at 5:15 am

hi!
I have another question about this.

Is that possible to export Datatables to excel sheet?
I have 2 grids bounded to one dataset (2 datatables) on the page,
and I want to export Datatables to excel sheet each?

I’m sorry about my bad english!
please help me!!

Reply
Like (0) Dislike (0)
Govind
September 18, 2009 at 5:25 am

How i able to use this code in console application.

HttpContext context = HttpContext.Current; is not working in console application.

Could you give some ieda.

Reply
Like (0) Dislike (0)
Sadheesh
June 24, 2010 at 1:43 am

Hi Kristensen,

context.Response.Clear() line is throwing me a error stating “Object reference not set to an instance of an object”

Any idea?

Reply
Like (0) Dislike (0)
ssk
October 14, 2010 at 3:23 am

By the way, people who got problem with encoding like me;
you can try this;
Response.ContentEncoding = System.Text.Encoding.Unicode;

Thanks for this post…

Reply
Like (0) Dislike (0)
Guest
December 28, 2010 at 7:08 am

I’ve used the code for export of data and it goes well apart from data like 1-2 converting to 2-Jan and exact similar scenario happens when data 5/6 that converts to 6-May.
Any help in this situation is well appreciated. Thanks all.

Reply
Like (0) Dislike (0)
Efflixi
February 2, 2011 at 2:11 pm

Using this, i was able to implement an excel export feature in less than 5 minutes into my project. Awesome work!

Reply
Like (0) Dislike (0)
John
February 24, 2011 at 12:03 pm

Where does the file save or get attched

Reply
Like (0) Dislike (0)
John
April 13, 2011 at 3:50 pm

I just do it this way in VB

Dim i As Integer

Dim tmp As String = “”
Dim tmptime As String = “”
Dim ExcelFile As String = “”
Dim stwWriter As System.IO.StringWriter = New System.IO.StringWriter
Dim htwWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(stwWriter)

Dim dgGrid As DataGrid = New DataGrid
dgGrid.DataSource = DT
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(htwWriter)

ExcelFile = “Report.xls”

Response.Clear()
Response.AddHeader(“content-disposition”, String.Format(“attachment; filename={0}”, ExcelFile))
Response.ContentType = “application/vnd.ms-excel”

Response.Write(stwWriter.ToString)
Response.End()

Reply
Like (0) Dislike (0)
julian
May 19, 2011 at 4:04 am

For an MVC application I made a few modifications to your code.

public void ExportToSpreadsheet(DataTable table, string name)
{
var resp = Response;

resp.Clear();
foreach (DataColumn column in table.Columns)
{
resp.Write(column.ColumnName + “,”);
}
resp.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
resp.Write(row[i].ToString().Replace(",", string.Empty) + ",");
}
resp.Write(Environment.NewLine);
}
resp.ContentType = "text/csv";
resp.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");
resp.End();
}

Reply
Like (0) Dislike (0)
Michael
November 23, 2011 at 3:47 am

Here’s another full, free, solution, showing how to easily create Excel 2007 files, containing your data.

You just need to pass it a DataSet or DataTable variable, plus the name of the Excel 2007 file that you’d like to create.

http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Reply
Like (0) Dislike (0)
Ebby
March 7, 2012 at 2:11 am

Add Reference COM component to project,

Goto Menu Website –> Add Reference.
Select tab COM.
Select Library –> Microsoft Excel Object Library

Then use the below code for exporting to excel,

using Microsoft.Office.Interop.Excel;

public void ExportToExcel(DataSet ds, string FileNameWithPath)

{

System.Data.DataTable dt = ds.Tables[0];

Excel.ApplicationClass excel = new Excel.ApplicationClass();

excel.Application.Workbooks.Add(Type.Missing);

int i = 1;

foreach (DataColumn col in dt.Columns)

{

excel.Columns.Cells[1,i] = col.ColumnName;

i = i + 1;

}

int prow=2;

foreach (DataRow row in dt.Rows)

{

int pcolumn = 1;

foreach (object obj in row.ItemArray)

{

excel.Cells[prow, pcolumn] = obj.ToString();

pcolumn = pcolumn + 1;

}

prow = prow + 1;

}

excel.ActiveWorkbook.SaveCopyAs(FileNameWithPath);

excel.ActiveWorkbook.Saved = true;

excel.Quit();

}

Reply