Source code

Download source archive ↓

src/openpyxl/worksheet/datavalidation.py

1# Copyright (c) 2010-2024 openpyxl
2
3from collections import defaultdict
4from itertools import chain
5from operator import itemgetter
6
7from openpyxl.descriptors.serialisable import Serialisable
8from openpyxl.descriptors import (
9    Bool,
10    NoneSet,
11    String,
12    Sequence,
13    Alias,
14    Integer,
15    Convertible,
16)
17from openpyxl.descriptors.nested import NestedText
18
19from openpyxl.utils import (
20    rows_from_range,
21    coordinate_to_tuple,
22    get_column_letter,
23)
24
25
26def collapse_cell_addresses(cells, input_ranges=()):
27    """ Collapse a collection of cell co-ordinates down into an optimal
28        range or collection of ranges.
29
30        E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation
31        object applied, attempt to collapse down to a single range, A1:B3.
32
33        Currently only collapsing contiguous vertical ranges (i.e. above
34        example results in A1:A3 B1:B3).
35    """
36
37    ranges = list(input_ranges)
38
39    # convert cell into row, col tuple
40    raw_coords = (coordinate_to_tuple(cell) for cell in cells)
41
42    # group by column in order
43    grouped_coords = defaultdict(list)
44    for row, col in sorted(raw_coords, key=itemgetter(1)):
45        grouped_coords[col].append(row)
46
47    # create range string from first and last row in column
48    for col, cells in grouped_coords.items():
49        col = get_column_letter(col)
50        fmt = "{0}{1}:{2}{3}"
51        if len(cells) == 1:
52            fmt = "{0}{1}"
53        r = fmt.format(col, min(cells), col, max(cells))
54        ranges.append(r)
55
56    return " ".join(ranges)
57
58
59def expand_cell_ranges(range_string):
60    """
61    Expand cell ranges to a sequence of addresses.
62    Reverse of collapse_cell_addresses
63    Eg. converts "A1:A2 B1:B2" to (A1, A2, B1, B2)
64    """
65    # expand ranges to rows and then flatten
66    rows = (rows_from_range(rs) for rs in range_string.split()) # list of rows
67    cells = (chain(*row) for row in rows) # flatten rows
68    return set(chain(*cells))
69
70
71from .cell_range import MultiCellRange
72
73
74class DataValidation(Serialisable):
75
76    tagname = "dataValidation"
77
78    sqref = Convertible(expected_type=MultiCellRange)
79    cells = Alias("sqref")
80    ranges = Alias("sqref")
81
82    showDropDown = Bool(allow_none=True)
83    hide_drop_down = Alias('showDropDown')
84    showInputMessage = Bool(allow_none=True)
85    showErrorMessage = Bool(allow_none=True)
86    allowBlank = Bool(allow_none=True)
87    allow_blank = Alias('allowBlank')
88
89    errorTitle = String(allow_none = True)
90    error = String(allow_none = True)
91    promptTitle = String(allow_none = True)
92    prompt = String(allow_none = True)
93    formula1 = NestedText(allow_none=True, expected_type=str)
94    formula2 = NestedText(allow_none=True, expected_type=str)
95
96    type = NoneSet(values=("whole", "decimal", "list", "date", "time",
97                           "textLength", "custom"))
98    errorStyle = NoneSet(values=("stop", "warning", "information"))
99    imeMode = NoneSet(values=("noControl", "off", "on", "disabled",
100                              "hiragana", "fullKatakana", "halfKatakana", "fullAlpha","halfAlpha",
101                              "fullHangul", "halfHangul"))
102    operator = NoneSet(values=("between", "notBetween", "equal", "notEqual",
103                               "lessThan", "lessThanOrEqual", "greaterThan", "greaterThanOrEqual"))
104    validation_type = Alias('type')
105
106    def __init__(self,
107                 type=None,
108                 formula1=None,
109                 formula2=None,
110                 showErrorMessage=False,
111                 showInputMessage=False,
112                 showDropDown=False,
113                 allowBlank=False,
114                 sqref=(),
115                 promptTitle=None,
116                 errorStyle=None,
117                 error=None,
118                 prompt=None,
119                 errorTitle=None,
120                 imeMode=None,
121                 operator=None,
122                 allow_blank=None,
123                 ):
124        self.sqref = sqref
125        self.showDropDown = showDropDown
126        self.imeMode = imeMode
127        self.operator = operator
128        self.formula1 = formula1
129        self.formula2 = formula2
130        if allow_blank is not None:
131            allowBlank = allow_blank
132        self.allowBlank = allowBlank
133        self.showErrorMessage = showErrorMessage
134        self.showInputMessage = showInputMessage
135        self.type = type
136        self.promptTitle = promptTitle
137        self.errorStyle = errorStyle
138        self.error = error
139        self.prompt = prompt
140        self.errorTitle = errorTitle
141
142
143    def add(self, cell):
144        """Adds a cell or cell coordinate to this validator"""
145        if hasattr(cell, "coordinate"):
146            cell = cell.coordinate
147        self.sqref += cell
148
149
150    def __contains__(self, cell):
151        if hasattr(cell, "coordinate"):
152            cell = cell.coordinate
153        return cell in self.sqref
154
155
156class DataValidationList(Serialisable):
157
158    tagname = "dataValidations"
159
160    disablePrompts = Bool(allow_none=True)
161    xWindow = Integer(allow_none=True)
162    yWindow = Integer(allow_none=True)
163    dataValidation = Sequence(expected_type=DataValidation)
164
165    __elements__ = ('dataValidation',)
166    __attrs__ = ('disablePrompts', 'xWindow', 'yWindow', 'count')
167
168    def __init__(self,
169                 disablePrompts=None,
170                 xWindow=None,
171                 yWindow=None,
172                 count=None,
173                 dataValidation=(),
174                ):
175        self.disablePrompts = disablePrompts
176        self.xWindow = xWindow
177        self.yWindow = yWindow
178        self.dataValidation = dataValidation
179
180
181    @property
182    def count(self):
183        return len(self)
184
185
186    def __len__(self):
187        return len(self.dataValidation)
188
189
190    def append(self, dv):
191        self.dataValidation.append(dv)
192
193
194    def to_tree(self, tagname=None):
195        """
196        Need to skip validations that have no cell ranges
197        """
198        ranges = self.dataValidation # copy
199        self.dataValidation = [r for r in self.dataValidation if bool(r.sqref)]
200        xml = super().to_tree(tagname)
201        self.dataValidation = ranges
202        return xml