openpyxl封装为类方法时实现单元格范围写入的问题咨询

现有代码已知问题 #

• 类的实例方法write未定义self作为第一个参数，不符合Python类实例方法的语法规范，调用时会出现参数数量不匹配报错。
• days_of_the_month_cells字典中每周配置存在重复的"Wed"键，Python字典同键会自动覆盖前序定义，直接导致G列对应的单元格范围配置丢失，需要根据实际业务修正重复键名（当前代码中H列配置被错误赋值给Wed键）。
• main.py中实例化Write_To_Excel时传参顺序错误：构造函数第三个参数为broke，原代码重复传入total_good，漏传total_broke。
• 构造函数参数使用了Python内置函数名all作为变量名，存在命名冲突风险，易引发不可预期的逻辑错误。

实现思路 #

1. openpyxl不支持直接给任意单元格范围批量赋值同一个值，需要先读取范围对象，逐行逐单元格遍历赋值，兼容单列、多列、任意矩形范围场景。
2. 逐层遍历days_of_the_month_cells字典，取出所有配置的单元格范围字符串。
3. 遍历每个范围下的所有单元格，写入目标数值；预留值映射扩展能力，后续可根据周、星期维度匹配不同的指标值写入。

修正后的完整代码 #

excel_writer.py #

from openpyxl import load_workbook

# 注意：原配置存在重复Wed键，此处临时将H列键名改为Unknown_H，请根据实际业务修正为正确的星期标识
days_of_the_month_cells = {
    "Week 1": {
        "Sun": "D6:D22",
        "Mon": "E6:E22",
        "Tue": "F6:F22",
        "Wed": "G6:G22",
        "Unknown_H": "H6:H22",
        "Thu": "I6:I22",
        "Fri": "J6:J22",
        "Sat": "K6:K22",
    }, "Week 2": {
        "Sun": "D23:D39",
        "Mon": "E23:E39",
        "Tue": "F23:F39",
        "Wed": "G23:G39",
        "Unknown_H": "H23:H39",
        "Thu": "I23:I39",
        "Fri": "J23:J39",
        "Sat": "K23:K39",
    }, "Week 3": {
        "Sun": "D40:D56",
        "Mon": "E40:E56",
        "Tue": "F40:F56",
        "Wed": "G40:G56",
        "Unknown_H": "H40:H56",
        "Thu": "I40:I56",
        "Fri": "J40:J56",
        "Sat": "K40:K56",
    }, "Week 4": {
        "Sun": "D57:D73",
        "Mon": "E57:E73",
        "Tue": "F57:F73",
        "Wed": "G57:G73",
        "Unknown_H": "H57:H73",
        "Thu": "I57:I73",
        "Fri": "J57:J73",
        "Sat": "K57:K73",
    }
}


class Write_To_Excel:
    def __init__(self, cull, all_total, broke, good, hold, joblot):
        self.cull = cull
        self.all_total = all_total
        self.broke = broke
        self.good = good
        self.hold = hold
        self.joblot = joblot
        # 统一管理文件路径，后续修改无需调整核心逻辑
        self.input_path = 'XLSXs/input/Finished Goods Daily Report 2022.xlsx'
        self.output_path = 'XLSXs/output/Finished Goods Daily Report 2022.xlsx'

    def write(self, target_value=None):
        """
        写入数据到Excel
        :param target_value: 指定所有单元格范围统一写入的数值，不传默认写入cull值
        """
        wb = load_workbook(self.input_path)
        ws = wb.active

        # 遍历所有周配置
        for week, day_range_map in days_of_the_month_cells.items():
            # 遍历当周每天对应的单元格范围
            for day, cell_range in day_range_map.items():
                range_data = ws[cell_range]
                # 遍历范围所有单元格赋值
                for row in range_data:
                    # 单列场景下row直接是单元格对象
                    if not isinstance(row, tuple):
                        row.value = target_value if target_value is not None else self.cull
                    # 多列/矩形范围场景下row是该行单元格组成的元组
                    else:
                        for cell in row:
                            cell.value = target_value if target_value is not None else self.cull

        wb.save(self.output_path)

main.py #

from excel_writer import Write_To_Excel

total_cull = ending_inventory.sum()
total_all = Total_Riper("all.pdf").calculate()
total_broke = Total_Riper("broke.pdf").calculate()
total_good = Total_Riper("good.pdf").calculate()
total_hold = Total_Riper("hold.pdf").calculate()
total_joblot = Total_Riper("joblot.pdf").calculate()

# 修正原传参顺序错误，第三个参数传入total_broke
excel_writer = Write_To_Excel(total_cull, total_all, total_broke, total_good, total_hold, total_joblot)
# 示例：所有范围写入total_cull，若要写入其他值直接传参即可，比如excel_writer.write(total_all)
excel_writer.write()

扩展说明 #

• 如果需要不同列/不同周写入不同指标值，只需调整write方法中的赋值逻辑，根据week、day参数判断取self下对应的属性赋值即可。
• 如果需要写入指定名称的工作表而非默认active表，可在类初始化时增加sheet_name参数，加载工作簿后通过wb[sheet_name]获取目标工作表。
• 写入前建议增加输入文件存在性校验，避免文件不存在抛出报错。

内容的提问来源于stack exchange，提问作者littlejiver