Python 操作 Excel 条件格式指南

周一早上九点你的邮箱被各种报表塞满。打开财务发来的季度销售数据几千行数字挤在屏幕上眼睛扫过去一片黑压压。老板在旁边等着汇报问你这个季度哪个产品卖得最好、哪些区域掉得厉害。你拿着鼠标划来划去半天找不出个所以然。这场景但凡用Excel干过活的人都懂——数据有了但看不见。条件格式就是干这个用的。它能让数字“开口说话”高于平均值的自动标绿低于警戒线的自动飘红重复值、排名、异常点一眼扫过去清清楚楚。但问题来了每月、每周甚至每天都要手动重复这些操作时间全耗在“格式化”上了。Python能帮你解决这个麻烦。用代码操作Excel条件格式本质上就是把你手动点鼠标的步骤写成脚本。数据更新了脚本跑一遍格式自动生成。市面上主流的几个库——openpyxl、xlsxwriter、Spire.XLS——各有各的脾气选对了工具事半功倍。一、openpyxl最接地气的全能选手openpyxl是处理Excel最常用的库读写都支持对条件格式的支持也比较完善。它的设计思路很符合直觉先定规则再上格式。比如你想把成绩表里不及格小于60分的单元格标红代码可以这么写from openpyxl import load_workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import CellIsRule wb load_workbook(成绩表.xlsx) ws wb.active # 定义红色填充 red_fill PatternFill(start_colorFF0000, end_colorFF0000, fill_typesolid) # 添加条件格式规则单元格值小于60 ws.conditional_formatting.add(B2:B100, CellIsRule(operatorlessThan, formula[60], fillred_fill)) wb.save(成绩表_格式化.xlsx)这段代码干的事和你手动点“条件格式突出显示单元格规则小于”一模一样区别在于——下次再来100份报表它也能一秒干完。openpyxl支持的规则类型不少CellIsRule基于值、FormulaRule基于公式、ColorScaleRule色阶、IconSetRule图标集、DataBarRule数据条都有。想做复杂点的逻辑比如“既要大于平均线又要属于前10%”用公式规则就能搞定。有个坑得提醒你openpyxl在读取带条件格式的现有文件时规则信息可能会丢失。所以用它创建格式没问题但别指望完美读取别人设好的规则。二、xlsxwriter写报表的一把好手如果你只需要“生成”报表不需要修改已有文件xlsxwriter是更轻量级的选择。它专注于写入功能纯粹性能也不错。xlsxwriter的语法有点不一样用的是字典传参import xlsxwriter workbook xlsxwriter.Workbook(销售报表.xlsx) worksheet workbook.add_worksheet() # 准备数据 data [320, 450, 280, 490, 350, 420, 380] worksheet.write_column(A1, data) # 定义两种格式 green_format workbook.add_format({bg_color: green}) red_format workbook.add_format({bg_color: red}) # 设置条件格式大于400标绿小于等于400标红 worksheet.conditional_format(A1:A7, {type: cell, criteria: , value: 400, format: green_format}) worksheet.conditional_format(A1:A7, {type: cell, criteria: , value: 400, format: red_format}) workbook.close()xlsxwriter最实用的功能之一是配合pandas用。你拿pandas做完数据处理直接用pd.ExcelWriter指定xlsxwriter引擎然后调用条件格式方法数据分析报表生成一条龙。import pandas as pd df pd.DataFrame({销售额: [320, 450, 280, 490, 350]}) writer pd.ExcelWriter(报表.xlsx, enginexlsxwriter) df.to_excel(writer, sheet_name销售数据, indexFalse) workbook writer.book worksheet writer.sheets[销售数据] # 对B列销售额应用三色阶 worksheet.conditional_format(B2:B6, {type: 3_color_scale}) writer.close()xlsxwriter不支持读取已有文件也不能修改。但如果你是从零开始建报表它够快、够稳、够干净。三、Spire.XLS商业级功能的代表Spire.XLS是个商业库功能覆盖面比前两个更广。它的API设计更接近Excel本身的逻辑支持的操作也更多——比如设置公式条件、处理跨工作表引用、精细化控制格式选项。有些场景下用前两个库实现起来比较费劲比如“隔行变色”这种需求。用Spire.XLS可以写得很直白from spire.xls import * from spire.xls.common import * workbook Workbook() workbook.LoadFromFile(数据.xlsx) sheet workbook.Worksheets[0] # 添加条件格式 conditionalFormat sheet.ConditionalFormats.Add() conditionalFormat.AddRange(sheet.Range[2, 1, sheet.LastRow, sheet.LastColumn]) # 偶数行设白色背景 condition1 conditionalFormat.AddCondition() condition1.FirstFormula MOD(ROW(),2)0 condition1.FormatType ConditionalFormatType.Formula condition1.BackColor Color.get_White() # 奇数行设浅灰背景 condition2 conditionalFormat.AddCondition() condition2.FirstFormula MOD(ROW(),2)1 condition2.FormatType ConditionalFormatType.Formula condition2.BackColor Color.get_LightGray() workbook.SaveToFile(隔行变色.xlsx, ExcelVersion.Version2016) workbook.Dispose()Spire.XLS还支持一些“开箱即用”的高级功能Top/Bottom规则、高于/低于平均值、介于两个数之间。这些在Excel里是内置选项用openpyxl得绕个弯用Spire.XLS直接调方法就行。不过它是收费库有30天试用期。项目预算够、追求开发效率的情况下可以考虑。四、怎么选看你的场景三个库没有绝对的好坏关键看你想干什么。openpyxl最适合“既要读又要写”的场景。你需要修改现有文件、保留原有内容、同时添加新格式用它最稳妥。社区活跃遇到问题好搜答案。xlsxwriter适合“纯生成”的场景。跑数据分析脚本自动输出带格式的报表发给老板或客户。性能好文件体积控制得也不错。配合pandas用体验很顺。Spire.XLS适合复杂格式需求的场景。要做多条件嵌套、大量使用公式规则、或者嫌自己造轮子太麻烦。商业环境、预算允许的情况下能省不少开发时间。五、几个实战技巧公式规则里引用要写对。在条件格式里用公式注意相对引用和绝对引用。A1100是针对每个单元格判断A$1100就变成全跟第一行比。规则有顺序。Excel按规则添加顺序执行后面的规则可能覆盖前面的。代码里添加规则的顺序就是最终生效的顺序。别贪多。一个工作表里规则太多文件打开会卡。能用一两条规则解决的别绕复杂逻辑。先清空再添加。如果反复运行脚本记得先删掉旧规则再添新的不然会叠加上去结果乱套。回到开头那个周一早上的场景。用Python写完条件格式脚本之后你只需要把新季度数据拖进文件夹双击运行。再打开Excel时销售冠军已经标成绿色下滑区域标成橙色异常值标成红色。老板指着屏幕问“这个月怎么回事”你三秒就能找到问题出在哪。那多出来的半小时不用再对着黑压压的数字发呆。