openpyxl结合pandas进行单元格数据标红

一点点说明

困惑了半天，用过dataFrame.style.apply()和excel_writer = pd.ExcelWriter(“output.xlsx”, engine=“openpyxl”)去实现，各种报错。
比如：AttributeError: ‘OpenpyxlWriter’ object has no attribute ‘save’
——改用_save
比如：ValueError: Styles supplied as string must follow CSS rule formats, for example ‘attr: val;’
比如：AttributeError: ‘str’ object has no attribute ‘fill’
比如：AttributeError: property ‘book’ of ‘OpenpyxlWriter’ object has no setter
比如：AttributeError: ‘Styler’ object has no attribute ‘style’
最后试出来这个方法，这里做个记录。直接上代码

代码

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# 读取原文档，用于遍历单元格
mergeData = pd.read_excel(out_file, keep_default_na=False).astype(str)

# 使用openpyxl加载新的excel文件
book = load_workbook(out_file)
sheet = book.active

# 单元格填充红色
redFill = PatternFill(start_color="FFFF0000",
                      end_color="FFFF0000",
                      fill_type="solid")

# 校验单元格的值，如果不满足规定值则记录下其行列值
for i, row in enumerate(mergeData.index):
    for j, col in enumerate(mergeData.columns):   
        # TODO： 需要自己实现check_vlaue方法     
        if check_value(mergeData.loc[row, col]) != True:
            sheet.cell(row=i+2, column=j+1).fill = redFill  # openpyxl的行列索引从1开始

# 保存新的excel文件
book.save(out_file)