how to detect merged cells in c# using MS interop excel

Question

I want to detect merged cells either in a row/entire sheet(preferable).Here is my code

Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application(); 
Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(source);
//Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[sheetNumber];
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[objInMemory._sheetName];
xl.ScreenUpdating = false;
ws.Columns.ClearFormats();
ws.Rows.ClearFormats();
int colCount = ws.UsedRange.Columns.Count;
int rowCount = ws.UsedRange.Rows.Count;
int strtRow = ws.UsedRange.Rows[1].Row;
int strtCol = ws.UsedRange.Columns[1].Column;


 Microsoft.Office.Interop.Excel.Range objRange = null;

Neither this piece of code

if (ws.Cells.MergeCells)
{

}

Nor this piece of code(only for row1)

for (int j = strtCol; j < strtCol + colCount; j++)
{
    objRange = ws.Cells[strtRow, j];

    if (ws.Cells[strtRow, j].MergeCells)
    {
        message = "The Sheet Contains Merged Cells";
        break;
    }  
}

seem to work..Kindly let me know how to check if a sheet/specific range contains merged cells.

Answer 1

If you want to check if a Range contains merged cells, then the MergeCells property is what you're after.

If a range is merged, it will return true. If a range contains merged cells (i.e. some are merged, some aren't), it will return DBNull.Value.

So, this should work for your entire sheet:

object mergeCells = ws.UsedRange.MergeCells;
var containsMergedCells = mergeCells == DBNull.Value || (bool)mergeCells;

Answer 2

A pedantic edition to Charles Mager's answer is as follows: the behaviour of MergeCells seems to be broken for Ranges that contain cells which are all part of merged areas, but such that the areas are different. So the practical advice would be to follow Charle's answer of OR-ing the MergeCells result with a check for Null, because the "True" value can't really be trusted to mean anything. To demonstrate my point, try this experiment:

First, merge cells A1 and B1. Merge cells A2 and B2. Then run the following from the immediate window of the VBA editor. This will be the result of MergeCells, for various selections:

• ?ActiveSheet.Range("A1:B1").MergeCells: True. As we'd expect, as it's the entire merged area.
• ?ActiveSheet.Range("A1:A2").MergeCells: True. Not expected. This range cuts across two merged areas. However, all cells are merged to some area, so maybe that's what the function measures? Nope. See following experiments.
• ?ActiveSheet.Range("A1:B2").MergeCells: Null. This is inconsistent with the last result. So neither meaning fits experiment: "all cells are merged to some area" or "all cells are merged to the same area".

Conclusion:

The behaviour is a bit fragmented. Here's my best guess at defining it based on the above experiments.

• If the value of this is True, we know that all cells are merged to some area. We can't guarantee this is the same area.
• If the value of this is Null, we know that some or all cells are merged to some area.
• If the value of this is False, no cells are merged at all.

So, you could write messy, fragmented logic that checks all three cases. Or you could do what Charles did, wrap around this, and build something with more sensible behaviour:

• If the value of this is True or null, we know that some cells are merged to some area.
• If the value of this is False, no cells are merged at all.

From there, you can use Range.MergeArea to figure out what's merged where, with more precision.

Some Code

And just for the heck of it, here's another variation on Charles' answer with safer casting, assuming you are working from a wrapper object with a RawRange property of type Excel Interop Range.

public bool SomeCellsMerged()
{
    object mergeCells = RawRange.MergeCells;
    if(mergeCells is bool someCellsMerged)
    {
        if (someCellsMerged) return true;
    }
    return mergeCells == DBNull.Value;
}

Answer 3

MergeCells is not a cells function, it's range function, so instead of:

if (ws.Cells[strtRow, j].MergeCells)

you need:

_Excel.Range range = (_Excel.Range) ws.Cells[strtRow, j];
if(range.MergeCells) //returns true if cell is merged or false if its not

Answer 4

Please remove ws.Columns.ClearFormats(); and ws.Rows.ClearFormats(); for the range.MergeCells property to work.