# Copyright (c) 2010-2024 openpyxl
|
|
from copy import copy
|
from operator import attrgetter
|
|
from openpyxl.descriptors import Strict
|
from openpyxl.descriptors import MinMax
|
from openpyxl.descriptors.sequence import UniqueSequence
|
from openpyxl.descriptors.serialisable import Serialisable
|
|
from openpyxl.utils import (
|
range_boundaries,
|
range_to_tuple,
|
get_column_letter,
|
quote_sheetname,
|
)
|
|
class CellRange(Serialisable):
|
"""
|
Represents a range in a sheet: title and coordinates.
|
|
This object is used to perform operations on ranges, like:
|
|
- shift, expand or shrink
|
- union/intersection with another sheet range,
|
|
We can check whether a range is:
|
|
- equal or not equal to another,
|
- disjoint of another,
|
- contained in another.
|
|
We can get:
|
|
- the size of a range.
|
- the range bounds (vertices)
|
- the coordinates,
|
- the string representation,
|
|
"""
|
|
min_col = MinMax(min=1, max=18278, expected_type=int)
|
min_row = MinMax(min=1, max=1048576, expected_type=int)
|
max_col = MinMax(min=1, max=18278, expected_type=int)
|
max_row = MinMax(min=1, max=1048576, expected_type=int)
|
|
|
def __init__(self, range_string=None, min_col=None, min_row=None,
|
max_col=None, max_row=None, title=None):
|
if range_string is not None:
|
if "!" in range_string:
|
title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string)
|
else:
|
min_col, min_row, max_col, max_row = range_boundaries(range_string)
|
|
self.min_col = min_col
|
self.min_row = min_row
|
self.max_col = max_col
|
self.max_row = max_row
|
self.title = title
|
|
if min_col > max_col:
|
fmt = "{max_col} must be greater than {min_col}"
|
raise ValueError(fmt.format(min_col=min_col, max_col=max_col))
|
if min_row > max_row:
|
fmt = "{max_row} must be greater than {min_row}"
|
raise ValueError(fmt.format(min_row=min_row, max_row=max_row))
|
|
|
@property
|
def bounds(self):
|
"""
|
Vertices of the range as a tuple
|
"""
|
return self.min_col, self.min_row, self.max_col, self.max_row
|
|
|
@property
|
def coord(self):
|
"""
|
Excel-style representation of the range
|
"""
|
fmt = "{min_col}{min_row}:{max_col}{max_row}"
|
if (self.min_col == self.max_col
|
and self.min_row == self.max_row):
|
fmt = "{min_col}{min_row}"
|
|
return fmt.format(
|
min_col=get_column_letter(self.min_col),
|
min_row=self.min_row,
|
max_col=get_column_letter(self.max_col),
|
max_row=self.max_row
|
)
|
|
@property
|
def rows(self):
|
"""
|
Return cell coordinates as rows
|
"""
|
for row in range(self.min_row, self.max_row+1):
|
yield [(row, col) for col in range(self.min_col, self.max_col+1)]
|
|
|
@property
|
def cols(self):
|
"""
|
Return cell coordinates as columns
|
"""
|
for col in range(self.min_col, self.max_col+1):
|
yield [(row, col) for row in range(self.min_row, self.max_row+1)]
|
|
|
@property
|
def cells(self):
|
from itertools import product
|
return product(range(self.min_row, self.max_row+1), range(self.min_col, self.max_col+1))
|
|
|
def _check_title(self, other):
|
"""
|
Check whether comparisons between ranges are possible.
|
Cannot compare ranges from different worksheets
|
Skip if the range passed in has no title.
|
"""
|
if not isinstance(other, CellRange):
|
raise TypeError(repr(type(other)))
|
|
if other.title and self.title != other.title:
|
raise ValueError("Cannot work with ranges from different worksheets")
|
|
|
def __repr__(self):
|
fmt = u"<{cls} {coord}>"
|
if self.title:
|
fmt = u"<{cls} {title!r}!{coord}>"
|
return fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord)
|
|
|
def __hash__(self):
|
return hash((self.min_row, self.min_col, self.max_row, self.max_col))
|
|
|
def __str__(self):
|
fmt = "{coord}"
|
title = self.title
|
if title:
|
fmt = u"{title}!{coord}"
|
title = quote_sheetname(title)
|
return fmt.format(title=title, coord=self.coord)
|
|
|
def __copy__(self):
|
return self.__class__(min_col=self.min_col, min_row=self.min_row,
|
max_col=self.max_col, max_row=self.max_row,
|
title=self.title)
|
|
|
def shift(self, col_shift=0, row_shift=0):
|
"""
|
Shift the focus of the range according to the shift values (*col_shift*, *row_shift*).
|
|
:type col_shift: int
|
:param col_shift: number of columns to be moved by, can be negative
|
:type row_shift: int
|
:param row_shift: number of rows to be moved by, can be negative
|
:raise: :class:`ValueError` if any row or column index < 1
|
"""
|
|
if (self.min_col + col_shift <= 0
|
or self.min_row + row_shift <= 0):
|
raise ValueError("Invalid shift value: col_shift={0}, row_shift={1}".format(col_shift, row_shift))
|
self.min_col += col_shift
|
self.min_row += row_shift
|
self.max_col += col_shift
|
self.max_row += row_shift
|
|
|
def __ne__(self, other):
|
"""
|
Test whether the ranges are not equal.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* != *other*.
|
"""
|
try:
|
self._check_title(other)
|
except ValueError:
|
return True
|
|
return (
|
other.min_row != self.min_row
|
or self.max_row != other.max_row
|
or other.min_col != self.min_col
|
or self.max_col != other.max_col
|
)
|
|
|
def __eq__(self, other):
|
"""
|
Test whether the ranges are equal.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* == *other*.
|
"""
|
return not self.__ne__(other)
|
|
|
def issubset(self, other):
|
"""
|
Test whether every cell in this range is also in *other*.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* <= *other*.
|
"""
|
self._check_title(other)
|
|
return other.__superset(self)
|
|
__le__ = issubset
|
|
|
def __lt__(self, other):
|
"""
|
Test whether *other* contains every cell of this range, and more.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* < *other*.
|
"""
|
return self.__le__(other) and self.__ne__(other)
|
|
|
def __superset(self, other):
|
return (
|
(self.min_row <= other.min_row <= other.max_row <= self.max_row)
|
and
|
(self.min_col <= other.min_col <= other.max_col <= self.max_col)
|
)
|
|
|
def issuperset(self, other):
|
"""
|
Test whether every cell in *other* is in this range.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* >= *other* (or *other* in *range*).
|
"""
|
self._check_title(other)
|
|
return self.__superset(other)
|
|
__ge__ = issuperset
|
|
|
def __contains__(self, coord):
|
"""
|
Check whether the range contains a particular cell coordinate
|
"""
|
cr = self.__class__(coord)
|
return self.__superset(cr)
|
|
|
def __gt__(self, other):
|
"""
|
Test whether this range contains every cell in *other*, and more.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range
|
:return: ``True`` if *range* > *other*.
|
"""
|
return self.__ge__(other) and self.__ne__(other)
|
|
|
def isdisjoint(self, other):
|
"""
|
Return ``True`` if this range has no cell in common with *other*.
|
Ranges are disjoint if and only if their intersection is the empty range.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range.
|
:return: ``True`` if the range has no cells in common with other.
|
"""
|
self._check_title(other)
|
|
# Sort by top-left vertex
|
if self.bounds > other.bounds:
|
self, other = other, self
|
|
return (self.max_col < other.min_col
|
or self.max_row < other.min_row
|
or other.max_row < self.min_row)
|
|
|
def intersection(self, other):
|
"""
|
Return a new range with cells common to this range and *other*
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range.
|
:return: the intersecting sheet range.
|
:raise: :class:`ValueError` if the *other* range doesn't intersect
|
with this range.
|
"""
|
if self.isdisjoint(other):
|
raise ValueError("Range {0} doesn't intersect {0}".format(self, other))
|
|
min_row = max(self.min_row, other.min_row)
|
max_row = min(self.max_row, other.max_row)
|
min_col = max(self.min_col, other.min_col)
|
max_col = min(self.max_col, other.max_col)
|
|
return CellRange(min_col=min_col, min_row=min_row, max_col=max_col,
|
max_row=max_row)
|
|
__and__ = intersection
|
|
|
def union(self, other):
|
"""
|
Return the minimal superset of this range and *other*. This new range
|
will contain all cells from this range, *other*, and any additional
|
cells required to form a rectangular ``CellRange``.
|
|
:type other: openpyxl.worksheet.cell_range.CellRange
|
:param other: Other sheet range.
|
:return: a ``CellRange`` that is a superset of this and *other*.
|
"""
|
self._check_title(other)
|
|
min_row = min(self.min_row, other.min_row)
|
max_row = max(self.max_row, other.max_row)
|
min_col = min(self.min_col, other.min_col)
|
max_col = max(self.max_col, other.max_col)
|
return CellRange(min_col=min_col, min_row=min_row, max_col=max_col,
|
max_row=max_row, title=self.title)
|
|
__or__ = union
|
|
|
def __iter__(self):
|
"""
|
For use as a dictionary elsewhere in the library.
|
"""
|
for x in self.__attrs__:
|
if x == "title":
|
continue
|
v = getattr(self, x)
|
yield x, v
|
|
|
def expand(self, right=0, down=0, left=0, up=0):
|
"""
|
Expand the range by the dimensions provided.
|
|
:type right: int
|
:param right: expand range to the right by this number of cells
|
:type down: int
|
:param down: expand range down by this number of cells
|
:type left: int
|
:param left: expand range to the left by this number of cells
|
:type up: int
|
:param up: expand range up by this number of cells
|
"""
|
self.min_col -= left
|
self.min_row -= up
|
self.max_col += right
|
self.max_row += down
|
|
|
def shrink(self, right=0, bottom=0, left=0, top=0):
|
"""
|
Shrink the range by the dimensions provided.
|
|
:type right: int
|
:param right: shrink range from the right by this number of cells
|
:type down: int
|
:param down: shrink range from the top by this number of cells
|
:type left: int
|
:param left: shrink range from the left by this number of cells
|
:type up: int
|
:param up: shrink range from the bottom by this number of cells
|
"""
|
self.min_col += left
|
self.min_row += top
|
self.max_col -= right
|
self.max_row -= bottom
|
|
|
@property
|
def size(self):
|
""" Return the size of the range as a dictionary of rows and columns. """
|
cols = self.max_col + 1 - self.min_col
|
rows = self.max_row + 1 - self.min_row
|
return {'columns':cols, 'rows':rows}
|
|
|
@property
|
def top(self):
|
"""A list of cell coordinates that comprise the top of the range"""
|
return [(self.min_row, col) for col in range(self.min_col, self.max_col+1)]
|
|
|
@property
|
def bottom(self):
|
"""A list of cell coordinates that comprise the bottom of the range"""
|
return [(self.max_row, col) for col in range(self.min_col, self.max_col+1)]
|
|
|
@property
|
def left(self):
|
"""A list of cell coordinates that comprise the left-side of the range"""
|
return [(row, self.min_col) for row in range(self.min_row, self.max_row+1)]
|
|
|
@property
|
def right(self):
|
"""A list of cell coordinates that comprise the right-side of the range"""
|
return [(row, self.max_col) for row in range(self.min_row, self.max_row+1)]
|
|
|
class MultiCellRange(Strict):
|
|
|
ranges = UniqueSequence(expected_type=CellRange)
|
|
|
def __init__(self, ranges=set()):
|
if isinstance(ranges, str):
|
ranges = [CellRange(r) for r in ranges.split()]
|
self.ranges = set(ranges)
|
|
|
def __contains__(self, coord):
|
if isinstance(coord, str):
|
coord = CellRange(coord)
|
for r in self.ranges:
|
if coord <= r:
|
return True
|
return False
|
|
|
def __repr__(self):
|
ranges = " ".join([str(r) for r in self.sorted()])
|
return f"<{self.__class__.__name__} [{ranges}]>"
|
|
|
def __str__(self):
|
ranges = u" ".join([str(r) for r in self.sorted()])
|
return ranges
|
|
|
def __hash__(self):
|
return hash(str(self))
|
|
|
def sorted(self):
|
"""
|
Return a sorted list of items
|
"""
|
return sorted(self.ranges, key=attrgetter('min_col', 'min_row', 'max_col', 'max_row'))
|
|
|
def add(self, coord):
|
"""
|
Add a cell coordinate or CellRange
|
"""
|
cr = coord
|
if isinstance(coord, str):
|
cr = CellRange(coord)
|
elif not isinstance(coord, CellRange):
|
raise ValueError("You can only add CellRanges")
|
if cr not in self:
|
self.ranges.add(cr)
|
|
|
def __iadd__(self, coord):
|
self.add(coord)
|
return self
|
|
|
def __eq__(self, other):
|
if isinstance(other, str):
|
other = self.__class__(other)
|
return self.ranges == other.ranges
|
|
|
def __ne__(self, other):
|
return not self == other
|
|
|
def __bool__(self):
|
return bool(self.ranges)
|
|
|
def remove(self, coord):
|
if not isinstance(coord, CellRange):
|
coord = CellRange(coord)
|
self.ranges.remove(coord)
|
|
|
def __iter__(self):
|
for cr in self.ranges:
|
yield cr
|
|
|
def __copy__(self):
|
ranges = {copy(r) for r in self.ranges}
|
return MultiCellRange(ranges)
|
