There is a way to iterate over Excel tables with OpenPyXL. The functionality has changed in newer...
Inserting rows into Excel table without corrupting the table
With OpenPyXL you can insert rows into a worksheet with the worksheet method insert_rows. First, we need to load our table from the Excel workbook named "Table".
from openpyxl import load_workbook
SAVE_FILENAME = "New Table.xlsx"
wb = load_workbook("Table.xlsx")
ws = wb.active
Our Table workbook contains the following table starting from Cell A1 and the table size $A$2:$B$7:
Now we are ready to insert our rows with insert_rows. insert_rows takes two arguments,
amount. The argument
idx is the row number that you want to insert rows before. The
amount gives the number of rows you want to insert. Let's insert one row before row 5.
Our table now looks like below, but wait! There is a problem. Our table does not include the last row anymore. The table size is still $A$2:$B$7. Remember that when you are inserting rows into an Excel workbook, Excel extends the table dimensions for you. OpenPyXL does not extend the table by default, so we need to change the tables reference.
Changing the table references
Ok, let's change the references. Our table name is Table1 in the Excel workbook. Changing the references is easy. While wer'e at it we can also insert new values in the table cells.
table = ws.tables["Table1"]
table.ref = "A2:B8"
ws["A5"] = 6
ws["B5"] = "Cucumber"
Much better! The source code is available on GitHub.