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)

public class MergeCells : OpenXmlCompositeElement

The following table lists the possible child types:

  • MergeCell <x:mergeCell>

[ISO/IEC 29500-1 1st Edition] mergeCells (Merge Cells)

This collection expresses all the merged cells in the sheet.


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

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

end example]

Parent Elements

worksheet (§

Child Elements


mergeCell (Merged Cell)




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();
                    mergeCells = new MergeCells();

                    // Insert a MergeCells object into the specified position.
                    if (worksheet.Elements<CustomSheetView>().Count() > 0)
                         worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().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) };
            Console.WriteLine("The two cells are now merged.\nPress a key.");

        // 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
            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.