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

获取 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> 
        </sheetData>
    </worksheet>
使用 Open XML SDK，可以创建使用与元素对应的 SpreadsheetML 强类型类的文档结构和内容。 可以在 命名空间中找到 DocumentFormat.OpenXML.Spreadsheet 这些类。 下表列出了对应于 、、sheets、 worksheetsheet和 sheetData 元素的类的workbook类名。
<sheets/>
DocumentFormat.OpenXML.Spreadsheet.Sheets
块级结构（如工作表、文件版本和 ISO/IEC 29500 规范中指定的其他项）的容器。
<sheet/>
DocumentFormat.OpenXml.Spreadsheet.Sheet
指向工作表定义文件的工作表。
<worksheet/>
DocumentFormat.OpenXML.Spreadsheet。 Worksheet
包含工作表数据的工作表定义文件。
<sheetData/>
DocumentFormat.OpenXML.Spreadsheet.SheetData
按行分组在一起的单元格表。
DocumentFormat.OpenXml.Spreadsheet.Row
单元格表中的行。
DocumentFormat.OpenXml.Spreadsheet.Cell
行中的单元格。
DocumentFormat.OpenXml.Spreadsheet.CellValue
单元格的值。
// 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);
        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 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)
        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 获取列名称。
// 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 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.
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);
' 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 编写的完整示例代码。
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();
            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());
                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);
        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);
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()
            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())
                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)
        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 类库参考