批量获取Excel单元格属性

发布于 2024-12-20 17:30:28 字数 482 浏览 0 评论 0原文

当从 Excel 中的某个范围中获取值时，“批量”获取值（作为二维数组）比循环遍历每一行和每一列要高效得多。例如：

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

对于 25 个单元格来说，差别不大，但是对于 10,000 行 x 20 列来说，差别肯定很大。到目前为止，一切都很好。

我的问题是：如何“批量”获取其他属性？

例如，我想找出哪些单元格以某种方式着色。我想做一些类似“range.Interior.Color”的事情，但它只返回一个值，而不是值数组。所以我最终会进行循环，这可能会慢 100 倍甚至 1000 倍。对于大桌子来说，这确实是一个杀手。

看起来 .Formula 的行为就像 .Value 一样：我可以一次获取多个。但我还没有说服颜色发挥得很好。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦亿 2024-12-27 17:30:28

我认为您无法以数组形式获取这些属性，因为 Excel 存储这些信息的方式不同。 Excel 不会单独存储每个单元格的格式，而是存储特定的格式混合以及使用该格式的范围的内部“列表”。

您可以通过创建一个具有各种格式的小测试文件并将其保存为 XML 格式（至少在 2010 年，您需要使用“XML Spreadsheet 2003”）来了解格式的存储方式。

这篇文章也可能帮助。

回复收藏 0 原文

不喜欢何必死缠烂打 2024-12-27 17:30:28

“我想查找哪些单元格以某种方式着色”

在 VBA 中，您可以使用按格式搜索的 Find 方法运行快速例程。例如，查找与 A1 中的单元格具有相同单元格字体颜色和内部颜色的所有单元格。我想你可以在 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

在此处输入图像描述

"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

回复收藏 0 原文

~没有更多了~