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