Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting Excel Cell Properties in Bulk

Tags:

excel

vsto

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.

like image 342
Michael Zlatkovsky - Microsoft Avatar asked Dec 09 '11 20:12

Michael Zlatkovsky - Microsoft


2 Answers

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.

like image 162
Rachel Hettinger Avatar answered Oct 06 '22 03:10

Rachel Hettinger


"I want to find which cells are colored a certain way"

in VBA you can run a quick routine using the Find method that searches by format. For example to find all cells with the same cell font colour and interior colour as the cell in A1. I presume you can use something similar in VSTO

Sub FindFormat()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim strAddress As String
    With Application.FindFormat
        .Interior.ColorIndex = [a1].Interior.ColorIndex
        .Font.Color = [a1].Font.Color
    End With
    Set rng1 = Cells.Find("", [a1], xlFormulas, , , , , , True)
    If Not rng1 Is Nothing Then
        strAddress = rng1.Address
        Set rng2 = rng1
        Do
            Set rng1 = Cells.Find("", rng1, xlFormulas, , , , , , True)
            Set rng2 = Union(rng1, rng2)
        Loop While rng1.Address <> strAddress
        MsgBox "Range similar format to A1 is " & rng2.Address
    End If
End Sub

enter image description here

like image 44
brettdj Avatar answered Oct 06 '22 02:10

brettdj