def __init__(self, formula, origin):
# Excel errors out when a workbook has formulae in R1C1 notation,
# regardless of the calcPr:refMode setting, so I'm assuming the
# formulae stored in the workbook must be in A1 notation.
col, self.row = coordinate_from_string(origin)
self.col = column_index_from_string(col)
self.tokenizer = Tokenizer(formula)
def translate_formula(self, dest):
"""
Convert the formula into A1 notation.
The formula is converted into A1 assuming it is assigned to the cell
whose address is `dest` (no worksheet name).
"""
tokens = self.get_tokens()
if not tokens:
return ""
elif tokens[0].type == Token.LITERAL:
return tokens[0].value
out = ['=']
# per the spec:
# A compliant producer or consumer considers a defined name in the
# range A1-XFD1048576 to be an error. All other names outside this
# range can be defined as names and overrides a cell reference if an
# ambiguity exists. (I.18.2.5)
dcol, drow = coordinate_from_string(dest)
dcol = column_index_from_string(dcol)
row_delta = drow - self.row
col_delta = dcol - self.col
for token in tokens:
if token.type == Token.OPERAND and token.subtype == Token.RANGE:
out.append(self.translate_range(token.value, row_delta,
col_delta))
else:
out.append(token.value)
return "".join(out)
def add_comment_shape(self, root, idx, coord):
col, row = coordinate_from_string(coord)
row -= 1
column = column_index_from_string(col) - 1
shape = _shape_factory(row, column)
shape.set('id', "_x0000_s%04d" % idx)
root.append(shape)
def translate_col(col_str, cdelta):
"""
Translate a range col-snippet by the given number of columns
"""
if col_str.startswith('$'):
return col_str
else:
try:
return get_column_letter(
column_index_from_string(col_str) + cdelta)
except ValueError:
raise TranslatorError("Formula out of range")
def append(self, iterable):
"""Appends a group of values at the bottom of the current sheet.
* If it's a list: all values are added in order, starting from the first column
* If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)
:param iterable: list, range or generator, or dict containing values to append
:type iterable: list|tuple|range|generator or dict
Usage:
* append(['This is A1', 'This is B1', 'This is C1'])
* **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
* **or** append({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self._current_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
if cell.parent and cell.parent != self:
raise ValueError(
"Cells cannot be copied from other worksheets")
cell.parent = self
cell.col_idx = col_idx
cell.row = row_idx
else:
cell = Cell(self,
row=row_idx,
col_idx=col_idx,
value=content)
self._cells[(row_idx, col_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iterable.items():
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
else:
self._invalid_row(iterable)
self._current_row = row_idx
def __init__(self,
worksheet,
column=None,
row=None,
value=None,
col_idx=None,
style_array=None):
super(Cell, self).__init__(worksheet, style_array)
self.row = row
"""Row number of this cell (1-based)"""
# _value is the stored value, while value is the displayed value
self._value = None
self._hyperlink = None
self.data_type = 'n'
if value is not None:
self.value = value
self._comment = None
if column is not None:
col_idx = column_index_from_string(column)
self.col_idx = col_idx
"""Column number of this cell (1-based)"""
def reindex(self):
"""
Set boundaries for column definition
"""
if not all([self.min, self.max]):
self.min = self.max = column_index_from_string(self.index)
def test_bad_column_index(column):
with pytest.raises(ValueError):
column_index_from_string(column)
def test_column_index(column, idx):
assert column_index_from_string(column) == idx