CODEWITHSUNDEEP
openpyxl
crashwap
crashwap

Reputation: 3068

Python - find cell reference within merged_cells collection in openpyxl

I wish to identify if a cell in a worksheet is found within the merged_cells collection returned by openpyxl.

The merged_cells range looks like this (VSCode debugger):

enter image description here

I have the cell reference J31 - which is found in this collection. How would I write a function that returns true if that cell is found in the merged_cells.ranges collection?

Upvotes: 0

Views: 548

Answers (2)

cssyphus
cssyphus

Reputation: 40106

Further to D.Banakh's answer (+1), try something like this (building upon a previous example I wrote for someone else, since there is little context to your question):

for cell in ws.merged_cells.ranges:
    #print(cellRef +' ==> '+ str(cell.min_row) +'/'+ str(cell.max_row) +'/'+ str(cell.min_col) +'/'+ str(cell.max_col))
    if (int(cell.min_row) <= int(row) <= int(cell.max_row)) and (int(cell.min_col) <= int(col) <= int(cell.max_col)):
        print('Cell ' +cellRef+ ' is a merged cell')

Example within a context:

import re

cellBorders = fnGetCellBorders(ws, cellRef)
if ('T' in cellBorders) or  ('L' in cellBorders) or  ('R' in cellBorders) or  ('B' in cellBorders) or  ('M' in cellBorders):
    print('Cell has border *OR* is a merged cell and borders cannot be checked')

def getCellBorders(ws, cellRef):
    tmp = ws[cellRef].border
    brdrs = ''

    if tmp.top.style is not None: brdrs += 'T'
    if tmp.left.style is not None: brdrs += 'L'
    if tmp.right.style is not None: brdrs += 'R'
    if tmp.bottom.style is not None: brdrs += 'B'

    if (brdrs == '') and ('condTableTopLeftCell' in refs):
        if fnIsCellWithinMergedRange(ws, cellRef): brdrs = 'M'
    return brdrs

def fnIsCellWithinMergedRange(ws, cellRef):
    ans = False
    col = fnAlphaToNum(re.sub('[^A-Z]', '', cellRef))
    row = re.sub('[^0-9]', '', cellRef)
    for cell in ws.merged_cells.ranges:
        if (int(cell.min_row) <= int(row) <= int(cell.max_row)) and (int(cell.min_col) <= int(col) <= int(cell.max_col)):
            ans = True
    return ans

def fnAlphaToNum(ltr):
    ab = ["MT", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
    return ab.index(ltr)

References:

OpenPyXL - How to query cell borders?

How to detect merged cells in excel with openpyxl

https://bitbucket.org/openpyxl/openpyxl/issues/911/borders-on-merged-cells-are-not-preserved

Upvotes: 0

D. Banakh
D. Banakh

Reputation: 9

for cell in ^^merged_range^^:
    if cell==your_special_cell:
         return True

^^merged_range^^ must be of type openpyxl.worksheet.cell_range

Upvotes: 1

Related Questions