How to unmerge cells

Unmerging cells through CellRange’s MergedRange property is not allowed (an exception will be thrown).To unmerge cells we need to create a range from the ExcelWorksheet and then unmerge it. This kind of implementation was chosen for performance reasons.

Here is the sample code which demonstrates how to properly unmerge cells:

// Set some value (value will be set to merged cell range - not cell alone) ws.Cells[0, 0].Value = 1;

// UnmergeCells_BadWay(ws);

UnmergeCells_GoodWay(ws);

ef.Save("Merge and unmerge cells.xlsx");}

privatestaticvoid UnmergeCells_GoodWay(ExcelWorksheet ws){// Get some cell in the rangeExcelCell cell = ws.Cells[0, 0];

// Get merged range in which that cell is.CellRange mergedRange = cell.MergedRange;

// You need to get new range that is made from the worksheet (it's a bit different than previous - which was made from cell).CellRange wsMergedRange = ws.Cells.GetSubrangeAbsolute(mergedRange.FirstRowIndex, mergedRange.FirstColumnIndex, mergedRange.LastRowIndex, mergedRange.LastColumnIndex);

// Unmerge cells (note that all cells would now have the value that was set to merged range). wsMergedRange.Merged = false;

' Set some value (value will be set to merged cell range - not cell alone) ws.Cells(0, 0).Value = 1

' UnmergeCells_BadWay(ws)

UnmergeCells_GoodWay(ws)

ef.Save("Merge and unmerge cells.xlsx")End Sub

Private Sub UnmergeCells_GoodWay(ws As ExcelWorksheet)' Get some cell in the rangeDim cell As ExcelCell = ws.Cells(0, 0)

' Get merged range in which that cell is.Dim mergedRange As CellRange = cell.MergedRange

' You need to get new range that is made from the worksheet (it's a bit different than previous - which was made from cell).Dim wsMergedRange As CellRange = ws.Cells.GetSubrangeAbsolute(mergedRange.FirstRowIndex, mergedRange.FirstColumnIndex, mergedRange.LastRowIndex, mergedRange.LastColumnIndex)

' Unmerge cells (note that all cells would now have the value that was set to merged range). wsMergedRange.Merged = False

' We want that only cell in the upper-left corner has merged value -> so delete values from other cells.For i As Integer = mergedRange.FirstRowIndex To mergedRange.LastRowIndexFor j As Integer = mergedRange.FirstColumnIndex To mergedRange.LastColumnIndexIf i <> mergedRange.FirstRowIndex OrElse j <> mergedRange.FirstColumnIndex Then ws.Cells(i, j).Value = NothingEnd IfNextNextEnd Sub

Private Sub UnmergeCells_BadWay(ws As ExcelWorksheet)' Get some cell in the rangeDim cell As ExcelCell = ws.Cells(0, 0)

' Get merged range in which that cell is.Dim mergedRange As CellRange = cell.MergedRange

' The following line will throw an exception! mergedRange.Merged = FalseEnd Sub