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!!
Check if you can insert a row/column between merge cells in safe mode (press the control key immediately after clicking Excel icon, it should pop up a confirmation dialog to ask whether you want to start in safe mode) or Right-click the Start button (lower-left corner) in Windows, and click Run. , type excel /safe, and ...
To insert multiple rows into the worksheet, call the insertRows method of the Cells collection. The InsertRows method takes two parameters: Row index, the index of the row from where the new rows will be inserted. Number of rows, total number of rows that need to be inserted.
Right-click the merged cell B1:D1, select "paste special -> formulas" You should see the merged cell being 0. Type Col1 in the merged cell. You should now see all B2, C2, D2 to be Col1, i.e. now you can reference the merged cell as you expect it to be.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With