Python 自动化之 Excel 条件格式与数据验证

Excel 的条件格式和数据验证能自动标记异常数据和规范输入openpyxl 可以批量设置。一、条件格式fromopenpyxl.formatting.ruleimportCellIsRule,DataBarRule,ColorScaleRulefromopenpyxl.stylesimportPatternFill# 大于80标绿ws.conditional_formatting.add(A1:A100,CellIsRule(operatorgreaterThan,formula[80],fillPatternFill(start_colorC6EFCE,end_colorC6EFCE,fill_typesolid)))# 低于60标红ws.conditional_formatting.add(A1:A100,CellIsRule(operatorlessThan,formula[60],fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)))# 数据条ws.conditional_formatting.add(B1:B100,DataBarRule(start_typemin,end_typemax,color5B9BD5))# 色阶ws.conditional_formatting.add(C1:C100,ColorScaleRule(start_typemin,start_color63BE7B,mid_typepercentile,mid_value50,mid_colorFFEB84,end_typemax,end_colorF8696B))二、数据验证fromopenpyxl.worksheet.datavalidationimportDataValidation# 下拉列表dvDataValidation(typelist,formula1合格,不合格,优秀,allow_blankTrue)dv.error请从下拉列表中选择dv.errorTitle输入错误ws.add_data_validation(dv)dv.add(D2:D100)# 整数范围限制dv2DataValidation(typewhole,operatorbetween,formula10,formula2100)dv2.error请输入 0-100 之间的整数ws.add_data_validation(dv2)dv2.add(A2:A100) 觉得有用的话点赞 关注【张老师技术栈】吧