How to detect merged cells in an Excel sheet?

Question

I'm trying to read data from an Excel sheet that contains merged cells. When reading merged cells with openpyxl the first merged cell contain the value and the rest of the cells are empty.

I would like to know about each cell if it is merged and how many cells are merged but I couldn't find any function that does so. The sheet have empty others cells, so I can't use that.

Audie

Note for future readers: don't load_workbook(..., read_only=True) as information regarding merged cells is unavailable in read-only mode. – Interpose

Answer 1

You can use merged_cells.ranges (merged_cell_ranges has been deprecated in version 2.5.0-b1 (2017-10-19), changed to merged_cells.ranges) on the sheet (can't seem to find per row) like this:

from openpyxl import load_workbook
wb = load_workbook(filename='a file name')
sheet_ranges = wb['Sheet1']

print(sheet_ranges.merged_cells.ranges)

Wove

The new merged_cells method you mentioned seems to be undocumented at all, except for the quick deprecation mention you linked. Am I mistaken ? (no result here) – Weaver

Useful note (that I found at least): ranges returns a list of MergeCell objects. Each of those has a bounds attribute which returns a tuple of the 4 corners of the merged cell in the format of (col_low, row_low, col_high, row_high) – Filiform

Answer 2

To test if a single cell is merged or not you can check the type:

cell = sheet.cell(row=15, column=14)
if isinstance(cell, MergedCell):
  print("Oh no, the cell is merged!")
else:
  print("This cell is not merged.")

To "unmerge" all cells you can use the function unmerge_cells

for items in sorted(sheet.merged_cell_ranges):
  print(items)
  sheet.unmerge_cells(str(items))

Beardsley

Openpyxl seems to have changed sense this answer was posted. The suggestion here does not work anymore. – Pecoraro

It should work for the OpenPyXL version 2.6.2 which is the current version as far as I know – Beardsley

I can confirm, it works in 2.6.2 - I've not tried with 3.0.X but at the time this question was answered, I believe 2.6.2 was the most current. – Animalcule

It's not working on 3.0.4 see – Encephalo

This is working for me as of the latest version (3.0.7) as well as development (r8820/r8821), and even 3.0.4 Sure, merged_cell_ranges is deprecated, but it still works. – Prim

This solution is incorrect and problem is not with the openpyxl version at all. The problem is that the first cell of merged cells determines as Cell instead of MergedCell (so the first answer where it's explicitly specified in merged_cells.ranges as MergedCell is correct and this one is not) – Spandex

Answer 3

To test if a single cell is merged, I loop through sheet.merged_cells.ranges like @A. Lau suggests. Unfortunately, checking the cell type like @0x4a6f4672 shows does not work any more.

Here is a function that shows you how to do this.

def testMerge(row, column):
    cell = sheet.cell(row, column)
    for mergedCell in sheet.merged_cells.ranges:
        if (cell.coordinate in mergedCell):
            return True
    return False

Pecoraro

Answer 4

The question asks about detecting merged cells and reading them, but so far the provided answers only deal with detecting and unmerging. Here is a function which returns the logical value of the cell, the value that the user would see as contained on a merged cell:

import sys
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell


def cell_value(sheet, coord):
  cell = sheet[coord]
  if not isinstance(cell, MergedCell):
    return cell.value

  # "Oh no, the cell is merged!"
  for range in sheet.merged_cells.ranges:
    if coord in range:
      return range.start_cell.value

  raise AssertionError('Merged cell is not in any merge range!')


workbook = load_workbook(sys.argv[1])
print(cell_value(workbook.active, sys.argv[2]))

Deuterogamy

Answer 5

These all helped (thanks), but when I used the approaches with a couple of spreadsheets, it wasn't unmerging all the cells I expected. I had to loop and restest for merges to finally get them all to complete. In my case, it took 4 passes to get everything to unmerge as expected:

    mergedRanges = sheet_ranges.merged_cells.ranges
    ### How many times do we run unmerge?
    i=0
    ### keep testing and removing ranges until they are all actually gone
    while mergedRanges:
        for entry in mergedRanges:
            i+=1
            print("  unMerging: " + str(i) + ": " +str(entry))
            ws.unmerge_cells(str(entry))

Abscond

Answer 6

here another way will be useful if you parse row by row

import openpyxl
wb=load_workbook('workbook.xlsx')
sheet=wb['yousheet']
def isMerged(cell):
   return type(cell) == openpyxl.cell.cell.MergedCell

isMerged(sheet['A1'])#True or False

Excrescency