Conditional Formatting 条件格式
ColorScale 色阶
语法
ColorScaleRule(start_type=None, start_value=None, start_color=None, mid_type=None, mid_value=None, mid_color=None, end_type=None, end_value=None, end_color=None)
type的值有
- num 数字
- percent 百分比
- max 最大
- min 最小
- formula 公式
- percentile 百分点数值
色阶有双色色阶以及三色色阶，如果是双色的话就没有mid_type等参数
双色色阶
三色色阶
实例
import openpyxl
from openpyxl.formatting.rule import ColorScaleRule
wb = openpyxl.load_workbook('/Users/junliangchen/Desktop/data.xlsx')
ws = wb.active
rule = ColorScaleRule(start_type='percent',start_value=30,start_color='FC556C', # 红
mid_type='percent',mid_value=60,mid_color='E5E500', # 黄
end_type='percent',end_value=90,end_color='1BA135') # 绿
ws.conditional_formatting.add('A2:A9',rule) # 将设置好的规则添加到工作表的条件格式中
wb.save('/Users/junliangchen/Desktop/data.xlsx')
IconSet 图标集
语法
IconSetRule(icon_style=None, type=None, values=None, showValue=None, percent=None, reverse=None)
参数详解
- icon_style：图标的样式
'3Arrows', '3ArrowsGray', '3Flags', '3TrafficLights1',
'3TrafficLights2', '3Signs', '3Symbols', '3Symbols2',
'4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating',
'4TrafficLights', '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'
- type同上
- showValue：bool值，是否显示值
- reverse：bool值，
图片说明
实例
import openpyxl
from openpyxl.formatting.rule import IconSetRule
from openpyxl.formatting.formatting import ConditionalFormattingList
wb = openpyxl.load_workbook('/Users/junliangchen/Desktop/data.xlsx')
ws = wb.active
ws.conditional_formatting = ConditionalFormattingList() # 清除工作表中的条件格式
rule = IconSetRule(icon_style='3Arrows',values=[0,60,80],type='percent',showValue=True)
ws.conditional_formatting.add('A2:A9',rule) # 将设置好的规则添加到工作表的条件格式中
wb.save('/Users/junliangchen/Desktop/data.xlsx')
DataBar 数据条
语法
DataBarRule(start_type=None, start_value=None, end_type=None, end_value=None, color=None, showValue=None, minLength=None, maxLength=None)
这边数据条可以设置比较少，没有Excel软件里的功能丰富，设置一下type，value，color就可以了，type 还是和上面的一样。
FormulaRule
语法
当满足公式条件时，设置样式（字体，边框，填充色）
FormulaRule(formula=None, stopIfTrue=None, font=None, border=None, fill=None)
import openpyxl
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill
from openpyxl.formatting.formatting import ConditionalFormattingList
wb = openpyxl.load_workbook(r'/Users/junliangchen/Desktop/data.xlsx')
ws = wb.active
ws.conditional_formatting = ConditionalFormattingList() # 把原本的条件格式列表清空
r = PatternFill(start_color='FA2B44', # 红色
end_color='FA2B44',
fill_type='solid')
# 下面公式的意思是，A列里名称包含A，且C列的值大于40的单元格设置条件格式，当满足公式时，设置红色的填充色
rule1 = FormulaRule(formula=['=AND(IFERROR(FIND("A",A2),FALSE)=1,C2>40)'],fill=r)
ws.conditional_formatting.add('C2:C7',rule1)
wb.save(r'/Users/junliangchen/Desktop/data.xlsx')
CellIsRule
语法
CellIsRule(operator=None, formula=None, stopIfTrue=None, font=None, border=None, fill=None)
参数
- operator
">": "greaterThan",
">=": "greaterThanOrEqual",
"<": "lessThan",
"<=": "lessThanOrEqual",
"=": "equal",
"==": "equal",
"!=": "notEqual"
'between','notBetween'
单元格规则能设置就是这些
CellIsRule
实例
数字在5-15之间，都设置成红色填充
import openpyxl
from openpyxl.formatting.rule import CellIsRule
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.styles import PatternFill
wb = openpyxl.load_workbook('/Users/junliangchen/Desktop/data.xlsx')
ws = wb.active
r = PatternFill(start_color='FA2B44', # 红色
end_color='FA2B44',
fill_type='solid')
ws.conditional_formatting = ConditionalFormattingList() # 清除工作表中的条件格式
rule = CellIsRule(operator='between',formula=[5,15],fill=r)
ws.conditional_formatting.add('A1:A9',rule) # 将设置好的规则添加到工作表的条件格式中
wb.save('/Users/junliangchen/Desktop/data.xlsx')
Rule
除了上面这些规则可以设置，还有以下这些。
p1
p2
p3
p4
p5
语法
Rule(type, dxfId=int, priority=int, stopIfTrue=bool, aboveAverage=bool, percent=bool, bottom=bool, operator=, text=str, timePeriod=, rank=int, stdDev=int, equalAverage=bool, formula=(), colorScale=None, dataBar=None, iconSet=None, dxf=None)
关键参数
type
选择规则的类型
'expression', 'cellIs', 'colorScale', 'dataBar',
'iconSet', 'top10', 'uniqueValues', 'duplicateValues', 'containsText',
'notContainsText', 'beginsWith', 'endsWith', 'containsBlanks',
'notContainsBlanks', 'containsErrors', 'notContainsErrors', 'timePeriod', 'aboveAverage'
operator
'lessThan', 'lessThanOrEqual', 'equal', 'notEqual', 'greaterThanOrEqual',
'greaterThan', 'between', 'notBetween', 'containsText', 'notContains',
'beginsWith', 'endsWith'
timePeriod时间点
当你设置的规则是根据“发生日期”，就会用到以下参数。
'today', 'yesterday', 'tomorrow', 'last7Days',
'thisMonth', 'lastMonth', 'nextMonth', 'thisWeek', 'lastWeek',
'nextWeek'
好多参数对照上面图片，或者打开Excel软件看看，都能明白它的意思。就不细说了。
注意：这里面参数并没有设置fill,border,font的，需要通过dxf设置样式
需要导入下面模块
from openpyxl.styles.differential import DifferentialStyle
实例
给单元格区域中，文本等于price的设置红色填充
import openpyxl
from openpyxl.formatting.rule import Rule
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.styles import PatternFill,Font
from openpyxl.styles.differential import DifferentialStyle
wb = openpyxl.load_workbook('/Users/junliangchen/Desktop/data.xlsx')
ws = wb.active
ws.conditional_formatting = ConditionalFormattingList() # 清除工作表中的条件格式
r = PatternFill(start_color='FA2B44', # 红色
end_color='FA2B44',
fill_type='solid')
my_sytle = DifferentialStyle(font=Font(name='宋体',size=12,bold=True),fill=r) # 自定义的样式
rule = Rule(type='containsText',text='price',dxf=my_sytle,operator='containsText',formula=['NOT(ISERROR(SEARCH("price",A1)))'])
ws.conditional_formatting.add('A1:A9',rule) # 将设置好的规则添加到工作表的条件格式中
wb.save('/Users/junliangchen/Desktop/data.xlsx')
问题
我一开始是这样子写的，当单元格text=price时设置红色填充色，但是这样子写没有效果，然后我看官方文档是多了一个formula参数。
rule = Rule(type='containsText',text='price',dxf=my_sytle,operator='containsText')
官方的教程
这就很不合理了，明明说好用A方案来解决，你却用了B方案，明明要求是设置当等于特定文本时设置样式，你却用满足某个公式时设置样式。那如果要满足公式设置样式的话，还不如像下面这样写。
rule = Rule(type='expression',formula=['NOT(ISERROR(SEARCH("price",A1)))'],dxf=my_sytle)
清除条件格式
上面列子有提到
from openpyxl.formatting.formatting import ConditionalFormattingList
worksheet.conditional_formatting = ConditionalFormattingList() # 清除工作表中的条件格式
