How to detect merged cells in a worksheet

Spire.XLS provides a class named Worksheet, it contains a MergedCells property which makes it easy for us to obtain the merged cells in a worksheet. This property will return an array of the merged cell ranges, after that we can do any desired operations such as unmerge and apply formatting on the cells in the ranges. This article explains how to detect all merged cells in a worksheet and unmerge them once using Spire.XLS.

Here we used a template Excel file which has some merged cells:

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Access the first worksheet by passing its sheet index.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the merged cell ranges in the first worksheet and put them into a CellRange array.

CellRange[] range = sheet.MergedCells;

Step 4: Traverse through the array and unmerge the merged cells.

foreach (CellRange cell in range)
{
    cell.UnMerge();
}

Step 5: Save the file.

workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);

After executing the code, we'll get the following output file:

How to detect merged cells in a worksheet

Full codes:

[C#]

using Spire.Xls;

namespace Detect_Merged_Cells
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            CellRange[] range = sheet.MergedCells;
            foreach (CellRange cell in range)
            {
                cell.UnMerge();
            }
            workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
        }
    }
}

[VB.NET]

Imports Spire.Xls

Namespace Detect_Merged_Cells
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("Sample.xlsx")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			Dim range As CellRange() = sheet.MergedCells
			For Each cell As CellRange In range
				cell.UnMerge()
			Next
			workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010)
		End Sub
	End Class
End Namespace