Openpyxl - Merge same cells in column

Question

I'm having trouble making a function that looks through a column (or each column) in the dataframe I'm writing and merging consecutive cells ie.

Would appreciate the help if anyone has done something like this. I've seen one response that uses pandas ExcelWriter, but don't think this is what I'm looking for.

Answer 1

This code will do the needful.

from openpyxl import load_workbook
file = "test.xlsx"
# Load workbook
wb = load_workbook(filename=file)
ws = wb['Sheet1']
mylist = []
for cell in ws['A']:
    mylist.append(cell.value)
mergecount=0
startcell=1
for row in range(1, len(mylist)):
    print(row, mylist[row-1], mylist[row])
    if mylist[row-1] == mylist[row]:
        mergecount += 1
    else:
        print(row, mylist[row-1], mylist[row], startcell, mergecount)
        if mergecount > 0:
            ws.merge_cells(start_row=startcell, start_column=1, end_row=startcell+mergecount, end_column=1)
        mergecount = 0
        startcell = row+1
if mergecount > 0:
    ws.merge_cells(start_row=startcell, start_column=1, end_row=startcell+mergecount, end_column=1)
wb.save(file)

Input and output sheets