How to Merge and Unmerge Cells

by Chaknith Bin

Merging cells refers to the process of combining two or more adjacent cells into a single larger cell. Unmerging cells, on the other hand, is the opposite process where a merged cell is divided back into its original individual cells. This feature allows for flexibility, consistent alignment, and better data analysis.

IronXL enables the merging and unmerging of cells in a spreadsheet programmatically.

How to Merge and Unmerge Cells

1. Download C# library to merge and unmerge cells
2. Load existing or create a branch new spreadsheet
3. Utilize Merge method to merge wanted range
4. Perform unmerge by specifying merge region range address or index to Unmerge method
5. Export the modified spreadsheet

Install with NuGet

Install-Package IronXL.Excel

nuget.org/packages/IronXL.Excel/

or

Download DLL

Download DLL

Manually install into your project

Merge Cells Example

The Merge method can be utilized to merge a range of cells. This process combines the cells without erasing any existing values or data, but only the value of the first cell in the merged region will be displayed. However, the values of the merged cells remain accessible in IronXL.

The code example below demonstrates how to merge a range of cells by specifying their addresses.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-merge.cs

using IronXL;

WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

var range = workSheet["B2:B5"];

// Merge cells B7 to E7
workSheet.Merge("B7:E7");

// Merge selected range
workSheet.Merge(range.RangeAddressAsString);

workBook.SaveAs("mergedCell.xlsx");

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

Private range = workSheet("B2:B5")

' Merge cells B7 to E7
workSheet.Merge("B7:E7")

' Merge selected range
workSheet.Merge(range.RangeAddressAsString)

workBook.SaveAs("mergedCell.xlsx")

Demonstration

Retrieve Merged Regions Example

Retrieving merged regions is a useful feature for identifying the displayed value in spreadsheet visualization software like Microsoft Excel. To obtain a list of merged regions, you can utilize the GetMergedRegions method.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-retrieve-merged-regions.cs

using IronXL;
using System.Collections.Generic;
using System;

WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply merge
workSheet.Merge("B4:C4");
workSheet.Merge("A1:A4");
workSheet.Merge("A6:D9");

// Retrieve merged regions
List<IronXL.Range> retrieveMergedRegions = workSheet.GetMergedRegions();

foreach (IronXL.Range mergedRegion in retrieveMergedRegions)
{
    Console.WriteLine(mergedRegion.RangeAddressAsString);
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

Unmerge Cells Example

Unmerging merged regions can be accomplished using two different approaches. The first and simplest method involves specifying the cell addresses, such as "B3:B6", to unmerge.

Alternatively, you can also unmerge cells based on the index of the merged region. The merged regions are listed in chronological order. To do this, you can retrieve the merged regions first and pass the desired index to the Unmerge method.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-unmerge.cs

using IronXL;

WorkBook workBook = WorkBook.Load("mergedCell.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Unmerge the merged region of B7 to E7
workSheet.Unmerge("B7:E7");

workBook.SaveAs("unmergedCell.xlsx");

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("mergedCell.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Unmerge the merged region of B7 to E7
workSheet.Unmerge("B7:E7")

workBook.SaveAs("unmergedCell.xlsx")

Demonstration