CODEWITHSUNDEEP
pythonpandasopenpyxl
holdenweb
holdenweb

Reputation: 37203

Locating merged cell ranges in openpyxl

I'm working on extracting some data from a .xlsx file using openpyxl and Pandas.

I can't find a cell property (or indeed any other information) that indicates where I can find out which cells are merged in the spreadsheets. How do I know which cells are merged together?

Upvotes: 7

Views: 8323

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19557

(EDITED to fully answer the question and update 2018-08-31).

If you want to know which ranges are merged you can examine the merged_cells.ranges worksheet attribute, which is a list of openpyxl.worksheet.cell_range.CellRange objects.

The CellRange object implements __contains__, so it's possible to see whether cell A3 is part of a cell range by evaluating

any('A3' in rng for rng in ws.merged_cells.ranges)

To find out which range it's a part of:

for rng in ws.merged_cells.ranges:
    if 'A3' in rng:
        break
else:
    rng = None

The content of a merged cell is actually stored against the top-left cell in the range. Accessing that is left as an exercise for the reader, as is using other forms of cell reference than the standard Excel strings.

Upvotes: 10

Related Questions