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.

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

Solution 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

Comments

[sadhana]

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

[Charles Mager]

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.

[sadhana]

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?

[Charles Mager]

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.

[sadhana]

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?

[sadhana]

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..