Source code

Download source archive ↓

src/openpyxl/formatting/rule.py

1# Copyright (c) 2010-2024 openpyxl
2
3from openpyxl.descriptors.serialisable import Serialisable
4from openpyxl.descriptors import (
5    Typed,
6    String,
7    Sequence,
8    Bool,
9    NoneSet,
10    Set,
11    Integer,
12    Float,
13)
14from openpyxl.descriptors.excel import ExtensionList
15from openpyxl.styles.colors import Color, ColorDescriptor
16from openpyxl.styles.differential import DifferentialStyle
17
18from openpyxl.utils.cell import COORD_RE
19
20
21class ValueDescriptor(Float):
22    """
23    Expected type depends upon type attribute of parent :-(
24
25    Most values should be numeric BUT they can also be cell references
26    """
27
28    def __set__(self, instance, value):
29        ref = None
30        if value is not None and isinstance(value, str):
31            ref = COORD_RE.match(value)
32        if instance.type == "formula" or ref:
33            self.expected_type = str
34        else:
35            self.expected_type = float
36        super().__set__(instance, value)
37
38
39class FormatObject(Serialisable):
40
41    tagname = "cfvo"
42
43    type = Set(values=(['num', 'percent', 'max', 'min', 'formula', 'percentile']))
44    val = ValueDescriptor(allow_none=True)
45    gte = Bool(allow_none=True)
46    extLst = Typed(expected_type=ExtensionList, allow_none=True)
47
48    __elements__ = ()
49
50    def __init__(self,
51                 type,
52                 val=None,
53                 gte=None,
54                 extLst=None,
55                ):
56        self.type = type
57        self.val = val
58        self.gte = gte
59
60
61class RuleType(Serialisable):
62
63    cfvo = Sequence(expected_type=FormatObject)
64
65
66class IconSet(RuleType):
67
68    tagname = "iconSet"
69
70    iconSet = NoneSet(values=(['3Arrows', '3ArrowsGray', '3Flags',
71                           '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2',
72                           '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights',
73                           '5Arrows', '5ArrowsGray', '5Rating', '5Quarters']))
74    showValue = Bool(allow_none=True)
75    percent = Bool(allow_none=True)
76    reverse = Bool(allow_none=True)
77
78    __elements__ = ("cfvo",)
79
80    def __init__(self,
81                 iconSet=None,
82                 showValue=None,
83                 percent=None,
84                 reverse=None,
85                 cfvo=None,
86                ):
87        self.iconSet = iconSet
88        self.showValue = showValue
89        self.percent = percent
90        self.reverse = reverse
91        self.cfvo = cfvo
92
93
94class DataBar(RuleType):
95
96    tagname = "dataBar"
97
98    minLength = Integer(allow_none=True)
99    maxLength = Integer(allow_none=True)
100    showValue = Bool(allow_none=True)
101    color = ColorDescriptor()
102
103    __elements__ = ('cfvo', 'color')
104
105    def __init__(self,
106                 minLength=None,
107                 maxLength=None,
108                 showValue=None,
109                 cfvo=None,
110                 color=None,
111                ):
112        self.minLength = minLength
113        self.maxLength = maxLength
114        self.showValue = showValue
115        self.cfvo = cfvo
116        self.color = color
117
118
119class ColorScale(RuleType):
120
121    tagname = "colorScale"
122
123    color = Sequence(expected_type=Color)
124
125    __elements__ = ('cfvo', 'color')
126
127    def __init__(self,
128                 cfvo=None,
129                 color=None,
130                ):
131        self.cfvo = cfvo
132        self.color = color
133
134
135class Rule(Serialisable):
136
137    tagname = "cfRule"
138
139    type = Set(values=(['expression', 'cellIs', 'colorScale', 'dataBar',
140                        'iconSet', 'top10', 'uniqueValues', 'duplicateValues', 'containsText',
141                        'notContainsText', 'beginsWith', 'endsWith', 'containsBlanks',
142                        'notContainsBlanks', 'containsErrors', 'notContainsErrors', 'timePeriod',
143                        'aboveAverage']))
144    dxfId = Integer(allow_none=True)
145    priority = Integer()
146    stopIfTrue = Bool(allow_none=True)
147    aboveAverage = Bool(allow_none=True)
148    percent = Bool(allow_none=True)
149    bottom = Bool(allow_none=True)
150    operator = NoneSet(values=(['lessThan', 'lessThanOrEqual', 'equal',
151                            'notEqual', 'greaterThanOrEqual', 'greaterThan', 'between', 'notBetween',
152                            'containsText', 'notContains', 'beginsWith', 'endsWith']))
153    text = String(allow_none=True)
154    timePeriod = NoneSet(values=(['today', 'yesterday', 'tomorrow', 'last7Days',
155                              'thisMonth', 'lastMonth', 'nextMonth', 'thisWeek', 'lastWeek',
156                              'nextWeek']))
157    rank = Integer(allow_none=True)
158    stdDev = Integer(allow_none=True)
159    equalAverage = Bool(allow_none=True)
160    formula = Sequence(expected_type=str)
161    colorScale = Typed(expected_type=ColorScale, allow_none=True)
162    dataBar = Typed(expected_type=DataBar, allow_none=True)
163    iconSet = Typed(expected_type=IconSet, allow_none=True)
164    extLst = Typed(expected_type=ExtensionList, allow_none=True)
165    dxf = Typed(expected_type=DifferentialStyle, allow_none=True)
166
167    __elements__ = ('colorScale', 'dataBar', 'iconSet', 'formula')
168    __attrs__ = ('type', 'rank', 'priority', 'equalAverage', 'operator',
169                 'aboveAverage', 'dxfId', 'stdDev', 'stopIfTrue', 'timePeriod', 'text',
170                 'percent', 'bottom')
171
172
173    def __init__(self,
174                 type,
175                 dxfId=None,
176                 priority=0,
177                 stopIfTrue=None,
178                 aboveAverage=None,
179                 percent=None,
180                 bottom=None,
181                 operator=None,
182                 text=None,
183                 timePeriod=None,
184                 rank=None,
185                 stdDev=None,
186                 equalAverage=None,
187                 formula=(),
188                 colorScale=None,
189                 dataBar=None,
190                 iconSet=None,
191                 extLst=None,
192                 dxf=None,
193                ):
194        self.type = type
195        self.dxfId = dxfId
196        self.priority = priority
197        self.stopIfTrue = stopIfTrue
198        self.aboveAverage = aboveAverage
199        self.percent = percent
200        self.bottom = bottom
201        self.operator = operator
202        self.text = text
203        self.timePeriod = timePeriod
204        self.rank = rank
205        self.stdDev = stdDev
206        self.equalAverage = equalAverage
207        self.formula = formula
208        self.colorScale = colorScale
209        self.dataBar = dataBar
210        self.iconSet = iconSet
211        self.dxf = dxf
212
213
214def ColorScaleRule(start_type=None,
215                 start_value=None,
216                 start_color=None,
217                 mid_type=None,
218                 mid_value=None,
219                 mid_color=None,
220                 end_type=None,
221                 end_value=None,
222                 end_color=None):
223
224    """Backwards compatibility"""
225    formats = []
226    if start_type is not None:
227        formats.append(FormatObject(type=start_type, val=start_value))
228    if mid_type is not None:
229        formats.append(FormatObject(type=mid_type, val=mid_value))
230    if end_type is not None:
231        formats.append(FormatObject(type=end_type, val=end_value))
232    colors = []
233    for v in (start_color, mid_color, end_color):
234        if v is not None:
235            if not isinstance(v, Color):
236                v = Color(v)
237            colors.append(v)
238    cs = ColorScale(cfvo=formats, color=colors)
239    rule = Rule(type="colorScale", colorScale=cs)
240    return rule
241
242
243def FormulaRule(formula=None, stopIfTrue=None, font=None, border=None,
244                fill=None):
245    """
246    Conditional formatting with custom differential style
247    """
248    rule = Rule(type="expression", formula=formula, stopIfTrue=stopIfTrue)
249    rule.dxf =  DifferentialStyle(font=font, border=border, fill=fill)
250    return rule
251
252
253def CellIsRule(operator=None, formula=None, stopIfTrue=None, font=None, border=None, fill=None):
254    """
255    Conditional formatting rule based on cell contents.
256    """
257    # Excel doesn't use >, >=, etc, but allow for ease of python development
258    expand = {">": "greaterThan", ">=": "greaterThanOrEqual", "<": "lessThan", "<=": "lessThanOrEqual",
259              "=": "equal", "==": "equal", "!=": "notEqual"}
260
261    operator = expand.get(operator, operator)
262
263    rule = Rule(type='cellIs', operator=operator, formula=formula, stopIfTrue=stopIfTrue)
264    rule.dxf = DifferentialStyle(font=font, border=border, fill=fill)
265
266    return rule
267
268
269def IconSetRule(icon_style=None, type=None, values=None, showValue=None, percent=None, reverse=None):
270    """
271    Convenience function for creating icon set rules
272    """
273    cfvo = []
274    for val in values:
275        cfvo.append(FormatObject(type, val))
276    icon_set = IconSet(iconSet=icon_style, cfvo=cfvo, showValue=showValue,
277                       percent=percent, reverse=reverse)
278    rule = Rule(type='iconSet', iconSet=icon_set)
279
280    return rule
281
282
283def DataBarRule(start_type=None, start_value=None, end_type=None,
284                end_value=None, color=None, showValue=None, minLength=None, maxLength=None):
285    start = FormatObject(start_type, start_value)
286    end = FormatObject(end_type, end_value)
287    data_bar = DataBar(cfvo=[start, end], color=color, showValue=showValue,
288                       minLength=minLength, maxLength=maxLength)
289    rule = Rule(type='dataBar', dataBar=data_bar)
290
291    return rule