CODEWITHSUNDEEP
pythonexcelopenpyxlxlrd
Katrina Marrie
Katrina Marrie

Reputation: 47

Getting coordinates of a certain merged cell

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 one row. 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 'one row'?

Upvotes: 2

Views: 1128

Answers (3)

nlaan
nlaan

Reputation: 67

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)

Upvotes: 0

moken
moken

Reputation: 6664

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)

Upvotes: 0

d6stringer
d6stringer

Reputation: 81

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()

Upvotes: 2

Related Questions