How to detect merged cells in excel with openpyxl

Question

I am trying to read data from an excel sheet containing merged cells. When reading merged cells with openpyxl, the first merged cell contains the value, and the rest of the cells are empty.

I would like to know about each cell if it is combined and how many cells are combined, but I could not find any function that does this. There are other empty cells on the sheet, so I cannot use this.

Thanks any help, I prefer the openpyxl module, but other modules may work.

Thanks everyone!

Answer 1

You can use merged_cells.ranges ( merged_cell_ranges is deprecated in version 2.5.0-b1 (2017-10-19) , changed to merged_cells.ranges ) on a sheet (I can not find in the line) 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 check if one 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 “expand” all cells, you can use the unmerge_cells function

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

Answer 3

To check if one cell is merged, I iterate through sheet.merged_cells.ranges, e.g. @A. Lau offers. Unfortunately, checking the cell type, for example, showing @ 0x4a6f4672, no longer works.

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

All this helped (thanks), but when I used approaches with a couple of spreadsheets, it did not merge all the cells that I expected. I had to loop and retest the mergers to finally get them all to complete. In my case, it took 4 passes, so that everything would open, 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))