MergeCells Classe

Definição

[DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))]
[DocumentFormat.OpenXml.OfficeAvailability(DocumentFormat.OpenXml.FileFormatVersions.Office2007)]
[DocumentFormat.OpenXml.SchemaAttr(22, "mergeCells")]
public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

[DocumentFormat.OpenXml.SchemaAttr(22, "mergeCells")]
public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

[DocumentFormat.OpenXml.SchemaAttr("x:mergeCells")]
public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

[DocumentFormat.OpenXml.SchemaAttr("x:mergeCells")]
public class MergeCells : DocumentFormat.OpenXml.TypedOpenXmlCompositeElement

[DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))]
public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

[DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))]
[DocumentFormat.OpenXml.OfficeAvailability(DocumentFormat.OpenXml.FileFormatVersions.Office2007)]
public class MergeCells : DocumentFormat.OpenXml.OpenXmlCompositeElement

[<DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))>]
[<DocumentFormat.OpenXml.OfficeAvailability(DocumentFormat.OpenXml.FileFormatVersions.Office2007)>]
[<DocumentFormat.OpenXml.SchemaAttr(22, "mergeCells")>]
type MergeCells = class
    inherit OpenXmlCompositeElement

type MergeCells = class
    inherit OpenXmlCompositeElement

[<DocumentFormat.OpenXml.SchemaAttr(22, "mergeCells")>]
type MergeCells = class
    inherit OpenXmlCompositeElement

[<DocumentFormat.OpenXml.SchemaAttr("x:mergeCells")>]
type MergeCells = class
    inherit OpenXmlCompositeElement

[<DocumentFormat.OpenXml.SchemaAttr("x:mergeCells")>]
type MergeCells = class
    inherit TypedOpenXmlCompositeElement

[<DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))>]
type MergeCells = class
    inherit OpenXmlCompositeElement

[<DocumentFormat.OpenXml.ChildElementInfo(typeof(DocumentFormat.OpenXml.Spreadsheet.MergeCell))>]
[<DocumentFormat.OpenXml.OfficeAvailability(DocumentFormat.OpenXml.FileFormatVersions.Office2007)>]
type MergeCells = class
    inherit OpenXmlCompositeElement

Public Class MergeCells
Inherits OpenXmlCompositeElement

Public Class MergeCells
Inherits TypedOpenXmlCompositeElement

Herança

Object

OpenXmlElement

OpenXmlCompositeElement

MergeCells

Herança

Object

OpenXmlElement

OpenXmlCompositeElement

TypedOpenXmlCompositeElement

MergeCells

Atributos

ChildElementInfoAttribute OfficeAvailabilityAttribute SchemaAttrAttribute

Exemplos

O exemplo de código seguinte intercala duas células adjacentes numa folha de cálculo existente. Depois de executar o exemplo, observe o ficheiro e repare nas células unidas.

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

namespace MergeCellsEx  
{  
    class Program  
    {  
        // Merge two adjacent cells in a worksheet.  
        // Notice that after the merge, only the content from one cell is preserved.  
        static void Main(string[] args)  
        {  
            string docName = @"C:\Users\Public\Documents\MergeCellsEx.xlsx";  
            string sheetName = "mySheet";  
            string cell1Name = "A2";  
            string cell2Name = "B2";  

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))  
            {  
                Worksheet worksheet = GetWorksheet(document, sheetName);  
                // Create Spreadsheet cells.  
                CreateSpreadsheetCell(worksheet, cell1Name);  
                CreateSpreadsheetCell(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  
                        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();  
            }  
            Console.WriteLine("The two cells are now merged.\nPress a key.");  
            Console.ReadKey();  
        }  

        // 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);  
            return worksheetPart.Worksheet;  
        }  

        // Create a spreadsheet cell.   
        private static void CreateSpreadsheetCell(Worksheet worksheet, string cellName)  
        {  
            string columnName = GetColumnName(cellName);  
            uint rowIndex = 2;  
            IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r  
                .RowIndex.Value == rowIndex);  
            Row row = rows.First();  
            IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference  
                .Value == cellName);  
        }  

        // Parse the cell name 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;  
        }  
    }  
}  

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

Module Module1  
    ' Merge two adjacent cells in a worksheet.  
    ' Notice that after the merge, only the content from one cell is preserved.  
    Sub Main(ByVal args As String())  
        Dim docName As String = "C:\Users\Public\Documents\MergeCellsEx.xlsx"  
        Dim sheetName As String = "mySheet"  
        Dim cell1Name As String = "A2"  
        Dim cell2Name As String = "B2"  

        Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)  
            Dim worksheet As Worksheet = GetWorksheet(document, sheetName)  
            ' Create Spreadsheet cells.  
            CreateSpreadsheetCell(worksheet, cell1Name)  
            CreateSpreadsheetCell(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())  
                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)  
            worksheet.Save()  
        End Using  
        Console.WriteLine("The two cells are now merged." & vbLf & "Press a key.")  
        Console.ReadKey()  
    End Sub  

    ' 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)  
        Dim worksheetPart As WorksheetPart = DirectCast(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)  
        Return worksheetPart.Worksheet  
    End Function  

    ' Create a spreadsheet cell.   
    Private Sub CreateSpreadsheetCell(ByVal worksheet As Worksheet, ByVal cellName As String)  
        Dim columnName As String = GetColumnName(cellName)  
        Dim rowIndex As UInteger = 2  
        Dim rows As IEnumerable(Of Row) = worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex.Value = rowIndex)  
        Dim row As Row = rows.First()  
        Dim cells As IEnumerable(Of Cell) = row.Elements(Of Cell)().Where(Function(c) c.CellReference.Value = cellName)  
    End Sub  

    ' Parse the cell name 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 New Regex("[A-Za-z]+")  
        Dim match As Match = regex.Match(cellName)  
        Return match.Value  
    End Function  
End Module  

Comentários

[ISO/IEC 29500-1 1st Edition]

mergeCells (Unir Células)

Esta coleção expressa todas as células unidas na folha.

[Exemplo:

Este exemplo mostra que três intervalos são intercalados. A formatação e o conteúdo do intervalo intercalado são sempre armazenados na célula superior esquerda.

<mergeCells>  
  <mergeCell ref="C2:F2"/>  
  <mergeCell ref="B19:C20"/>  
  <mergeCell ref="E19:G19"/>  
</mergeCells>  

exemplo final]

[Nota: a definição de Esquema XML W3C do modelo de conteúdo deste elemento (CT_MergeCells) está localizada em {A.2. nota de fim]

ISO/IEC29500: 2008.