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.

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)

Answer 2

To test if a single cell is merged or not you can check the class (name):

cell = sheet.cell(row=15, column=14)
if type(cell).__name__ == '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))

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

Answer 4

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

Answer 5

The question asks about detecting merged cells and reading them, but so far the provided questions 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]))