How to work with merged cells in Excel with `openpyxl` in Python?

How to work with merged cells in Excel with openpyxl in Python

Import openpyxl module and read the workbook:

import openpyxl

wb = openpyxl.load_workbook('attachments/device.xlsx')

Print merged cells:

for ws, name in zip(wb.worksheets, wb.sheetnames):
    print("Sheet: ", name)
    print(ws.merged_cells)

Sheet:  smoke transducer
A3:A8 A10:A13
Sheet:  temperature and humidity sensor
A4:A7 A9:A11
Sheet:  infrared sensor
A4:A7 A9:A12
Sheet:  Dc voltage transmitter
A4:A7 A9:A10
Sheet:  water depth sensor
A4:A5 A7:A11

Check the type of ws.merged_cells

type(ws.merged_cells)

openpyxl.worksheet.cell_range.MultiCellRange

Refer the below link for CellRange and MergedCellRange:

• https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.cell_range.html#openpyxl.worksheet.cell_range.CellRange
• https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.merge.html#openpyxl.worksheet.merge.MergedCellRange

Check the type of ws.merged_cells.ranges, it is set.

print(type(ws.merged_cells.ranges))

<class 'set'>

Check type of ranges again:

for cell_range in ws.merged_cells.ranges:
    print(type(cell_range))
    print(type(cell_range).__bases__)
    break

<class 'openpyxl.worksheet.merge.MergedCellRange'>
(<class 'openpyxl.worksheet.cell_range.CellRange'>,)

Refer the link for cell method on sheet: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.cell

We can use the cell method to get value:

ws.cell(row=cell_range.min_row, column=cell_range.min_col).value

technical experience

We can use unmerge_cells method to unmerged cells, see https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.unmerge_cells for more info.

Here is the full program

for ws, name in zip(wb.worksheets, wb.sheetnames):
    merged_cells = list(ws.merged_cells.ranges)
    for cell_range in merged_cells:
        # Get the value of the merged cell
        cell_value = ws.cell(
            row=cell_range.min_row,
            column=cell_range.min_col,
        ).value
        
        # Unmerge cells
        ws.unmerge_cells(
            start_row=cell_range.min_row,
            start_column=cell_range.min_col,
            end_row=cell_range.max_row,
            end_column=cell_range.max_col,
        )
        
        # Fill value to the unmerged cells that is empty
        for row in range(cell_range.min_row, cell_range.max_row + 1):
            for col in range(cell_range.min_col, cell_range.max_col + 1):
                ws.cell(row=row, column=col).value = cell_value