Pandas结合XlsxWriter：如何根据DataFrame内容修改单元格格式并添加批注

现有一组经过多轮处理的DataFrame，存储了数据批注以及标注数据是否合格的标记字段，示例数据如下：

import pandas as pd
import numpy as np

df = pd.DataFrame({'Name': ['a','a','b','b'],
                   'Measurements': ['temp','pressure','temp','pressure'],
                   'Values': [1, 2, -1, np.nan],
                   'Comment': ['','','Is negative', 'Is NaN'],
                   'IsBad':[False, False, True, True] })
measurements = df.reset_index().pivot(index='Name',columns='Measurements',values='Values')
comments = df.reset_index().pivot(index='Name',columns='Measurements',values='Comment')
bad_cells = df.reset_index().pivot(index='Name',columns='Measurements',values='IsBad')

需要将上述数据导出为Excel文件，实现两个效果：

• 根据IsBad字段的值对对应单元格设置高亮格式
• 为异常单元格插入批注说明异常原因

之前尝试时遇到的问题：可以为每个透视后的DataFrame单独创建工作表导出，但无法引用其他工作表的内容为单元格添加批注；同时认为XlsxWriter不便于通过循环遍历df对象行的方式逐个定义每个单元格的属性。

使用XlsxWriter作为导出引擎，配合pandas的ExcelWriter即可实现需求，无需拆分多个工作表，直接在同一个工作表中遍历单元格设置属性即可，示例代码如下：

# 定义导出路径
output_path = "测量数据校验结果.xlsx"

# 创建ExcelWriter对象，指定引擎为xlsxwriter
with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer:
    # 先将测量值表写入工作表
    measurements.to_excel(writer, sheet_name='校验结果')
    
    # 获取workbook和worksheet对象
    workbook  = writer.book
    worksheet = writer.sheets['校验结果']
    
    # 定义异常单元格格式，可按需调整
    bad_format = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
    
    # 获取数据维度：行偏移为1（表头占1行），列偏移为1（索引列占1列）
    row_offset = 1
    col_offset = 1
    
    # 遍历所有数据行和列
    for i in range(len(measurements.index)):
        for j in range(len(measurements.columns)):
            # 检查当前单元格是否为异常
            if bad_cells.iloc[i, j]:
                # 计算Excel中对应的行号和列号
                excel_row = i + row_offset
                excel_col = j + col_offset
                # 设置单元格格式
                worksheet.write(excel_row, excel_col, measurements.iloc[i, j], bad_format)
                # 插入批注
                comment = comments.iloc[i, j]
                worksheet.write_comment(excel_row, excel_col, comment)

代码核心逻辑说明：

• 先将主数据表写入Excel，获取工作表操作对象
• 提前定义好异常单元格的样式
• 按索引遍历每个数据单元格，匹配bad_cells和comments表中对应位置的内容，为异常单元格批量设置格式和添加批注
• 所有操作都在同一个工作表完成，不需要额外创建其他工作表存储辅助数据

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