1

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!

SAKURA
  • 937
  • 11
  • 29
  • I am not familiar with `xlwings`. If there is a specific tag, I suggest you drop `dataframe` and replace with this tag if there is. If not, it's no clear to me what the significance of the password protect is for runtime. Also, I wouldn't expect that kind of runtime for the data size; is there some kind of animation going on? – roganjosh Dec 05 '18 at 19:07
  • @roganjosh thanks for the suggestion. As far as I know, `xlwings` can edit files with password protection, unlike pandas. I try to avoid animation by setting formulas to `manual` in excel and turning off `screen_updating`. – SAKURA Dec 05 '18 at 19:26
  • @SAKURA 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: http://docs.xlwings.org/en/stable/datastructures.html – Felix Zumstein Dec 06 '18 at 08:33
  • @FelixZumstein Thank you, please post an answer here so that I can accept it. – SAKURA Dec 07 '18 at 15:00
  • @SAKURA done that. – Felix Zumstein Dec 08 '18 at 09:13

1 Answers1

0

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

Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62