Python代码操作Excel条件格式的实战指南_Python

周一早上九点，你的邮箱被各种报表塞满。

打开财务发来的季度销售数据，几千行数字挤在屏幕上，眼睛扫过去一片黑压压。老板在旁边等着汇报，问你这个季度哪个产品卖得最好、哪些区域掉得厉害。你拿着鼠标划来划去，半天找不出个所以然。

这场景，但凡用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_color='ff0000', end_color='ff0000', fill_type='solid')

# 添加条件格式规则：单元格值小于60
ws.conditional_formatting.add('b2:b100', 
                              cellisrule(operator='lessthan', 
                                        formula=['60'], 
                                        fill=red_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', engine='xlsxwriter')
df.to_excel(writer, sheet_name='销售数据', index=false)

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天试用期。项目预算够、追求开发效率的情况下可以考虑。