Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...