Exporting a DataTable to Excel



In many cases Microsoft Excel can do much for you and reduce your programming efforts. For example, if you must provide a printable version of a data collection or you need to provide a report with grouping, you may send all information to Excel and let the end-user do it with Excel. Therefore, knowing how to export a data collection (i.e. a DataTable) to Microsoft Excel can be helpful and sometimes reliving!

In ASP.NET, you may export a GridView control to excel but I do not think it is a good way (except in certain cases) because we mostly do not display the whole columns and rows in a grid (i.e. for paging reason). Plus, exporting a GridView control needs to re-render the grid into response output stream and it might be time/resource consuming.

In the rest of this article we will write a handy function to export a DataTable to Microsoft Excel. The features of this function are as follows:

1. Is very quick

2. Is very lightweight

3. You may choose which columns to be exported

4. You may specify your desired encoding

Ok let’s get started. The method we are going to write is named ExportToSpreadsheet and have the following signature:

public static void ExportToSpreadsheet(DataTable table, string name, string ColumnList)

The 1st argument is the DataTable object we are going to export. The 2nd one is the name of generated target file and ColumnList is a comma-separated list of columns to be exported. Not always do we export all columns. Thus we should be able to choose which columns to be exported.

The file we are going to generate is a Comma Separated Value (.csv) file because Excel knows this file and opens it like a spread-sheet. In the other hand, we do not have to create a real .xls file.

Ok, for the implementation part we first convert the ColumnList argument to an IList collection. This is necessary because we need to find a column name in the list later.

IList<string> collist = new List<string>();

string[] columnstr = ColumnList.ToUpper().Split(‘,’);

foreach (string s in columnstr)

collist.Add(s);

The above code splits the ColumnList argument into a string array and adds each string item to an IList object. I had to use foreach statement because the code was written in C# 2.0. In C# 3.0 you may omit Foreach and use .ToList() extension instead. I mean the above code will get shorter in this way:

IList<string> collist = ColumnList.ToUpper().Split(‘,’).ToList();

Since we are sending the .CSV file to HttpResponse, we create a new HttpContext instance, and set the ContentEncoding property. We would better set ContentEncoding since the encoding used in clients might be different from the response encoding! For example suppose you have a website whose response encoding is UTF-8 but your clients use Windows-1256 encoding. If you export the .CSV file in UTF-8 format clients will not show the strings correctly. In this code I have used Encoding.Default but you may let the the encoding to be chosen as a new argument for ExportToSpreadsheet method.

context.Response.ContentEncoding = Encoding.Default;

ontext.Response.Clear();

Afterwards, we start to loop over the DataTable.Columns collection to create the titles. For column titles I have used DataColumn.Caption property (and not DataColumn.ColumnName) since the title is not necessarily equal to the column name. For instance a column name might be Cust_Title but the caption should be “Customer’s Full Name”. I recommend to use typed datasets because setting such properties are very easy in VS.:

foreach (DataColumn column in table.Columns)

{

if (ColumnList==”” || collist.Contains(column.ColumnName.ToUpper()))

context.Response.Write(column.Caption + “;”);

}

After creating column titles, we loop over rows and add them to the .csv :

foreach (DataRow row in table.Rows)

{

foreach(DataColumn column in table.Columns)

{

if (ColumnList == “” || collist.Contains(column.ColumnName.ToUpper()))

context.Response.Write(row[column].ToString().Replace(“;”, string.Empty) + “;”);

}

context.Response.Write(Environment.NewLine);

}

At the end, we alter the http header in following way to get the download process started:

context.Response.ContentType = “text/csv”;

context.Response.AppendHeader(“Content-Disposition”, “attachment; filename=” + name);

context.Response.End();

That’s it. You may download a .PDF version of this article HERE.


Advertisements

14 thoughts on “Exporting a DataTable to Excel

  1. Brainer says:

    It worked like a charm, I translated it to vb, It can be useful for someone so here is:

    Public Sub ExportToSpreadsheet(ByVal table As DataTable, ByVal name As String, ByVal ColumnList As String)
    Dim collist As ArrayList = New ArrayList

    Dim columnstr As String() = ColumnList.ToUpper.Split(“,”)
    Dim s As String

    For Each s In columnstr
    collist.Add(s)
    Next

    Context.Response.ContentEncoding = Encoding.Default
    Context.Response.Clear()

    Dim column As DataColumn
    For Each column In table.Columns

    If ColumnList = “” Or collist.Contains(column.ColumnName.ToUpper()) Then
    Response.Write(column.Caption + “,”)
    End If

    Next

    Dim row As DataRow
    For Each row In table.Rows
    Context.Response.Write(Environment.NewLine)
    For Each column In table.Columns

    If ColumnList = “” Or collist.Contains(column.ColumnName.ToUpper()) Then
    Context.Response.Write(row(column).ToString().Replace(“,”, String.Empty) + “,”)
    End If

    Next

    Next

    Context.Response.ContentType = “text/csv”

    Context.Response.AppendHeader(“Content-Disposition”, “attachment; filename=” + name)

    Context.Response.End()

    End Sub

  2. Beck says:

    Can I check. For your coding it only works for one worksheet per excel? Or is it possible to create multiple worksheets in one excel?

  3. Well, I am so excited which find this page because I have already been seeking
    some information for this for merely three hours! Youve taught me to be considerably indeed and through scaning
    this post I’ve got located many new and useful information about this subject

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s