openpyxl - adding new rows in excel file with merged cell existing

Question

Before

After

So, I was trying to add 6 rows into the excelsheet. And I used openpyxl.worksheet.worksheet.Worksheet.insert_rows(ws,idx=0,amount=6) to help me accomplish the task. This line works perfect with normal excel file.

But, when it comes to the excel file contained merged cells. The program will not working properly just like the image I attached.

Could someone give me some ideas about how to solve the issue. I ran out all the ideas and need some inspirations.

Thank you very much for whoever answers my questions!!

Answer 1

Let's say you want to add 3 rows at the top of your table, you'll first have to shift down the merged cells and then insert the rows; For this we are going to use the shift(col_shift=0, row_shift=0) method;

Help on method shift in module openpyxl.worksheet.cell_range:

shift(col_shift=0, row_shift=0) method of openpyxl.worksheet.cell_range.CellRange instance
    Shift the range according to the shift values (*col_shift*, *row_shift*).

    :type col_shift: int
    :param col_shift: number of columns to be moved by, can be negative
    :type row_shift: int
    :param row_shift: number of rows to be moved by, can be negative
    :raise: :class:`ValueError` if any row or column index < 1

---

The number of rows you want to insert must coincide with the numbers of rows you shift; So for your example you'll just have to:

merged_cells_range = ws.merged_cells.ranges
for merged_cell in merged_cells_range:
    merged_cell.shift(0,3)
ws.insert_rows(1,3)

So in the end the merged cells are preserved