c# - 使用 C# Closed XML 在 excel 中合并列的性能

Question

我有一个列表，它包含另外 9 个属性，因为它包含一个子列表。

例如

Public Class BaseClass
{
    public string Item1 { get; set; }
    public string Item2 { get; set; }
    public List<ChildClass> Item3 { get; set; }
    ............
}

Public Class ChildClass
{
    public string Item1 { get; set; }
    public string Item2 { get; set; }
    public string Item3 { get; set; }
}

我编程将集合转换为具有列分组的 Excel 文件。在我的实际原始列表中，我有 3000 多个项目，每个项目都有一个计数为 4 到 6 的子列表。要迭代集合，1500 个集合需要 20 多分钟。

示例 Excel 工作表快照:

请引用以下示例代码，它生成带有虚拟数据的 Excel

我正在使用 ClosedXML - 请从 NuGet 获取引用

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


class Program
{
    static void Main(string[] args)
    {
        Program obj = new Program();
        obj.GenerateData();
    }

    public void GenerateData()
    {
        XLWorkbook xlWorkBook = new XLWorkbook();
        IXLWorksheet xlWorkSheet = xlWorkBook.Worksheets.Add("SearchResults");

        Stopwatch stopwatch = new Stopwatch();
        List<TimeSpan> timeElapsedList = new List<TimeSpan>();

        Console.WriteLine("Excel Generation Started...");

        stopwatch.Start();
        int counter = 0;
        int length = 1500;
        int rowCount = 1;

        for (int i = 0; i < length; i++)
        {
            Stopwatch loopstopwatch = new Stopwatch();
            loopstopwatch.Start();
            counter++;

            #region Loop Statements Start
            int rowHeight = 5;

            int minHt = rowCount;
            int maxHt = rowCount + rowHeight - 1;

            xlWorkSheet.Cell(rowCount, 1).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 1).Value = "Row " + i + " - Item 1";
            xlWorkSheet.Cell(rowCount, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("A" + minHt.ToString() + ":A" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 2).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 2).Value = "Row " + i + " - Item 2";
            xlWorkSheet.Cell(rowCount, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("B" + minHt.ToString() + ":B" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 3).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 3).Value = "Row " + i + " - Item 3";
            xlWorkSheet.Cell(rowCount, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("C" + minHt.ToString() + ":C" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 4).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 4).Value = "Row " + i + " - Item 4";
            xlWorkSheet.Cell(rowCount, 4).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("D" + minHt.ToString() + ":D" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 5).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 5).Value = "Row " + i + " - Item 5";
            xlWorkSheet.Cell(rowCount, 5).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("E" + minHt.ToString() + ":E" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 6).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 6).Value = "Row " + i + " - Item 6";
            xlWorkSheet.Cell(rowCount, 6).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("F" + minHt.ToString() + ":F" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 7).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 7).Value = "Row " + i + " - Item 7";
            xlWorkSheet.Cell(rowCount, 7).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("G" + minHt.ToString() + ":G" + maxHt).Column(1).Merge();

            xlWorkSheet.Cell(rowCount, 8).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(rowCount, 8).Value = "Row " + i + " - Item 8";
            xlWorkSheet.Cell(rowCount, 8).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("H" + minHt.ToString() + ":H" + maxHt).Column(1).Merge();

            for (int j = 0; j < rowHeight; j++)
            {
                xlWorkSheet.Cell(rowCount, 9).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 9).Value = "Row " + i + " | SubRow" + j + " - Sub Item 1";
                xlWorkSheet.Cell(rowCount, 10).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 10).Value = "Row " + i + " | SubRow" + j + " - Sub Item 2";
                xlWorkSheet.Cell(rowCount, 11).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 11).Value = "Row " + i + " | SubRow" + j + " - Sub Item 3";
                xlWorkSheet.Cell(rowCount, 12).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 12).Value = "Row " + i + " | SubRow" + j + " - Sub Item 4";
                xlWorkSheet.Cell(rowCount, 13).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 13).Value = "Row " + i + " | SubRow" + j + " - Sub Item 5";
                xlWorkSheet.Cell(rowCount, 14).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 14).Value = "Row " + i + " | SubRow" + j + " - Sub Item 6";
                xlWorkSheet.Cell(rowCount, 15).WorksheetColumn().Width = 25;
                xlWorkSheet.Cell(rowCount, 15).Value = "Row " + i + " | SubRow" + j + " - Sub Item 7";

                rowCount++;
            }

            xlWorkSheet.Cell(minHt, 16).WorksheetColumn().Width = 25;
            xlWorkSheet.Cell(minHt, 16).Value = "Row " + i + " - Item 9";
            xlWorkSheet.Cell(minHt, 16).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            xlWorkSheet.Range("P" + minHt.ToString() + ":P" + maxHt).Column(1).Merge();

            #endregion

            loopstopwatch.Stop();
            timeElapsedList.Add(loopstopwatch.Elapsed);

            Console.WriteLine("Iteration No: {0} - Time Taken {1}", counter, loopstopwatch.Elapsed);
        }

        stopwatch.Stop();

        Console.WriteLine("Excel Construction Completed !");
        Console.WriteLine("Total Time Taken to Complete {0}", stopwatch.Elapsed);
        Console.WriteLine("Minimum Time : {0}", timeElapsedList.Min());
        Console.WriteLine("Maximum Time : {0}", timeElapsedList.Max());
    }

}

请帮助我如何在时间管理方面通过列合并优化代码。

Answer 1

如果您确定要合并的范围不相交(看起来确实如此)，则可以通过使用以下 Merge 重载获得更好的性能:

IXLRange Merge(bool checkIntersect)

并传递 false(您当前使用的无参数 Merge 重载传递 true)作为 checkIntersect。看起来检查交集的 ClosedXML 实现具有 O(N^2) 时间复杂度。

很快，将所有 .Merge() 调用替换为 .Merge(false)，执行时间将接近没有合并的调用。