Python script to append DataFrame data to an Excel table using xlwings

append_df_to_excel_table.py

# This code was provided by Sven Bosau
# Website: https://pythonandvba.com
# YouTube: https://youtube.com/@codingisfun

import xlwings as xw
import pandas as pd

# Constants
WORKBOOK_PATH = 'workbook.xlsx'
SHEET_NAME = 'Sheet1'
TABLE_NAME = 'Table1'

# Assuming this is your DataFrame
df = pd.DataFrame({
    'Column1': ['New data 1', 'New data 2'],
    'Column2': ['More new data 1', 'More new data 2']
})

# Convert your DataFrame to a list of lists
new_data = df.values.tolist()

# Use context manager to handle the workbook
with xw.Book(WORKBOOK_PATH) as wb:
    # Select your table
    sht = wb.sheets[SHEET_NAME]
    table = sht.range(TABLE_NAME)

    # Append your data
    table.value = table.value + new_data

    # Save your workbook
    wb.save()