xlwings - efficient way to find and replace-CodePudding

I am trying to do the below in excel using xlwings

a) Read the input file (which has 15 sheets) Old_file.xlsx

b) Find and Replace the keyword Old Name with New Name (from the python list)

c) I have a python list of new names. So, Save the file with a pattern including {New Name}.xlsx`

So, I tried the below

new_names_list = ['Rick','Tom','Joshua']
workbook = xw.Book('Old_file.xlsx') # has 15 worksheets
wks = workbook.sheets
for name in new_names_list:
    for sheet in wks:
        sheet.used_range.api.Replace("Old Name", name)
    file_name = f"FY2022_{name}_Excel_Worksheet_Oct_2022_1.0.xlsx"
    print(file_name)
    workbook.save(r'C:/Users/John/Downloads/files_template/file_name')

When I do this, I get the below error and my keyword replacements also doesn't work correctly

----------------------------------------------------------- KeyError Traceback (most recent call last) in

~\Anaconda3\lib\site-packages\xlwings\main.py in save(self, path, password) 1063 path = utils.fspath(path) 1064
with self.app.properties(display_alerts=False): -> 1065 self.impl.save(path, password=password) 1066 1067 @property

~\Anaconda3\lib\site-packages\xlwings_xlwindows.py in save(self, path, password) 801 ".xla": FileFormat.xlAddIn, 802 } --> 803 file_format = ext_to_file_format[target_ext] 804 if (saved_path != "") and (path is None): 805 # Previously saved: Save under existing name

KeyError: ''

Is there any other better way to write this?

update

File "<unknown>", line 8
    workbook.save(r'C:\Users\John\Downloads\files_template\'   file_name)
                                                                                       ^
SyntaxError: EOL while scanning string literal

CodePudding user response：

True the orig workbook doesn't change but what is in memory will and that would be an issue and why the pop up occurred. You could try something like this where you change the name using the previous name as the search string.

In the code below the the first name in the new_names_list is the first search string so we can skip that and loop to the next name. Then for the first name we want to change 'Rick' we perform the same search/replace but use the previous name in new_names_list as the search string.
For the next name 'Tom' this time we use the previous name 'Rick' as the search string. etc...
So yes no need to re-open the workbook.

import xlwings as xw

new_names_list = ['Old Name', 'Rick','Tom','Joshua']
workbook = xw.Book('drop_out.xlsx') # has 15 worksheets
wks = workbook.sheets
for enum, name in enumerate(new_names_list):
    if name != new_names_list[0]:
        for sheet in wks:
            sheet.used_range.api.Replace(new_names_list[enum-1], name)

        file_name = f"FY2022_{name}_Excel_Worksheet_Oct_2022_1.0.xlsx"
        print(file_name)
        workbook.save(r'C:/Users/John/Downloads/files_template/'   file_name)