c# - 使用openxml合并excel中的相邻单元格

Question

我想使用 openxml 在 excel 中合并五个相邻的单元格。我试图通过添加一个单元格来对以下代码片段进行修改，但它不起作用。它只对两个单元格正常工作。如果我想合并多个单元格怎么办

// Given a document name, a worksheet name, and the names of two adjacent cells, merges the two cells.
    // When two cells are merged, only the content from one cell is preserved:
    // the upper-left cell for left-to-right languages or the upper-right cell for right-to-left languages.
    private static void MergeTwoCells(Worksheet worksheet, string cell1Name, string cell2Name)
    {
        // Open the document for editing.
        // Verify if the specified cells exist, and if they do not exist, create them.
        //CreateSpreadsheetCellIfNotExist(worksheet, cell1Name);
        //CreateSpreadsheetCellIfNotExist(worksheet, cell2Name);

            MergeCells mergeCells;
            if (worksheet.Elements<MergeCells>().Count() > 0)
            {
                mergeCells = worksheet.Elements<MergeCells>().First();
            }
            else
            {
                mergeCells = new MergeCells();

                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
                }
                else if (worksheet.Elements<DataConsolidate>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<DataConsolidate>().First());
                }
                else if (worksheet.Elements<SortState>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SortState>().First());
                }
                else if (worksheet.Elements<AutoFilter>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<AutoFilter>().First());
                }
                else if (worksheet.Elements<Scenarios>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<Scenarios>().First());
                }
                else if (worksheet.Elements<ProtectedRanges>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<ProtectedRanges>().First());
                }
                else if (worksheet.Elements<SheetProtection>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetProtection>().First());
                }
                else if (worksheet.Elements<SheetCalculationProperties>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetCalculationProperties>().First());
                }
                else
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                }
            }

            // Create the merged cell and append it to the MergeCells collection.

            string s1 = cell1Name + ":" + cell2Name ;
            MergeCell mergeCell = new MergeCell() { Reference = s1 };
            mergeCells.Append(mergeCell);

            worksheet.Save();

    }

Answer 1

您无需更改代码。如果要合并多个单元格，只需确保 cell1Name 是要合并的单元格的左上角单元格，cell2Name 是右下单元格(即 A1:B2 将合并单元格 A1、A2、B1 和 B2)。

此外，只有左上角单元格中的值会被保留(或右上角单元格中的值，对于从右到左的文本)。