还有其他更快的方法吗？

below code does the following: it takes a range, then finds distinct values in a range, and stores them in a d_distinct array, also for every distinct value it creates the distinct color, then using the Excel.FormatCondition it colors the range... (my current range is A1:HM232)

下面的代码执行以下操作:它需要一个范围,然后在一个范围内找到不同的值,并将它们存储在一个d_distinct数组中,也为它创建不同颜色的每个不同值,然后使用Excel.FormatCondition它为范围着色.. 。(我目前的范围是A1:HM232)

for (int t = 0; t < d_distinct.Length; t++ )
{                        
    Excel.FormatCondition cond =
        (Excel.FormatCondition)range.FormatConditions.Add(
        Excel.XlFormatConditionType.xlCellValue,
        Excel.XlFormatConditionOperator.xlEqual, 
        "="+d_distinct[t],
        mis, mis, mis, mis, mis);
    cond.Interior.PatternColorIndex = 
        Excel.Constants.xlAutomatic;
    cond.Interior.TintAndShade = 0;
    cond.Interior.Color = ColorTranslator.ToWin32(c[t]);
    cond.StopIfTrue = false;                        
}

But this works too slow... user will have to sit and wait for about a minute... I did this with this way since, otherwise if I do it with one line of code simply doing this (which colors amazingly fast)

但是这个工作太慢了...用户将不得不坐下等待大约一分钟...我这样做是因为,否则,如果我用一行代码执行此操作(简单快速地执行此操作)

range.FormatConditions.AddColorScale(3);

I will not be able to request the color of the cell... (i can have more than ten distinct values in a range)

我将无法请求单元格的颜色...(我可以在一个范围内有十个以上的不同值)

can you help me to make my first way work faster? thanks in advance!

你能帮助我让我的第一条路更快地工作吗?提前致谢!

2 个解决方案

#1

Try turning off screen updating while the code is running and turn it back on afterwards. In VBA, that would be:

尝试在代码运行时关闭屏幕更新,然后再打开它。在VBA中,那将是:

Application.ScreenUpdating = False
// do stuff
Application.ScreenUpdating = True

As you are not using VBA, try http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.screenupdating(office.11).aspx for guidance

由于您没有使用VBA,请尝试http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.screenupdating(office.11).aspx获取指导

#2

Have you tried using the OpenXML SDK to work with Office 2007? I have used it for word and found it much faster than using vba or COM. Try version 2.0 or 1.0. I think 2.0 is still CTP though. Much more intuitive than vba in my book though. Otherwise, the screenupdating trick is probably the easiest thing to do.

您是否尝试过使用OpenXML SDK来使用Office 2007?我用它来说话,发现它比使用vba或COM快得多。尝试2.0或1.0版。我认为2.0仍然是CTP。比我书中的vba更直观。否则,屏幕更新技巧可能是最简单的事情。

#1