Fast reading of Excel worksheets into a .NET program

Reading data from Excel into a C# or VB.NET program using Interop.Excel.dll can be very slow because each read operation goes through a slow path in Windows (about 50ms), so instead of looping through each Range or Cell object to read one cell at a time, it is much better to read the entire Range (multiple cells) of interest in one go, and then do the looping on an array inside the program, without having to pull data in from Excel on each iteration.

First get the dimensions of the sheet you wish to process. Here is a handy function to do that:

 ''' <summary>
 ''' Gets dimensions of Excel worksheet, assuming the first used cell is at (1,1).
 ''' </summary>
 ''' <param name="w">Worksheet object</param>
 ''' <param name="numrows">Returns the number of rows found to be used in the sheet</param>
 ''' <param name="numcols">Returns the number of columns found to be used in the sheet</param>
 ''' <remarks>return values are 1 based. Works even when there are blank rows or columns which appear to split the sheet.</remarks>
 Private Sub GetDimensionsOfWorksheet(ByVal w As Excel.Worksheet, ByRef numrows As Long, ByRef numcols As Long)
 'helpful hint for this at https://stackoverflow.com/questions/10752971/first-blank-row-in-excel/10753301#10753301
     numcols = DirectCast(w.Cells(1, w.Columns.Count), Excel.Range).End(Excel.XlDirection.xlToLeft).Column
     numrows = DirectCast(w.Cells(w.Rows.Count, 1), Excel.Range).End(Excel.XlDirection.xlUp).Row
 End Sub

Then use this to read in the range of values:

Dim lastcol As Long
Dim lastrow As Long
GetDimensionsOfWorksheet(worksheet, lastrow, lastcol)
Dim range As Excel.Range = worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(lastrow, lastcol))
Dim cellsStr(,) As Object = range.Value2

You might need to use .Value instead of .Value2 for cellsStr().

Then you can loop from 1 to lastrow and 1 to lastcol as array indexes of cellsStr(). For example

For r As Long = 1 To lastrow
    For c As Long = 1 To lastcol
        Debug.Print("Row " & r & " Col " & c & " Value=" & CStr(cellsStr(r, c)))
    Next c
Next r

My measurements are an improvement from 9.6ms per cell to 1.5ms per cell based on a 5 worksheet, 650 used cells spreadsheet.

Thanks to https://stackoverflow.com/a/39898500/8157 for this technique.