Объединение двух смежных ячеек в электронной таблице

Статья
09/24/2014

Дата последнего изменения: 30 ноября 2010 г.

Применимо к: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

В этой статье
Получение объекта SpreadsheetDocument
Базовая структура документа SpreadsheetML
Механизм работы примера кода
Пример кода

В этом разделе показано, как использовать классы в пакете SDK Open XML версии 2.0 для Microsoft Office для слияния двух смежных ячеек в электронной таблице программным путем.

Для компиляции кода, представленного в этом разделе, требуются следующие директивы сборки.

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Text.RegularExpressions

Получение объекта SpreadsheetDocument

В Open XML SDK класс SpreadsheetDocument представляет пакет документа Excel. Чтобы открыть для работы документ Excel, необходимо создать на основе документа экземпляр класса SpreadsheetDocument. После создания экземпляра класса на основе документа можно получить доступ к основной части книги, которая содержит листы. Текст документа представлен в пакете в формате XML с использованием разметки SpreadsheetML.

Чтобы создать экземпляр класса на основе документа, необходимо вызвать один из перегруженных методов Open(). Доступно несколько таких методов с разными сигнатурами. В примере кода в этом разделе используется метод Open(String, Boolean) с сигнатурой, требующей два параметра. Первый параметр принимает строку с полным путем, представляющим открываемый документ. Второй может принимать значение true или false и указывает, будет ли файл открыт для редактирования. Если этот параметр принимает значение false, внесенные в документ изменения не сохраняются.

Код, вызывающий метод Open, показан в приведенной ниже инструкции using.

// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true)) 
{
    // Insert other code here.
}

' Open the document for editing.
Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
    ' Insert other code here.
End Using

Инструкцию using рекомендуется использовать вместо традиционной последовательности .Open, .Save, .Close. Это позволяет обеспечить автоматический вызов метода Dispose (внутреннего метода, используемого пакетом SDK Open XML для очистки ресурсов) при достижении закрывающей скобки. Блок, следующий за инструкцией using, создает область для объекта, создаваемого или именуемого в инструкции using, в данном случае — document.

Базовая структура документа SpreadsheetML

Базовая структура документа SpreadsheetML состоит из элементов Sheets и Sheet, которые ссылаются на листы в книге Workbook. Для каждого листа Worksheet создается отдельный XML-файл. Например, разметка SpreadsheetML для книги с двумя листами MySheet1 и MySheet2 хранится в файле Workbook.xml. Эта разметка приведена ниже.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<workbook xmlns=https://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="https://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. sheetData представляет таблицу и содержит один или несколько элементов Row. row содержит один или несколько элементов Cell. Каждая ячейка содержит один или несколько элементов CellValue, которые представляют значение ячейки. Например, разметка SpreadsheetML для первого листа книги, в котором для ячейки A1 задано значение 100, размещена в файле Sheet1.xml и показана в следующем примере.

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

Использование SDK Open XML версии 2.0 позволяет создать структуру и содержимое документа с помощью строго типизированных классов, соответствующих элементам SpreadsheetML. Эти классы находятся в пространстве имен DocumentFormat.OpenXML.Spreadsheet. В следующей таблице представлены имена классов, соответствующих элементам workbook, sheets, sheet, worksheet и sheetData.

Элемент SpreadsheetML	Класс SDK Open XML версии 2.0	Описание
workbook	DocumentFormat.OpenXML.Spreadsheet.Workbook	Корневой элемент основной части документа.
sheets	DocumentFormat. OpenXML.Spreadsheet.Sheets	Контейнер для структур уровня блока, таких как sheet, fileVersion и др., определенных в спецификации 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.

// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell. 
private 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);
        worksheet.Save();
    }
    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);
            worksheet.Save();
        }
    }
}

' Given a Worksheet and a cell name, verifies that the specified cell exists.
' If it does not exist, creates a new cell.
Private Sub CreateSpreadsheetCellIfNotExist(ByVal worksheet As Worksheet, ByVal 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.ToString())

    ' 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 Row = New Row()
        row.RowIndex = New UInt32Value(rowIndex)

        Dim cell As Cell = New Cell()
        cell.CellReference = New StringValue(cellName)

        row.Append(cell)
        worksheet.Descendants(Of SheetData)().First().Append(row)
        worksheet.Save()
    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 Cell = New Cell
            cell.CellReference = New StringValue(cellName)

            row.Append(cell)
            worksheet.Save()
        End If
    End If
End Sub

