Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# Copyright (c) 2010-2024 openpyxl
3from collections import defaultdict
4from itertools import chain
5from operator import itemgetter
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
19from openpyxl.utils import (
20 rows_from_range,
21 coordinate_to_tuple,
22 get_column_letter,
23)
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.
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.
33 Currently only collapsing contiguous vertical ranges (i.e. above
34 example results in A1:A3 B1:B3).
35 """
37 ranges = list(input_ranges)
39 # convert cell into row, col tuple
40 raw_coords = (coordinate_to_tuple(cell) for cell in cells)
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)
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)
56 return " ".join(ranges)
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))
71from .cell_range import MultiCellRange
74class DataValidation(Serialisable):
76 tagname = "dataValidation"
78 sqref = Convertible(expected_type=MultiCellRange)
79 cells = Alias("sqref")
80 ranges = Alias("sqref")
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')
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)
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')
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
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
150 def __contains__(self, cell):
151 if hasattr(cell, "coordinate"):
152 cell = cell.coordinate
153 return cell in self.sqref
156class DataValidationList(Serialisable):
158 tagname = "dataValidations"
160 disablePrompts = Bool(allow_none=True)
161 xWindow = Integer(allow_none=True)
162 yWindow = Integer(allow_none=True)
163 dataValidation = Sequence(expected_type=DataValidation)
165 __elements__ = ('dataValidation',)
166 __attrs__ = ('disablePrompts', 'xWindow', 'yWindow', 'count')
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
181 @property
182 def count(self):
183 return len(self)
186 def __len__(self):
187 return len(self.dataValidation)
190 def append(self, dv):
191 self.dataValidation.append(dv)
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