Convenience method for accessing individual merged cells, rather than just a reference

[caramdache]

Merged cells are a little bit of a red herring. Sometimes you want to access the actual cells rather than the reference/the MergedCell's. For example to obtain indivual borders for each of the cells in the merge. Which is required to be able to obtain the exact borders.

In doing this work, I have found the following method useful. It may be a desirable inclusion in the library, probably under a more sophisticated form.

def merged_cells(cell)
    return nil unless cell.worksheet.merged_cells
    
    cell.worksheet.merged_cells.each { |mcell|
        if mcell.ref.first_row == cell.row && mcell.ref.first_col == cell.column
            return mcell.ref.row_range.collect { |row|
                       mcell.ref.col_range.collect { |col|
                           cell.worksheet[row][col]
                       }
                   }
        end
    }
    
    nil
end

[caramdache]

Here's a more elaborate version.

module RubyXL
    module CellConvenienceMethods
        def borders
            merged_cells.flat_map { |cell|
                [:top, :left, :right, :bottom].filter_map { |direction|
                    direction if cell.get_border(direction)
                }
            }.uniq
        end

        def merged_cells
            worksheet.merged_cells.each { |mcell|
                row_range, col_range = mcell.ref.row_range, mcell.ref.col_range

                if row_range.begin == self.row && col_range.begin == self.column
                    return row_range.flat_map { |r| col_range.map { |c| worksheet[r][c] } }
                end
            } if worksheet.merged_cells
            
            [self]
        end
    end
end

[harsh183]

Bumping this, I would definitely find it useful in one of my scripts.

[weshatheleopard]

@harsh183 Please propose the consumer facing API.

[harsh183]

Something like sheet.merged_cell_at([row, col]) since we don't want to change any behavior of [][] access giving normal cells for even merged ones. This can return MergedCell.ref() would give us access to the range variables.

sheet = parsed_file[0]
sheet.merged_cell_at([5, 3])
sheet.merged_cell_at(RubyXL::Reference.ref2ind('A1')) # why I'm thinking of array input

Possibly having merged cells also support .value would be nice as well (at the moment I access the first cell and .value on that). Other properties like border (what OP is mentioning), fill, alignment and other things I can directly access would be nice to haves similar to the current Cell API.

[weshatheleopard]

@harsh183 So you are saying that for given (X,Y) you want to know the (X1....X2, Y1.....Y2) of the merged cell, of which the cell (X,Y) is a part of?

[harsh183]

Yeah that works, and being able to get/set properties of the entire merged cell.

[caramdache]

Yeah that works, and being able to get/set properties of the entire merged cell.

Some properties are not set at the global merged cell level, but at the level of the individual cells which are merged. Borders are one example. The API should allow support for that.

[weshatheleopard]

@caramdache precisely, so use your chance, go ahead and propose an API!

[harsh183]

@caramdache do you mean something like merged_cell.first_cell or maybe some iterator that gives all the individual cells that are part of a given merged cell for those you can't set at a global merged cell level?

And the rest can have a merged cell level set for things that can be like content?

For my project I'm just looking for merged_cell_at(x,y) but this is a part of the gem that can really be expanded on in interesting directions I feel.

[caramdache]

@harsh183 I meant an iterator that provides the individual cells that are part of a merge range.

@weshatheleopard my use case was to be able to obtain the borders of a group of merged cells. The borders are defined for each individual cell, hence the need for an iterator to obtain these cells.

[harsh183]

Yeah, I think that'll be generally useful for a lot of things.