条件格式¶
Excel支持三种不同类型的条件格式：内置格式、标准格式和自定义格式。内置将特定规则与预定义样式结合在一起。标准条件格式将特定规则与自定义格式结合起来。此外，还可以定义使用差分样式应用自定义格式的自定义公式。
注解
不同规则的语法差异很大，因此OpenPYXL无法知道规则是否有意义。
创建格式规则的基本语法是：
>>> from openpyxl.formatting import Rule
>>> from openpyxl.styles import Font, PatternFill, Border
>>> from openpyxl.styles.differential import DifferentialStyle
>>> dxf = DifferentialStyle(font=Font(bold=True), fill=PatternFill(start_color='EE1111', end_color='EE1111'))
>>> rule = Rule(type='cellIs', dxf=dxf, formula=["10"])
因为某些规则的签名可能非常冗长，所以也有一些方便工厂来创建它们。
内置格式¶
内置条件格式为：
ColorScale
IconSet
DataBar
内置格式包含一系列格式设置，这些设置将类型与整数结合起来进行比较。可能的类型有： 'num', 'percent', 'max', 'min', 'formula', 'percentile' .
ColorScale¶
您可以使用2色或3色的色阶。2个色阶产生从一种颜色到另一种颜色的渐变；3个色阶对2个渐变使用额外的颜色。
创建色阶规则的完整语法是：
>>> from openpyxl.formatting.rule import ColorScale, FormatObject
>>> from openpyxl.styles import Color
>>> first = FormatObject(type='min')
>>> last = FormatObject(type='max')
>>> # colors match the format objects:
>>> colors = [Color('AA0000'), Color('00AA00')]
>>> cs2 = ColorScale(cfvo=[first, last], color=colors)
>>> # a three color scale would extend the sequences
>>> mid = FormatObject(type='num', val=40)
>>> colors.insert(1, Color('00AA00'))
>>> cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
>>> # create a rule with the color scale
>>> from openpyxl.formatting.rule import Rule
>>> rule = Rule(type='colorScale', colorScale=cs3)
创建色阶规则有一个方便的函数
>>> from openpyxl.formatting.rule import ColorScaleRule
>>> rule = ColorScaleRule(start_type='percentile', start_value=10, start_color='FFAA0000',
... mid_type='percentile', mid_value=50, mid_color='FF0000AA',
... end_type='percentile', end_value=90, end_color='FF00AA00')
IconSet¶
从以下一组图标中选择： '3Arrows', '3ArrowsGray', '3Flags', '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2', '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights', '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'
创建IConset规则的完整语法是：
>>> from openpyxl.formatting.rule import IconSet, FormatObject
>>> first = FormatObject(type='percent', val=0)
>>> second = FormatObject(type='percent', val=33)
>>> third = FormatObject(type='percent', val=67)
>>> iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, percent=None, reverse=None)
>>> # assign the icon set to a rule
>>> from openpyxl.formatting.rule import Rule
>>> rule = Rule(type='iconSet', iconSet=iconset)
创建IConset规则有一个方便的功能：
>>> from openpyxl.formatting.rule import IconSetRule
>>> rule = IconSetRule('5Arrows', 'percent', [10, 20, 30, 40, 50], showValue=None, percent=None, reverse=None)
DataBar¶
目前，OpenPYXL支持原始规范中定义的数据条。在后来的扩展中添加了边界和方向。
创建数据条规则的完整语法是：
>>> from openpyxl.formatting.rule import DataBar, FormatObject
>>> first = FormatObject(type='min')
>>> second = FormatObject(type='max')
>>> data_bar = DataBar(cfvo=[first, second], color="638EC6", showValue=None, minLength=None, maxLength=None)
>>> # assign the data bar to a rule
>>> from openpyxl.formatting.rule import Rule
>>> rule = Rule(type='dataBar', dataBar=data_bar)
创建数据条规则有一个方便的功能：
>>> from openpyxl.formatting.rule import DataBarRule
>>> rule = DataBarRule(start_type='percentile', start_value=10, end_type='percentile', end_value='90',
... color="FF638EC6", showValue="None", minLength=None, maxLength=None)
标准条件格式¶
标准条件格式为：
平均值
百分比
唯一或重复
价值
等级
>>> from openpyxl import Workbook
>>> from openpyxl.styles import Color, PatternFill, Font, Border
>>> from openpyxl.styles.differential import DifferentialStyle
>>> from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> # Create fill
>>> redFill = PatternFill(start_color='EE1111',
... end_color='EE1111',
... fill_type='solid')
>>>
>>> # Add a two-color scale
>>> # Takes colors in excel 'RRGGBB' style.
>>> ws.conditional_formatting.add('A1:A10',
... ColorScaleRule(start_type='min', start_color='AA0000',
... end_type='max', end_color='00AA00')
... )
>>>
>>> # Add a three-color scale
>>> ws.conditional_formatting.add('B1:B10',
... ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000',
... mid_type='percentile', mid_value=50, mid_color='0000AA',
... end_type='percentile', end_value=90, end_color='00AA00')
... )
>>>
>>> # Add a conditional formatting based on a cell comparison
>>> # addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill)
>>> # Format if cell is less than 'formula'
>>> ws.conditional_formatting.add('C2:C10',
... CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
>>>
>>> # Format if cell is between 'formula'
>>> ws.conditional_formatting.add('D2:D10',
... CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
>>>
>>> # Format using a formula
>>> ws.conditional_formatting.add('E1:E10',
... FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
>>>
>>> # Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling:
>>> myFont = Font()
>>> myBorder = Border()
>>> ws.conditional_formatting.add('E1:E10',
... FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
>>>
>>> # Highlight cells that contain particular text by using a special formula
>>> red_text = Font(color="9C0006")
>>> red_fill = PatternFill(bgColor="FFC7CE")
>>> dxf = DifferentialStyle(font=red_text, fill=red_fill)
>>> rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf)
>>> rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
>>> ws.conditional_formatting.add('A1:F40', rule)
>>> wb.save("test.xlsx")
格式化整行¶
有时您希望对多个单元格应用条件格式，例如包含特定值的一行单元格。
>>> ws.append(['Software', 'Developer', 'Version'])
>>> ws.append(['Excel', 'Microsoft', '2016'])
>>> ws.append(['openpyxl', 'Open source', '2.6'])
>>> ws.append(['OpenOffice', 'Apache', '4.1.4'])
>>> ws.append(['Word', 'Microsoft', '2010'])
我们希望突出显示开发人员所在的行。我们通过创建表达式规则并使用公式来确定哪些行包含由Microsoft开发的软件来实现这一点。
>>> red_fill = PatternFill(bgColor="FFC7CE")
>>> dxf = DifferentialStyle(fill=red_fill)
>>> r = Rule(type="expression", dxf=dxf, stopIfTrue=True)
>>> r.formula = ['$A2="Microsoft"']
>>> ws.conditional_formatting.add("A1:C10", r)
注解
公式使用 绝对的 引用引用的列， B 在这种情况下；但是 相对的 此例中的行号 1 应用格式的范围。这可能很难纠正，但即使将规则添加到工作表的条件格式集合中，也可以对其进行调整。