Для получения имени столбца в коде создается регулярное выражение, выделяющее из имени ячейки компонент имени столбца. Это регулярное выражение сопоставляет любое сочетание букв в верхнем и нижнем регистре. Дополнительные сведения о регулярных выражениях см. в статье, посвященной элементам языка регулярных выражений. Код получает имя столбца путем вызова метода Regex.Match.

// Given a cell name, parses the specified cell to get the column name.
private 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.
Private Function GetColumnName(ByVal cellName As String) As String
    ' Create a regular expression to match the column name portion of the cell name.
    Dim regex As Regex = 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.
private 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.
Private Function GetRowIndex(ByVal cellName As String) As UInteger
    ' Create a regular expression to match the row index portion the cell name.
    Dim regex As Regex = New Regex("\d+")
    Dim match As Match = regex.Match(cellName)
    Return UInteger.Parse(match.Value)
End Function

Пример кода

В приведенном ниже коде выполняется слияние двух смежных ячеек в пакете документов SpreadsheetDocument. При слиянии двух ячеек сохраняется содержимое только одной из них. В языках с письмом слева направо сохраняется содержимое ячейки, расположенной левее и выше. В языках с письмо справа налево сохраняется содержимое ячейки, расположенной, правее и выше. Для вызова в программе метода MergeTwoCells можно воспользоваться приведенным ниже примером кода, в котором выполняется слияние ячеек B2 и C2 на листе "Jane" в файле "Sheet9.xlsx".

string docName = @"C:\Users\Public\Documents\Sheet9.xlsx";
string sheetName = "Jane";
string cell1Name = "B2";
string cell2Name = "C2";
MergeTwoCells(docName, sheetName, cell1Name, cell2Name);

Dim docName As String = "C:\Users\Public\Documents\Sheet9.xlsx"
Dim sheetName As String = "Jane"
Dim cell1Name As String = "B2"
Dim cell2Name As String = "C2"
MergeTwoCells(docName, sheetName, cell1Name, cell2Name)

Ниже приведен полный пример кода на языках C# и Visual Basic.

// 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(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 == 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);

        worksheet.Save();
    }
}
// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell. 
private 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);
        worksheet.Save();
    }
    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);
            worksheet.Save();
        }
    }
}

// Given a SpreadsheetDocument and a worksheet name, get the specified worksheet.
private static Worksheet GetWorksheet(SpreadsheetDocument document, string worksheetName)
{
    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id)
    if (sheets.Count() == 0)
        return null;
    else
        return worksheetPart.Worksheet;
}

// Given a cell name, parses the specified cell to get the column name.
private 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.
private 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 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 Sub MergeTwoCells(ByVal docName As String, ByVal sheetName As String, ByVal cell1Name As String, ByVal cell2Name As String)
' Open the document for editing.
Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

Using (document)
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 MergeCell = New MergeCell()
mergeCell.Reference = New StringValue((cell1Name + (":" + cell2Name)))
mergeCells.Append(mergeCell)

worksheet.Save()
End Using
End Sub

' Given a SpreadsheetDocument and a worksheet name, get the specified worksheet.
Private Function GetWorksheet(ByVal document As SpreadsheetDocument, ByVal worksheetName As String) As Worksheet
Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = worksheetName)
If (sheets.Count = 0) Then
' The specified worksheet does not exist.
Return Nothing
End If
Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First.Id), WorksheetPart)

Return worksheetPart.Worksheet
End Function

' Given a Worksheet and a cell name, verifies that the specified cell exists.
' If it does not exist, creates a new cell.
Private Sub CreateSpreadsheetCellIfNotExist(ByVal worksheet As Worksheet, ByVal 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.ToString())

' 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 Row = New Row()
row.RowIndex = New UInt32Value(rowIndex)

Dim cell As Cell = New Cell()
cell.CellReference = New StringValue(cellName)

row.Append(cell)
worksheet.Descendants(Of SheetData)().First().Append(row)
worksheet.Save()
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 Cell = New Cell
cell.CellReference = New StringValue(cellName)

row.Append(cell)
worksheet.Save()
End If
End If
End Sub

' Given a cell name, parses the specified cell to get the column name.
Private Function GetColumnName(ByVal cellName As String) As String
' Create a regular expression to match the column name portion of the cell name.
Dim regex As Regex = 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.
Private Function GetRowIndex(ByVal cellName As String) As UInteger
' Create a regular expression to match the row index portion the cell name.
Dim regex As Regex = New Regex("\d+")
Dim match As Match = regex.Match(cellName)
Return UInteger.Parse(match.Value)
End Function