Exporting a DataSet to a CSV File in ASP.Net
If you have the need to export data from a SQL Server Dataset to a CSV file through your website application, you can write to the response object in the Page_Init function of your page to produce the file. The user will not see the page at all but will be prompted to open or save the file on their desktop.
Here is the Page_Init function with the Response Write that you will need:
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
Dim objData As System.Data.DataSet = New System.Data.DataSet
Dim objCSV As String = BuildCSV(objData)
Dim objFilename As String = "Excel" & Today.ToString("yyyy-mm-dd") & ".csv"
With Response
.AddHeader("Content-disposition", "attachment;filename=" & objFilename)
.ContentType = "text/plain"
.Write(objCSV)
.End()
End With
End Sub
And here is the supporting function to return the DataSet as a String:
Public Shared Function BuildCSV(ByRef objData As System.Data.DataSet) As String
Dim objList As System.Data.DataSet = objData
Dim objCSVOverall As New System.Text.StringBuilder
Dim objCSVRow As New System.Text.StringBuilder
'*** Build the header row
For intCount As Integer = 0 To objList.Tables(0).Columns.Count - 1
Dim CurrentColumn As System.Data.DataColumn = objList.Tables(0).Columns(intCount)
objCSVRow.Append(Chr(34) & CurrentColumn.ColumnName & Chr(34) & ",")
Next
objCSVOverall.AppendLine(objCSVRow.ToString.Substring(0, objCSVRow.ToString.Length - 1))
'*** iterate through the rows
For Each CurrentRow As System.Data.DataRow In objList.Tables(0).Rows
objCSVRow = New System.Text.StringBuilder
For intCount As Integer = 0 To objList.Tables(0).Columns.Count - 1
Dim objValue As String = CurrentRow.Item(intCount).ToString
Dim CurrentColumn As System.Data.DataColumn = objList.Tables(0).Columns(intCount)
objCSVRow.Append(Chr(34) & objValue.Replace(Chr(34), "'") & Chr(34) & ",")
Next
objCSVOverall.AppendLine(objCSVRow.ToString.Substring(0, objCSVRow.ToString.Length - 1))
Next
Return objCSVOverall.ToString
End Function
