xlsx Faster processing with progress bar

[k7s41gx]

Hey All,


I have an extremely large .xlsx worksheet. As in 311,000 rows and 33 columns per row. I am trying to import this data into a datatable. I have tried various methods and have one that works but is extremely slow. I have tried using interop filters and special cells to filter said data but the import on interop is very slow. I have also tried using OLE as well. However I am unaware of a method to track the progress of the fill using a progress bar or filter the data before being imported. For example if I filter the .xlsx workbook by say "1072" in column 21 then it brings the total row count from 311,000 to say 53,000 rows. Much smaller amount to work with. Code below.

INTEROP METHOD.

For i As Integer = 2 To counta
If CStr(xlWorkSheet2.Cells(i, 21).value) = Form3.Label1.Text Then
t1 = CStr(xlWorkSheet2.Cells(i, 1).value)
t2 = CStr(xlWorkSheet2.Cells(i, 2).value)
t3 = CStr(xlWorkSheet2.Cells(i, 3).value)
t4 = CStr(xlWorkSheet2.Cells(i, 4).value)
t5 = CStr(xlWorkSheet2.Cells(i, 5).value)
t6 = CStr(xlWorkSheet2.Cells(i, 6).value)
t7 = CStr(xlWorkSheet2.Cells(i, 7).value)
t8 = CStr(xlWorkSheet2.Cells(i, 8).value)
t9 = CStr(xlWorkSheet2.Cells(i, 9).value)
t10 = CStr(xlWorkSheet2.Cells(i, 10).value)
t11 = CStr(xlWorkSheet2.Cells(i, 11).value)
t12 = CStr(xlWorkSheet2.Cells(i, 12).value)
t13 = CStr(xlWorkSheet2.Cells(i, 13).value)
t14 = CStr(xlWorkSheet2.Cells(i, 14).value)
t15 = CStr(xlWorkSheet2.Cells(i, 15).value)
t16 = CStr(xlWorkSheet2.Cells(i, 16).value)
t17 = CStr(xlWorkSheet2.Cells(i, 17).value)
t18 = CStr(xlWorkSheet2.Cells(i, 18).value)
t19 = CStr(xlWorkSheet2.Cells(i, 19).value)
t20 = CStr(xlWorkSheet2.Cells(i, 20).value)
t21 = CStr(xlWorkSheet2.Cells(i, 21).value)
t22 = CStr(xlWorkSheet2.Cells(i, 22).value)
t23 = CStr(xlWorkSheet2.Cells(i, 23).value)
t24 = CStr(xlWorkSheet2.Cells(i, 24).value)
t25 = CStr(xlWorkSheet2.Cells(i, 25).value)
t26 = CStr(xlWorkSheet2.Cells(i, 26).value)
t27 = CStr(xlWorkSheet2.Cells(i, 27).value)
t28 = CStr(xlWorkSheet2.Cells(i, 28).value)
t29 = CStr(xlWorkSheet2.Cells(i, 29).value)
t30 = CStr(xlWorkSheet2.Cells(i, 30).value)
t31 = CStr(xlWorkSheet2.Cells(i, 31).value)
t32 = CStr(xlWorkSheet2.Cells(i, 32).value)
t33 = CStr(xlWorkSheet2.Cells(i, 33).value)
TextBox2.Text = "Row " & i
RAW.Rows.Add(t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, t28, t29, t30, t31, t32, t33)
ProgressBar1.Value = ProgressBar1.Value + 1
Else
ProgressBar1.Value = ProgressBar1.Value + 1
End If
Next


OLE METHOD (FASTEST BUT NO FILTER OR PROGRESS)

Public Sub OXER()
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim RAW As New DataTable
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "2018RAW.xlsx") & "';Extended Properties=Excel 12.0; HDR=YES")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [2018RAW$]", MyConnection)
MyCommand.Fill(RAW)
MyConnection.Close()
Form3.DataGridView1.DataSource = RAW
End Sub


Any help would be greatly appreciated!

Continue reading...