Python openpyxl insert new row in table

Question

I'm currently trying to insert a new row into a table in my excel file. In the internet I found this:

    import openpyxl
    wb = openpyxl.load_workbook(excel_file_name)
    sheet = wb['Sheet1']
   
    sheet.insert_rows(1, amount=10)

    wb.save(excel_file_name)
    wb.close()

Without the table content in my excel file it works fine. Using it with the table in my excel file it always wants to restore the file, because it cannot read something.

The content of my excel file:

What is the content of your table? Might be related to problem in this question [Openpyxl created excel file with table causes file that requires recovery error](https://stackoverflow.com/questions/62300178/openpyxl-created-excel-file-with-table-causes-file-that-requires-recovery-error) — chickity china chinese chicken, Nov 25 '21 at 19:53
@chickitychinachinesechicken Surname, Lastname and address... That problem cannot really help me since I want to insert a row into an existing table. ^^' — XaniXxable, Nov 25 '21 at 20:16
do you want to insert a new row or 10 new rows? `amount=10` parameter in `sheet.insert_rows(1, amount=10)` will insert 10 rows before the first row starting at index `1`. — chickity china chinese chicken, Nov 25 '21 at 21:40
your code suggests you want to insert only one row at index 1 for `Nr.` with `value=10`, have you tried `sheet.insert_rows(idx=2, amount=1); sheet['B2']=10` ? — chickity china chinese chicken, Nov 25 '21 at 21:44
Thanks you @chickitychinachinesechicken. That solved my problem. I will poste the correct code as an answer! — XaniXxable, Nov 26 '21 at 16:49

score 1 · Accepted Answer · answered Nov 26 '21 at 16:50

The solution is to use in the function insert_rows(idx=row, amount=amount to insert a new row in a table.

This is the working code:

    import openpyxl
    wb = openpyxl.load_workbook(EXCEL_FILE_NAME)
    sheet = wb['Sheet1']

    sheet.insert_rows(idx=2, amount=1)
    cell_object = sheet.cell(
        row=2, column=1)
    cell_object.value = 11

    wb.save(EXCEL_FILE_NAME)
    wb.close()

Python openpyxl insert new row in table

1 Answers1