This documentation is archived and is not being maintained.

MergeCells Class

Merge Cells.When the object is serialized out as xml, its qualified name is x:mergeCells.

Namespace:  DocumentFormat.OpenXml.Spreadsheet
Assembly:  DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

[ChildElementInfoAttribute(typeof(MergeCell))]
public class MergeCells : OpenXmlCompositeElement

The following table lists the possible child types:

  • MergeCell <x:mergeCell>


[ISO/IEC 29500-1 1st Edition]

18.3.1.55 mergeCells (Merge Cells)

This collection expresses all the merged cells in the sheet.

[Example:

This example shows that three ranges are merged. The formatting and content for the merged range is always stored in the top left cell.


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

end example]

Parent Elements

worksheet (§18.3.1.99)

Child Elements

Subclause

mergeCell (Merged Cell)

§18.3.1.54

Attributes

Description

count (Count)

A count of merged cell collections.

The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.

[Note: The W3C XML Schema definition of this element’s content model (CT_MergeCells) is located in §A.2. end note]

© ISO/IEC29500: 2008.

The following code example merges two adjacent cells in an existing spreadsheet. After you run the example, take a look at the file and notice the merged cells.

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;
        }
    }
}


Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
Show: