Excel 的条件格式和数据验证能自动标记异常数据和规范输入，openpyxl 可以批量设置。

一、条件格式

from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule
from openpyxl.styles import PatternFill

# 大于80标绿
ws.conditional_formatting.add("A1:A100", CellIsRule(
    operator="greaterThan", formula=["80"],
    fill=PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
))

# 低于60标红
ws.conditional_formatting.add("A1:A100", CellIsRule(
    operator="lessThan", formula=["60"],
    fill=PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
))

# 数据条
ws.conditional_formatting.add("B1:B100", DataBarRule(
    start_type="min", end_type="max", color="5B9BD5"
))

# 色阶
ws.conditional_formatting.add("C1:C100", ColorScaleRule(
    start_type="min", start_color="63BE7B",
    mid_type="percentile", mid_value=50, mid_color="FFEB84",
    end_type="max", end_color="F8696B"
))

二、数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# 下拉列表
dv = DataValidation(type="list", formula1='"合格,不合格,优秀"', allow_blank=True)
dv.error = "请从下拉列表中选择"
dv.errorTitle = "输入错误"
ws.add_data_validation(dv)
dv.add("D2:D100")

# 整数范围限制
dv2 = DataValidation(type="whole", operator="between", formula1="0", formula2="100")
dv2.error = "请输入 0-100 之间的整数"
ws.add_data_validation(dv2)
dv2.add("A2:A100")

💡 觉得有用的话，点赞 + 关注【张老师技术栈】吧！