How can I iterate through excel files sheets and insert formula in Python?

Question

I get this error

TypeError: 'Workbook' object is not subscriptable

when i run this code

import xlsxwriter
from openpyxl import load_workbook

in_folder = r'xxx' #Input folder
out_folder = r'xxx' #Output  folder

if not os.path.exists(out_folder):
    os.makedirs(out_folder)

file_exist = False  
dir_list = os.listdir(in_folder)
for xlfile in dir_list:
    if xlfile.endswith('.xlsx') or xlfile.endswith('.xls'):
        file_exist = True
        str_file = os.path.join(in_folder, xlfile)
        work_book = xlsxwriter.Workbook(filename=str_file)
        work_sheet = work_book['test1'] #error above is thrown here
        work_sheet.write_formula('C2', '=A2+B2') #Add formular but not sure of  how to apply it  to the entire column.
        out_Path = os.path.join(out_folder,work_book)

Edit: I managed to figure out the above and using this code:-

work_book = openpyxl.load_workbook(os.path.join(in_folder,xlfile)) 
work_sheet = work_book['test1']

However, the issue formulas still exists in the new code below:-

from openpyxl import load_workbook

in_folder = r'xxx' #Input folder
out_folder = r'xxx' #Output  folder

if not os.path.exists(out_folder):
    os.makedirs(out_folder)

file_exist = False  
dir_list = os.listdir(in_folder)
for xlfile in dir_list:
    if xlfile.endswith('.xlsx') or xlfile.endswith('.xls'):
        str_file = xlfile        
        work_book = openpyxl.load_workbook(os.path.join(in_folder,str_file))
        work_sheet = work_book['Sheet1']
        row_count  = work_sheet.max_row
        for row in work_sheet.iter_rows(min_row=1, min_col=1, max_row=work_sheet.max_row):
            print(row_count)
            for i, cellObj in enumerate(work_sheet['U'], 2):
                cellObj.value = f'=Q{row_count}-T{row_count}'
            work_book.save(os.path.join(out_folder, xlfile))

Ideally, I would like to loop through a folder with .xlsx files, add a formular and apply it to the entire column (U). In this case, I would like to save the files(with the formula effected) in another folder(out_folder).

Answer 1

Documentation for xlsxwriter.Workbook shows

 work_book.get_worksheet_by_name('test1')

Maybe openpyxl or other module could use ['test1']