Creating a fast Excel add-in (C#, VB.NET). Reading and updating cells.

I will start with a citation: “I am reading a sheet from an Excel File… I am pooling (with a While loop) the entire worksheet to process each cell … The problem is that it is VERY SLOW because the file is huge… Is there a faster way to read the information?”

This is a known problem: doing things cell by cell in Excel is a slow operation. The Excel object model provides two ways to speed up the code.

Updating cells

First off, you can set ScreenUpdating = false before modifying the cells and return it back to true after the changes are made. This prevents Excel from drawing the modified value of EVERY cell. Instead, Excel repaints all cells after you set ScreenUpdating = true.

Secondly, you can assign an array to the Range.Value property; assuming that the range of cells and the array of values have corresponding dimensions.

The ways discussed above were tested in the template[d] code below. Note that the code sample starts with calling the function IsEditing() described in How to check programmatically if the user is editing an Excel cell.

C#:

if (IsEditing()) {
    System.Media.SystemSounds.Beep.Play();
    return;
}
string[,] arr = InitializeData();
DateTime startTime = DateTime.Now;
%%method%%(arr);
DateTime stopTime = DateTime.Now;
TimeSpan elapsedTime = stopTime - startTime;
System.Diagnostics.Debug.WriteLine(elapsedTime.TotalMilliseconds.ToString());

VB.NET:

If IsEditing() Then
    System.Media.SystemSounds.Beep.Play()
    Return
End If
Dim arr As String(,) = InitializeData()
Dim startTime As DateTime = DateTime.Now
%%method%%(arr)
Dim stopTime As DateTime = DateTime.Now
Dim elapsedTime As TimeSpan = stopTime - startTime
System.Diagnostics.Debug.WriteLine(elapsedTime.TotalMilliseconds.ToString())

The results are:

If you update a single range, the ScreenUpdating trickery will not get the Fast method to work faster. If, however, you update several ranges, wrapping the update process with ScreenUpdating=false and ScreenUpdating=true is a good idea: the more ranges you process in a go, the more speed you’ll gain.

Reading cells

If the range contains many cells, Range.Value returns an object type that you can cast to the array of the corresponding type. To check if Range.Value returns an array, you use the Type.IsArray property, for instance range.GetType().IsArray.

Updating cells. The methods.

C#:

private void Slowest(string[,] arr) {
    Excel._Worksheet sheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    Excel.Range sheetCells = sheet.Cells;
    Excel.Range cellFirst = sheetCells[1, 1] as Excel.Range;
    Excel.Range cellLast = sheetCells[MaxRows, MaxColumns] as Excel.Range;
    Excel.Range theRange = sheet.get_Range(cellFirst, cellLast);
    for (int iRow = 1; iRow < = MaxRows; iRow++) {
        for (int iColumn = 1; iColumn <= MaxColumns; iColumn++) {
            (theRange.Cells[iRow, iColumn] as Excel.Range).set_Value(Type.Missing,arr[iRow, iColumn]);
        }
    }
}

VB.NET:

Private Sub Slowest(arr As String(,))
    Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
    Dim sheetCells As Excel.Range = sheet.Cells
    Dim cellFirst As Excel.Range = TryCast(sheetCells(1, 1), Excel.Range)
    Dim cellLast As Excel.Range = TryCast(sheetCells(MaxRows, MaxColumns), Excel.Range)
    Dim theRange As Excel.Range = sheet.Range(cellFirst, cellLast)
    For iRow As Integer = 0 To MaxRows - 1
        For iColumn As Integer = 0 To MaxColumns - 1
            TryCast(theRange.Cells(iRow + 1, iColumn + 1), Excel.Range).Value = _
                arr(iRow, iColumn)
        Next
    Next
End Sub

C#:

private void Slow(string[,] arr) {
    ExcelApp.ScreenUpdating = false;
    Slowest(arr);
    ExcelApp.ScreenUpdating = true;
}

VB.NET:

Private Sub Slow(arr As String(,))
    ExcelApp.ScreenUpdating = False
    Slowest(arr)
    ExcelApp.ScreenUpdating = True
End Sub

C#:

private void Fast(string[,] arr) {
    Excel._Worksheet sheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    Excel.Range sheetCells = sheet.Cells;
    Excel.Range cellFirst = sheetCells[1, 1] as Excel.Range;
    Excel.Range cellLast = sheetCells[MaxRows, MaxColumns] as Excel.Range;
    Excel.Range theRange = sheet.get_Range(cellFirst, cellLast);
    theRange.set_Value(Type.Missing, arr);
}

VB.NET:

Private Sub Fast(arr As String(,))
    Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
    Dim sheetCells As Excel.Range = sheet.Cells
    Dim cellFirst As Excel.Range = TryCast(sheetCells(1, 1), Excel.Range)
    Dim cellLast As Excel.Range = TryCast(sheetCells(MaxRows, MaxColumns), Excel.Range)
    Dim theRange As Excel.Range = sheet.Range(cellFirst, cellLast)
    theRange.Value = arr
End Sub

Good luck!

UPD: Added the VB.NET version of the code as per suggestion. Thank you, Henri!