Python: Write Data to Columns in Protected .xlsm Excel File

Question

I have a password-protected .xlsm file with formulas in some columns and macros.

Is there a way for me to write data from pandas into specific columns in this .xlsm file?

I have tried xlwings, but it takes 15 minutes to update 10k rows, even with Application.ScreenUpdating = False.

Currently I am using a function shown below:

import xlwings as xw

def enter_data(path, sheetname, start_column, df):
wb = xw.Book(path)
ws = wb.sheets[sheetname]

for i in range(len(df.columns)):
    column = chr(ord(start_column)+i)

    for index, row in df.iterrows():
        cell = column.upper() + str(index + 3)
        ws.range(cell).value = row[i]

wb.save(path)
wb.close()

I am aware of a workaround in which I save the pandas dataframe to csv, and copy-paste the csv to the password-protected .xlsm file, but I am interested in a fully-automated process, if possible.

Thank you!

Answer 1

You must not loop through single cells with xlwings. If you assign your array directly to a cell, it will be fast enough, e.g. wb.sheets[0].range('A1').value = my_dataframe. See: https://docs.xlwings.org/en/stable/datastructures.html