ASP.NET - Export To CSV VBScript

• Tweet
• 

    Public Function ToCSV(ByVal DataTable As DataTable)
        'create the stringbuilder that would hold the data
        Dim sb As New StringBuilder()
        'check if there are columns in the datatable
        Dim column As New DataColumn()
        If DataTable.Columns.Count <> 0 Then
            'loop thru each of the columns for headers
            For Each column In DataTable.Columns
                'append the column name followed by the separator
                sb.Append("""" & column.ColumnName & " "",")
            Next
            'append a carriage return
            sb.Append(vbCrLf)

            'loop thru each row of the datatable
            Dim myRow As DataRow

            For Each myRow In DataTable.Rows
                'loop thru each column in the datatable
                Dim myColumn As DataColumn

                For Each myColumn In DataTable.Columns

                    'get the value for the row on the specified column
                    ' and append the separator
                    sb.Append("""" & myRow(myColumn).ToString() & """,")
                Next
                'append a carriage return
                sb.Append(vbCrLf)
            Next
        End If
        Return sb.ToString()
    End Function


    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("sConnectionString").ConnectionString)
        myConnection.Open()

        'Get member details

        'If we are displaying all the clubs then use a slightly different stored procedure
        Dim sSQL As String
        sSQL = "proc_Report_companyAllClubs"
        Dim myCommand As SqlCommand = New SqlCommand(sSQL, myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.Parameters.AddWithValue("@vchCompanyType", Request.QueryString("vchCompanyType"))
        myCommand.Parameters.AddWithValue("@vchClubType", Request.QueryString("vchClubType"))
        myCommand.Parameters.AddWithValue("@vchOutdoorAffiliation", Request.QueryString("vchOutdoorAffiliation"))
        myCommand.Parameters.AddWithValue("@vchArenaAffiliation", Request.QueryString("vchArenaAffiliation"))
        myCommand.Parameters.AddWithValue("@vchOutdoorAffiliationFeePaid", Request.QueryString("vchOutdoorAffiliationFeePaid"))
        myCommand.Parameters.AddWithValue("@vchArenaAffiliationFeePaid", Request.QueryString("vchArenaAffiliationFeePaid"))
        myCommand.Parameters.AddWithValue("@vchOverseasClub", Request.QueryString("vchOverseasClub"))
        Dim myDataAdapter As New SqlDataAdapter(myCommand)
        Dim oTable1 As New DataTable("Data")
        myDataAdapter.Fill(oTable1)
        myConnection.Close()

        'Add tables to dataset
        Dim myDataset As New DataSet()
        myDataset.Tables.Add(oTable1)

        'Sort the dataset based on the selected group by (if this is a handicap grouping then sort by integer representation first)
        Dim sSortBy As String = ""
        sSortBy = sGroupBy

        myDataset.Tables(0).DefaultView.Sort = sSortBy

        Dim myDataTable As DataTable = myDataset.Tables(0)
        Response.ContentType = "Application/x-msexcel"
        Response.AddHeader("content-disposition", "attachment;filename=entries.csv")
        Response.Write(ToCSV(myDataTable))
        Response.End()
    End Sub

Tags:
• CSV
• export