Enter blank rows in a table using openpyxl

I've not used this package before but reading the openpyxl documentation, it looks like you could do something like this:

row_number = 9
insert_pt = row_number + 1
no_rows = 20

ws.insert_rows(insert_pt, no_rows)

OR Here is a solution using Pandas and Numpy:

# Import libraries
import pandas as pd
import numpy as np

# Read data
df = pd.read_excel("ProjectName_Migration_Workbook.xlsx")

# Create user-defined function 'blankRowInsert'
def blankRowInsert(df, insert_pt, no_rows)
    ip = insert_pt-1
    before_insert = ip
    no_insert = no_rows
    orig_rows = len(df)

    df_new = df.iloc[0:before_insert]

    for index in range(no_insert):
      df_new = df_new(pd.DataFrame([[np.nan] * len(df_new.columns)], columns = df_new.columns, ), ingore_index=True
    
    df_new = df_new.append(df.iloc[ip:orig_rows], ignore_index = True)

    return(df_new)
    
# Test the function
new_df = blankRowInsert(df=df, insert_pt=9, no_rows = 20)

You could then use the following to write the new dataframe with inserted blank rows back to an excel file:

new_df.to_excel('your_new_excel_file.xlsx', index = False)

Assuming you know the table's name, you can access it through ws.tables:

from openpyxl import load_workbook
from openpyxl.worksheet.cell_range import CellRange

wb = openpyxl.load_workbook("book.xlsx")
ws = wb.active
table = ws.tables['Table1']

To get its range you can do:

print(table.ref)

Which gives, for example:

D5:F11

Now, you can convert it to a CellRange to be able to change it and get the first row:

table_range = CellRange(table.ref)
print(table_range.min_row)

Will give:

5

So now to add empty rows, first expand the table:

table_range.expand(down=20)
table.ref = table_range.coord

And then add the actual rows to the sheet:

ws.insert_rows(table_range.min_row + 9, 20)

Don't forget to wb.save in the end.

Full example code:

from openpyxl import load_workbook
from openpyxl.worksheet.cell_range import CellRange

start_row = 9
num_rows = 20

wb = load_workbook("book.xlsx")
ws = wb.active
table = ws.tables['Table1']

table_range = CellRange(table.ref)
table_range.expand(down=num_rows)
table.ref = table_range.coord

ws.insert_rows(table_range.min_row + start_row, num_rows)
wb.save("book.xlsx")