Exporting a DataTable to Excel (DataTable to CSV)

Jun 1 2011 6:10 PM

It seems like each time I need to do this in a project (that is create an Excel file from a GridView or SQL Query) I have to look for my little snippet on how to do this in a reusable manner. Well here you go a nice Extension Method that will return CSV from any System.Data.DataTable.

public static String ToCSV(this DataTable dt)
{
    StringBuilder sb = new StringBuilder();

    for (int x = 0; x < dt.Columns.Count; x++)
    {
        if (x != 0)
            sb.Append(",");
        sb.Append(dt.Columns[x].ColumnName);
    }
    sb.AppendLine();
    foreach (DataRow row in dt.Rows)
    {
        for (int x = 0; x < dt.Columns.Count; x++)
        {
            if (x != 0)
                sb.Append(",");
            sb.Append(row[dt.Columns[x]].ToString());
        }

        sb.AppendLine();
    }
    return sb.ToString();
}

4 Comments

Dr Herbie said on Jun 24 2009 at 7:55 PM

I have a similar method.

You need to check for commas in your string fields and surround with quotes (e.g. Some text, "Some text, with a comma", Some more text)
Kris Krause said on Jun 25 2009 at 2:20 AM

And you need to remove the non-printable hi-bit characters. And what if the column type is a System.Guid? And what if the column value is null?
Ajay said on Jul 13 2009 at 10:45 PM

Brilliant, exactly what I needed. thanks..
Steven said on Sep 9 2009 at 8:28 AM

I love the extension stuff it's making my life so much easier!! Using this I can recreate the tostring method that used to exist in ADO (before .NET) where you could pass your column and row delimiters (blow out an HTML table for instance).