Source code for pygsheets.address

from pygsheets.exceptions import InvalidArgumentValue, IncorrectCellLabel
import re

[docs]class Address(object): """ Represents the address of a cell. This can also be unbound in an axes. So 'A' is also a valid address but this requires explict setting of param `allow_non_single`. First index correspond to the rows, second index corresponds to columns. Integer Indexes start from 1. >>> a = Address('A2') >>> a.index (2, 1) >>> a.label 'A2' >>> a[0] 2 >>> a[1] 1 >>> a = Address((1, 4)) >>> a.index (1, 4) >>> a.label D1 >>> b = a + (3,0) >>> b <Address D4> >>> b == (4, 4) True >>> column_a = Address((None, 1), True) >>> column_a <Address A> >>> row_2 = Address('2', True) >>> row_2 <Address 2> """ _MAGIC_NUMBER = 64 def __init__(self, value, allow_non_single=False): self._is_single = True self.allow_non_single = allow_non_single if isinstance(value, str): self._value = self._label_to_coordinates(value) elif isinstance(value, tuple) or isinstance(value, list): assert len(value) == 2, 'tuple should be of length 2' assert type(value[0]) is int or value[0] is None, 'address row should be int' assert type(value[1]) is int or value[1] is None, 'address col should be int' self._value = tuple(value) self._validate() elif not value and self.allow_non_single: self._value = (None, None) self._validate() elif isinstance(value, Address): self._value = self._label_to_coordinates(value.label) else: raise IncorrectCellLabel('Only labels in A1 notation, coordinates as a tuple or ' 'pygsheets.Address objects are accepted.') @property def label(self): """ Label of the current address in A1 format.""" return self._value_as_label() @property def row(self): """Row of the address""" return self._value[0] @property def col(self): """Column of the address""" return self._value[1] @row.setter def row(self, value): self._value = value, self._value[1] @col.setter def col(self, value): self._value = self._value[0], value @property def index(self): """Current Address in tuple format. Both axes starts at 1.""" return tuple(self._value) def _validate(self): if not self.allow_non_single and (self._value[0] is None or self._value[0] is None): raise InvalidArgumentValue("Address cannot be unbounded if allow_non_single is not set.") if self._value[0]: row = int(self._value[0]) if row < 1: raise InvalidArgumentValue('Address coordinates may not be below zero: ' + repr(self._value)) if self._value[1]: col = int(self._value[1]) if col < 1: raise InvalidArgumentValue('Address coordinates may not be below zero: ' + repr(self._value)) def _value_as_label(self): """Transforms tuple coordinates into a label of the form A1.""" self._validate() row_label, column_label = '', '' if self._value[0]: row_label = str(self._value[0]) if self._value[1]: col = int(self._value[1]) div = col column_label = '' while div: (div, mod) = divmod(div, 26) if mod == 0: mod = 26 div -= 1 column_label = chr(mod + self._MAGIC_NUMBER) + column_label return '{}{}'.format(column_label, row_label) def _label_to_coordinates(self, label): """Transforms a label in A1 notation into numeric coordinates and returns them as tuple.""" m = re.match(r'([A-Za-z]*)(\d*)', label) if m: column_label = row, col =, 0 if column_label: for i, c in enumerate(reversed(column_label)): col += (ord(c) - self._MAGIC_NUMBER) * (26 ** i) col = int(col) else: col = None row = int(row) if row else None if not m or (not self.allow_non_single and not (row and col)): raise IncorrectCellLabel('Not a valid cell label format: {}.'.format(label)) return row, col def __repr__(self): return '<%s %s>' % (self.__class__.__name__, str(self.label)) def __iter__(self): return iter(self._value) def __getitem__(self, item): return self._value[item] def __setitem__(self, key, value): current_value = list(self._value) current_value[key] = value self._value = tuple(current_value) def __add__(self, other): if type(other) is tuple or isinstance(other, Address): return Address((self._value[0] + other[0], self._value[1] + other[1])) else: raise NotImplementedError def __sub__(self, other): if type(other) is tuple or isinstance(other, Address): return Address((self._value[0] - other[0], self._value[1] - other[1])) else: raise NotImplementedError def __eq__(self, other): if isinstance(other, Address): return self.label == other.label elif type(other) is str: return self.label == other elif type(other) is tuple or type(other) is list: return self._value == tuple(other) else: return super(Address, self).__eq__(other) def __ne__(self, other): return not self.__eq__(other) def __bool__(self): return not (self._value[0] is None and self._value[1] is None) __nonzero__ = __bool__
[docs]class GridRange(object): """ Represents a rectangular (can be unbounded) range of adresses on a sheet. All indexes are one-based and are closed, ie the start index and the end index is inclusive Missing indexes indicate the range is unbounded on that side. A:B, A1:B3, 1:2 are all valid index, but A:1, 2:D are not grange.start = (1, None) will make the range unbounded on column grange.indexes = ((None, None), (None, None)) will make the range completely unbounded, ie. whole sheet Example: >>> grange = GridRange(worksheet=wks, start='A1', end='D4') >>> grange <GridRange Sheet1!A1:D4> >>> grange.start = 'A' # will remove bounding in rows <GridRange Sheet1!A:D> >>> grange.start = 'A1' # cannot add bounding at just start <GridRange Sheet1!A:D> >>> grange.indexes = ('A1', 'D4') # cannot add bounding at just start <GridRange Sheet1!A1:D4> >>> grange.end = (3, 5) # tuples will also work <GridRange Sheet1!A1:C5> >>> grange.end = (None, 5) # make unbounded on rows <GridRange Sheet1!1:5> >>> grange.end = (None, None) # make it unbounded on one index <GridRange Sheet1!1:1> >>> grange.start = None # make it unbounded on both indexes <GridRange Sheet1> >>> grange.start = 'A1' # make it unbounded on single index,now AZ100 is bottom right cell of worksheet <GridRange Sheet1:A1:AZ100> >>> 'A1' in grange True >>> (100,100) in grange False >>> for address in grange: >>> print(address) Address((1,1)) Address((1,2)) ... Reference: `GridRange API docs <>`__ """ def __init__(self, label=None, worksheet=None, start=None, end=None, worksheet_title=None, worksheet_id=None, propertiesjson=None): """ :param label: label in A1 format :param worksheet: worksheet object this grange belongs to :param start: start address of the range :param end: end address of the range :param worksheet_title: worksheet title if worksheet not given :param worksheet_id: worksheet id if worksheet not given :param propertiesjson: json with all range properties """ self._worksheet_title = worksheet_title self._worksheet_id = worksheet_id self._worksheet = worksheet self._start = Address(start, True) self._end = Address(end, True) # if fill_bounds and self._start and not self._end: # if not worksheet: # raise InvalidArgumentValue('worksheet need to fill bounds.') # self._end = Address((worksheet.rows, worksheet.cols), True) # if fill_bounds and self._end and not self._start: # self._start = Address('A1', True) if propertiesjson: self.set_json(propertiesjson) elif label: self._calculate_addresses(label) else: self._apply_index_constraints() self._calculate_label() @property def start(self): """ address of top left cell (index). """ if not self._start and self._end: top_index = [1, 1] if not self._end[0]: top_index[0] = None if not self._end[1]: top_index[1] = None return Address(tuple(top_index), True) return self._start @start.setter def start(self, value): # prev = self._start, self._end self._start = Address(value, allow_non_single=True) self._apply_index_constraints() self._calculate_label() @property def end(self): """ address of bottom right cell (index) """ if not self._end and self._start: if not self._worksheet: raise InvalidArgumentValue('worksheet is required for unbounded ranges') bottom_index = [self._worksheet.rows, self._worksheet.cols] if not self._start[0]: bottom_index[0] = None if not self._start[1]: bottom_index[1] = None return Address(tuple(bottom_index), True) return self._end @end.setter def end(self, value): # prev = self._start, self._end self._end = Address(value, allow_non_single=True) self._apply_index_constraints() self._calculate_label() @property def indexes(self): """ Indexes of this range as a tuple """ return self.start, self.end @indexes.setter def indexes(self, value): if type(value) is not tuple: raise InvalidArgumentValue("Please provide a tuple") self._start, self._end = Address(value[0], True), Address(value[1], True) self._apply_index_constraints() self._calculate_label() @property def label(self): """ Label in A1 notation format """ return self._calculate_label() @label.setter def label(self, value): if type(value) is not str: raise InvalidArgumentValue('non string value for label') self._calculate_addresses(value) @property def worksheet_id(self): """ Id of woksheet this range belongs to """ if self._worksheet: return return self._worksheet_id @worksheet_id.setter def worksheet_id(self, value): if self._worksheet: if == value: return else: raise InvalidArgumentValue("This range already has a worksheet with different id set.") self._worksheet_id = value @property def worksheet_title(self): """ Title of woksheet this range belongs to """ if self._worksheet: return self._worksheet.title return self._worksheet_title @worksheet_title.setter def worksheet_title(self, value): if not value: return if self._worksheet: if self._worksheet.title == value: return else: raise InvalidArgumentValue("This range already has a worksheet with different title set.") self._worksheet_title = value self._calculate_label()
[docs] @staticmethod def create(data, wks=None): """ create a Gridrange from various type of data :param data: can be string in A format,tuple or list, dict in GridRange format, GridRange object :param wks: worksheet to link to (optional) :return: GridRange object """ if isinstance(data, GridRange): grange = data elif isinstance(data, str): grange = GridRange(label=data, worksheet=wks) elif isinstance(data, tuple) or isinstance(data, list): if len(data) < 2: raise InvalidArgumentValue("start and end required") grange = GridRange(start=data[0], end=data[1], worksheet=wks) elif isinstance(data, dict): grange = GridRange(propertiesjson=data, worksheet=wks) else: raise InvalidArgumentValue(data) if wks: grange.set_worksheet(wks) return grange
[docs] def set_worksheet(self, value): """ set the worksheet of this grid range. """ self._worksheet = value self._worksheet_id = self._worksheet_title = value.title self._calculate_label()
def _apply_index_constraints(self): if not self._start or not self._end: return # # If range was single celled, and one is set to none, make both unbound # if prev and prev[0] == prev[1]: # if not self._start: # self._end = self._start # return # if not self._end: # self._start = self._end # return # # if range is not single celled, and one index is unbounded make it single celled # if not self._end: # self._end = self._start # if not self._start: # self._start = self._end # Check if unbound on different axes if ((self._start[0] and not self._start[1]) and (not self._end[0] and self._end[1])) or \ (not self._start[0] and self._start[1]) and (self._end[0] and not self._end[1]): self._start, self._end = Address(None, True), Address(None, True) raise InvalidArgumentValue('Invalid indexes set. Indexes should be unbounded at same axes.') # If one axes is unbounded on an index, make other index also unbounded on same axes if self._start[0] is None or self._end[0] is None: self._start[0], self._end[0] = None, None elif self._start[1] is None or self._end[1] is None: self._start[1], self._end[1] = None, None # verify # if (self._start[0] and not self._end[0]) or (not self._start[0] and self._end[0]) or \ # (self._start[1] and not self._end[1]) or (not self._start[1] and self._end[1]): # self._start, self._end = Address(None, True), Address(None, True) # raise InvalidArgumentValue('Invalid start and end set for this range') if self._start and self._end: if self._start[0]: assert self._start[0] <= self._end[0] if self._start[1]: assert self._start[1] <= self._end[1] self._calculate_label() def _calculate_label(self): """update label from values """ label = "'" + self.worksheet_title + "'" if self.worksheet_title else '' if self.start and self.end: label += "!" + self.start.label + ":" + self.end.label return label def _calculate_addresses(self, label): """ update values from label """ self._start, self._end = Address(None, True), Address(None, True) if len(label.split('!')) > 1: self.worksheet_title = label.split('!')[0] rem = label.split('!')[1] if ":" in rem: self._start = Address(rem.split(":")[0], allow_non_single=True) self._end = Address(rem.split(":")[1], allow_non_single=True) else: self._start = Address(rem, allow_non_single=True) elif self._worksheet: if ":" in label: self._start = Address(label.split(":")[0], allow_non_single=True) self._end = Address(label.split(":")[1], allow_non_single=True) else: self._start = Address(label, allow_non_single=True) else: pass self._apply_index_constraints()
[docs] def to_json(self): """ Get json representation of this grid range. """ if self.worksheet_id is None: raise Exception("worksheet id not set for this range.") return_dict = {"sheetId": self.worksheet_id} if self.start[0]: return_dict["startRowIndex"] = self.start[0] - 1 if self.start[1]: return_dict["startColumnIndex"] = self.start[1] - 1 if self.end[0]: return_dict["endRowIndex"] = self.end[0] if self.end[1]: return_dict["endColumnIndex"] = self.end[1] return return_dict
[docs] def set_json(self, namedjson): """ Apply a Gridrange json to this named range. :param namedjson: json object of the GridRange format Reference: `GridRange docs <>`__ """ if 'sheetId' in namedjson: self.worksheet_id = namedjson['sheetId'] start_row_idx = namedjson.get('startRowIndex', None) end_row_idx = namedjson.get('endRowIndex', None) start_col_idx = namedjson.get('startColumnIndex', None) end_col_idx = namedjson.get('endColumnIndex', None) start_row_idx = start_row_idx + 1 if start_row_idx is not None else start_row_idx start_col_idx = start_col_idx + 1 if start_col_idx is not None else start_col_idx self._start = Address((start_row_idx, start_col_idx), True) self._end = Address((end_row_idx, end_col_idx), True) self._calculate_label()
[docs] def get_bounded_indexes(self): """ get bounded indexes of this range based on worksheet size, if the indexes are unbounded """ start_r, start_c = tuple(iter(self.start)) if self.start else (None, None) end_r, end_c = tuple(iter(self.end)) if self.end else (None, None) start_r = start_r if start_r else 1 start_c = start_c if start_c else 1 if not self._worksheet and not (end_r or end_c): raise InvalidArgumentValue('Worksheet not set for calculating size.') end_r = end_r if end_r else self._worksheet.rows end_c = end_c if end_c else self._worksheet.cols return Address((start_r, start_c)), Address((end_r, end_c))
@property def height(self): """ Height of this gridrange """ start, end = self.get_bounded_indexes() return end[0] - start[0] + 1 @property def width(self): """ Width of this gridrange """ start, end = self.get_bounded_indexes() return end[1] - start[1] + 1 def contains(self, address): return self.start[0] <= address.row <= self.end[0] and self.start[1] <= address.col <= self.end[1] def __repr__(self): return '<%s %s>' % (self.__class__.__name__, str(self.label)) def __eq__(self, other): if isinstance(other, GridRange): return self.label == other.label elif type(other) is str: return self.label == other else: return super(GridRange, self).__eq__(other) def __ne__(self, other): return not self.__eq__(other) def __contains__(self, item): try: item = Address(item) except IncorrectCellLabel: raise InvalidArgumentValue("Gridrange can only contain an address") return self.contains(item) def __iter__(self): for r in range(self.start[0], self.end[0]+1): for c in range(self.start[1], self.end[1]+1): yield Address((r, c))