python - 如何遍历 excel 文件表并在 Python 中插入公式？

Question

我收到这个错误

TypeError: 'Workbook' object is not subscriptable

当我运行这段代码

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)

编辑：我设法弄清楚上述内容并使用此代码：-

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

但是，问题公式仍然存在于下面的新代码中：-

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))

理想情况下，我想遍历一个包含 .xlsx 文件的文件夹，添加一个公式并将其应用于整个列 (U)。在这种情况下，我想将文件（使用公式）保存在另一个文件夹（out_folder）中。

标签： pythonexcelpandasexcel-formulaopenpyxl

Answer 1

xlsxwriter.Workbook的文档显示

 work_book.get_worksheet_by_name('test1')

也许openpyxl或其他模块可以使用['test1']