在一次调用中从Excel中检索多个单元格属性？

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.

我需要检索几千个单元格的背景属性（Range.Interior.Color）。由于COM-Interop限制，单独循环每个单元格非常慢。

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?

是否可以从单个调用中包含多个单元格的Range中检索不是.Text，.Value或.Value2的单元格属性？

2 个解决方案

#1

You can do this, but its not trivial, so I'm not really sure if its worth the hassle trying to emulate a similar behavior.

你可以做到这一点，但它不是微不足道的，所以我不确定它是否值得尝试模仿类似行为的麻烦。

Basically you need to create a macro in Excel that does the work for you, and then simply bring back the results once the macro has finished. This basically emulates the behaviour of Value. I'm not really sure why MS decided to not implement all properties in Range to behave the same way, wierd.

基本上你需要在Excel中创建一个为你工作的宏，然后在宏完成后简单地恢复结果。这基本上模拟了Value的行为。我不确定为什么MS决定不在Range中实现所有属性以同样的方式行事，这很奇怪。

In order to do this you need to reference the Microsoft Visual Basic for Application Extensibility 5.3 COM library. This gives you the required tools to dynamically build and add macros to an Excel workbook.

为此，您需要引用Microsoft Visual Basic for Application Extensibility 5.3 COM库。这为您提供了动态构建宏并将其添加到Excel工作簿所需的工具。

The first step is to create a method that adds a module and the desired macro to the workbook you are interacting with (you could also open a blank workbook and create the macro there if you are using the function on different sheets you are opening and closing).

第一步是创建一个方法，将模块和所需的宏添加到您正在与之交互的工作簿中（如果您在打开和关闭的不同工作表上使用该功能，还可以打开一个空白工作簿并在那里创建宏））。

The following example adds a macro that returns an array with all the cell colors in the specified range. This is written in VBA, I don't have VS with me but the C# version should be pretty straightforward to port.

下面的示例添加一个宏，该宏返回一个数组，其中包含指定范围内的所有单元格颜色。这是用VBA编写的，我没有VS和我，但C＃版本应该非常简单。

Sub AddCode()
   Dim wb As Workbook
   Dim xPro As VBIDE.VBProject
   Dim xCom As VBIDE.VBComponent
   Dim xMod As VBIDE.CodeModule

   Set wb = ActiveWorkbook

   With wb
       Set xPro = .VBProject
       Set xCom = xPro.VBComponents.Add(vbext_ct_StdModule)
       Set xMod = xCom.CodeModule

       With xMod
           .AddFromString "Public Function GetInteriorColors(r As Range) As Variant" & vbCrLf & _
                          "    Dim colors() As Long" & vbCrLf & _
                          "    Dim row As Integer" & vbCrLf & _
                          "    Dim column As Integer" & vbCrLf & _
                          "    ReDim colors(r.Rows.Count - 1, r.Columns.Count - 1)" & vbCrLf & _
                          "    For row = 1 To r.Rows.Count" & vbCrLf & _
                          "        For column = 1 To r.Columns.Count" & vbCrLf & _
                          "            colors(row - 1, column - 1) = r.Cells(row, column).Interior.Color" & vbCrLf & _
                          "        Next" & vbCrLf & _
                          "    Next" & vbCrLf & _
                          "    GetInteriorColors = colors" & vbCrLf & _
                          "End Function"
       End With
   End With
End Sub

Something to note; I'm sometimes getting a weird error when executing AddFromString. The macro code is added correctly to the module but I sometimes get an error; swallowing it doesn't seem to be harmful but if you have the same issue I'd look into it.

需要注意的事项;执行AddFromString时，我有时会遇到奇怪的错误。宏代码正确添加到模块，但我有时会收到错误;吞咽它似乎没有害处，但如果你有同样的问题，我会调查它。

Now, once you have the macro, bringing back the result is easy (again, written in VBA):

现在，一旦你有了宏，带回结果很容易（再次，用VBA编写）：

Public Function GetColors(r As Range) As Long()
    //Note the absolute path to the macro; this is probably needed if the macro is in a different workbook.
    GetColors = Application.Run(ActiveWorkbook.Name & "!GetInteriorColors", r)
End Function

#2

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

我会尝试以下（用VBA编写，但可以转换为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.

此代码将位图放在剪贴板上。此位图是从工作表的副本创建的，但是，单元格内容将被删除，因此您将只看到单元格的背景，此外行和列具有相同的高度和长度。然后，您的C＃程序可以获取此位图并获取单个像素，因为行和列具有相同的高度和宽度，因此可以轻松计算单元格的位置。

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).

我知道这只是一种解决方法，但我认为没有更好的解决方案。它只是一种方式（不能写，只能读），并且难以扩展到其他属性（可能是边框和字体颜色）。

#1