影刀RPA Excel条件格式：数据高亮与可视化 影刀RPA Excel条件格式数据高亮与可视化署名林焱什么情况用什么你用RPA采集了500条商品价格数据到Excel老板说把涨价的标红降价的标绿超过1000块的加粗。手动一个个改格式500行改到天亮。条件格式就是让Excel自动根据数据值改变单元格样式。影刀RPA中通过Python代码块openpyxl可以实现。场景推荐方式简单颜色标记大于X标红Python openpyxl条件格式数据条/色阶/图标集Python openpyxl规则对象复杂多条件样式Python openpyxl自定义规则怎么做基础一单元格值规则importopenpyxlfromopenpyxl.stylesimportPatternFill,Font,Border,Sidefromopenpyxl.formatting.ruleimportCellIsRule,ColorScaleRule,DataBarRule,FormulaRule wbopenpyxl.load_workbook(rD:\报表\价格监控.xlsx)wswb[价格数据]# 定义样式red_fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)green_fillPatternFill(start_colorC6EFCE,end_colorC6EFCE,fill_typesolid)yellow_fillPatternFill(start_colorFFEB9C,end_colorFFEB9C,fill_typesolid)red_fontFont(color9C0006,boldTrue)green_fontFont(color006100,boldTrue)# 规则1价格 1000 标红加粗ws.conditional_formatting.add(C2:C500,CellIsRule(operatorgreaterThan,formula[1000],fillred_fill,fontred_font))# 规则2价格 50 标绿ws.conditional_formatting.add(C2:C500,CellIsRule(operatorlessThan,formula[50],fillgreen_fill,fontgreen_font))# 规则3价格 50-1000 之间黄色ws.conditional_formatting.add(C2:C500,CellIsRule(operatorbetween,formula[50,1000],fillyellow_fill))wb.save(rD:\报表\价格监控.xlsx)基础二涨跌对比标记拼多多店群自动化上架方案# 假设D列是涨跌幅正值涨负值跌ws.conditional_formatting.add(D2:D500,CellIsRule(operatorgreaterThan,formula[0],fillgreen_fill,fontgreen_font))ws.conditional_formatting.add(D2:D500,CellIsRule(operatorlessThan,formula[0],fillred_fill,fontred_font))基础三色阶热力图效果# 三色色阶红→黄→绿color_scaleColorScaleRule(start_typemin,start_colorF8696B,# 最小值红色mid_typepercentile,mid_value50,mid_colorFFEB84,# 中位数黄色end_typemax,end_color63BE7B# 最大值绿色)ws.conditional_formatting.add(C2:C500,color_scale)基础四数据条# 数据条在单元格里画条形图data_barDataBarRule(start_typemin,end_typemax,color638EC6,# 蓝色数据条showValueTrue)ws.conditional_formatting.add(C2:C500,data_bar)基础五公式条件格式# 用公式做更灵活的判断# 标记重复值同一列中出现2次以上的标黄ws.conditional_formatting.add(A2:A500,FormulaRule(formula[COUNTIF($A$2:$A$500,A2)1],fillyellow_fill))# 标记空白行B列为空的整行标灰ws.conditional_formatting.add(A2:F500,FormulaRule(formula[$B2\\],fillPatternFill(start_colorD9D9D9,end_colorD9D9D9,fill_typesolid)))# 标记含特定关键词的行ws.conditional_formatting.add(A2:F500,FormulaRule(formula[ISNUMBER(SEARCH(清仓,$B2))],fillred_fill))完整流程示例库存预警报表importopenpyxlfromopenpyxl.stylesimportPatternFill,Fontfromopenpyxl.formatting.ruleimportCellIsRule,FormulaRule,ColorScaleRule,DataBarRule# yd_input: file_pathfile_pathyd_input.get(file_path,rD:\报表\库存监控.xlsx)wbopenpyxl.load_workbook(file_path)wswb[库存]max_rowws.max_row data_rangefA2:F{max_row}# 样式定义critical_fillPatternFill(start_colorFF0000,end_colorFF0000,fill_typesolid)warning_fillPatternFill(start_colorFFC000,end_colorFFC000,fill_typesolid)ok_fillPatternFill(start_color00B050,end_color00B050,fill_typesolid)white_fontFont(colorFFFFFF,boldTrue)# 规则1库存 安全库存D列 E列→ 红色ws.conditional_formatting.add(fD2:D{max_row},FormulaRule(formula[f$D2$E2],fillcritical_fill,fontwhite_font))# 规则2库存 安全库存×1.5 → 黄色ws.conditional_formatting.add(fD2:D{max_row},FormulaRule(formula[f$D2$E2*1.5],fillwarning_fill))# 规则3库存 安全库存×3 → 绿色ws.conditional_formatting.add(fD2:D{max_row},FormulaRule(formula[f$D2$E2*3],fillok_fill,fontwhite_font))# 规则4库存数量列加数据条ws.conditional_formatting.add(fD2:D{max_row},DataBarRule(start_typemin,end_typemax,color4472C4,showValueTrue))# 规则5金额列色阶ws.conditional_formatting.add(fF2:F{max_row},ColorScaleRule(start_typemin,start_color63BE7B,mid_typepercentile,mid_value50,mid_colorFFEB84,end_typemax,end_colorF8696B))wb.save(file_path)yd_output{status:ok,rules_added:5}有什么坑坑一条件格式范围写错导致规则不生效现象加了条件格式但Excel打开后没有任何变化。原因范围字符串格式不对openpyxl需要标准Excel范围格式如C2:C500不能写成C2-C500或C2:C500:。解决用f-string动态拼范围确保格式正确max_rowws.max_row# 正确range_strfC2:C{max_row}ws.conditional_formatting.add(range_str,rule)# 错误# ws.conditional_formatting.add(C2-C500, rule) ❌# ws.conditional_formatting.add(fC2:C{max_row}:, rule) ❌ 多了冒号坑二多个规则同时匹配时优先级混乱现象一个单元格同时满足多个条件格式规则但显示的颜色不对。原因openpyxl添加规则时后添加的规则优先级更高。Excel中规则从上到下匹配先匹配到的先生效除非设置了停止如果为真。解决按优先级从低到高添加规则最后添加的最优先或者用stopIfTrue参数TEMU店群如何管理运营fromopenpyxl.formatting.ruleimportCellIsRule# 先加低优先级规则ws.conditional_formatting.add(C2:C500,CellIsRule(operatorbetween,formula[50,1000],fillyellow_fill,stopIfTrueFalse))# 再加高优先级规则后加的优先级高ws.conditional_formatting.add(C2:C500,CellIsRule(operatorgreaterThan,formula[1000],fillred_fill,stopIfTrueTrue))ws.conditional_formatting.add(C2:C500,CellIsRule(operatorlessThan,formula[50],fillgreen_fill,stopIfTrueTrue))坑三FormulaRule的公式引用必须用绝对列相对行现象用FormulaRule做整行标记但只有第一行生效其他行格式不变。原因条件格式的公式和普通Excel公式一样会自动扩展到范围内的所有单元格。但扩展时行号会变化列号也会变化。如果你不想让列号变化必须用$锁定列。解决# 正确$D2 锁定D列行号自动变化ws.conditional_formatting.add(A2:F500,FormulaRule(formula[$D2100],fillred_fill))# 这个公式在A2单元格判断 $D2100# 扩展到A3时自动变成 $D3100# 扩展到B2时仍然是 $D2100因为$锁定了D列# 错误D2 不锁列# FormulaRule(formula[D2100], fillred_fill) ❌# 扩展到B2时变成 E2100引用了错误的列坑四条件格式太多导致Excel卡顿现象一个工作表加了20多条条件格式规则后Excel打开卡顿操作延迟。原因每条规则在打开文件时都会重新计算规则越多越慢。尤其色阶和数据条计算量大。解决合并相似规则减少总数# 不推荐10个CellIsRule分别标记10个区间# for i in range(10):# ws.conditional_formatting.add(...)# 推荐用一个色阶代替多个区间规则ws.conditional_formatting.add(C2:C500,ColorScaleRule(start_typenum,start_value0,start_colorF8696B,mid_typenum,mid_value500,mid_colorFFEB84,end_typenum,end_value1000,end_color63BE7B))# 一个色阶规则覆盖0-1000所有值比10个区间规则高效