Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 238
Reut.s Avatar asked Sep 19 '16 13:09

Reut.s


People also ask

How do I filter an Excel spreadsheet with merged cells?

Click anywhere in the data range, and in the Ribbon, go to Home > Sort & Filter > Filter.

Can you Vlookup a merged cell?

The Vlookup function will work in merged cells as usual in Google Sheets, but the output won't be what you are seeking. What's the solution to it? For example, if an item has three prices based on its grade, we may enter such data as below. Item: Mango (A1:A3 merged and entered it).


5 Answers

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)
like image 105
A. L Avatar answered Sep 28 '22 22:09

A. L


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))
like image 27
0x4a6f4672 Avatar answered Sep 28 '22 22:09

0x4a6f4672


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
like image 43
Kade Avatar answered Sep 28 '22 22:09

Kade


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]))
like image 44
Ángel Avatar answered Sep 28 '22 23:09

Ángel


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))
like image 36
KMiller Avatar answered Sep 29 '22 00:09

KMiller