合并电子表格文档中的两个相邻单元格

本主题演示如何使用 Open XML SDK for Office 中的类以编程方式合并电子表格文档中的两个相邻单元格。

---

获取 SpreadsheetDocument 对象

在 Open XML SDK 中 SpreadsheetDocument ， 类表示 Excel 文档包。 若要打开和使用 Excel 文档，请从文档创建 类的 SpreadsheetDocument 实例。 基于文档创建实例后，即可获取对包含工作表的主工作簿部件的访问权限。 文档中的文本使用 SpreadsheetML 标记在包中表示为 XML。

从调用方法之 Open 一的文档创建类实例。 提供了多个方法，每个方法都有不同的签名。 本主题中的示例代码使用 Open (String， boolean) 方法与需要两个参数的签名。 第一个参数采用表示要打开的文档的完整路径字符串。 第二个 true 参数为 或 false ，表示是否希望打开文件进行编辑。 如果此参数为 false，则不会保存对文档所做的任何更改。

---

spreadsheetML 文档的基本结构

文档的基本文档结构SpreadsheetML由 和 Sheet 元素组成Sheets，这些元素引用工作簿中的工作表。 将为每张工作表创建单独的 XML 文件。 例如， SpreadsheetML 具有两个 Workbook 工作表 MySheet1 和 MySheet2 的 位于 Workbook.xml 文件中，并在以下代码示例中显示。

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
        <sheets>
            <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
            <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
        </sheets>
    </workbook>

工作表 XML 文件包含一个或多个块级元素，如 SheetData 表示单元格表，并包含一个或多个 Row 元素。 包含 row 一个或多个 Cell 元素。 每个单元格都包含一个 CellValue 表示单元格值的元素。 例如， SpreadsheetML 工作簿中第一个工作表的 （在单元格 A1 中只有值 100） 位于 Sheet1.xml 文件中，并显示在以下代码示例中。

    <?xml version="1.0" encoding="UTF-8" ?> 
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <sheetData>
            <row r="1">
                <c r="A1">
                    <v>100</v> 
                </c>
            </row>
        </sheetData>
    </worksheet>

使用 Open XML SDK，可以创建使用与元素对应的 SpreadsheetML 强类型类的文档结构和内容。 可以在 命名空间中找到 DocumentFormat.OpenXML.Spreadsheet 这些类。 下表列出了对应于 、、sheets、 worksheetsheet和 sheetData 元素的类的workbook类名。

SpreadsheetML 元素	Open XML SDK 类	说明
<workbook/>	DocumentFormat.OpenXML.Spreadsheet.Workbook	主文档部件的根元素。
<sheets/>	DocumentFormat.OpenXML.Spreadsheet.Sheets	块级结构（如工作表、文件版本和 ISO/IEC 29500 规范中指定的其他项）的容器。
<sheet/>	DocumentFormat.OpenXml.Spreadsheet.Sheet	指向工作表定义文件的工作表。
<worksheet/>	DocumentFormat.OpenXML.Spreadsheet。 Worksheet	包含工作表数据的工作表定义文件。
<sheetData/>	DocumentFormat.OpenXML.Spreadsheet.SheetData	按行分组在一起的单元格表。
<row/>	DocumentFormat.OpenXml.Spreadsheet.Row	单元格表中的行。
<c/>	DocumentFormat.OpenXml.Spreadsheet.Cell	行中的单元格。
<v/>	DocumentFormat.OpenXml.Spreadsheet.CellValue	单元格的值。

---

示例代码的工作方式

打开电子表格文件进行编辑后，代码将验证指定的单元格是否存在，如果它们不存在，它将通过调用 CreateSpreadsheetCellIfNotExist 方法将其追加到相应的 Row 对象来创建它们。

C#

// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell. 
static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
{
    string columnName = GetColumnName(cellName);
    uint rowIndex = GetRowIndex(cellName);

    IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex?.Value == rowIndex);

    // If the Worksheet does not contain the specified row, create the specified row.
    // Create the specified cell in that row, and insert the row into the Worksheet.
    if (rows.Count() == 0)
    {
        Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
        Cell cell = new Cell() { CellReference = new StringValue(cellName) };
        row.Append(cell);
        worksheet.Descendants<SheetData>().First().Append(row);
    }
    else
    {
        Row row = rows.First();

        IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference?.Value == cellName);

        // If the row does not contain the specified cell, create the specified cell.
        if (cells.Count() == 0)
        {
            Cell cell = new Cell() { CellReference = new StringValue(cellName) };
            row.Append(cell);
        }
    }
}

Visual Basic

