如何遍历excel文件表并在Python中插入公式? [英] How can I iterate through excel files sheets and insert formula in Python?

问题描述

我收到此错误

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)

我设法找出上面并使用此代码:-

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

理想情况下，我想循环浏览一个包含.xlsx文件的文件夹，添加一个公式并将其应用于整个列(U).在这种情况下，我想将文件(使用公式生效)保存在另一个文件夹(out_folder)中.

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

推荐答案

xlsxwriter.Workbook 显示

 work_book.get_worksheet_by_name('test1')

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

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

这篇关于如何遍历excel文件表并在Python中插入公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！