How to read all Excel items to Listview without using looping?

Question

Is it possible to read all items in excel items without using looping ? I have almost 20 thousand rows in my excel it takes too long to put the items in Listview.

This is my current code :

 xlWorkBook = xlApp.Workbooks.Open(FileName)

 xlWorkSheet = xlWorkBook.Worksheets("ExportedFromDatGrid")
 xlApp.Sheets("ExportedFromDatGrid").activate()
 xlApp.Range("A2").Activate()

 Dim cCount As Integer
 Dim azr As Microsoft.Office.Interop.Excel.Range
 azr = xlWorkSheet.UsedRange

            For cCount = 2 To azr.Rows.Count
                Dim newitem As New ListViewItem()
                Dim excelvalue As String = (Format(azr.Cells(cCount, 2).value, "yyyy-MM-dd"))

                Dim fromdate As String

                fromdate = dtpFrom.Value.ToString("yyyy-MM-dd")
                Dim todate As String

                todate = dtpTo.Value.ToString("yyyy-MM-dd")

                'MessageBox.Show(fromdate & "FROM - <<  TO- >>>>" & todate)

                If ((excelvalue >= fromdate) And (excelvalue <= todate)) Then

                    newitem.Text = "CGC-" & azr.Cells(cCount, 1).value.ToString
                    newitem.SubItems.Add(Format(azr.Cells(cCount, 2).value, "yyyy-MM-dd HH:mm:00"))
                    newitem.SubItems.Add(azr.Cells(cCount, 3).value.ToString)
                    newitem.SubItems.Add(azr.Cells(cCount, 4).value.ToString)
                    newitem.SubItems.Add(azr.Cells(cCount, 5).value.ToString)

                    lvAll.Items.Add(newitem)

                End If

            Next
            xlWorkBook.Close()
            xlApp.Quit()

            System.Runtime.InteropServices.Marshal.ReleaseComObject(azr)

See [ListView.VirtualMode](https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.listview.virtualmode?view=netframework-4.8) — , Mar 12 '20 at 03:10
I'll bet it does take too long; but what user is going to scroll through 20,000 records? — Mary, Mar 14 '20 at 10:09
Does this code actually work and the only problem is that it is slow? — Mary, Mar 14 '20 at 10:35
@Mary yes the code was working but it does slow. it takes around 10minutes to put 20k records on listview, 20k records is attendance of employee for this year. — Bap mop, Mar 16 '20 at 00:50

Mary · Accepted Answer · 2020-03-16T06:43:38.757

The idea here is to speed up the update of the user interface.

Create a list of ListViewItem and add to it on each iteration of your loop. Resolve the fromdate and todate once outside the loop. We want a new item on each iteration so, I moved the Dim of newitem inside the loop.

I think it would be better to leave this date data as dates for the comparison. It would save 20,000 conversions .ToString for excelvalue assuming this is a date value but I left that code alone. Look into this if your code is still too slow.

Next we update the user interface in a single operation. BeginUpdate...EndUpdate prevents the UI from repainting on each addition. .AddRange adds the whole array at once.

In general I think it would be quicker to use the OleDb provider for Excel and pull all your data into a DataTable using ADO.net . Then loop on the rows of the data table. Again, this is something to look into if your code is still to slow.

Dim lst As New List(Of ListViewItem)
Dim fromdate = dtpFrom.Value.ToString("yyyy-MM-dd")
Dim todate = dtpTo.Value.ToString("yyyy-MM-dd")
  For cCount = 2 To azr.Rows.Count
    Dim excelvalue As String = (Format(azr.Cells(cCount, 2).value, "yyyy-MM-dd"))
       If ((excelvalue >= fromdate) And (excelvalue <= todate)) Then
          Dim newitem As New ListViewItem()
          If ((excelvalue >= fromdate) And (excelvalue <= todate)) Then 
              newitem.Text = "CGC-" & azr.Cells(cCount, 1).value.ToString
              newitem.SubItems.Add(Format(azr.Cells(cCount, 2).value, "yyyy-MM-dd HH:mm:00"))
              newitem.SubItems.Add(azr.Cells(cCount, 3).value.ToString)
              newitem.SubItems.Add(azr.Cells(cCount, 4).value.ToString)
              newitem.SubItems.Add(azr.Cells(cCount, 5).value.ToString)
              lst.Add(newitem)
            End If
  Next
            xlWorkBook.Close()
            xlApp.Quit()
            lvAll.BeginUpdate()
            lvAll.Items.AddRange(lst.ToArray)
            lvAll.EndUpdate()

In the code that calls this you need to add

GC.Collect()
GC.WaitForPendingFinalizers()

I did a little changes on your code due to value is not showing. also what is use of ``GC.Collect()`` and ``GC.WaitForPendingFinalizers()`` though? — Bap mop, Mar 16 '20 at 01:27
Here is a link regarding GC.Collect and GC.WaitForPendingFinalizers(). Essentially it will rid you of ghost copies of Excel. https://stackoverflow.com/questions/10309365/the-proper-way-to-dispose-excel-com-object-using-vb-net — Mary, Mar 16 '20 at 07:16

score 0 · Answer 2 · answered Mar 12 '20 at 14:59

You can use arrays to read data ranges directly from Excel much quicker than looping.

dim ValArray As Object(,)
ObjRange = xlWorkSheet.UsedRange

ValArray = ObjRange.Value2

Then you will have the data in a VB.NET array which is much faster to loop through.

You can then loop through it like this

For i = 0 To valArray.GetUpperBound(0)
    Debug.print(valArray(i,0))
    Debug.print(valArray(i,1))
    'etc...
Next

This should be quite fast in comparison, perhaps 10-20x.

How to read all Excel items to Listview without using looping?

2 Answers2