影刀RPA Excel条件格式与样式：自动标红超标数据

影刀RPA Excel条件格式与样式设置自动标红超标数据报表不只是数据的堆砌——能把超标数据自动标红、把关键行加粗、给表头加背景色这样的报表才有可读性。openpyxl提供了完整的样式控制能力。这篇文章从基础的字体颜色到复杂的条件格式给你一套拿来就用的模板。基础样式设置字体、颜色、对齐fromopenpyxlimportWorkbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Side wbWorkbook()wswb.active# 表头样式 header_fontFont(name微软雅黑,size12,boldTrue,colorFFFFFF)header_fillPatternFill(start_color4472C4,end_color4472C4,fill_typesolid)header_alignmentAlignment(horizontalcenter,verticalcenter)# 写入表头并应用样式headers[订单号,客户名,金额,状态]forcol,headerinenumerate(headers,1):cellws.cell(row1,columncol,valueheader)cell.fontheader_font cell.fillheader_fill cell.alignmentheader_alignment# 数据行样式默认 data_fontFont(name微软雅黑,size10)data_alignmentAlignment(horizontalleft,verticalcenter)forrow_dataindata_list:ws.append(row_data)forcellinws[ws.max_row]:cell.fontdata_font cell.alignmentdata_alignment单元格边框thin_borderBorder(leftSide(stylethin,colorD9D9D9),rightSide(stylethin,colorD9D9D9),topSide(stylethin,colorD9D9D9),bottomSide(stylethin,colorD9D9D9),)[video(video-6rjKmJYv-1783501884107)(type-csdn)(url-https://live.csdn.net/v/embed/525010)(image-https://v-blog.csdnimg.cn/asset/f4faa587144cb7070f19e8b36813806b/cover/Cover0.jpg)(title-店群矩阵自动化突破运营极限)]forrowinws.iter_rows():forcellinrow:cell.borderthin_border列宽自适应forcolinws.columns:max_length0col_lettercol[0].column_letterforcellincol:ifcell.value:# 中文字符算2个字符宽度cell_len0forcharinstr(cell.value):cell_len2if\u4e00char\u9fffelse1max_lengthmax(max_length,cell_len)ws.column_dimensions[col_letter].widthmin(max_length2,40)# 最宽不超过40条件格式按数值动态标色超标金额标红fromopenpyxl.formatting.ruleimportCellIsRule# 金额列C列大于10000的标红加粗red_fontFont(colorFF0000,boldTrue)red_fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)ws.conditional_formatting.add(C2:C1000,CellIsRule(operatorgreaterThan,formula[10000],fontred_font,fillred_fill))多条件分层标色三色渐变fromopenpyxl.formatting.ruleimportColorScaleRule# 金额列低(绿) → 中(黄) → 高(红)ws.conditional_formatting.add(C2:C1000,ColorScaleRule(start_typemin,start_color63BE7B,# 最小值 绿色mid_typepercentile,mid_value50,mid_colorFFEB84,# 中间值 黄色end_typemax,end_colorF8696B# 最大值 红色))根据另一列的值决定当前列的样式比如状态列为已取消时整行变灰fromopenpyxl.formatting.ruleimportFormulaRule# 如果D列状态 已取消整行标灰gray_fillPatternFill(start_colorD9D9D9,end_colorD9D9D9,fill_typesolid)gray_fontFont(color808080)ws.conditional_formatting.add(A2:D1000,FormulaRule(formula[$D2已取消],fillgray_fill,fontgray_font))注意$D2的写法$D表示固定D列2会随行号自动变化。实战给销售报表自动标色一个完整的销售报表样式条件格式fromopenpyxlimportWorkbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Sidefromopenpyxl.formatting.ruleimportCellIsRule,FormulaRule wbWorkbook()wswb.active ws.title销售日报# 表头 headers[日期,商品,销量,单价,金额,达标]header_fillPatternFill(start_color2F5496,end_color2F5496,fill_typesolid)header_fontFont(name微软雅黑,size11,boldTrue,colorFFFFFF)forcol,hinenumerate(headers,1):cellws.cell(row1,columncol,valueh)cell.fontheader_font cell.fillheader_fill cell.alignmentAlignment(horizontalcenter,verticalcenter)# 数据假设从影刀变量传入 sales_data[[2026-07-01,商品A,120,100,12000,是],[2026-07-01,商品B,45,200,9000,否],[2026-07-01,商品C,8,500,4000,否],]forrow_datainsales_data:ws.append(row_data)# 条件格式1金额 10000 标红 ws.conditional_formatting.add(E2:E1000,CellIsRule(operatorgreaterThan,formula[10000],fontFont(colorFF0000,boldTrue),fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)))# 条件格式2达标否 整行标黄 ws.conditional_formatting.add(A2:F1000,FormulaRule(formula[$F2否],fillPatternFill(start_colorFFEB9C,end_colorFFEB9C,fill_typesolid)))# 数字格式金额列显示¥和千分位 fromopenpyxl.styles.numbersimportFORMAT_NUMBER_COMMA_SEPARATEDforrowinws.iter_rows(min_row2,min_col5,max_col5):forcellinrow:cell.number_format¥#,##0.00# 边框 thin_borderBorder(leftSide(stylethin,colorB4C6E7),rightSide(stylethin,colorB4C6E7),topSide(stylethin,colorB4C6E7),bottomSide(stylethin,colorB4C6E7),)forrowinws.iter_rows():forcellinrow:cell.borderthin_border# 冻结首行 ws.freeze_panesA2wb.save(rC:\RPA_data\sales_report_styled.xlsx)print(报表生成完成)数字格式temu店群自动化报活动案例openpyxl支持Excel的数字格式码fromopenpyxl.styles.numbersimportBUILTIN_FORMATS cell.number_format¥#,##0.00# ¥1,234.56cell.number_format0.00%# 35.00%cell.number_formatyyyy-mm-dd# 2026-07-01cell.number_formatyyyy-mm-dd hh:mm:ss# 2026-07-01 14:30:00cell.number_format#,##0# 1,234整数千分位cell.number_format0.00# 1234.00固定两位小数cell.number_format000000# 000123固定6位前面补零注意设置number_format只是改变了显示格式不影响实际存储的值。存的是1234.567显示为¥1,234.57读出来还是1234.567。行高设置# 设置表头行高ws.row_dimensions[1].height30# 设置数据行行高forrowinrange(2,ws.max_row1):ws.row_dimensions[row].height22总结openpyxl的样式控制覆盖了Excel格式的绝大部分需求。表头用深色背景白字数据区用条件格式动态标色超标标红、达标标绿数字列设千分位格式边界加细边框冻结首行。这一套下来你的自动化生成的报表比很多人手动做的还好看。作者林焱