在C#中的Excel中格式化多行的最快方法 [英] Fastest way to format multiple rows in an excel in C#

问题描述

我有一个庞大的数据集，我想将其写入Excel，并且需要根据业务逻辑对行进行条件格式设置.因此，对于数据插入部分，我正在使用数据数组填充Excel，并且工作速度非常快.但是，在格式化行时，我发现性能严重下降.进行格式化几乎要花费两倍多的时间.

I have a huge dataset that I want to write into the Excel and need to perform conditional formatting of rows based on a business logic. So, for the data insertion part, I am using a data array to populate the Excel and it works pretty fast. However, I see a severe performance degradation when it comes to formatting the rows. It almost takes more than double the time just to do the formatting.

到目前为止，我正在将格式化应用于单个行并遍历一系列行.但是，我想知道是否可以一次选择多行并将批量格式化选项应用于这些行: 这是我现在所拥有的:

As of now, I am applying formatting to individual rows and loop through a series of rows. However, I am wondering if I can select multiple rows at a time and apply bulk formatting options to those rows: Here is what I have right now:

foreach (int row in rowsToBeFormatted)
{
    Excel.Range range = (Excel.Range)xlsWorksheet.Range[xlsWorksheet.Cells[row + introFormat, 1], xlsWorksheet.Cells[row + introFormat, 27]];
    range.Font.Size = 11;
    range.Interior.ColorIndex = 15;
    range.Font.Bold = true;
}

这是一个演示如何尝试选择范围中的多行并应用格式的演示:

And here is a demo of how I am trying to select multiple rows to the range and apply the formatting:

string excelrange = "A3:AA3,A83:AA83,A88:AA88,A94:AA94,A102:AA102,A106:AA106,A110:AA110,...." (string with more than 3000 characters)
xlsWorksheet.get_Range(excelrange).Interior.Color = Color.SteelBlue;

但是，执行代码时出现以下错误:

However, I get the following error when I execute the code:

HRESULT的异常:0x800A03EC

Exception from HRESULT: 0x800A03EC

，内部异常没有任何内容.有什么想法可以达到预期的效果吗?

and there is nothing in inner exception. Any ideas how can I achieve the desired result?

推荐答案

根据问题下的注释，范围字符串的硬编码限制为255个字符，但是我找不到任何有关它的文档.另一位评论者建议使用分号作为分隔符，但

As per comments under the question, there's hard-coded limit of 255 characters for a range string, however I wasn't able to find any documentation about it. Another commenter suggested to use semicolon as separator, but the documentation clearly states that comma should be used as union operator in range string:

以应用程序语言的 A1样式表示形式的范围名称.它可以包括范围运算符(冒号)，相交运算符(空格)或联合运算符(逗号).它还可以包含美元符号，但它们会被忽略.您可以在范围的任何部分中使用本地定义的名称.如果使用名称，则假定该名称使用应用程序的语言.

The name of the range in A1-style notation in the language of the application. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they are ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the application.

那我们从这里去哪里呢?单独格式化每个范围确实效率不高. Application界面提供方法

So where do we go from here? Formatting each range individually is indeed inefficient. Application interface provides method Union, but calling it in a loop is as inefficient as individual formatting. So the natural choice is to use the range string limit to the maximum and thus minimizing number of calls to COM interface.

您可以将整个范围分割为多个格式；每个不超过255个字符的限制.我将使用枚举器实现它:

You can split the full range to format into chunks; each not exceeding 255 characters limit. I would implement it using enumerators:

static IEnumerable<string> GetChunks(IEnumerable<string> ranges)
{
    const int MaxChunkLength = 255;
    var sb = new StringBuilder(MaxChunkLength);
    foreach (var range in ranges)
    {
        if (sb.Length > 0)
        {
            if (sb.Length + range.Length + 1 > MaxChunkLength)
            {
                yield return sb.ToString();
                sb.Clear();
            }
            else
            {
                sb.Append(",");
            }
        }
        sb.Append(range);
    }
    if (sb.Length > 0)
    {
        yield return sb.ToString();
    }
}

var rowsToFormat = new[] { 3, 83, 88, 94, 102, 106, 110/*, ...*/ }
var rowRanges = rowsToFormat.Select(row => "A" + row + ":" + "AA" + row);

foreach (var chunk in GetChunks(rowRanges))
{
    var range = xlsWorksheet.Range[chunk];
    // do formatting stuff here
}

上述内容比单个格式快10到15倍:

The above is 10-15 times faster than individual formatting:

foreach (var rangeStr in rowRanges)
{
    var range = xlsWorksheet.Range[rangeStr];
    // do formatting stuff here
}

我还可以看到更多的优化空间，例如对连续的行进行分组，但是如果您要格式化具有小计的离散行，那将无济于事.

I can also see further space for optimization like grouping contiguous rows, but in case you are formatting discrete rows with subtotals, it won't help.

这篇关于在C#中的Excel中格式化多行的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！