Export DataTable to Excel

How does one export the contents of a datatable to an Excel spreadsheet?

There is a bunch of sample VB.Net code on the net that deals with this – the exporting the contents of a System.Data.DataTable to a comma-delimited file (CSV or more commonly referred to as Excel) via an ASP.Net website.  It’s pretty straight-forward code as shown below.

Private Sub WriteDelimitedData(dt As DataTable, fileName As String, delimiter As String)



    ' Prepare the output stream

    Response.Clear()

    Response.ContentType = "text/csv"

    Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}", fileName))



    ' Write the csv column headers

    For i As Integer = 0 To dt.Columns.Count - 1

        Response.Write(dt.Columns(i).ColumnName)

        Response.Write(If((i < dt.Columns.Count - 1), delimiter, Environment.NewLine))

    Next



    ' Write the data

    For Each row As DataRow In dt.Rows

        For i As Integer = 0 To dt.Columns.Count - 1

            Response.Write(row(i).ToString())

            Response.Write(If((i < dt.Columns.Count - 1), delimiter, Environment.NewLine))

        Next

    Next



    Response.[End]()



End Sub

The thing to note is that if you have string data that includes comma’s, you may hit a snag. That is because Excel splits the column at that comma and everything goes kittywampass.  The solution is to use quoted identifiers. That is, every string is surrounded by quotes.  This is accomplished as follows – note the addition of a double quote before and after every string element.

Private Sub WriteDelimitedData(dt As DataTable, fileName As String, delimiter As String)</pre>

' Prepare the output stream

Response.Clear()

Response.ContentType = "text/csv"

Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}", fileName))



' Write the csv column headers

For i As Integer = 0 To dt.Columns.Count - 1

Response.Write("""" &amp; dt.Columns(i).ColumnName &amp; """")

Response.Write(If((i &lt; dt.Columns.Count - 1), delimiter, Environment.NewLine))

Next



' Write the data

For Each row As DataRow In dt.Rows

For i As Integer = 0 To dt.Columns.Count - 1

Response.Write("""" &amp; row(i).ToString() &amp; """")

Response.Write(If((i &lt; dt.Columns.Count - 1), delimiter, Environment.NewLine))

Next

Next



Response.[End]()



End Sub
I hope this helps someone. It seems that Excel is very widely used by system users and allowing them to utilize system data within Excel can make your software that much more valuable and that is a good thing.
(Visited 147 times, 1 visits today)