SpreadSheetML - How detect if current cell is a MergeCell RRS feed

  • Question

  • Hi,

    I'm looking for a way to know if the current Cell object is a MergeCell and how find its starting and ending columns.

    I hope my question is clear enough.

    Best regards.

    foreach (Row r in sheetData.Elements()) 
     foreach (Cell c in r.Elements()) 
      if (c.CellValue == null) 
     // Does this cell object is MergeCell ? 
     // How do i know columns associated with this cell ? 
    Wednesday, November 24, 2010 10:01 AM


  • The code below is a VBA macro from Excel 2010, It creates a merge cell of three columns in a range. Then it itereates through that range row by row, and checks each column in that row to see if it is part of the merge cells. The cell's area's DesplayFormat.MergeCells is tested for 'True'. The simple macro reports when it finds a merge cell, displaying the column address. In this example the reported cells were shown in the VBA immediate window thus:

    Merged with the address of $B$2
    Merged with the address of $C$2
    Merged with the address of $D$2

    Here is the macro:

    Sub Macro1()
    ' Macro1 Demonstration code - No Warranty Associated with this snippet
      Dim oRange As Range
      With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
      Set oRange = Range("A1:E3")
      Sheet1.Visible = xlSheetVisible
      For Each r In oRange.Rows
        For Each c In Selection.Cells
          If c.Areas(1).DisplayFormat.MergeCells = True Then
          Debug.Print "Merged with the address of " & c.Address
          End If
    End Sub

    From your question it appears you are parsing the XML and iterating through the worksheet in a similar manner. The code in the macro should translate on a line-for-line basis to your VB .NET example.


    Hope this helps.

    Chris Jensen
    • Proposed as answer by cjatms Thursday, December 2, 2010 7:31 PM
    • Marked as answer by Bessie Zhao Monday, December 6, 2010 9:47 AM
    Thursday, December 2, 2010 7:31 PM