Source code

Download source archive ↓

src/openpyxl/worksheet/cell_range.py

1# Copyright (c) 2010-2024 openpyxl
2
3from copy import copy
4from operator import attrgetter
5
6from openpyxl.descriptors import Strict
7from openpyxl.descriptors import MinMax
8from openpyxl.descriptors.sequence import UniqueSequence
9from openpyxl.descriptors.serialisable import Serialisable
10
11from openpyxl.utils import (
12    range_boundaries,
13    range_to_tuple,
14    get_column_letter,
15    quote_sheetname,
16)
17
18class CellRange(Serialisable):
19    """
20    Represents a range in a sheet: title and coordinates.
21
22    This object is used to perform operations on ranges, like:
23
24    - shift, expand or shrink
25    - union/intersection with another sheet range,
26
27    We can check whether a range is:
28
29    - equal or not equal to another,
30    - disjoint of another,
31    - contained in another.
32
33    We can get:
34
35    - the size of a range.
36    - the range bounds (vertices)
37    - the coordinates,
38    - the string representation,
39
40    """
41
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)
46
47
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)
55
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
61
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))
68
69
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
76
77
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}"
87
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        )
94
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)]
102
103
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)]
111
112
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))
117
118
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)))
127
128        if other.title and self.title != other.title:
129            raise ValueError("Cannot work with ranges from different worksheets")
130
131
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)
137
138
139    def __hash__(self):
140        return hash((self.min_row, self.min_col, self.max_row, self.max_col))
141
142
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)
150
151
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)
156
157
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*).
161
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        """
168
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
176
177
178    def __ne__(self, other):
179        """
180        Test whether the ranges are not equal.
181
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
190
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        )
197
198
199    def __eq__(self, other):
200        """
201        Test whether the ranges are equal.
202
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)
208
209
210    def issubset(self, other):
211        """
212        Test whether every cell in this range is also in *other*.
213
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)
219
220        return other.__superset(self)
221
222    __le__ = issubset
223
224
225    def __lt__(self, other):
226        """
227        Test whether *other* contains every cell of this range, and more.
228
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)
234
235
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        )
242
243
244    def issuperset(self, other):
245        """
246        Test whether every cell in *other* is in this range.
247
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)
253
254        return self.__superset(other)
255
256    __ge__ = issuperset
257
258
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)
265
266
267    def __gt__(self, other):
268        """
269        Test whether this range contains every cell in *other*, and more.
270
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)
276
277
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.
282
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)
288
289        # Sort by top-left vertex
290        if self.bounds > other.bounds:
291            self, other = other, self
292
293        return (self.max_col < other.min_col
294                or self.max_row < other.min_row
295                or other.max_row < self.min_row)
296
297
298    def intersection(self, other):
299        """
300        Return a new range with cells common to this range and *other*
301
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))
310
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)
315
316        return CellRange(min_col=min_col, min_row=min_row, max_col=max_col,
317                         max_row=max_row)
318
319    __and__ = intersection
320
321
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``.
327
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)
333
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)
340
341    __or__ = union
342
343
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
353
354
355    def expand(self, right=0, down=0, left=0, up=0):
356        """
357        Expand the range by the dimensions provided.
358
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
372
373
374    def shrink(self, right=0, bottom=0, left=0, top=0):
375        """
376        Shrink the range by the dimensions provided.
377
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
391
392
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}
399
400
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)]
405
406
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)]
411
412
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)]
417
418
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)]
423
424
425class MultiCellRange(Strict):
426
427
428    ranges = UniqueSequence(expected_type=CellRange)
429
430
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)
435
436
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
444
445
446    def __repr__(self):
447        ranges = " ".join([str(r) for r in self.sorted()])
448        return f"<{self.__class__.__name__} [{ranges}]>"
449
450
451    def __str__(self):
452        ranges = u" ".join([str(r) for r in self.sorted()])
453        return ranges
454
455
456    def __hash__(self):
457        return hash(str(self))
458
459
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'))
465
466
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)
478
479
480    def __iadd__(self, coord):
481        self.add(coord)
482        return self
483
484
485    def __eq__(self, other):
486        if  isinstance(other, str):
487            other = self.__class__(other)
488        return self.ranges == other.ranges
489
490
491    def __ne__(self, other):
492        return not self == other
493
494
495    def __bool__(self):
496        return bool(self.ranges)
497
498
499    def remove(self, coord):
500        if not isinstance(coord, CellRange):
501            coord = CellRange(coord)
502        self.ranges.remove(coord)
503
504
505    def __iter__(self):
506        for cr in self.ranges:
507            yield cr
508
509
510    def __copy__(self):
511        ranges = {copy(r) for r in self.ranges}
512        return MultiCellRange(ranges)