Excel支持三種不同類型的條件格式：內(nèi)置、標準和自定義俺抽。內(nèi)置條件格式將特定的規(guī)則與預(yù)定義的樣式結(jié)合起來。標準條件格式將特定規(guī)則與自定義格式結(jié)合起來蒋伦。此外沃斤，還可以定義使用差異樣式應(yīng)用自定義格式的自定義公式圣蝎。
注意
不同規(guī)則的語法變化很大刃宵，所以openpyxl不可能知道規(guī)則是否正確衡瓶。
創(chuàng)建格式規(guī)則的基本語法是：
>>> 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"])
由于某些規(guī)則非常復(fù)雜，因此有一些方便的工廠函數(shù)來創(chuàng)建它們牲证。
內(nèi)置格式?
內(nèi)置條件格式為：
- ColorScale
- IconSet
- DataBar
內(nèi)置格式包含一系列格式設(shè)置哮针，這些設(shè)置類型與整數(shù)結(jié)合起來進行比較√古郏可能的類型有：“num”十厢、“percent”、“max”捂齐、“min”蛮放、“formula”、“percentile”奠宜。
ColorScale?
你可以有2或3種顏色的色階包颁。兩個色階從一種顏色到另一種顏色產(chǎn)生漸變；三個色階對兩個漸變使用附加顏色压真。
創(chuàng)建色階規(guī)則的代碼為：
>>> 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)
有一個創(chuàng)建色階規(guī)則的便利函數(shù)
>>> 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”`
創(chuàng)建IconSet規(guī)則的代碼為：
>>> 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)
創(chuàng)建IconSet規(guī)則有一個方便的功能：
>>> from openpyxl.formatting.rule import IconSetRule
>>> rule = IconSetRule('5Arrows', 'percent', [10, 20, 30, 40, 50], showValue=None, percent=None, reverse=None)
DataBar?
目前，openpyxl支持原始規(guī)范中定義的數(shù)據(jù)庫唆鸡。在后來的擴展中添加了一些邊界和方向涝影。
創(chuàng)建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)
有一個創(chuàng)建DataBar的便利函數(shù)：
>>> 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")
格式化整行?
有時您希望將條件格式應(yīng)用于多個單元格，例如包含特定值的一行單元格争占。
>>> 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'])
我們想突出顯示開發(fā)者是微軟的行燃逻。我們通過創(chuàng)建一個表達式規(guī)則并使用一個公式來識別哪些包含微軟開發(fā)的軟件序目。
>>> 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”伯襟；但對應(yīng)用格式的范圍使用相對行號（在本例中為“1”）猿涨。正確處理此問題可能很困難，但即使規(guī)則已添加到工作表的Conditional
format集合中姆怪，也可以對其進行調(diào)整叛赚。
