Thursday, October 11, 2012

ASP.NET : Export DataTable to CSV

ASP.NET : Export DataTable to CSV

CSV (Comma-separated values) CSV Report is the fastest and smallest for view data in excel without format.
Source will show you below (VB).



Step 1 : Create test DataTable.

    Private _TestData As DataTable

    Private Property TestData As DataTable
        Get
            'Declare Datatable
            Dim dt As New DataTable("TableName")

            'Add Column
            Dim dcCustomerID As New DataColumn()
            dcCustomerID.ColumnName = "CustomerID"
            dcCustomerID.DataType = System.Type.[GetType]("System.String")

            Dim dcCustomerName As New DataColumn()
            dcCustomerName.ColumnName = "CustomerName"
            dcCustomerName.DataType = System.Type.[GetType]("System.String")

            dt.Columns.Add(dcCustomerID)
            dt.Columns.Add(dcCustomerName)

            'Declare Datarow
            Dim dr As DataRow
            dr = dt.NewRow()
            dr(dcCustomerID.ColumnName) = "1"
            dr(dcCustomerName.ColumnName) = "Bank of America"
            dt.Rows.Add(dr)

            dr = dt.NewRow()
            dr(dcCustomerID.ColumnName) = "2"
            dr(dcCustomerName.ColumnName) = "Dow jones"
            dt.Rows.Add(dr)

            dr = dt.NewRow()
            dr(dcCustomerID.ColumnName) = "3"
            dr(dcCustomerName.ColumnName) = "Million Dollar"
            dt.Rows.Add(dr)

            _TestData = dt

            Return _TestData
        End Get
        Set(value As DataTable)
            _TestData = value
        End Set
    End Property

Step 2 : Click Export.

    Protected Sub btnCSV_Click(sender As Object, e As System.EventArgs) Handles btnCSV.Click
        ExportDataTable2CSV(TestData)
    End Sub

    Sub ExportDataTable2CSV(ByVal dt As DataTable)
        Context.Response.AddHeader("content-disposition", "attachment; filename=FileName.csv")
        Context.Response.ContentType = "application/csv"
        Context.Response.Clear()
        Context.Response.Charset = "windows-874"
        Context.Response.ContentEncoding = System.Text.Encoding.GetEncoding(874)
        Page.EnableViewState = False
        Context.Response.Write(ConvertDataTable2CSV(dt))
        Context.Response.End()
    End Sub

    Function ConvertDataTable2CSV(ByVal dtSource As DataTable) As String
        Dim sb As New StringBuilder
        Dim nCount As Integer = dtSource.Columns.Count
        ' Header Row
        Dim sbLine As New StringBuilder
        For iCol As Integer = 0 To dtSource.Columns.Count - 1
            sbLine.Append(",")
            sbLine.Append(dtSource.Columns(iCol).ColumnName)
        Next
        sb.AppendLine(sbLine.ToString.Substring(1))
        For Each dr As DataRow In dtSource.Rows()
            sbLine = New StringBuilder
            For iCol As Integer = 0 To dtSource.Columns.Count - 1
                sbLine.Append(",")
                If Not Convert.IsDBNull(dr(iCol)) Then
                    If (dr(iCol).GetType().ToString() = "System.String") Then
                        sbLine.Append(dr(iCol).ToString.ToString)
                    Else
                        sbLine.Append(dr(iCol))
                    End If
                End If
            Next
            sb.AppendLine(sbLine.ToString.Substring(1))
        Next
        Return sb.ToString()
    End Function

Pls enjoy ZomDev

2 comments: