Visual Basic - Writing a Datatable to Excel

Question

I am working on a console application that will grab all of the .sql files in a specific folder, use those to query a database, and then export the results of each to an excel file. I've gotten everything down up to the datatable. I am asking for any advice or guidance on getting that datatable to excel. Any help would be appreciated. Thank you.

Imports System.Data.OleDb

Module SqlExport

Sub Main()

    Dim SQLString As String
    Dim SQLDirectory As New IO.DirectoryInfo("\\Datastore\scripts\SQL")
    Dim SQLQueries As IO.FileInfo() = SQLDirectory.GetFiles("*.sql")
    Dim CurrentQuery As IO.FileInfo

    Dim dt As New DataTable

    For Each CurrentQuery In SQLQueries
        SQLString = System.IO.File.ReadAllText(CurrentQuery.FullName)

        Using connection As New OleDb.OleDbConnection("provider=advantage ole db provider;data source=\\database\dba;advantage server type=ads_remote_server;trimtrailingspaces=true;")

            Dim command As New OleDbCommand(SQLString, connection)
            Dim da As New OleDb.OleDbDataAdapter(command)

            da.Fill(dt)

        End Using

    Next

End Sub

End Module

The first suggested link on the right looks perfect: http://stackoverflow.com/questions/1746701/export-datatable-to-excel-file — Derek Tomes, Jul 11 '13 at 22:34

score 1 · Accepted Answer · answered Jan 07 '14 at 15:04

Thank you all for this. I ended up using the following to get the results I was looking for.

Dim da As New OleDb.OleDbDataAdapter(command)

da.Fill(ds)

Dim Excel As Object = CreateObject("Excel.Application")
    With Excel
        .SheetsInNewWorkbook = 1
        .Workbooks.Add()
        .Worksheets(1).Select()
        .Range("A1").select()

        Dim i As Integer = 1

        For col = 0 To ds.Tables(0).Columns.Count - 1
            If Asc(ds.Tables(0).Columns(col).ColumnName) = 36 Then
                .Columns(i).NumberFormat = "General"
                .Cells(1, i).value = ds.Tables(0).Columns(col).ColumnName.Substring(1)
            Else
                .Columns(i).NumberFormat = "@"
                .Cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
            End If
            i += 1
        Next

        Dim j As Integer = 1
        For col = 0 To ds.Tables(0).Columns.Count - 1
            i = 2
            For row = 0 To ds.Tables(0).Rows.Count - 1
                .Cells(i, j).Value = ds.Tables(0).Rows(row).ItemArray(col).ToString
                i += 1
            Next
            j += 1
        Next

        Dim fileName As String = Path.GetFileNameWithoutExtension(CurrentQuery.Name)

        .ActiveWorkbook.SaveAs(Filename:="\\reports\" & value & "\" & fileName & DateTime.Now.ToString("yyyy-MM-dd") & ".xlsx", _
        WriteResPassword:="123456", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False)

        .Workbooks.Close()
        .Quit()
End With

score 0 · Answer 2 · answered Jul 11 '13 at 23:29

In Manual way ..

First you have to create your XLS file and make colums header like fields in your database

Assumed that is "TRANSFER.XLS" ..

Dim sFN = "Transfer.XLS"
Dim cnXLS As OleDbConnection
Dim cmdXLS As OleDbCommand
Dim dr as DataRow

cnXLS = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "data source=" & sFN & ";Extended Properties=Excel 8.0;")
cnXLS.Open()

For x as Integer = 0 to dt.Table(0).Rows.Count -1 '---> this is your dt
  dr = dt.Table(0).Rows(x)
  cmdXLS = New OleDbCommand("INSERT INTO [Sheet1$] ( name, .... ) VALUES ( dr.item("name", ....)", cnXLS)
  cmdXLS.ExecuteNonQuery

Next
cnXLS.close()

Visual Basic - Writing a Datatable to Excel

2 Answers2