' Given a Worksheet and a cell name, verifies that the specified cell exists.
' If it does not exist, creates a new cell. 
Sub CreateSpreadsheetCellIfNotExist(worksheet As Worksheet, cellName As String)
    Dim columnName As String = GetColumnName(cellName)
    Dim rowIndex As UInteger = GetRowIndex(cellName)

    Dim rows As IEnumerable(Of Row) = worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex?.Value = rowIndex)

    ' If the Worksheet does not contain the specified row, create the specified row.
    ' Create the specified cell in that row, and insert the row into the Worksheet.
    If rows.Count() = 0 Then
        Dim row As New Row() With {
            .RowIndex = New UInt32Value(rowIndex)
        }
        Dim cell As New Cell() With {
            .CellReference = New StringValue(cellName)
        }
        row.Append(cell)
        worksheet.Descendants(Of SheetData)().First().Append(row)
    Else
        Dim row As Row = rows.First()

        Dim cells As IEnumerable(Of Cell) = row.Elements(Of Cell)().Where(Function(c) c.CellReference?.Value = cellName)

        ' If the row does not contain the specified cell, create the specified cell.
        If cells.Count() = 0 Then
            Dim cell As New Cell() With {
                .CellReference = New StringValue(cellName)
            }
            row.Append(cell)
        End If
    End If
End Sub

为了获取列名称，代码会新建一个正则表达式以匹配单元格名称的列名称部分。 此正则表达式匹配任何大写字母或小写字母的组合。 有关正则表达式的详细信息，请参阅正则表达式语言元素（该链接可能指向英文页面）。 代码通过调用 Regex.Match 获取列名称。

C#

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

Visual Basic

' Given a cell name, parses the specified cell to get the column name.
Function GetColumnName(cellName As String) As String
    ' Create a regular expression to match the column name portion of the cell name.
    Dim regex As New Regex("[A-Za-z]+")
    Dim match As Match = regex.Match(cellName)

    Return match.Value
End Function

为了获取行索引，代码会新建一个正则表达式以匹配单元格名称的行索引部分。 此正则表达式匹配任何十进制数字组合。 下面的代码示例创建一个正则表达式以匹配单元格名称的行索引部分（由十进制数字组成）。

C#

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

Visual Basic

' Given a cell name, parses the specified cell to get the row index.
Function GetRowIndex(cellName As String) As UInteger
    ' Create a regular expression to match the row index portion the cell name.
    Dim regex As New Regex("\d+")
    Dim match As Match = regex.Match(cellName)

    Return UInteger.Parse(match.Value)
End Function

示例代码

以下代码合并文档包中的两个 Row 相邻单元格。 当合并两个单元格时，只会保留其中一个单元格中的内容。 在从左到右的语言中，保留左上方单元格中的内容。 在从右到左的语言中，保留右上方单元格中的内容。

以下是使用 C# 和 Visual Basic 编写的完整示例代码。

C#

static void MergeTwoCells(string docName, string sheetName, string cell1Name, string cell2Name)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        Worksheet? worksheet = GetWorksheet(document, sheetName);
        if (worksheet is null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
        {
            return;
        }

        // 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.
        MergeCell mergeCell = new MergeCell() { Reference = new StringValue(cell1Name + ":" + cell2Name) };
        mergeCells.Append(mergeCell);
    }
}

// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell. 
static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
{
    string columnName = GetColumnName(cellName);
    uint rowIndex = GetRowIndex(cellName);

    IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex?.Value == rowIndex);

    // If the Worksheet does not contain the specified row, create the specified row.
    // Create the specified cell in that row, and insert the row into the Worksheet.
    if (rows.Count() == 0)
    {
        Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
        Cell cell = new Cell() { CellReference = new StringValue(cellName) };
        row.Append(cell);
        worksheet.Descendants<SheetData>().First().Append(row);
    }
    else
    {
        Row row = rows.First();

        IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference?.Value == cellName);

        // If the row does not contain the specified cell, create the specified cell.
        if (cells.Count() == 0)
        {
            Cell cell = new Cell() { CellReference = new StringValue(cellName) };
            row.Append(cell);
        }
    }
}

// Given a SpreadsheetDocument and a worksheet name, get the specified worksheet.
static Worksheet? GetWorksheet(SpreadsheetDocument document, string worksheetName)
{
    WorkbookPart workbookPart = document.WorkbookPart ?? document.AddWorkbookPart();
    IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);

    string? id = sheets.First().Id;
    WorksheetPart? worksheetPart = id is not null ? (WorksheetPart)workbookPart.GetPartById(id) : null;

    return worksheetPart?.Worksheet;
}

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

Visual Basic

