Quantcast

ASP.NET: Export a DataTable to Excel

Get the WebProNews Newsletter:


[ Business]

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/

ASP.NET: Export a DataTable to Excel
About Mads Kristensen
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/ WebProNews Writer
Top Rated White Papers and Resources
  • Guest

    good post!

    • Guest

      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

       

      • Tin77

        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
        
        • Nilesh Kale

           

          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.

        • Guest

          hi, i get all colums of row value in single call with delimiter semicolon. How can i get the values in each cell separately

  • Guest

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

  • amit

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

  • Ricomyer

    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?

  • Guest

    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?

  • Guest

    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 ?

    • Balaji

      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 .

  • http://www.jeevan4u.com Jeevan

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

  • http://www.singleboersen-vergleich.org Andre

    I

  • UBK

    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

  • UBK

    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

  • Guest

    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

  • Chris

    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

    • Chris

      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.

  • raj

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

  • Jessica Lee

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

  • Jessica Lee

    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!!

  • Govind

    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.

  • Sadheesh

    Hi Kristensen,

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

    Any idea?

  • http://www.ssksorgulama.info ssk

    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…

  • Guest

    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.

  • Efflixi

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

  • John

    Where does the file save or get attched

  • John

    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()

  • julian

    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();
    }

  • http://www.mikesknowledgebase.com Michael

    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

  • Ebby

    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();

    }

  • pradeep

    Please help!!! I am unable to export datatable values to Excel and below code for your reference. Thanks in advance.

    private string ExportToSpreadsheet(DataTable table, string name)
    {
    try
    {

    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.WriteFile(name);
    Response.Redirect("/Admin/Utilities.aspx", false);

    return context.Response.ToString();

    }
    catch (Exception ex)
    {
    ex.Message.ToString();
    }

    return null;
    }

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom