none
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) 
       continue; 
    
     // Does this cell object is MergeCell ? 
     // How do i know columns associated with this cell ? 
     } 
    } 
    
    
    Wednesday, November 24, 2010 10:01 AM

Answers

  • 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
      
      Range("B2:D2").Select
      With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
      Selection.Merge
      Set oRange = Range("A1:E3")
      oRange.Select
      Sheet1.Visible = xlSheetVisible
      For Each r In oRange.Rows
        r.Select
        For Each c In Selection.Cells
          c.Select
          If c.Areas(1).DisplayFormat.MergeCells = True Then
          Debug.Print "Merged with the address of " & c.Address
          End If
        Next
      Next
    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 cjatmsModerator 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
    Moderator