Insert row + shifting cells in openpyxl

Question

Does openpyxl support the insert row method with "shift cells right/down"?

I was thinking inserting some cells on specific columns. This is to fix dynamic data between the "Header" and "Total" while getting the format of the cells in between the Header and Total. (see Excel Reference).

Tried using insert_row method but it does insert for the entire row/column (correct me if i'm wrong here) https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/worksheet/worksheet.html#Worksheet.insert_rows

Excel Reference

Any comments, feedbacks or suggestions will be greatly appreciated! Thank you in advance!!

TookieWookie TookieWookie · Accepted Answer · 2020-05-20T08:41:02

(Sources:

I believe the method that you are looking for is openpyxl.worksheet.worksheet.Worksheet.move_range(). This method allows you shift a range of cells within a worksheet. You can specify either the number of rows to move the range, or the number of columns to move the range, or both. Here is the docstring for the method:

    """
    Move a cell range by the number of rows and/or columns:
    down if rows > 0 and up if rows < 0
    right if cols > 0 and left if cols < 0
    Existing cells will be overwritten.
    Formulae and references will not be updated.
    """

CAUTION: WHATEVER CONTENT IS IN THE DESTINATION CELLS WILL BE OVERWRITTEN

I have created an example worksheet that is populated by a 9x9 grid to demonstrate.

from openpyxl import Workbook

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active

# create a 9x9 grid of numbers
for row in range(1, 10):
    ws1.append(range(10))

# move some numbers
ws1.move_range("H5:J9", rows=2, cols=3)

# move some numbers down 1 row
ws1.move_range("A3:B9", rows=1)

wb.save(filename = dest_filename)

Voila! The range H5:J9 has moved down 2 rows and right 3 columns, and the range A3:B9 has moved down 1 row. This is the same result was would have been achieved in normal Excel using Insert>Shift cells down and Insert>Shift cells right.

Insert row + shifting cells in openpyxl

1 Answers