vsto - Getting Excel Cell Properties in Bulk

Question

When fetching values from a range in Excel, it is much more efficient to get the values in "bulk" (as a 2D array) than looping through each row and column. For example:

Dim range = Globals.Table.Range("A1:E5")
Dim values(,) As Object = range.Value

With 25 cells it doesn't make much of a difference, but with 10,000 rows by 20 columns it certainly does. So far so good.

My question is: How can one do "bulk" fetching for other properties?

For example, I want to find which cells are colored a certain way. I would love to do something like "range.Interior.Color", but that returns just one value, not an array of values. And so I end up looping, which is probably 100 or even 1000 times slower. For large tables, this is really a killer.

---

It looks like .Formula behaves just like .Value: I can fetch multiple ones at a time. But I've yet to cajole colors into playing nice.

Answer 1

I don't think you can get those properties as an array because of how Excel stores that information. Excel does not store formatting for each cell individually but rather stores a particular blend of formats along with an internal "list" of the ranges that use that format.

You can get a sense of how the formatting is stored by creating a small test file with various formats and saving it as XML format (in 2010 at least, you need to use "XML Spreadsheet 2003").

This article may also help.