OLEDB Create Table (Excel) locked after creation

[Gtripodi]

I was writing up a bit of code to help a mate out here, after completing it I am stuck wondering why the output excel file seems to be open exclusively until the application exits, I would assume that once the class was disposed (Closed) that the file (and all related connections,etc) should be released, but that does not appear to be the case. This also prevents me from opening the excel file through Process.Start Running the bellow class will put the excel file on your desktop but if you try to open it prior to the application being closed it will show as corrupt.




Imports System.Data.OleDb

Public Class FormExportToExcel

Dim SourceDTable As New DataTable With {.TableName = "ExcelExample"} 'Test Table

Private Sub FormExportToExcel_Load(sender As Object, e As EventArgs) Handles MyBase.Load

'Add columns to test table
With SourceDTable
For i As Integer = 0 To 10
.Columns.Add(New DataColumn With {
.ColumnName = "Column" & i,
.DataType = GetType(String)})
Next

'Add rows to test table
For i As Integer = 0 To 10000
Dim NRow As DataRow = SourceDTable.NewRow
For Each Dcol As DataColumn In SourceDTable.Columns
NRow(Dcol.ColumnName) = Dcol.ColumnName & "- Value " & i
Next
.Rows.Add(NRow)
Next

End With

End Sub

Private Sub ButtonExportExcel_Click(sender As Object, e As EventArgs) Handles ButtonExportExcel.Click
'initiate export class
Using ExportExcel As New ExportExcel(SourceDTable)
ExportExcel.ShowDialog()
End Using

End Sub

Public Class ExportExcel
Inherits Form
Dim ProgBar As New ProgressBar With {.Name = "ProgBar"}
Dim SourceDT As DataTable
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
StartPosition = FormStartPosition.Manual
Location = Cursor.Position
Height = 75
Width = 700

Controls.Add(ProgBar)

SourceDT = _DT
TargetPath = My.Computer.FileSystem.SpecialDirectories.Desktop & "\" & SourceDT.TableName & ".XLS"
BGW.RunWorkerAsync(SourceDT)

End Sub

Private Sub BGW_DoWork(sender As Object, e As ComponentModel.DoWorkEventArgs) Handles BGW.DoWork

'If output already exists, delete it
If My.Computer.FileSystem.FileExists(TargetPath) Then My.Computer.FileSystem.DeleteFile(TargetPath)

'create OLEDB Connection
Using OleDBConn As New 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

'Convert DataColumn Names to Names+ Types for CreateTable Command
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 & " DATE,"
End Select
Next

ColNames = ColNames.Substring(0, ColNames.Length - 1)
ColParams = ColParams.Substring(0, ColParams.Length - 1)

ColNamesTypes = ColNamesTypes.Substring(0, ColNamesTypes.Length - 1)

'execute Create Table Command
Using CreateTableCMD As New OleDbCommand("CREATE TABLE " & SourceDT.TableName &
"(" & ColNamesTypes & ")", OleDBConn)
CreateTableCMD.ExecuteNonQuery()
End Using

Dim TotalRows As Integer = SourceDT.Rows.Count
Dim i As Integer = 1

'Add the data to the Excel Sheet
For Each Drow As DataRow In SourceDT.Rows
Dim InsertCMD As New OleDbCommand("INSERT INTO " & SourceDT.TableName & " (" & ColNames & ") VALUES (" &
ColParams & ")", OleDBConn)
For Each Dcol As DataColumn In SourceDT.Columns
InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, Drow(Dcol.ColumnName))
Next
InsertCMD.ExecuteNonQuery()
BGW.ReportProgress(CInt(100 * i / TotalRows))
i += 1
Next

OleDBConn.Close()


End Using
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


End Class




Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

Continue reading...