update "table.ref" to max row

# ws - work sheet
row_num = ws.max_row + 1
table.ref[:-1] + str(row_num)  # A1:W98

add new row data

row_data = [1,2,3,4,5,]
for col, value in enumerate(row_data):
    ws.cell(row=row, column=col+1).value = value

This has been very useful for me and it is adapted so you can add multiple rows from a pandas DataFrame at once. The table headers must coincide with the table dataframe column names.

from openpyxl import load_workbook
def add_df_data_to_existing_excel_table(excel_file, sheet_name, table_name, df_new_data):
    """
    Add a DataFrame to an existing Excel table. The DataFrame must have the same columns as the table.
    
    parameters
    ----------
    excel_file: str, path to the Excel file
    sheet_name: str, name of the sheet
    table_name: str, name of the table
    df_new_data: DataFrame, data to be added to the table
    """
    wb=load_workbook(excel_file)
    table = wb[sheet_name].tables[table_name]
    row_num = wb[sheet_name].max_row + 1
    column_key = {name: index+1 for index, name in enumerate(table.column_names)}

    for d in df_new_data.to_dict(orient='records'):
        for col, value in d.items():
            wb[sheet_name].cell(row=row_num, column=column_key[col]).value = value
        row_num += 1

    table.ref = table.ref[:-1] + str(row_num)  # A1:W98
    wb.save(excel_file)  

from openpyxl import load_workbook
wb=load_workbood(myfile)
ws=wb['mytab']
tab = ws.tables["Table1"]
tab.ref = f"A1:{ws.max_column}{ws.max_row}"

from openpyxl import load_workbook

filename= r'C:\Users\PC/test.xlsx'

wb = load_workbook(filename)
ws = wb['Hoja1']
ws["A1"] = "AAA"
ws["A2"] = "BBB"

wb.save(filename)