上个月帮同事处理一张成绩表时他正按住鼠标左键从第一行往下拖手动给 70 到 80 分的人上色。表有两千多行旁边还开着筛选。我说这种标记应该交给 Excel 条件格式他第一反应是条件格式我会啊但每个月都要从系统重新导出我总不能每张表都打开 Excel 再设一遍吧。这个场景恰好说明了 Python 操作 Excel 最容易被忽视的价值你不需要在 Excel 里点鼠标而是可以用脚本生成一个“自带判断规则”的 xlsx 文件。规则写进去之后数据是变的颜色也跟着变真正被自动化的不是涂色这个动作而是“哪些行该特殊对待”的决策。这篇文章打算从交替行颜色、最大值最小值、范围值三个最常见需求讲起。表面上是讲 openpyxl 的条件格式 API实际上更想帮你建立一条判断链路先想清楚规则作用在哪个范围再决定用单元格规则还是公式规则最后才写代码。1. 先搞清楚条件格式和“直接填色”的区别很多人第一次用 Python 给 Excel 添加样式时会写出类似这样的代码ws[A2].fill PatternFill(start_colorF2F2F2, end_colorF2F2F2, fill_typesolid)这种写法当然有效但它是在给单元格写入一个静态填充色。问题在于如果这张表下个月又多出 20 行或者某一个数据被更新静态颜色不会跟着变化。你可能得重新跑脚本或者再人工去补颜色。1.1 工具选择这次用 openpyxlPython 读写 xlsx 的主流方案里openpyxl 是对条件格式支持最直观的一个。它的 API 能同时覆盖FormulaRule、CellIsRule、ColorScaleRule这几类规则也支持读取已有 xlsx 后追加规则。安装很简单python -m pip install openpyxl要明确一个前提openpyxl 处理的是.xlsx不是旧版的.xls。如果你手里还是老格式通常要先转成 xlsx或者直接用其他兼容工具处理。在动手之前可以先建立一个心智模型Excel 条件格式不是写在单元格里的“已完成样式”。它更像是写在工作表规则层的一组判断逻辑。打开文件时Excel 会根据当前数据重新计算这些规则。openpyxl 只是负责把规则写进文件并不负责在 Python 里替你计算结果。所以如果脚本保存后你用 pandas 再读一次读到的仍然是原始数据而不是带颜色的数据。颜色是否出现取决于最后用 Excel、WPS 或 LibreOffice 打开文件时程序是否支持并重新计算条件格式。1.2 条件格式真正值得关注的地方从工作流角度看条件格式的最小组件并不是“颜色”而是“规则”。比如同样是标记 70 到 80 分静态填色只对当前这 2000 行有效。条件格式数据修改后低于 70 或高于 80 的格子会自动取消高亮新进入区间的格子会自动被涂色。这个过程不需要重新执行 Python。条件格式的价值是把一次性的人工判断固化成了可复用的规则。尤其适合系统定期导出、报表每月重跑、数据会变化但判断逻辑不变的场景。2. 交替行颜色看起来简单但它其实是条件格式的“相对引用”课交替行颜色是很多人接触 Excel 条件格式时第一个想到的需求。单独看门槛不高但如果在 Python 里理解不到位最容易遇到“规则没效果”或“颜色出现在奇怪的位置”。2.1 最小可运行代码先创建一个最简单的表from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import FormulaRule wb Workbook() ws wb.active ws.title 成绩表 headers [姓名, 语文, 数学] rows [ [张一, 66, 72], [陈二, 82, 64], [李三, 78, 88], [王四, 55, 73], ] ws.append(headers) for row in rows: ws.append(row) gray_fill PatternFill( start_colorF2F2F2, end_colorF2F2F2, fill_typesolid ) # 高亮偶数行 ws.conditional_formatting.add( A2:C5, FormulaRule(formula[MOD(ROW(),2)0], fillgray_fill, stopIfTrueTrue) ) wb.save(交替行.xlsx)这里的关键点是formula[MOD(ROW(),2)0]。在 openpyxl 的FormulaRule中公式一般不带开头的“”因为 Excel 规则里会自动补全。加了也不一定会报错但为了统一和兼容我会坚持不写等号。2.2 公式为什么这样写以及常见误区ROW()返回当前单元格所在的行号。MOD(ROW(),2)0的含义是行号除以 2 余数是 0也就是偶数行。条件格式应用范围是A2:C5Excel 在计算时会把这个公式作用在整个区域内的每一个单元格上。正是因为公式里的ROW()不带绝对定位它才会随着行变化而变化。这里很容易犯的第一个错误是试图用 openpyxl 把一个静态颜色写满 A2 到 C5然后再“祈祷”以后插入行时颜色会扩展。条件格式不是这么用的。你要定义的是规则而不是结果。第二个常见误区是把应用范围写成A1:C5。如果这张表第一行是标题那么标题行也会参与公式计算。比如MOD(ROW(),2)1会高亮标题行。通常表格要有标题所以我会把范围从第二行开始也就是从实际数据的第一行开始。第三个需要点破的问题是交替行颜色并不一定非要用公式实现。Excel 的“套用表格样式”也能做到斑马纹而且会在新增行时自动扩展。那为什么还要用 Python 条件格式因为在批量报表生成流程里你往往不希望 Excel 会弹出一个“表格”对象更不希望用户不小心把表格式样搞乱。条件格式是纯规则不改变表结构用户仍然可以随意编辑数据。如果你要生成的是一个干净的普通数据区域同时又要保留交替行颜色条件格式就更合适。3. 最大最小值先想清楚你要的是“整列极值”还是“分组极值”最大最小值在需求描述里最容易出现歧义。有人要的是把某列中最大的那个单元格标黄最小的那个单元格标绿。有人要的其实是每个学生的三科成绩中标出他自己最高的一科。还有人要用红绿渐变直接体现整列数据的相对大小。这三种需求在 openpyxl 里对应三种不同写法。动手前先问清楚场景。3.1 高亮整列的总分最大值和最小值假设成绩表要加一列总分from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import FormulaRule wb Workbook() ws wb.active ws.title 成绩 rows [ [张一, 66, 72, 58], [陈二, 82, 64, 91], [李三, 78, 88, 66], [王四, 55, 73, 49], [赵五, 91, 68, 83], [孙六, 73, 62, 77], [周七, 47, 55, 69], [吴八, 86, 90, 61], ] ws.append([姓名, 语文, 数学, 英语, 总分]) for name, chinese, math, english in rows: ws.append([name, chinese, math, english, chinese math english]) last_row ws.max_row yellow_fill PatternFill(start_colorFFEB9C, end_colorFFEB9C, fill_typesolid) green_fill PatternFill(start_colorC6EFCE, end_colorC6EFCE, fill_typesolid) # 总分最高分标黄 ws.conditional_formatting.add( fE2:E{last_row}, FormulaRule(formula[fE2MAX($E$2:$E${last_row})], fillyellow_fill, stopIfTrueTrue) ) # 总分最低分标绿 ws.conditional_formatting.add( fE2:E{last_row}, FormulaRule(formula[fE2MIN($E$2:$E${last_row})], fillgreen_fill, stopIfTrueTrue) ) wb.save(最大最小值.xlsx)这个写法的关键理解点是range 的首个单元格是E2所以公式里用相对引用E2表示“当前单元格”。而$E$2:$E$16使用绝对引用表示整个比较范围不会变。如果你在 Excel 里手动创建条件格式选中区域后输入公式时Excel 会以“活动单元格”为基准调整相对引用。openpyxl 里的规则也遵循类似的基准所以你要保证公式里的相对引用能正确匹配应用范围的首个单元格。3.2 要显示分布而不是只标一个极值用 ColorScaleRule如果数据量很小只标最大最小值没问题。但当成绩达到几百行时你真正想看到的可能是整个分布的疏密而不是单独揪出某个格子。这时应该用颜色刻度from openpyxl.formatting.rule import ColorScaleRule ws.conditional_formatting.add( E2:E16, ColorScaleRule( start_typemin, start_colorFFFFFF, end_typemax, end_color1E90FF ) )ColorScaleRule会按取值范围把单元格分成一个渐变阶梯最小值偏向白色最大值偏向蓝色中间值按比例插值。它不用公式因为 Excel 天然支持这种规则。如果你的需求里提到“最大最小值”先确认一下用户是想精确找到唯一最大和唯一最小还是想看到整体高低趋势。前者用公式后者用色阶。两者解决的问题不同。3.3 每行一个极值公式要用行内相对引用还有一类场景某一行有语文、数学、英语三科要标出这个学生成绩最高的一科。这时可以把应用范围放在B2:D16ws.conditional_formatting.add( B2:D16, FormulaRule( formula[B2MAX($B2:$D2)], fillyellow_fill, stopIfTrueTrue ) )公式B2MAX($B2:$D2)的意思很直白当前格子等于这一行 B 到 D 的最大值时就标黄。它之所以能在每一行生效是因为行号 2 是相对引用。Excel 在每一行计算时会把行号改成所在行的行号。这里容易出错的地方是把范围写成$B$2:$D$2或B2:MAX($B$2:$D$2)前者会把所有行的比较范围都锁定在第一行后者比较范围会错位。判断建议在给数据添加极值类规则前先确认“极值”是按列全局比还是按行分组比。不要把两种公式混用。4. 范围值高亮能用 between 就别写成四条规则第三个需求是“范围值”。比如把 70 到 80 分之间的成绩标成橙色。这在 Excel 里最自然的规则是“介于”在 openpyxl 里对应CellIsRule。4.1 用 CellIsRule 实现区间高亮from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import CellIsRule wb Workbook() ws wb.active rows [ [张一, 66, 72, 58], [陈二, 82, 64, 91], [李三, 78, 88, 66], [王四, 55, 73, 49], ] ws.append([姓名, 语文, 数学, 英语]) for row in rows: ws.append(row) orange_fill PatternFill( start_colorFFD9B3, end_colorFFD9B3, fill_typesolid ) # 把语文成绩在 70 到 80 之间的格子标出来 ws.conditional_formatting.add( B2:B5, CellIsRule( operatorbetween, formula[70, 80], fillorange_fill, stopIfTrueTrue ) ) wb.save(范围值.xlsx)这里的公式列表很关键。between对应的formula必须按顺序写两个值第一个是下限第二个是上限。Excel 的“介于”默认包含边界也就是说 70 和 80 本身也会被高亮。有人会说我也可以用两个规则来实现一个写大于等于 70另一个写小于等于 80。理论上也可以但这种写法在 Excel 规则管理器里会比较啰嗦而且当两条规则发生冲突时调试优先级会很麻烦。能用between解决的问题就不要拆成多个规则。4.2 openpyxl 的 CellIsRule 支持哪些操作符写区间规则前先确认你需要的到底是开区间还是闭区间。下面是常见操作符操作符含义formula 示例equal等于[70]notEqual不等于[70]greaterThan大于[70]lessThan小于[80]greaterThanOrEqual大于等于[70]lessThanOrEqual小于等于[80]between介于包含边界[70, 80]notBetween不介于包含边界[70, 80]如果你的业务需求是“70 到 80 之间但不含 70 和 80”那就不能用between一次写完了。比较稳妥的方式是使用FormulaRulefrom openpyxl.formatting.rule import FormulaRule ws.conditional_formatting.add( B2:B5, FormulaRule( formula[AND(B270, B280)], fillorange_fill ) )这就是一个典型取舍between写法简单但边界是包含关系AND公式可以精确控制开区间和闭区间但代码看起来更复杂。不是每个规则都要追求最简化而是要匹配业务语义。5.
openpyxl实战：Python给Excel添加条件格式的三种高亮场景
上个月帮同事处理一张成绩表时他正按住鼠标左键从第一行往下拖手动给 70 到 80 分的人上色。表有两千多行旁边还开着筛选。我说这种标记应该交给 Excel 条件格式他第一反应是条件格式我会啊但每个月都要从系统重新导出我总不能每张表都打开 Excel 再设一遍吧。这个场景恰好说明了 Python 操作 Excel 最容易被忽视的价值你不需要在 Excel 里点鼠标而是可以用脚本生成一个“自带判断规则”的 xlsx 文件。规则写进去之后数据是变的颜色也跟着变真正被自动化的不是涂色这个动作而是“哪些行该特殊对待”的决策。这篇文章打算从交替行颜色、最大值最小值、范围值三个最常见需求讲起。表面上是讲 openpyxl 的条件格式 API实际上更想帮你建立一条判断链路先想清楚规则作用在哪个范围再决定用单元格规则还是公式规则最后才写代码。1. 先搞清楚条件格式和“直接填色”的区别很多人第一次用 Python 给 Excel 添加样式时会写出类似这样的代码ws[A2].fill PatternFill(start_colorF2F2F2, end_colorF2F2F2, fill_typesolid)这种写法当然有效但它是在给单元格写入一个静态填充色。问题在于如果这张表下个月又多出 20 行或者某一个数据被更新静态颜色不会跟着变化。你可能得重新跑脚本或者再人工去补颜色。1.1 工具选择这次用 openpyxlPython 读写 xlsx 的主流方案里openpyxl 是对条件格式支持最直观的一个。它的 API 能同时覆盖FormulaRule、CellIsRule、ColorScaleRule这几类规则也支持读取已有 xlsx 后追加规则。安装很简单python -m pip install openpyxl要明确一个前提openpyxl 处理的是.xlsx不是旧版的.xls。如果你手里还是老格式通常要先转成 xlsx或者直接用其他兼容工具处理。在动手之前可以先建立一个心智模型Excel 条件格式不是写在单元格里的“已完成样式”。它更像是写在工作表规则层的一组判断逻辑。打开文件时Excel 会根据当前数据重新计算这些规则。openpyxl 只是负责把规则写进文件并不负责在 Python 里替你计算结果。所以如果脚本保存后你用 pandas 再读一次读到的仍然是原始数据而不是带颜色的数据。颜色是否出现取决于最后用 Excel、WPS 或 LibreOffice 打开文件时程序是否支持并重新计算条件格式。1.2 条件格式真正值得关注的地方从工作流角度看条件格式的最小组件并不是“颜色”而是“规则”。比如同样是标记 70 到 80 分静态填色只对当前这 2000 行有效。条件格式数据修改后低于 70 或高于 80 的格子会自动取消高亮新进入区间的格子会自动被涂色。这个过程不需要重新执行 Python。条件格式的价值是把一次性的人工判断固化成了可复用的规则。尤其适合系统定期导出、报表每月重跑、数据会变化但判断逻辑不变的场景。2. 交替行颜色看起来简单但它其实是条件格式的“相对引用”课交替行颜色是很多人接触 Excel 条件格式时第一个想到的需求。单独看门槛不高但如果在 Python 里理解不到位最容易遇到“规则没效果”或“颜色出现在奇怪的位置”。2.1 最小可运行代码先创建一个最简单的表from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import FormulaRule wb Workbook() ws wb.active ws.title 成绩表 headers [姓名, 语文, 数学] rows [ [张一, 66, 72], [陈二, 82, 64], [李三, 78, 88], [王四, 55, 73], ] ws.append(headers) for row in rows: ws.append(row) gray_fill PatternFill( start_colorF2F2F2, end_colorF2F2F2, fill_typesolid ) # 高亮偶数行 ws.conditional_formatting.add( A2:C5, FormulaRule(formula[MOD(ROW(),2)0], fillgray_fill, stopIfTrueTrue) ) wb.save(交替行.xlsx)这里的关键点是formula[MOD(ROW(),2)0]。在 openpyxl 的FormulaRule中公式一般不带开头的“”因为 Excel 规则里会自动补全。加了也不一定会报错但为了统一和兼容我会坚持不写等号。2.2 公式为什么这样写以及常见误区ROW()返回当前单元格所在的行号。MOD(ROW(),2)0的含义是行号除以 2 余数是 0也就是偶数行。条件格式应用范围是A2:C5Excel 在计算时会把这个公式作用在整个区域内的每一个单元格上。正是因为公式里的ROW()不带绝对定位它才会随着行变化而变化。这里很容易犯的第一个错误是试图用 openpyxl 把一个静态颜色写满 A2 到 C5然后再“祈祷”以后插入行时颜色会扩展。条件格式不是这么用的。你要定义的是规则而不是结果。第二个常见误区是把应用范围写成A1:C5。如果这张表第一行是标题那么标题行也会参与公式计算。比如MOD(ROW(),2)1会高亮标题行。通常表格要有标题所以我会把范围从第二行开始也就是从实际数据的第一行开始。第三个需要点破的问题是交替行颜色并不一定非要用公式实现。Excel 的“套用表格样式”也能做到斑马纹而且会在新增行时自动扩展。那为什么还要用 Python 条件格式因为在批量报表生成流程里你往往不希望 Excel 会弹出一个“表格”对象更不希望用户不小心把表格式样搞乱。条件格式是纯规则不改变表结构用户仍然可以随意编辑数据。如果你要生成的是一个干净的普通数据区域同时又要保留交替行颜色条件格式就更合适。3. 最大最小值先想清楚你要的是“整列极值”还是“分组极值”最大最小值在需求描述里最容易出现歧义。有人要的是把某列中最大的那个单元格标黄最小的那个单元格标绿。有人要的其实是每个学生的三科成绩中标出他自己最高的一科。还有人要用红绿渐变直接体现整列数据的相对大小。这三种需求在 openpyxl 里对应三种不同写法。动手前先问清楚场景。3.1 高亮整列的总分最大值和最小值假设成绩表要加一列总分from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import FormulaRule wb Workbook() ws wb.active ws.title 成绩 rows [ [张一, 66, 72, 58], [陈二, 82, 64, 91], [李三, 78, 88, 66], [王四, 55, 73, 49], [赵五, 91, 68, 83], [孙六, 73, 62, 77], [周七, 47, 55, 69], [吴八, 86, 90, 61], ] ws.append([姓名, 语文, 数学, 英语, 总分]) for name, chinese, math, english in rows: ws.append([name, chinese, math, english, chinese math english]) last_row ws.max_row yellow_fill PatternFill(start_colorFFEB9C, end_colorFFEB9C, fill_typesolid) green_fill PatternFill(start_colorC6EFCE, end_colorC6EFCE, fill_typesolid) # 总分最高分标黄 ws.conditional_formatting.add( fE2:E{last_row}, FormulaRule(formula[fE2MAX($E$2:$E${last_row})], fillyellow_fill, stopIfTrueTrue) ) # 总分最低分标绿 ws.conditional_formatting.add( fE2:E{last_row}, FormulaRule(formula[fE2MIN($E$2:$E${last_row})], fillgreen_fill, stopIfTrueTrue) ) wb.save(最大最小值.xlsx)这个写法的关键理解点是range 的首个单元格是E2所以公式里用相对引用E2表示“当前单元格”。而$E$2:$E$16使用绝对引用表示整个比较范围不会变。如果你在 Excel 里手动创建条件格式选中区域后输入公式时Excel 会以“活动单元格”为基准调整相对引用。openpyxl 里的规则也遵循类似的基准所以你要保证公式里的相对引用能正确匹配应用范围的首个单元格。3.2 要显示分布而不是只标一个极值用 ColorScaleRule如果数据量很小只标最大最小值没问题。但当成绩达到几百行时你真正想看到的可能是整个分布的疏密而不是单独揪出某个格子。这时应该用颜色刻度from openpyxl.formatting.rule import ColorScaleRule ws.conditional_formatting.add( E2:E16, ColorScaleRule( start_typemin, start_colorFFFFFF, end_typemax, end_color1E90FF ) )ColorScaleRule会按取值范围把单元格分成一个渐变阶梯最小值偏向白色最大值偏向蓝色中间值按比例插值。它不用公式因为 Excel 天然支持这种规则。如果你的需求里提到“最大最小值”先确认一下用户是想精确找到唯一最大和唯一最小还是想看到整体高低趋势。前者用公式后者用色阶。两者解决的问题不同。3.3 每行一个极值公式要用行内相对引用还有一类场景某一行有语文、数学、英语三科要标出这个学生成绩最高的一科。这时可以把应用范围放在B2:D16ws.conditional_formatting.add( B2:D16, FormulaRule( formula[B2MAX($B2:$D2)], fillyellow_fill, stopIfTrueTrue ) )公式B2MAX($B2:$D2)的意思很直白当前格子等于这一行 B 到 D 的最大值时就标黄。它之所以能在每一行生效是因为行号 2 是相对引用。Excel 在每一行计算时会把行号改成所在行的行号。这里容易出错的地方是把范围写成$B$2:$D$2或B2:MAX($B$2:$D$2)前者会把所有行的比较范围都锁定在第一行后者比较范围会错位。判断建议在给数据添加极值类规则前先确认“极值”是按列全局比还是按行分组比。不要把两种公式混用。4. 范围值高亮能用 between 就别写成四条规则第三个需求是“范围值”。比如把 70 到 80 分之间的成绩标成橙色。这在 Excel 里最自然的规则是“介于”在 openpyxl 里对应CellIsRule。4.1 用 CellIsRule 实现区间高亮from openpyxl import Workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import CellIsRule wb Workbook() ws wb.active rows [ [张一, 66, 72, 58], [陈二, 82, 64, 91], [李三, 78, 88, 66], [王四, 55, 73, 49], ] ws.append([姓名, 语文, 数学, 英语]) for row in rows: ws.append(row) orange_fill PatternFill( start_colorFFD9B3, end_colorFFD9B3, fill_typesolid ) # 把语文成绩在 70 到 80 之间的格子标出来 ws.conditional_formatting.add( B2:B5, CellIsRule( operatorbetween, formula[70, 80], fillorange_fill, stopIfTrueTrue ) ) wb.save(范围值.xlsx)这里的公式列表很关键。between对应的formula必须按顺序写两个值第一个是下限第二个是上限。Excel 的“介于”默认包含边界也就是说 70 和 80 本身也会被高亮。有人会说我也可以用两个规则来实现一个写大于等于 70另一个写小于等于 80。理论上也可以但这种写法在 Excel 规则管理器里会比较啰嗦而且当两条规则发生冲突时调试优先级会很麻烦。能用between解决的问题就不要拆成多个规则。4.2 openpyxl 的 CellIsRule 支持哪些操作符写区间规则前先确认你需要的到底是开区间还是闭区间。下面是常见操作符操作符含义formula 示例equal等于[70]notEqual不等于[70]greaterThan大于[70]lessThan小于[80]greaterThanOrEqual大于等于[70]lessThanOrEqual小于等于[80]between介于包含边界[70, 80]notBetween不介于包含边界[70, 80]如果你的业务需求是“70 到 80 之间但不含 70 和 80”那就不能用between一次写完了。比较稳妥的方式是使用FormulaRulefrom openpyxl.formatting.rule import FormulaRule ws.conditional_formatting.add( B2:B5, FormulaRule( formula[AND(B270, B280)], fillorange_fill ) )这就是一个典型取舍between写法简单但边界是包含关系AND公式可以精确控制开区间和闭区间但代码看起来更复杂。不是每个规则都要追求最简化而是要匹配业务语义。5.
 自定义缓动函数实现弹跳效果)
