openpyxl3.0官方文档（27）—— 条件格式
2020-07-11 本文已影响0人
Sinchard
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种颜色的色阶。两个色阶从一种颜色到另一种颜色产生渐变；三个色阶对两个渐变使用附加颜色。
创建色阶规则的代码为：
>>> 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支持原始规范中定义的数据库。在后来的扩展中添加了一些边界和方向。
创建DataBar的代码为：
>>> 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)
有一个创建DataBar的便利函数：
>>> 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)
标准条件格式¶
标准条件格式为：
- Average
- Percent
- Unique or duplicate
- Value
- Rank
>>> 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'])
我们想突出显示开发者是微软的行。我们通过创建一个表达式规则并使用一个公式来识别哪些包含微软开发的软件。
>>> 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”）。正确处理此问题可能很困难，但即使规则已添加到工作表的Conditional
format集合中，也可以对其进行调整。
