Retrieve multiple cell properties from Excel in a single call?

Question

I need to retrieve the background property (Range.Interior.Color) of a few thousand cells. Looping through each cell individually is very slow due to COM-Interop limitations.

Is it possible to retrieve cell properties that aren't .Text, .Value or .Value2 from a Range containing more than one cell in a single call?

Answer 1

I would try the following (written in VBA but can be transformed to C#):

Public Sub GetColors()
    Dim ewsTarget As Worksheet: Set ewsTarget = ActiveWorkbook.Worksheets(1)
    ewsTarget.Copy , ewsTarget.Parent.Worksheets(ewsTarget.Parent.Worksheets.Count)
    Dim ewsCopy As Worksheet: Set ewsCopy = ewsTarget.Parent.Worksheets(ewsTarget.Parent.Worksheets.Count)
    ewsCopy.UsedRange.ClearContents
    ewsCopy.UsedRange.Columns.EntireColumn.ColumnWidth = 0.5
    ewsCopy.UsedRange.Rows.EntireRow.RowHeight = 5#
    ewsCopy.UsedRange.CopyPicture xlScreen, xlBitmap
    ewsCopy.Delete
End Sub

This code puts a bitmap on the clipboard. This bitmap is created from a copy of the worksheet, however, cell contents are removed, so you will see only the background of the cell, furthermore rows and columns are of the same height and length. Your C# program can then acquire this bitmap and get individual pixels considering that the location of cells can be calculated easily since rows and columns have the same height and width.

I know it's only a workaround but I don't think there was any better solution. It is one way only (cannot write, only read), and difficult to extend to other properties (maybe to border and font colors).