openpyxl in Python delete_rows function breaks the merged cell

Question

I use openpyxl 2.6.0 in Python3 (with Django).

I want to manipulate my excel skelton sheet (some cells are merged) to delete a row. But after deleting the row, the merged cells are no longer merged.

I want to keep the merged cells even if some rows are deleted.

Code and excel sample is below.

def excelsample(request):

    wb = openpyxl.load_workbook(os.path.join(settings.BASE_DIR, 'static', 'excel', 'sample.xlsx'))

    sheet = wb['Sheet1']
    sheet.delete_rows(6) # delete 6th row

    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="{fn}"'.format(fn=urllib.parse.quote("excelsample.xlsx"))

    wb.save(response)

    return response

Before:

After:

Answer 1

There is an issue on the openpyxl bug tracker covering this. It has been marked as invalid.

The basic problem is that for this to work, all merged cell ranges would need to be checked and updated after the row delete. There are several complex/difficult issues of similar nature that have been deemed to be of minimal value and thus have not been implemented.

You could do this yourself if needed. There is an attribute (merged_cells) on the worksheet that you could update after the row delete.

Code:

def delete_row_with_merged_ranges(sheet, idx):
    sheet.delete_rows(idx)
    for mcr in sheet.merged_cells:
        if idx < mcr.min_row:
            mcr.shift(row_shift=-1)
        elif idx <= mcr.max_row:
            mcr.shrink(bottom=1)

Test Code:

wb = openpyxl.load_workbook('csvfile.xlsx')

sheet = wb['Sheet2']
delete_row_with_merged_ranges(sheet, 6)

wb.save('csvfile2.xlsx')

Answer 2

For openpyxl 3.04 and Python 3.8.5 the code in Stephen Rauch's answer works only if we change < to <= in the elif clause:

def delete_row_with_merged_ranges(sheet, idx):
    sheet.delete_rows(idx)
    for mcr in sheet.merged_cells:
        if idx < mcr.min_row:
            mcr.shift(row_shift=-1)
        elif idx < mcr.max_row:
            mcr.shrink(bottom=1)