如何让openpyxl兼容Styler格式DataFrame，生成无需手动调整的HR对比Excel

你的问题核心是Pandas Styler对象无法直接和openpyxl的格式修改API联动，但可以通过「先导出带高亮的Excel，再用openpyxl二次编辑格式」的方式解决，不需要替换工具，也不是代码顺序问题。以下是完整实现方案：

关键思路 #

1. 先用Styler导出带差异高亮的Excel文件
2. 用openpyxl加载这个已生成的文件，单独设置列宽、日期格式
3. 优化原有代码的效率（比如用矢量化操作替代逐行apply）

修改后的完整代码 #

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import numbers

# ----------------------
# 1. 读取并预处理数据
# ----------------------
# 读取新数据（本周）
new_WFRL = pd.read_excel(
    "H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 6.24.24.xlsx",
    usecols=["Empl ID", "Name", "Grade", "Step", "Grade Entry Date", "Step Date", "WGI Due Dt"],
    parse_dates=["Grade Entry Date", "Step Date", "WGI Due Dt"],
    index_col=False
).sort_values("Name").add_prefix("New ")

# 读取旧数据（上周）
old_WFRL = pd.read_excel(
    "H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 6.14.24.xlsx",
    usecols=["Empl ID", "Name", "Grade", "Step", "Grade Entry Date", "Step Date", "WGI Due Dt"],
    parse_dates=["Grade Entry Date", "Step Date", "WGI Due Dt"],
    index_col=False
).sort_values("Name").add_prefix("Old ")

# 合并数据
merged_WFRL = pd.merge(old_WFRL, new_WFRL, how="outer", left_on="Old Empl ID", right_on="New Empl ID")

# ----------------------
# 2. 筛选有变动的行（用矢量化操作替代apply，提升效率）
# ----------------------
merged_WFRL["changes"] = ((merged_WFRL["Old Grade"] == merged_WFRL["New Grade"]) & 
                          (merged_WFRL["Old Step"] == merged_WFRL["New Step"])).astype(int)
merged_changes = merged_WFRL[merged_WFRL["changes"] == 0].drop("changes", axis=1)

# ----------------------
# 3. 定义高亮规则
# ----------------------
def highlight(df):
    grade_mismatch = df['Old Grade'] != df['New Grade']
    step_mismatch = df['Old Step'] != df['New Step']

    # 初始化样式矩阵
    style_df = pd.DataFrame('', index=df.index, columns=df.columns)
    style_df.loc[grade_mismatch, 'New Grade'] = 'background-color: yellow'
    style_df.loc[step_mismatch, 'New Step'] = 'background-color: red'
    
    return style_df

# 应用高亮样式
color_changes = merged_changes.style.apply(highlight, axis=None)

# ----------------------
# 4. 导出带高亮的Excel，再用openpyxl设置列宽和日期格式
# ----------------------
output_path = "H:/DIR/Human Resources/HR Audits/WFRL Comparison Reports/6.24.24v6_final.xlsx"

# 第一步：导出Styler的高亮内容
color_changes.to_excel(output_path, index=False, engine="openpyxl")

# 第二步：用openpyxl加载文件，调整格式
wb = load_workbook(output_path)
ws = wb.active

# 设置列宽（根据实际需求调整宽度值）
column_widths = {
    'A': 12,  # Old Empl ID
    'B': 20,  # Old Name
    'C': 8,   # Old Grade
    'D': 8,   # Old Step
    'E': 15,  # Old Grade Entry Date
    'F': 15,  # Old Step Date
    'G': 15,  # Old WGI Due Dt
    'H': 12,  # New Empl ID
    'I': 20,  # New Name
    'J': 8,   # New Grade
    'K': 8,   # New Step
    'L': 15,  # New Grade Entry Date
    'M': 15,  # New Step Date
    'N': 15   # New WGI Due Dt
}
for col, width in column_widths.items():
    ws.column_dimensions[col].width = width

# 设置日期列的格式（mm/dd/yyyy）
date_columns = ['E', 'F', 'G', 'L', 'M', 'N']  # 对应所有日期列的列标
for col in date_columns:
    for cell in ws[col]:
        cell.number_format = numbers.FORMAT_DATE_XLSX14  # 对应mm/dd/yyyy格式

# 保存最终文件
wb.save(output_path)

代码说明 #

• 用矢量化操作替代apply判断变动行，比原代码效率更高，尤其是数据量大时
• 先导出Styler的高亮内容，再用openpyxl加载文件调整格式，完美避开Styler与openpyxl的兼容性问题
• 列宽和日期格式可根据实际需求自由调整
• 日期格式使用openpyxl内置的FORMAT_DATE_XLSX14，对应你需要的mm/dd/yyyy格式

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