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.
Best Answer
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_workbookwb = load_workbook(filename='a file name')sheet_ranges = wb['Sheet1']print(sheet_ranges.merged_cells.ranges)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))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 Truereturn FalseThe 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 sysfrom openpyxl import load_workbookfrom openpyxl.cell.cell import MergedCelldef 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.valueraise AssertionError('Merged cell is not in any merge range!')workbook = load_workbook(sys.argv[1])print(cell_value(workbook.active, sys.argv[2]))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 gonewhile mergedRanges:for entry in mergedRanges:i+=1print(" unMerging: " + str(i) + ": " +str(entry))ws.unmerge_cells(str(entry))