# Copyright (c) 2010-2024 openpyxl import re from openpyxl.descriptors.serialisable import Serialisable from openpyxl.descriptors import ( Alias, Typed, Set, Float, DateTime, NoneSet, Bool, Integer, String, Sequence, MinMax, ) from openpyxl.descriptors.excel import ExtensionList, CellRange from openpyxl.descriptors.sequence import ValueSequence from openpyxl.utils import absolute_coordinate class SortCondition(Serialisable): tagname = "sortCondition" descending = Bool(allow_none=True) sortBy = NoneSet(values=(['value', 'cellColor', 'fontColor', 'icon'])) ref = CellRange() customList = String(allow_none=True) dxfId = Integer(allow_none=True) iconSet = NoneSet(values=(['3Arrows', '3ArrowsGray', '3Flags', '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2', '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights', '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'])) iconId = Integer(allow_none=True) def __init__(self, ref=None, descending=None, sortBy=None, customList=None, dxfId=None, iconSet=None, iconId=None, ): self.descending = descending self.sortBy = sortBy self.ref = ref self.customList = customList self.dxfId = dxfId self.iconSet = iconSet self.iconId = iconId class SortState(Serialisable): tagname = "sortState" columnSort = Bool(allow_none=True) caseSensitive = Bool(allow_none=True) sortMethod = NoneSet(values=(['stroke', 'pinYin'])) ref = CellRange() sortCondition = Sequence(expected_type=SortCondition, allow_none=True) extLst = Typed(expected_type=ExtensionList, allow_none=True) __elements__ = ('sortCondition',) def __init__(self, columnSort=None, caseSensitive=None, sortMethod=None, ref=None, sortCondition=(), extLst=None, ): self.columnSort = columnSort self.caseSensitive = caseSensitive self.sortMethod = sortMethod self.ref = ref self.sortCondition = sortCondition def __bool__(self): return self.ref is not None class IconFilter(Serialisable): tagname = "iconFilter" iconSet = Set(values=(['3Arrows', '3ArrowsGray', '3Flags', '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2', '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights', '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'])) iconId = Integer(allow_none=True) def __init__(self, iconSet=None, iconId=None, ): self.iconSet = iconSet self.iconId = iconId class ColorFilter(Serialisable): tagname = "colorFilter" dxfId = Integer(allow_none=True) cellColor = Bool(allow_none=True) def __init__(self, dxfId=None, cellColor=None, ): self.dxfId = dxfId self.cellColor = cellColor class DynamicFilter(Serialisable): tagname = "dynamicFilter" type = Set(values=(['null', 'aboveAverage', 'belowAverage', 'tomorrow', 'today', 'yesterday', 'nextWeek', 'thisWeek', 'lastWeek', 'nextMonth', 'thisMonth', 'lastMonth', 'nextQuarter', 'thisQuarter', 'lastQuarter', 'nextYear', 'thisYear', 'lastYear', 'yearToDate', 'Q1', 'Q2', 'Q3', 'Q4', 'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9', 'M10', 'M11', 'M12'])) val = Float(allow_none=True) valIso = DateTime(allow_none=True) maxVal = Float(allow_none=True) maxValIso = DateTime(allow_none=True) def __init__(self, type=None, val=None, valIso=None, maxVal=None, maxValIso=None, ): self.type = type self.val = val self.valIso = valIso self.maxVal = maxVal self.maxValIso = maxValIso class CustomFilter(Serialisable): tagname = "customFilter" val = String() operator = Set(values=['equal', 'lessThan', 'lessThanOrEqual', 'notEqual', 'greaterThanOrEqual', 'greaterThan']) def __init__(self, operator="equal", val=None): self.operator = operator self.val = val def _get_subtype(self): if self.val == " ": subtype = BlankFilter else: try: float(self.val) subtype = NumberFilter except ValueError: subtype = StringFilter return subtype def convert(self): """Convert to more specific filter""" typ = self._get_subtype() if typ in (BlankFilter, NumberFilter): return typ(**dict(self)) operator, term = StringFilter._guess_operator(self.val) flt = StringFilter(operator, term) if self.operator == "notEqual": flt.exclude = True return flt class BlankFilter(CustomFilter): """ Exclude blanks """ __attrs__ = ("operator", "val") def __init__(self, **kw): pass @property def operator(self): return "notEqual" @property def val(self): return " " class NumberFilter(CustomFilter): operator = Set(values= ['equal', 'lessThan', 'lessThanOrEqual', 'notEqual', 'greaterThanOrEqual', 'greaterThan']) val = Float() def __init__(self, operator="equal", val=None): self.operator = operator self.val = val string_format_mapping = { "contains": "*{}*", "startswith": "{}*", "endswith": "*{}", "wildcard": "{}", } class StringFilter(CustomFilter): operator = Set(values=['contains', 'startswith', 'endswith', 'wildcard'] ) val = String() exclude = Bool() def __init__(self, operator="contains", val=None, exclude=False): self.operator = operator self.val = val self.exclude = exclude def _escape(self): """Escape wildcards ~, * ? when serialising""" if self.operator == "wildcard": return self.val return re.sub(r"~|\*|\?", r"~\g<0>", self.val) @staticmethod def _unescape(value): """ Unescape value """ return re.sub(r"~(?P[~*?])", r"\g", value) @staticmethod def _guess_operator(value): value = StringFilter._unescape(value) endswith = r"^(?P\*)(?P[^\*\?]*$)" startswith = r"^(?P[^\*\?]*)(?P\*)$" contains = r"^(?P\*)(?P[^\*\?]*)\*$" d = {"wildcard": True, "term": value} for pat in [contains, startswith, endswith]: m = re.match(pat, value) if m: d = m.groupdict() term = d.pop("term") op = list(d)[0] return op, term def to_tree(self, tagname=None, idx=None, namespace=None): fmt = string_format_mapping[self.operator] op = self.exclude and "notEqual" or "equal" value = fmt.format(self._escape()) flt = CustomFilter(op, value) return flt.to_tree(tagname, idx, namespace) class CustomFilters(Serialisable): tagname = "customFilters" _and = Bool(allow_none=True) customFilter = Sequence(expected_type=CustomFilter) # min 1, max 2 __elements__ = ('customFilter',) def __init__(self, _and=None, customFilter=(), ): self._and = _and self.customFilter = customFilter class Top10(Serialisable): tagname = "top10" top = Bool(allow_none=True) percent = Bool(allow_none=True) val = Float() filterVal = Float(allow_none=True) def __init__(self, top=None, percent=None, val=None, filterVal=None, ): self.top = top self.percent = percent self.val = val self.filterVal = filterVal class DateGroupItem(Serialisable): tagname = "dateGroupItem" year = Integer() month = MinMax(min=1, max=12, allow_none=True) day = MinMax(min=1, max=31, allow_none=True) hour = MinMax(min=0, max=23, allow_none=True) minute = MinMax(min=0, max=59, allow_none=True) second = Integer(min=0, max=59, allow_none=True) dateTimeGrouping = Set(values=(['year', 'month', 'day', 'hour', 'minute', 'second'])) def __init__(self, year=None, month=None, day=None, hour=None, minute=None, second=None, dateTimeGrouping=None, ): self.year = year self.month = month self.day = day self.hour = hour self.minute = minute self.second = second self.dateTimeGrouping = dateTimeGrouping class Filters(Serialisable): tagname = "filters" blank = Bool(allow_none=True) calendarType = NoneSet(values=["gregorian","gregorianUs", "gregorianMeFrench","gregorianArabic", "hijri","hebrew", "taiwan","japan", "thai","korea", "saka","gregorianXlitEnglish","gregorianXlitFrench"]) filter = ValueSequence(expected_type=str) dateGroupItem = Sequence(expected_type=DateGroupItem, allow_none=True) __elements__ = ('filter', 'dateGroupItem') def __init__(self, blank=None, calendarType=None, filter=(), dateGroupItem=(), ): self.blank = blank self.calendarType = calendarType self.filter = filter self.dateGroupItem = dateGroupItem class FilterColumn(Serialisable): tagname = "filterColumn" colId = Integer() col_id = Alias('colId') hiddenButton = Bool(allow_none=True) showButton = Bool(allow_none=True) # some elements are choice filters = Typed(expected_type=Filters, allow_none=True) top10 = Typed(expected_type=Top10, allow_none=True) customFilters = Typed(expected_type=CustomFilters, allow_none=True) dynamicFilter = Typed(expected_type=DynamicFilter, allow_none=True) colorFilter = Typed(expected_type=ColorFilter, allow_none=True) iconFilter = Typed(expected_type=IconFilter, allow_none=True) extLst = Typed(expected_type=ExtensionList, allow_none=True) __elements__ = ('filters', 'top10', 'customFilters', 'dynamicFilter', 'colorFilter', 'iconFilter') def __init__(self, colId=None, hiddenButton=False, showButton=True, filters=None, top10=None, customFilters=None, dynamicFilter=None, colorFilter=None, iconFilter=None, extLst=None, blank=None, vals=None, ): self.colId = colId self.hiddenButton = hiddenButton self.showButton = showButton self.filters = filters self.top10 = top10 self.customFilters = customFilters self.dynamicFilter = dynamicFilter self.colorFilter = colorFilter self.iconFilter = iconFilter if blank is not None and self.filters: self.filters.blank = blank if vals is not None and self.filters: self.filters.filter = vals class AutoFilter(Serialisable): tagname = "autoFilter" ref = CellRange() filterColumn = Sequence(expected_type=FilterColumn, allow_none=True) sortState = Typed(expected_type=SortState, allow_none=True) extLst = Typed(expected_type=ExtensionList, allow_none=True) __elements__ = ('filterColumn', 'sortState') def __init__(self, ref=None, filterColumn=(), sortState=None, extLst=None, ): self.ref = ref self.filterColumn = filterColumn self.sortState = sortState def __bool__(self): return self.ref is not None def __str__(self): return absolute_coordinate(self.ref) def add_filter_column(self, col_id, vals, blank=False): """ Add row filter for specified column. :param col_id: Zero-origin column id. 0 means first column. :type col_id: int :param vals: Value list to show. :type vals: str[] :param blank: Show rows that have blank cell if True (default=``False``) :type blank: bool """ self.filterColumn.append(FilterColumn(colId=col_id, filters=Filters(blank=blank, filter=vals))) def add_sort_condition(self, ref, descending=False): """ Add sort condition for cpecified range of cells. :param ref: range of the cells (e.g. 'A2:A150') :type ref: string, is the same as that of the filter :param descending: Descending sort order (default=``False``) :type descending: bool """ cond = SortCondition(ref, descending) if self.sortState is None: self.sortState = SortState(ref=self.ref) self.sortState.sortCondition.append(cond)