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("""" & 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 SubI 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 132 times, 1 visits today)
Leave a Reply