export data table to excel in vb.net

that's a good question. My boss said he wanted in excel but they wont spend any money I know them.

I started down this route then discovered that the function is for a dataset so maybe if this cant be got working I could write back to a table in the sql server DB, if there is a limitation to get the data to excel. excel can read a csv file but it creates more stages.

Imports Microsoft


Imports System.Data.SqlClient
Module Module1
    Public Sub GetDifferences()
        Dim StrSelected As String
        Dim TableName As String
        Dim dtFieldNameRecords As DataTable
        Dim dtEditRecords As DataTable
        Dim PKName As String
        Dim ModelIDName As String
        Dim FieldName As String
        Dim y As Long
        Dim VehCatName As String
        Dim StrPath As String

        FieldName = ""
        TableName = ""
        VehCatName = ""
        PKName = ""
        ModelIDName = ""
        StrPath = ""

        Dim sSet As DataSet = New DataSet("Records")

        StrSelected = FrmMain.CBClients.SelectedValue.ToString()
        Select Case StrSelected
            Case = 1
                TableName = "tClient"
                PKName = "abiCode"
                VehCatName = "ABI_cat"
                ModelIDName = "abiCode"
                StrPath = "N:\Data\Abi\"
            Case = 2
                TableName = "CAPDATA"
                PKName = "CAPid_CAPcat"
                VehCatName = "CAP_cat"
                ModelIDName = "CapVehicleID"
                StrPath = "N:\Data\Cap\"
            Case = 3
                TableName = "GLASS FULL TABLE"
                PKName = "GLASSid_GLASScat"
                VehCatName = "GLASS_cat"
                ModelIDName = "Model_id"
                StrPath = "N:\Data\Glass\"
            Case = 4
                TableName = "TVIDATA"
                PKName = "DERIVATIVE_CODE"
                VehCatName = "TVIVehicleTyep"
                ModelIDName = "DERIVATIVE_ID"
                StrPath = "N:\Data\Thatcham\"
            Case Else
                TableName = "n/a"
        End Select

        dtFieldNameRecords = Nothing
        dtEditRecords = Nothing

        y = 0

        FrmMain.ProgressBar1.Step = 1
        FrmMain.ProgressBar1.Minimum = 1
        FrmMain.ProgressBar1.Value = FrmMain.ProgressBar1.Minimum


        dtFieldNameRecords = GetFields(TableName)

        Debug.Print(y)
        For Each drAccessRecord As DataRow In dtFieldNameRecords.Rows
            y = dtFieldNameRecords.Rows.Count
            FrmMain.ProgressBar1.Maximum = y
            FieldName = drAccessRecord("column_name")
            If FieldName = "BATCH" Or FieldName = PKName Or FieldName = ModelIDName Then

            Else

                dtEditRecords = GetEdits(FieldName, TableName, PKName, VehCatName)

                sSet.Tables.Add(dtEditRecords)
            End If
            FrmMain.ProgressBar1.PerformStep()
            FrmMain.Label3.Text = "# of Files Read Veh Cat = " & Math.Round((FrmMain.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
            FrmMain.Label3.Refresh()

        Next

        dtFieldNameRecords = Nothing
        dtEditRecords = Nothing

        DatatableToExcel(sSet, StrPath)

        MsgBox("finished")

    End Sub
    Public Function GetFields(ByVal TableName As String) As DataTable
        Dim StrProcName As String
        Dim dtFields = New DataTable
        Dim connectionString As String = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=ClientData;Integrated Security=True;MultipleActiveResultSets=True"

        StrProcName = "usp_tablefields"
        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                cmd.Parameters.AddWithValue("@TableName", TableName)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtFields.Load(reader)
            End Using
        End Using
        Return dtFields
    End Function

    Public Function GetEdits(ByVal FieldName As String, ByVal TableName As String, ByVal PKName As String, ByVal VehCat As String) As DataTable
        Dim StrProcName As String
        Dim dtFields = New DataTable
        Dim connectionString As String = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=ClientData;Integrated Security=True;MultipleActiveResultSets=True"

        StrProcName = "usp_ClientDifferencesLogic"
        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                cmd.Parameters.AddWithValue("@FieldName", FieldName)
                cmd.Parameters.AddWithValue("@TableName", TableName)
                cmd.Parameters.AddWithValue("@PKName", PKName)
                cmd.Parameters.AddWithValue("@VehCat", VehCat)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtFields.Load(reader)
            End Using
        End Using
        Return dtFields
    End Function

    Public Sub DatatableToExcel(ByVal dtTemp As DataTable, ByVal StrPath As String)
        Dim _excel As New Microsoft.Office.Interop.Excel.Application
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = _excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As System.Data.DataTable = dtTemp
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            _excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = StrPath & "datatable.xlsx"
        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        wBook.Close()
        _excel.Quit()
    End Sub

Select allOpen in new window

*the function is for a datatable

If you just want to create a CSV file, have you check my article?

Sorry Eric our timezones are out a little, yes I read your article and I am wondering to create an xml file instead as its in your article and might be better. will visual studio 15 have the bits in needs to do this? or will i need to add the additions you mentioned in your article. I ask as time probably has moved on a lot now.

Ive decided to export my data as XML. the microsoft website has an example which ive followed but i get an unhandled error when it tries to write the dataset and not sure why.

Imports System.Data.SqlClient
Module Module1
    Public Sub GetDifferences()
        Dim StrSelected As String
        Dim TableName As String
        Dim dtFieldNameRecords As DataTable
        Dim dtEditRecords As DataTable

        Dim PKName As String
        Dim ModelIDName As String
        Dim FieldName As String
        Dim y As Long
        Dim VehCatName As String
        Dim StrPath As String

        FieldName = ""
        TableName = ""
        VehCatName = ""
        PKName = ""
        ModelIDName = ""
        StrPath = ""

        Dim sSet As DataSet = New DataSet("Records")
        Dim DTExport As DataTable = New DataTable
        StrSelected = FrmMain.CBClients.SelectedValue.ToString()
        Select Case StrSelected
            Case = 1
                TableName = "tClient"
                PKName = "abiCode"
                VehCatName = "ABI_cat"
                ModelIDName = "abiCode"
                StrPath = "N:\Data\Abi"
            Case = 2
                TableName = "CAPDATA"
                PKName = "CAPid_CAPcat"
                VehCatName = "CAP_cat"
                ModelIDName = "CapVehicleID"
                StrPath = "N:\Data\Cap"
            Case = 3
                TableName = "GLASS FULL TABLE"
                PKName = "GLASSid_GLASScat"
                VehCatName = "GLASS_cat"
                ModelIDName = "Model_id"
                StrPath = "N:\Data\Glass"
            Case = 4
                TableName = "TVIDATA"
                PKName = "DERIVATIVE_CODE"
                VehCatName = "TVIVehicleTyep"
                ModelIDName = "DERIVATIVE_ID"
                StrPath = "N:\Data\Thatcham"
            Case Else
                TableName = "n/a"
        End Select

        dtFieldNameRecords = Nothing
        dtEditRecords = Nothing

        y = 0

        FrmMain.ProgressBar1.Step = 1
        FrmMain.ProgressBar1.Minimum = 1
        FrmMain.ProgressBar1.Value = FrmMain.ProgressBar1.Minimum


        dtFieldNameRecords = GetFields(TableName)

        Debug.Print(y)
        For Each drAccessRecord As DataRow In dtFieldNameRecords.Rows
            y = dtFieldNameRecords.Rows.Count
            FrmMain.ProgressBar1.Maximum = y
            FieldName = drAccessRecord("column_name")
            If FieldName = "BATCH" Or FieldName = PKName Or FieldName = ModelIDName Then

            Else

                dtEditRecords = GetEdits(FieldName, TableName, PKName, VehCatName)
                sSet.Tables.Add(dtEditRecords)

            End If
            FrmMain.ProgressBar1.PerformStep()
            FrmMain.Label3.Text = "# of Files Read Veh Cat = " & Math.Round((FrmMain.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
            FrmMain.Label3.Refresh()

        Next


        Debug.Print(StrPath)
        Dim xmlData As String = sSet.GetXml()
        sSet.WriteXml(StrPath)

        MsgBox("finished")
        dtFieldNameRecords = Nothing
        dtEditRecords = Nothing
    End Sub

Select allOpen in new window

row 89 is the problem and the path looks ok but what about filename of the xmlfile itself

what is the value of StrPath? it needs to contains the filename as well. Do you have write access to these folders the N: drive?

thats what it is i didnt specify that ok just looking now.

all done thank you Eric