C#Excel互操作以快速方式合并单元格 [英] C# Excel interop merge cells in fast way

问题描述

我想合并单元格并多次执行... 我之所以使用此合并，是因为我想要更多一张桌子. 每个表的列数不同，另一个表的列大小不同... 合并的数量和位置未知

I want to merge cells and to do it multiple times... The reason I'm using this merge its because I want more the one table. and a different number of columns for each table and different size of a column on the another... The number and the places of the merges are unknown

    using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp111
{
    class Program
    {
        static void Main(string[] args)
        {
            string x = @"C:\Users\hed-b\source\repos\TESTUserFormFromExcel\ConsoleApp111\bin\Debug\TEST.xltm";

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application
            {
                DisplayAlerts = false,
                Visible = false,
                ScreenUpdating = false,
            };

            Workbook wb = xlApp.Workbooks.Open(x);
            Worksheet ws = (Worksheet)wb.Worksheets[1];
            if (ws == null)
            {
                Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
                return;
            }
            Stopwatch stopWatch = Stopwatch.StartNew();

            for (int i = 2; i <= 10; i++)
            {
                ws.get_Range("A"+i, "B"+i).Merge();

            }
            for (int i = 2; i <= 10; i++)
            {
                ws.get_Range("C" + i, "D" + i).Merge();
            }
            for (int i = 2; i <= 10; i++)
            {
                ws.get_Range("E" + i, "H" + i).Merge();
            }
            Console.WriteLine(stopWatch.ElapsedMilliseconds);

            stopWatch = Stopwatch.StartNew();
            ws.get_Range("A" + 2, "H" + 10).Value2 = new string[10, 5]{
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
                { "aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaa","","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
            };

            ws.get_Range("A" + 11, "H" + 15).Value2 = new string[5, 8]{
                { "bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb"},
                { "bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb"},
                { "bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb"},
                { "bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb"},
                { "bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb","bbbbb"}
            };

            Console.WriteLine(stopWatch.ElapsedMilliseconds);

            xlApp.ScreenUpdating = true;
            xlApp.Visible = true;
        }
    }
}

我只是在寻找更好的方法来更快地完成它

I just looking for better way to do it faster

所有这些示例程序合并大约需要1724 ms.在我的实际程序中，我进行了更多的合并，并花费了5000多个毫秒. 合并了大部分时间...

All these example program merges take about 1724 ms. in my actual program I have much more merges and take more than 5000 ms. This merges its what take the most of the time...

推荐答案

如果立即执行合并并让Excel处理需要合并的多个范围，则可能会更好.

It'll probably perform better if you execute the merge at once and let Excel handle the multiple ranges you need to merge.

即定义一个包含多个区域的范围对象，并在其上调用Merge.它不会将所有内容合并到一个大的胖单元中，但是会合并定义的多个区域:

I.e. define a range object which holds the multiple areas and call the Merge on it. It won't merge everything into one big fat cell, but will merge the multiple areas as defined:

var ranges = String.Empty();

for (int i = 2; i <= 10; i++)
{
    ranges += $"A{i}:B{i},C{i}:D{i},E{i}:H{i},";
}

ws.get_Range(ranges.TrimEnd(',')).Merge();

这篇关于C#Excel互操作以快速方式合并单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！