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 copy import copy
4from operator import attrgetter
6from openpyxl.descriptors import Strict
7from openpyxl.descriptors import MinMax
8from openpyxl.descriptors.sequence import UniqueSequence
9from openpyxl.descriptors.serialisable import Serialisable
11from openpyxl.utils import (
12 range_boundaries,
13 range_to_tuple,
14 get_column_letter,
15 quote_sheetname,
16)
18class CellRange(Serialisable):
19 """
20 Represents a range in a sheet: title and coordinates.
22 This object is used to perform operations on ranges, like:
24 - shift, expand or shrink
25 - union/intersection with another sheet range,
27 We can check whether a range is:
29 - equal or not equal to another,
30 - disjoint of another,
31 - contained in another.
33 We can get:
35 - the size of a range.
36 - the range bounds (vertices)
37 - the coordinates,
38 - the string representation,
40 """
42 min_col = MinMax(min=1, max=18278, expected_type=int)
43 min_row = MinMax(min=1, max=1048576, expected_type=int)
44 max_col = MinMax(min=1, max=18278, expected_type=int)
45 max_row = MinMax(min=1, max=1048576, expected_type=int)
48 def __init__(self, range_string=None, min_col=None, min_row=None,
49 max_col=None, max_row=None, title=None):
50 if range_string is not None:
51 if "!" in range_string:
52 title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string)
53 else:
54 min_col, min_row, max_col, max_row = range_boundaries(range_string)
56 self.min_col = min_col
57 self.min_row = min_row
58 self.max_col = max_col
59 self.max_row = max_row
60 self.title = title
62 if min_col > max_col:
63 fmt = "{max_col} must be greater than {min_col}"
64 raise ValueError(fmt.format(min_col=min_col, max_col=max_col))
65 if min_row > max_row:
66 fmt = "{max_row} must be greater than {min_row}"
67 raise ValueError(fmt.format(min_row=min_row, max_row=max_row))
70 @property
71 def bounds(self):
72 """
73 Vertices of the range as a tuple
74 """
75 return self.min_col, self.min_row, self.max_col, self.max_row
78 @property
79 def coord(self):
80 """
81 Excel-style representation of the range
82 """
83 fmt = "{min_col}{min_row}:{max_col}{max_row}"
84 if (self.min_col == self.max_col
85 and self.min_row == self.max_row):
86 fmt = "{min_col}{min_row}"
88 return fmt.format(
89 min_col=get_column_letter(self.min_col),
90 min_row=self.min_row,
91 max_col=get_column_letter(self.max_col),
92 max_row=self.max_row
93 )
95 @property
96 def rows(self):
97 """
98 Return cell coordinates as rows
99 """
100 for row in range(self.min_row, self.max_row+1):
101 yield [(row, col) for col in range(self.min_col, self.max_col+1)]
104 @property
105 def cols(self):
106 """
107 Return cell coordinates as columns
108 """
109 for col in range(self.min_col, self.max_col+1):
110 yield [(row, col) for row in range(self.min_row, self.max_row+1)]
113 @property
114 def cells(self):
115 from itertools import product
116 return product(range(self.min_row, self.max_row+1), range(self.min_col, self.max_col+1))
119 def _check_title(self, other):
120 """
121 Check whether comparisons between ranges are possible.
122 Cannot compare ranges from different worksheets
123 Skip if the range passed in has no title.
124 """
125 if not isinstance(other, CellRange):
126 raise TypeError(repr(type(other)))
128 if other.title and self.title != other.title:
129 raise ValueError("Cannot work with ranges from different worksheets")
132 def __repr__(self):
133 fmt = u"<{cls} {coord}>"
134 if self.title:
135 fmt = u"<{cls} {title!r}!{coord}>"
136 return fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord)
139 def __hash__(self):
140 return hash((self.min_row, self.min_col, self.max_row, self.max_col))
143 def __str__(self):
144 fmt = "{coord}"
145 title = self.title
146 if title:
147 fmt = u"{title}!{coord}"
148 title = quote_sheetname(title)
149 return fmt.format(title=title, coord=self.coord)
152 def __copy__(self):
153 return self.__class__(min_col=self.min_col, min_row=self.min_row,
154 max_col=self.max_col, max_row=self.max_row,
155 title=self.title)
158 def shift(self, col_shift=0, row_shift=0):
159 """
160 Shift the focus of the range according to the shift values (*col_shift*, *row_shift*).
162 :type col_shift: int
163 :param col_shift: number of columns to be moved by, can be negative
164 :type row_shift: int
165 :param row_shift: number of rows to be moved by, can be negative
166 :raise: :class:`ValueError` if any row or column index < 1
167 """
169 if (self.min_col + col_shift <= 0
170 or self.min_row + row_shift <= 0):
171 raise ValueError("Invalid shift value: col_shift={0}, row_shift={1}".format(col_shift, row_shift))
172 self.min_col += col_shift
173 self.min_row += row_shift
174 self.max_col += col_shift
175 self.max_row += row_shift
178 def __ne__(self, other):
179 """
180 Test whether the ranges are not equal.
182 :type other: openpyxl.worksheet.cell_range.CellRange
183 :param other: Other sheet range
184 :return: ``True`` if *range* != *other*.
185 """
186 try:
187 self._check_title(other)
188 except ValueError:
189 return True
191 return (
192 other.min_row != self.min_row
193 or self.max_row != other.max_row
194 or other.min_col != self.min_col
195 or self.max_col != other.max_col
196 )
199 def __eq__(self, other):
200 """
201 Test whether the ranges are equal.
203 :type other: openpyxl.worksheet.cell_range.CellRange
204 :param other: Other sheet range
205 :return: ``True`` if *range* == *other*.
206 """
207 return not self.__ne__(other)
210 def issubset(self, other):
211 """
212 Test whether every cell in this range is also in *other*.
214 :type other: openpyxl.worksheet.cell_range.CellRange
215 :param other: Other sheet range
216 :return: ``True`` if *range* <= *other*.
217 """
218 self._check_title(other)
220 return other.__superset(self)
222 __le__ = issubset
225 def __lt__(self, other):
226 """
227 Test whether *other* contains every cell of this range, and more.
229 :type other: openpyxl.worksheet.cell_range.CellRange
230 :param other: Other sheet range
231 :return: ``True`` if *range* < *other*.
232 """
233 return self.__le__(other) and self.__ne__(other)
236 def __superset(self, other):
237 return (
238 (self.min_row <= other.min_row <= other.max_row <= self.max_row)
239 and
240 (self.min_col <= other.min_col <= other.max_col <= self.max_col)
241 )
244 def issuperset(self, other):
245 """
246 Test whether every cell in *other* is in this range.
248 :type other: openpyxl.worksheet.cell_range.CellRange
249 :param other: Other sheet range
250 :return: ``True`` if *range* >= *other* (or *other* in *range*).
251 """
252 self._check_title(other)
254 return self.__superset(other)
256 __ge__ = issuperset
259 def __contains__(self, coord):
260 """
261 Check whether the range contains a particular cell coordinate
262 """
263 cr = self.__class__(coord)
264 return self.__superset(cr)
267 def __gt__(self, other):
268 """
269 Test whether this range contains every cell in *other*, and more.
271 :type other: openpyxl.worksheet.cell_range.CellRange
272 :param other: Other sheet range
273 :return: ``True`` if *range* > *other*.
274 """
275 return self.__ge__(other) and self.__ne__(other)
278 def isdisjoint(self, other):
279 """
280 Return ``True`` if this range has no cell in common with *other*.
281 Ranges are disjoint if and only if their intersection is the empty range.
283 :type other: openpyxl.worksheet.cell_range.CellRange
284 :param other: Other sheet range.
285 :return: ``True`` if the range has no cells in common with other.
286 """
287 self._check_title(other)
289 # Sort by top-left vertex
290 if self.bounds > other.bounds:
291 self, other = other, self
293 return (self.max_col < other.min_col
294 or self.max_row < other.min_row
295 or other.max_row < self.min_row)
298 def intersection(self, other):
299 """
300 Return a new range with cells common to this range and *other*
302 :type other: openpyxl.worksheet.cell_range.CellRange
303 :param other: Other sheet range.
304 :return: the intersecting sheet range.
305 :raise: :class:`ValueError` if the *other* range doesn't intersect
306 with this range.
307 """
308 if self.isdisjoint(other):
309 raise ValueError("Range {0} doesn't intersect {0}".format(self, other))
311 min_row = max(self.min_row, other.min_row)
312 max_row = min(self.max_row, other.max_row)
313 min_col = max(self.min_col, other.min_col)
314 max_col = min(self.max_col, other.max_col)
316 return CellRange(min_col=min_col, min_row=min_row, max_col=max_col,
317 max_row=max_row)
319 __and__ = intersection
322 def union(self, other):
323 """
324 Return the minimal superset of this range and *other*. This new range
325 will contain all cells from this range, *other*, and any additional
326 cells required to form a rectangular ``CellRange``.
328 :type other: openpyxl.worksheet.cell_range.CellRange
329 :param other: Other sheet range.
330 :return: a ``CellRange`` that is a superset of this and *other*.
331 """
332 self._check_title(other)
334 min_row = min(self.min_row, other.min_row)
335 max_row = max(self.max_row, other.max_row)
336 min_col = min(self.min_col, other.min_col)
337 max_col = max(self.max_col, other.max_col)
338 return CellRange(min_col=min_col, min_row=min_row, max_col=max_col,
339 max_row=max_row, title=self.title)
341 __or__ = union
344 def __iter__(self):
345 """
346 For use as a dictionary elsewhere in the library.
347 """
348 for x in self.__attrs__:
349 if x == "title":
350 continue
351 v = getattr(self, x)
352 yield x, v
355 def expand(self, right=0, down=0, left=0, up=0):
356 """
357 Expand the range by the dimensions provided.
359 :type right: int
360 :param right: expand range to the right by this number of cells
361 :type down: int
362 :param down: expand range down by this number of cells
363 :type left: int
364 :param left: expand range to the left by this number of cells
365 :type up: int
366 :param up: expand range up by this number of cells
367 """
368 self.min_col -= left
369 self.min_row -= up
370 self.max_col += right
371 self.max_row += down
374 def shrink(self, right=0, bottom=0, left=0, top=0):
375 """
376 Shrink the range by the dimensions provided.
378 :type right: int
379 :param right: shrink range from the right by this number of cells
380 :type down: int
381 :param down: shrink range from the top by this number of cells
382 :type left: int
383 :param left: shrink range from the left by this number of cells
384 :type up: int
385 :param up: shrink range from the bottom by this number of cells
386 """
387 self.min_col += left
388 self.min_row += top
389 self.max_col -= right
390 self.max_row -= bottom
393 @property
394 def size(self):
395 """ Return the size of the range as a dictionary of rows and columns. """
396 cols = self.max_col + 1 - self.min_col
397 rows = self.max_row + 1 - self.min_row
398 return {'columns':cols, 'rows':rows}
401 @property
402 def top(self):
403 """A list of cell coordinates that comprise the top of the range"""
404 return [(self.min_row, col) for col in range(self.min_col, self.max_col+1)]
407 @property
408 def bottom(self):
409 """A list of cell coordinates that comprise the bottom of the range"""
410 return [(self.max_row, col) for col in range(self.min_col, self.max_col+1)]
413 @property
414 def left(self):
415 """A list of cell coordinates that comprise the left-side of the range"""
416 return [(row, self.min_col) for row in range(self.min_row, self.max_row+1)]
419 @property
420 def right(self):
421 """A list of cell coordinates that comprise the right-side of the range"""
422 return [(row, self.max_col) for row in range(self.min_row, self.max_row+1)]
425class MultiCellRange(Strict):
428 ranges = UniqueSequence(expected_type=CellRange)
431 def __init__(self, ranges=set()):
432 if isinstance(ranges, str):
433 ranges = [CellRange(r) for r in ranges.split()]
434 self.ranges = set(ranges)
437 def __contains__(self, coord):
438 if isinstance(coord, str):
439 coord = CellRange(coord)
440 for r in self.ranges:
441 if coord <= r:
442 return True
443 return False
446 def __repr__(self):
447 ranges = " ".join([str(r) for r in self.sorted()])
448 return f"<{self.__class__.__name__} [{ranges}]>"
451 def __str__(self):
452 ranges = u" ".join([str(r) for r in self.sorted()])
453 return ranges
456 def __hash__(self):
457 return hash(str(self))
460 def sorted(self):
461 """
462 Return a sorted list of items
463 """
464 return sorted(self.ranges, key=attrgetter('min_col', 'min_row', 'max_col', 'max_row'))
467 def add(self, coord):
468 """
469 Add a cell coordinate or CellRange
470 """
471 cr = coord
472 if isinstance(coord, str):
473 cr = CellRange(coord)
474 elif not isinstance(coord, CellRange):
475 raise ValueError("You can only add CellRanges")
476 if cr not in self:
477 self.ranges.add(cr)
480 def __iadd__(self, coord):
481 self.add(coord)
482 return self
485 def __eq__(self, other):
486 if isinstance(other, str):
487 other = self.__class__(other)
488 return self.ranges == other.ranges
491 def __ne__(self, other):
492 return not self == other
495 def __bool__(self):
496 return bool(self.ranges)
499 def remove(self, coord):
500 if not isinstance(coord, CellRange):
501 coord = CellRange(coord)
502 self.ranges.remove(coord)
505 def __iter__(self):
506 for cr in self.ranges:
507 yield cr
510 def __copy__(self):
511 ranges = {copy(r) for r in self.ranges}
512 return MultiCellRange(ranges)