迭代工作簿和工作表Openpyxl

Question

我试图使用Openpyxl迭代一些excel工作簿和这些工作簿中包含的所有工作表。我没有得到和错误，但我的代码的第二部分开始加载YK-2022.xlsx不工作。YK-2022.xlsx文件没有更新我想要的信息。有人知道为什么会这样吗？

我假设您无法使用wb =load_workbook这样的字符串加载多个工作簿(filename= 'Molina.xlsx'，'YK-2022.xlsx')。我有几个文件，我需要更新与相同的信息，并试图找出最有效的方式这样做。我对Openpyxl和Python相当陌生。提前谢谢你的帮助。

from openpyxl import load_workbook

wb = load_workbook(filename = 'Molina.xlsx')
print(wb.sheetnames)

for worksheets in wb.sheetnames:
    worksheet = wb[worksheets]
    print(worksheet)
sheet_ranges = wb.worksheets
sheet = wb.active
sheet["B15"] = "October 1, 2021"
sheet["B16"] = "October 1, 2022"
sheet["B43"] = " "
sheet["A41"] = "Period 10/1/2022-10/1/2023"
wb.save(filename="Molina.xlsx")

wb = load_workbook(filename = 'YK-2022.xlsx')
print(wb.sheetnames)

for worksheets in wb.sheetnames:
    worksheet = wb[worksheets]
    print(worksheet)
    
sheet_ranges = wb.worksheets
print(wb.sheetnames)
sheet["B15"] = "October 1, 2023"
sheet["B16"] = "October 1, 2024"
sheet["B43"] = " "
sheet["A41"] = "Period 10/1/2022-10/1/2023"
sheet["B50"] = "John Doe"
wb.save(filename="YK-2022.xlsx")

Answer 1

您需要循环-首先是文件列表，其次是工作表列表：

from fileinput import filename
from openpyxl import load_workbook

list_of_files = ["abc", "def"]

for file in list_of_files:
    file_name = f"{file}.xlsx"
    wb = load_workbook(filename=file_name)
    for worksheets in wb.sheetnames:
        print(worksheets)
        sheet = wb[worksheets]
        sheet["B15"] = "October 1, 2021"
        sheet["B16"] = "October 1, 2022"
        sheet["B43"] = " "
        sheet["A41"] = "Period 10/1/2022-10/1/2023"
        wb.save(filename=file_name)