def _emit(self, value=None):
# resolve the range into cells
sheet = self.cell and self.cell.sheet or ''
value = value is not None and value or self.value
if '!' in value:
sheet = ''
try:
addr_str = value.replace('$', '')
address = AddressRange.create(addr_str,
sheet=sheet,
cell=self.cell)
except ValueError:
# check for table relative address
table_name = None
if self.cell:
excel = self.cell.excel
if excel and '[' in addr_str:
table_name = excel.table_name_containing(self.cell.address)
if not table_name:
logging.getLogger('pycel').warning(
'Table Name not found: {}'.format(addr_str))
return '"{}"'.format(NAME_ERROR)
addr_str = '{}{}'.format(table_name, addr_str)
address = AddressRange.create(addr_str,
sheet=self.cell.address.sheet,
cell=self.cell)
if isinstance(address, AddressMultiAreaRange):
return ', '.join(self._emit(value=str(addr)) for addr in address)
else:
template = '_R_("{}")' if address.is_range else '_C_("{}")'
return template.format(address)
def emit(self):
# resolve the range into cells
sheet = self.cell and self.cell.sheet or ''
if '!' in self.value:
sheet = ''
try:
addr_str = self.value.replace('$', '')
address = AddressRange.create(addr_str,
sheet=sheet,
cell=self.cell)
except ValueError:
# check for table relative address
table_name = None
if self.cell:
excel = self.cell.excel
if excel and '[' in addr_str:
table_name = excel.table_name_containing(self.cell.address)
if not table_name:
return '"{}"'.format(NAME_ERROR)
addr_str = '{}{}'.format(table_name, addr_str)
address = AddressRange.create(addr_str,
sheet=self.cell.address.sheet,
cell=self.cell)
template = '_R_("{}")' if address.is_range else '_C_("{}")'
return template.format(address)
def emit(self):
# resolve the range into cells
sheet = self.cell and self.cell.sheet or ''
if '!' in self.value:
sheet = ''
try:
addr_str = self.value.replace('$', '')
address = AddressRange.create(addr_str, sheet=sheet, cell=self.cell)
except ValueError:
# check for table relative address
table_name = None
if self.cell:
excel = self.cell.excel
if excel and '[' in addr_str:
table_name = excel.table_name_containing(self.cell.address)
if not table_name:
logging.getLogger('pycel').warning(
'Table Name not found: {}'.format(addr_str))
return '"{}"'.format(NAME_ERROR)
addr_str = '{}{}'.format(table_name, addr_str)
address = AddressRange.create(
addr_str, sheet=self.cell.address.sheet, cell=self.cell)
template = '_R_("{}")' if address.is_range else '_C_("{}")'
return template.format(address)
def indirect(ref_text, a1=True, sheet=''):
# Excel reference: https://support.microsoft.com/en-us/office/
# indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261
try:
address = AddressRange.create(ref_text)
except ValueError:
return REF_ERROR
if address.row > MAX_ROW or address.col_idx > MAX_COL:
return REF_ERROR
if not address.has_sheet:
address = AddressRange.create(address, sheet=sheet)
return address
def test_address_range():
a = AddressRange('a1:b2')
b = AddressRange('A1:B2')
c = AddressRange(a)
assert a == b
assert b == c
assert b == AddressRange(b)
assert b == AddressRange.create(b)
assert AddressRange('sh!a1:b2') == AddressRange(a, sheet='sh')
assert AddressCell('C13') == AddressCell('R13C3')
with pytest.raises(ValueError):
AddressRange(AddressRange('sh!a1:b2'), sheet='sheet')
a = AddressRange('A:A')
assert 'A' == a.start.column
assert 'A' == a.end.column
assert 0 == a.start.row
assert 0 == a.end.row
b = AddressRange('1:1')
assert '' == b.start.column
assert '' == b.end.column
assert 1 == b.start.row
assert 1 == b.end.row
def evaluate(self, address):
""" evaluate a cell or cells in the spreadsheet
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:return: evaluted value/values
"""
try:
not_in_cell_map = address not in self.cell_map
except TypeError:
not_in_cell_map = True
if not_in_cell_map:
if (not isinstance(address, (str, AddressRange, AddressCell))
and isinstance(address, collections.Iterable)):
if not isinstance(address, (tuple, list)):
address = tuple(address)
# process a tuple or list of addresses
return type(address)(self.evaluate(c) for c in address)
address = AddressRange.create(address).address
if address not in self.cell_map:
self._gen_graph(address)
return self._evaluate(address)
def set_value(self, address, value):
""" Set the value of one or more cells or ranges
:param address: `str`, `AddressRange`, `AddressCell` or a tuple, list
or an iterable of these three
:param value: value to set. This can be a value or a tuple/list
which matches the shapes needed for the given address/addresses
"""
if (not isinstance(address, (AddressRange, AddressCell))
and isinstance(address, (tuple, list))):
assert isinstance(value, (tuple, list))
assert len(address) == len(value)
for addr, val in zip(address, value):
self.set_value(addr, val)
return
elif address not in self.cell_map:
address = AddressRange.create(address).address
assert address in self.cell_map
cell_or_range = self.cell_map[address]
if cell_or_range.value != value: # pragma: no branch
# need to be able to 'set' an empty cell
if cell_or_range.value is None:
cell_or_range.value = value
# reset the node + its dependencies
self._reset(cell_or_range)
# set the value
cell_or_range.value = value
def offset(reference, row_inc, col_inc, height=None, width=None):
# Excel reference: https://support.microsoft.com/en-us/office/
# offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66
"""
Returns a reference to a range that is a specified number of rows and
columns from a cell or range of cells.
"""
base_addr = AddressRange.create(reference)
if height is None:
height = base_addr.size.height
if width is None:
width = base_addr.size.width
new_row = base_addr.row + row_inc
end_row = new_row + height - 1
new_col = base_addr.col_idx + col_inc
end_col = new_col + width - 1
if new_row <= 0 or end_row > MAX_ROW or new_col <= 0 or end_col > MAX_COL:
return REF_ERROR
top_left = AddressCell((new_col, new_row, new_col, new_row),
sheet=base_addr.sheet)
if height == width == 1:
return top_left
else:
bottom_right = AddressCell((end_col, end_row, end_col, end_row),
sheet=base_addr.sheet)
return AddressRange(f'{top_left.coordinate}:{bottom_right.coordinate}',
sheet=top_left.sheet)
def evaluate(self, address):
""" evaluate a cell or cells in the spreadsheet
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:return: evaluated value/values
"""
if str(address) not in self.cell_map:
if list_like(address):
if not isinstance(address, (tuple, list)):
address = tuple(address)
# process a tuple or list of addresses
return type(address)(self.evaluate(c) for c in address)
address = AddressRange.create(address)
# get the sheet if not specified
if not address.has_sheet:
address = AddressRange(
address, sheet=self.excel.get_active_sheet_name())
if address.address not in self.cell_map:
self._gen_graph(address.address)
return self._evaluate(str(address))
def test_address_range():
a = AddressRange('a1:b2')
b = AddressRange('A1:B2')
c = AddressRange(a)
assert a == b
assert b == c
assert b == AddressRange(b)
assert b == AddressRange.create(b)
assert AddressRange('sh!a1:b2') == AddressRange(a, sheet='sh')
assert AddressCell('C13') == AddressCell('R13C3')
with pytest.raises(ValueError):
AddressRange(AddressRange('sh!a1:b2'), sheet='sheet')
a = AddressRange('A:A')
assert 'A' == a.start.column
assert 'A' == a.end.column
assert 0 == a.start.row
assert 0 == a.end.row
b = AddressRange('1:1')
assert '' == b.start.column
assert '' == b.end.column
assert 1 == b.start.row
assert 1 == b.end.row
def evaluate(self, address):
""" evaluate a cell or cells in the spreadsheet
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:return: evaluated value/values
"""
if str(address) not in self.cell_map:
if list_like(address):
if not isinstance(address, (tuple, list)):
address = tuple(address)
# process a tuple or list of addresses
return type(address)(self.evaluate(c) for c in address)
address = AddressRange.create(address)
# get the sheet if not specified
if not address.has_sheet:
address = AddressRange(
address, sheet=self.excel.get_active_sheet_name())
if address.address not in self.cell_map:
self._gen_graph(address.address)
result = self._evaluate(str(address))
if isinstance(result, tuple):
# trim excess dimensions
if len(result[0]) == 1:
result = tuple(row[0] for row in result)
if len(result) == 1:
result = result[0]
return result
def test_offset(crwh, refer, rows, cols, height, width):
expected = crwh
if isinstance(crwh, tuple):
start = AddressCell((crwh[0], crwh[1], crwh[0], crwh[1]))
end = AddressCell((crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1,
crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1))
expected = AddressRange.create(f'{start.coordinate}:{end.coordinate}')
result = offset(refer, rows, cols, height, width)
assert result == expected
refer_addr = AddressRange.create(refer)
if height == refer_addr.size.height:
height = None
if width == refer_addr.size.width:
width = None
assert offset(refer_addr, rows, cols, height, width) == expected
def emit(self):
# resolve the range into cells
sheet = self.cell and self.cell.sheet or ''
if '!' in self.value:
sheet = ''
address = AddressRange.create(
self.value.replace('$', ''), sheet=sheet, cell=self.cell)
template = '_R_("{}")' if address.is_range else '_C_("{}")'
return template.format(address)
def indirect(ref_text, a1=True):
# Excel reference: https://support.office.com/en-us/article/
# indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261
try:
address = AddressRange.create(ref_text)
except ValueError:
return REF_ERROR
if address.row > MAX_ROW or address.col_idx > MAX_COL:
return REF_ERROR
return address
def test_row(address, expected):
try:
address = AddressRange.create(address)
except ValueError:
pass
result = row(address)
if expected is None:
assert 1 == next(iter(result))
else:
assert expected == result
def test_row(address, expected):
try:
address = AddressRange.create(address)
except ValueError:
pass
result = row(address)
if expected is None:
assert 1 == next(iter(result))
else:
assert expected == result
def eval_conditional_formats(self, address):
"""Evaluate the conditional format (formulas) for a cell or cells
returns the conditional format id which is the key for the dict:
ExcelCompiler.conditional_formats
NOTE: conditional_formats are not saved in the persistent formats.
If needed they can be hand serialized into "extra_data"
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:return: evaluated objects ids
"""
if list_like(address):
if not isinstance(address, (tuple, list)):
address = tuple(address)
# process a tuple or list of addresses
return type(address)(self.eval_conditional_formats(c)
for c in address)
address = AddressRange.create(address)
# get the sheet if not specified
if not address.has_sheet:
address = AddressRange(address,
sheet=self.excel.get_active_sheet_name())
if address.is_range:
return tuple(
tuple(self.eval_conditional_formats(addr) for addr in row)
for row in address.rows)
cf_addr = str(address).replace('!', '.cf!')
if cf_addr not in self.cell_map:
phony_cell = _Cell(address)
formats = self.excel.conditional_format(address)
format_strs = []
for f in formats:
excel_formula = ExcelFormula(f.formula, cell=phony_cell)
python_code = excel_formula.python_code
format_strs.append(
f'({python_code}, {f.dxf_id}, {int(bool(f.stop_if_true))})'
)
self.conditional_formats[f.dxf_id] = f.dxf
python_code = f"=conditional_format_ids({', '.join(format_strs)})"
a_cell = _Cell(address, formula=python_code)
self.cell_map[cf_addr] = a_cell
self._gen_graph(a_cell.formula.needed_addresses)
return self.eval(self.cell_map[cf_addr])
def test_multi_area_ranges(excel, ATestCell):
cell = ATestCell('A', 1, excel=excel)
from unittest import mock
with mock.patch.object(excel, '_defined_names',
{'dname': (('$A$1', 's1'), ('$A$3:$A$4', 's2'))}):
multi_area_range = AddressMultiAreaRange(
tuple(AddressRange(addr, sheet=sh))
for addr, sh in excel._defined_names['dname'])
assert (multi_area_range, None) == range_boundaries('dname', cell)
assert multi_area_range == AddressRange.create('dname', cell=cell)
def set_value(self, address, value, set_as_range=False):
""" Set the value of one or more cells or ranges
:param address: `str`, `AddressRange`, `AddressCell` or a tuple, list
or an iterable of these three
:param value: value to set. This can be a value or a tuple/list
which matches the shapes needed for the given address/addresses
:param set_as_range: With a single range address and a list like value,
set to true to set the entire rnage to the inserted list.
"""
if list_like(value) and not set_as_range:
value = tuple(flatten(value))
if list_like(address):
address = (AddressCell(addr) for addr in flatten(address))
else:
address = flatten(AddressRange(address).resolve_range)
address = tuple(address)
assert len(address) == len(value)
for addr, val in zip(address, value):
self.set_value(addr, val)
return
elif address not in self.cell_map:
address = AddressRange.create(address).address
assert address in self.cell_map, (
f'Address "{address}" not found in the cell map. Evaluate the '
'address, or an address that references it, to place it in the cell map.'
)
if set_as_range and list_like(value) and not (value
and list_like(value[0])):
value = (value, )
cell_or_range = self.cell_map[address]
if cell_or_range.value != value: # pragma: no branch
# need to be able to 'set' an empty cell, set to not None
cell_or_range.value = value
# reset the node + its dependencies
if not self.cycles:
self._reset(cell_or_range)
# set the value
cell_or_range.value = value
def set_value(self, address, value, set_as_range=False):
""" Set the value of one or more cells or ranges
:param address: `str`, `AddressRange`, `AddressCell` or a tuple, list
or an iterable of these three
:param value: value to set. This can be a value or a tuple/list
which matches the shapes needed for the given address/addresses
:param set_as_range: With a single range address and a list like value,
set to true to set the entire rnage to the inserted list.
"""
if list_like(value) and not set_as_range:
value = tuple(flatten(value))
if list_like(address):
address = (AddressCell(addr) for addr in flatten(address))
else:
address = flatten(AddressRange(address).resolve_range)
address = tuple(address)
assert len(address) == len(value)
for addr, val in zip(address, value):
self.set_value(addr, val)
return
elif address not in self.cell_map:
address = AddressRange.create(address).address
assert address in self.cell_map
if set_as_range and list_like(value) and not (
value and list_like(value[0])):
value = (value, )
cell_or_range = self.cell_map[address]
if cell_or_range.value != value: # pragma: no branch
# need to be able to 'set' an empty cell
if cell_or_range.value is None:
cell_or_range.value = value
# reset the node + its dependencies
self._reset(cell_or_range)
# set the value
cell_or_range.value = value
def evaluate(self, address, _recursed=False):
""" evaluate a cell or cells in the spreadsheet
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:return: evaluated value/values
"""
if str(address) not in self.cell_map:
if list_like(address):
if not isinstance(address, (tuple, list)):
address = tuple(address)
# process a tuple or list of addresses
return type(address)(self.evaluate(c, True) for c in address)
address = AddressRange.create(address)
# get the sheet if not specified
if not address.has_sheet:
address = AddressRange(
address, sheet=self.excel.get_active_sheet_name())
if address.address not in self.cell_map:
self._gen_graph(address.address)
if not _recursed:
for cell in self.cell_map.values():
if isinstance(cell, _CellRange) or cell.formula:
cell.iterations = 0
result = self._evaluate(str(address))
if isinstance(result, tuple):
# trim excess dimensions
if len(result[0]) == 1:
result = tuple(row[0] for row in result)
if len(result) == 1:
result = result[0]
return result
def test_column(address, result):
try:
address = AddressRange.create(address)
except ValueError:
pass
assert result == column(address)
def get_formula_from_range(self, address):
addr = AddressRange.create(address)
found = addr.column in self.columns and str(addr.row) in self.rows
return '=linest()' if found else ''
def get_formula_from_range(self, address):
addr = AddressRange.create(address)
found = addr.column in self.columns and str(addr.row) in self.rows
return '=linest()' if found else ''
def test_address_absolute(address, expected):
assert AddressRange.create(address).abs_address == expected