Results 1 to 1 of 1

Thread: Export DataTable to Excel OLEDB (NO INTEROP)

  1. #1

    Thread Starter
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Export DataTable to Excel OLEDB (NO INTEROP)

    Here is a Class you can add to your project to export datatable to excel without the need of Interop. There is no parsing in this example so if you have NULL values you will have to handle that situation on your own.

    Code:
    Public Class ExportExcel
        Inherits Form
        Dim ProgBar As New ProgressBar With {.Name = "ProgBar"}
        Private WithEvents BGW As New ComponentModel.BackgroundWorker With {.WorkerReportsProgress = True}
        Dim TargetPath As String = String.Empty
    
        Public Sub New(ByVal _DT As DataTable)
            ProgBar.Dock = DockStyle.Fill
            Controls.Add(ProgBar)
    
            StartPosition = FormStartPosition.Manual
            Location = Cursor.Position
            Height = 75
            Width = 700
    
            TargetPath = My.Computer.FileSystem.SpecialDirectories.Desktop & "\" & _DT.TableName & ".XLS"
            BGW.RunWorkerAsync(_DT)
    
        End Sub
    
        Private Sub BGW_DoWork(sender As Object, e As ComponentModel.DoWorkEventArgs) Handles BGW.DoWork
            Dim _SourceDT As DataTable = DirectCast(e.Argument, DataTable)
            If My.Computer.FileSystem.FileExists(TargetPath) Then My.Computer.FileSystem.DeleteFile(TargetPath)
    
            Using OleDBConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TargetPath &
                                                       ";Extended Properties=Excel 8.0;")
                OleDBConn.Open()
    
                Dim ColNames As String = Nothing
                Dim ColParams As String = Nothing
                Dim ColNamesTypes As String = Nothing
    
    
                For Each DCol As DataColumn In _SourceDT.Columns
                    ColNames &= "[" & DCol.ColumnName & "],"
                    ColParams &= "@" & DCol.ColumnName & ","
                    Select Case DCol.DataType
                        Case GetType(String)
                            ColNamesTypes &= "[" & DCol.ColumnName & "]" & " String,"
                        Case GetType(DateTime)
                            ColNamesTypes &= "[" & DCol.ColumnName & "]" & " DateTime,"
                        Case GetType(Integer)
                            ColNamesTypes &= "[" & DCol.ColumnName & "]" & " INTEGER,"
                        Case GetType(Boolean)
                            ColNamesTypes &= "[" & DCol.ColumnName & "]" & " YESNO,"
                    End Select
                Next
    
                ColNames = ColNames.Substring(0, ColNames.Length - 1)
                ColParams = ColParams.Substring(0, ColParams.Length - 1)
                ColNamesTypes = ColNamesTypes.Substring(0, ColNamesTypes.Length - 1)
    
    
                Using CreateTableCMD As New OleDb.OleDbCommand("CREATE TABLE " & _SourceDT.TableName &
                                                                     "(" & ColNamesTypes & ")", OleDBConn)
                    CreateTableCMD.ExecuteNonQuery()
                End Using
    
                Dim TotalRows As Integer = _SourceDT.Rows.Count
                Dim i As Integer = 1
    
    
                For Each Drow As DataRow In _SourceDT.Rows
                    Using InsertCMD As New OleDb.OleDbCommand("INSERT INTO " & _SourceDT.TableName & " (" & ColNames & ") VALUES (" &
                                                          ColParams & ")", OleDBConn)
                        For Each Dcol As DataColumn In _SourceDT.Columns
                            Select Case Dcol.DataType
                                Case GetType(String)
                                    InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, Drow(Dcol.ColumnName).ToString)
                                Case GetType(DateTime)
                                    InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, CType(Drow(Dcol.ColumnName).ToString, DateTime))
                                Case GetType(Integer)
                                    InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, CType(Drow(Dcol.ColumnName).ToString, Integer))
                                Case GetType(Boolean)
                                    InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, CType(Drow(Dcol.ColumnName), Boolean))
                            End Select
                        Next
    
                        InsertCMD.ExecuteNonQuery()
                        BGW.ReportProgress(CInt(100 * i / TotalRows))
                        i += 1
                    End Using
                Next
                OleDBConn.Close()
            End Using
        End Sub
    
        Private Sub Me_Closed() Handles Me.FormClosed
            Process.Start(TargetPath)
        End Sub
    
        Private Sub BGW_ProgressChanged(sender As Object, e As ComponentModel.ProgressChangedEventArgs) Handles BGW.ProgressChanged
            If e.ProgressPercentage = 1 Then ProgBar.Show()
            ProgBar.Value = e.ProgressPercentage
            If e.ProgressPercentage = 100 Then ProgBar.Hide()
        End Sub
    
        Private Sub BGW_RunWorkerCompleted(sender As Object, e As ComponentModel.RunWorkerCompletedEventArgs) Handles BGW.RunWorkerCompleted
            Close()
        End Sub
    
    End Class
    Last edited by kpmc; Apr 30th, 2019 at 08:28 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width