如何用Python的openpyxl结合Pandas按条件更新Excel单元格及代码优化

需求说明 #

根据Pandas DataFrame中year、animal、branch等列的值，定位到Excel对应工作表的单元格，并依据imp列的值设置单元格填充颜色。

示例数据 #

year  animal branch   imp   val
0  2021     100      A  True  0.01
1  2021     101      B  True  0.02
2  2022     102      C False  0.03
3  2022     103      D  True  0.04

现有代码问题分析 #

你提供的代码存在以下问题：

1. 函数名与实际功能不符：get_col_number和get_row_number返回的是cell对象而非行号/列号，后续调用sheet.cell(row=row_num, column=col_num)会直接报错
2. 拼写错误：row["bransh"]应为row["branch"]
3. 查找逻辑冗余：每次查找都遍历整行/整列，效率低下
4. 无容错处理：若DataFrame中的值在Excel中不存在，会触发AttributeError
5. 遍历效率低：使用iterrows遍历DataFrame，性能不如itertuples

优化后的代码 #

import openpyxl
import pandas as pd
from openpyxl.styles import PatternFill

# 预定义填充样式，避免重复创建
YELLOW_FILL = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 加载Excel文件（替换为你的实际路径）
folder = "./"
book = openpyxl.load_workbook(folder + "input.xlsx")

# 预构建每个工作表的索引映射：animal→行号，branch→列号
sheet_mappings = {}
for sheet_name in book.sheetnames:
    sheet = book[sheet_name]
    # 构建animal到行号的映射（假设animal在第1列，从第2行开始）
    animal_to_row = {}
    for row in range(2, sheet.max_row + 1):
        animal_val = sheet.cell(row=row, column=1).value
        if animal_val is not None:
            animal_to_row[animal_val] = row
    # 构建branch到列号的映射（假设branch在第1行）
    branch_to_col = {}
    for col in range(1, sheet.max_column + 1):
        branch_val = sheet.cell(row=1, column=col).value
        if branch_val is not None:
            branch_to_col[branch_val] = col
    sheet_mappings[sheet_name] = {
        "animal_row": animal_to_row,
        "branch_col": branch_to_col
    }

# 遍历DataFrame更新Excel
for row in data.itertuples(index=False):
    year_str = str(row.year)
    if year_str not in sheet_mappings:
        continue  # 跳过不存在的工作表
    
    mapping = sheet_mappings[year_str]
    # 获取行号和列号
    row_num = mapping["animal_row"].get(row.animal)
    col_num = mapping["branch_col"].get(row.branch)
    
    if row_num and col_num and row.imp:
        sheet = book[year_str]
        cell = sheet.cell(row=row_num, column=col_num)
        cell.fill = YELLOW_FILL

# 保存更新后的文件
book.save(folder + "output.xlsx")

核心优化点 #

• 预构建索引映射：一次性遍历工作表生成animal/branch与行号/列号的对应关系，避免重复遍历整表，大幅提升效率
• 使用itertuples遍历：比iterrows更快，返回元组形式的数据，访问更高效
• 增加容错处理：判断工作表、行号、列号是否存在，避免数据不匹配导致程序崩溃
• 预创建样式对象：提前实例化填充样式，避免循环中重复创建对象
• 修正拼写错误：统一使用正确的branch字段名

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