Getting coordinates of a certain merged cell

Question

I am trying to get starting and end column of a merged cell in excel -

I am looking to know start and end column number of cell 'human readable'. This code gives me all merged cells in sheet -

from openpyxl import load_workbook
wb = load_workbook('Book2.xlsx')
sheet_ranges = wb['Sheet1']

print(sheet_ranges.merged_cells.ranges)

But how can I find only for 'human readable'?

Does it change columns? can you look for the string in the list of cells? There are some decent options for getting merged cell value here: https://stackoverflow.com/questions/23562366/how-to-get-value-present-in-a-merged-cell — d6stringer, Sep 19 '22 at 22:39

score 1 · Answer 1 · answered Sep 19 '22 at 22:55

This worked out for me

from openpyxl import load_workbook

def main():
    wb = load_workbook("/Book2.xlsx")
    sheet = wb['Sheet1']
    cell = content_checker(sheet)
    merged_span_check(sheet, cell)

def content_checker(sheet):
    for row in sheet:
        for cell in row:
            if cell.value == 'Human Readable':
                return cell

def merged_span_check(sheet, cell):
    for merged_cell in sheet.merged_cells.ranges:
        if cell.coordinate in merged_cell:
            print(merged_cell)

if __name__ == '__main__':
    main()

This will only work for the first cell with that content, you'll have to add some lists or something if you need to find multiple cells with the same value. — d6stringer, Sep 19 '22 at 23:01

moken · Answer 2 · 2022-10-05T04:46:41.357

0

Should be able to use list comprehension to find and print all instances, includes check being case insensitive.

from openpyxl import load_workbook
wb = load_workbook('Book2.xlsx')
sheet_ranges = wb['Sheet1']

search = 'human readable'
newlist = [c for c in sheet_ranges.merged_cells.ranges
           if search in c.start_cell.value.lower()]
print(newlist)

edited Oct 05 '22 at 04:46

answered Sep 20 '22 at 01:48

moken

3,227
8
13
23

score 0 · Answer 3 · answered Feb 07 '23 at 07:33

I collect all cells in the merged_cells first:

def collect_merge_dict(sheet):
    merge_dict = {}
    merge_ranges = sheet.merged_cells.ranges
    for index, merged_range in enumerate(merge_ranges):
        for col in range(merged_range.min_col, merged_range.max_col + 1):
            for row in range(merged_range.min_row, merged_range.max_row + 1):
                coord = (row, col)
                merge_dict[coord] = index

    return merge_dict

Then we can uses d6stringer's content_checker:

def content_checker(sheet):
    for row in sheet:
        for cell in row:
            if cell.value == 'Human Readable':
                return cell

And look up the cell:

def main():
    wb = load_workbook("/Book2.xlsx")
    sheet = wb['Sheet1']
    merge_dict = collect_merge_dict(sheet)
    cell = content_checker(sheet)

    index = merge_dict.get((cell.row, cell.column), -1)
    if index > -1:
        cell_range = list(sheet.merged_cells.ranges)[index]
        print(cell_range)

Getting coordinates of a certain merged cell

3 Answers3