Sub MergeTwoCells(docName As String, sheetName As String, cell1Name As String, cell2Name As String)
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim worksheet As Worksheet = GetWorksheet(document, sheetName)
        If worksheet Is Nothing OrElse String.IsNullOrEmpty(cell1Name) OrElse String.IsNullOrEmpty(cell2Name) Then
            Return
        End If

        ' Verify if the specified cells exist, and if they do not exist, create them.
        CreateSpreadsheetCellIfNotExist(worksheet, cell1Name)
        CreateSpreadsheetCellIfNotExist(worksheet, cell2Name)

        Dim mergeCells As MergeCells
        If worksheet.Elements(Of MergeCells)().Count() > 0 Then
            mergeCells = worksheet.Elements(Of MergeCells)().First()
        Else
            mergeCells = New MergeCells()

            ' Insert a MergeCells object into the specified position.
            If worksheet.Elements(Of CustomSheetView)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of CustomSheetView)().First())
            ElseIf worksheet.Elements(Of DataConsolidate)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of DataConsolidate)().First())
            ElseIf worksheet.Elements(Of SortState)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of SortState)().First())
            ElseIf worksheet.Elements(Of AutoFilter)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of AutoFilter)().First())
            ElseIf worksheet.Elements(Of Scenarios)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of Scenarios)().First())
            ElseIf worksheet.Elements(Of ProtectedRanges)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of ProtectedRanges)().First())
            ElseIf worksheet.Elements(Of SheetProtection)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of SheetProtection)().First())
            ElseIf worksheet.Elements(Of SheetCalculationProperties)().Count() > 0 Then
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of SheetCalculationProperties)().First())
            Else
                worksheet.InsertAfter(mergeCells, worksheet.Elements(Of SheetData)().First())
            End If
        End If

        ' Create the merged cell and append it to the MergeCells collection.
        Dim mergeCell As New MergeCell() With {
            .Reference = New StringValue(cell1Name & ":" & cell2Name)
        }
        mergeCells.Append(mergeCell)
    End Using
End Sub

' Given a Worksheet and a cell name, verifies that the specified cell exists.
' If it does not exist, creates a new cell. 
Sub CreateSpreadsheetCellIfNotExist(worksheet As Worksheet, cellName As String)
    Dim columnName As String = GetColumnName(cellName)
    Dim rowIndex As UInteger = GetRowIndex(cellName)

    Dim rows As IEnumerable(Of Row) = worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex?.Value = rowIndex)

    ' If the Worksheet does not contain the specified row, create the specified row.
    ' Create the specified cell in that row, and insert the row into the Worksheet.
    If rows.Count() = 0 Then
        Dim row As New Row() With {
            .RowIndex = New UInt32Value(rowIndex)
        }
        Dim cell As New Cell() With {
            .CellReference = New StringValue(cellName)
        }
        row.Append(cell)
        worksheet.Descendants(Of SheetData)().First().Append(row)
    Else
        Dim row As Row = rows.First()

        Dim cells As IEnumerable(Of Cell) = row.Elements(Of Cell)().Where(Function(c) c.CellReference?.Value = cellName)

        ' If the row does not contain the specified cell, create the specified cell.
        If cells.Count() = 0 Then
            Dim cell As New Cell() With {
                .CellReference = New StringValue(cellName)
            }
            row.Append(cell)
        End If
    End If
End Sub

' Given a SpreadsheetDocument and a worksheet name, get the specified worksheet.
Function GetWorksheet(document As SpreadsheetDocument, worksheetName As String) As Worksheet
    Dim workbookPart As WorkbookPart = If(document.WorkbookPart, document.AddWorkbookPart())
    Dim sheets As IEnumerable(Of Sheet) = workbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = worksheetName)

    Dim id As String = sheets.First().Id
    Dim worksheetPart As WorksheetPart = If(id IsNot Nothing, CType(workbookPart.GetPartById(id), WorksheetPart), Nothing)

    Return If(worksheetPart IsNot Nothing, worksheetPart.Worksheet, Nothing)
End Function

' Given a cell name, parses the specified cell to get the column name.
Function GetColumnName(cellName As String) As String
    ' Create a regular expression to match the column name portion of the cell name.
    Dim regex As New Regex("[A-Za-z]+")
    Dim match As Match = regex.Match(cellName)

    Return match.Value
End Function

' Given a cell name, parses the specified cell to get the row index.
Function GetRowIndex(cellName As String) As UInteger
    ' Create a regular expression to match the row index portion the cell name.
    Dim regex As New Regex("\d+")
    Dim match As Match = regex.Match(cellName)

    Return UInteger.Parse(match.Value)
End Function

---

另请参阅

• Open XML SDK 类库参考