CODEWITHSUNDEEP
pythonexcelopenpyxl
Reut.s
Reut.s

Reputation: 211

How to detect merged cells in an Excel sheet?

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.

Upvotes: 21

Views: 45361

Answers (6)

Kyrillos Akram
Kyrillos Akram

Reputation: 21

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

Upvotes: 2

0x4a6f4672
0x4a6f4672

Reputation: 28245

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

Upvotes: 12

Ángel
Ángel

Reputation: 950

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

Upvotes: 7

KMiller
KMiller

Reputation: 21

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

Upvotes: 2

Kade
Kade

Reputation: 1022

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

Upvotes: 8

A. L
A. L

Reputation: 12639

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)

Upvotes: 31

Related Questions