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.

Evaevacuant

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;

Bayle

This did not work.'containsMergedCells' is always false eventhough the columnheaders(row 1) in my sheet contain merged cells – Evaevacuant

Not sure what to suggest. I use something similar in production code with no issues and I've tested by executing this code on an empty sheet, merging A1:B1 & adding a value elsewhere and executing again - it works as expected - MergeCells is DBNull.Value. – Bayle

I am not able to figure out the exact problem...If you look at my code with the for loop you'll notice that it is the cell wise version of your code..Did your code work when the merged cell is not empty? – Evaevacuant

I've re-tested by adding a value in A1 before merging with B1 - yes, it works. How is the for loop important here? If you're trying to do this by range using this code, you wouldn't have the loop. – Bayle

i did not say that for loop is important..i just said that my code practically does the same thing which your code does though yours is more straight forward..anyways..If it is working for you but not for me then i guess this is not a coding problem..But i wonder what else had gone wrong? – Evaevacuant

Finally fixed the problem.A piece of code which is clearing the formatting of cells was causing the mergecells property to be always false.When that code is removed everything is working as expected..Thanks.. – Evaevacuant

Answer 2

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

Gurglet

Answer 3

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

Spracklen

Answer 4

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;
}

Interglacial