def hlookup(lookup_value, table_array, row_index_num, range_lookup=True):
""" Horizontal Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param row_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f
if not list_like(table_array):
return NA_ERROR
if list_like(lookup_value) or list_like(row_index_num):
raise NotImplementedError('Array Formulas not implemented')
if row_index_num <= 0:
return '#VALUE!'
if row_index_num > len(table_array[0]):
return '#REF!'
result_idx = match(lookup_value,
table_array[0],
match_type=bool(range_lookup))
if isinstance(result_idx, int):
return table_array[row_index_num - 1][result_idx - 1]
else:
# error string
return result_idx
def vlookup(lookup_value, table_array, col_index_num, range_lookup=True):
""" Vertical Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param col_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1
if not list_like(table_array):
return NA_ERROR
if list_like(lookup_value) or list_like(col_index_num):
raise NotImplementedError('Array Formulas not implemented')
if col_index_num <= 0:
return '#VALUE!'
if col_index_num > len(table_array[0]):
return '#REF!'
result_idx = match(lookup_value, [row[0] for row in table_array],
match_type=bool(range_lookup))
if isinstance(result_idx, int):
return table_array[result_idx - 1][col_index_num - 1]
else:
# error string
return result_idx
def lookup(lookup_value, lookup_array, result_range=None):
"""
There are two ways to use LOOKUP: Vector form and Array form
Vector form: lookup_array is list like (ie: n x 1)
Array form: lookup_array is rectangular (ie: n x m)
First row or column is the lookup vector.
Last row or column is the result vector
The longer dimension is the search dimension
:param lookup_value: value to match (value or cell reference)
:param lookup_array: range of cells being searched.
:param result_range: (optional vector form) values are returned from here
:return: #N/A if not found else value
"""
# Excel reference: https://support.microsoft.com/en-us/office/
# lookup-function-446d94af-663b-451d-8251-369d5e3864cb
if not list_like(lookup_array):
return NA_ERROR
height = len(lookup_array)
width = len(lookup_array[0])
# match across the largest dimension
if width <= height:
match_idx = _match(lookup_value, tuple(i[0] for i in lookup_array))
result = tuple(i[-1] for i in lookup_array)
else:
match_idx = _match(lookup_value, lookup_array[0])
result = lookup_array[-1]
if result_range is not None:
# if not a vector return NA
if not list_like(result_range):
return NA_ERROR
rr_height = len(result_range)
rr_width = len(result_range[0])
if rr_width < rr_height:
if rr_width != 1:
return NA_ERROR
result = tuple(i[0] for i in result_range)
else:
if rr_height != 1:
return NA_ERROR
result = result_range[0]
if isinstance(match_idx, int):
return result[match_idx - 1]
else:
# error string
return match_idx
def test_evaluate_entire_row_column(excel_compiler):
value = excel_compiler.evaluate(AddressRange('Sheet1!A:A'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:A18'))
assert value == expected
assert len(value) == 18
assert not list_like(value[0])
value = excel_compiler.evaluate(AddressRange('Sheet1!1:1'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D1'))
assert value == expected
assert len(value) == 4
assert not list_like(value[0])
value = excel_compiler.evaluate(AddressRange('Sheet1!A:B'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:B18'))
assert value == expected
assert len(value) == 18
assert len(value[0]) == 2
value = excel_compiler.evaluate(AddressRange('Sheet1!1:2'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D2'))
assert value == expected
assert len(value) == 2
assert len(value[0]) == 4
# now from the text based file
excel_compiler._to_text()
text_excel_compiler = ExcelCompiler._from_text(excel_compiler.filename)
value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:A'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:A18'))
assert value == expected
assert len(value) == 18
assert not list_like(value[0])
value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:1'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D1'))
assert value == expected
assert len(value) == 4
assert not list_like(value[0])
value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:B'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:B18'))
assert len(value) == 18
assert len(value[0]) == 2
assert value == expected
value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:2'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D2'))
assert value == expected
assert len(value) == 2
assert len(value[0]) == 4
def test_evaluate_entire_row_column(excel_compiler):
value = excel_compiler.evaluate(AddressRange('Sheet1!A:A'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:A18'))
assert value == expected
assert len(value) == 18
assert not list_like(value[0])
value = excel_compiler.evaluate(AddressRange('Sheet1!1:1'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D1'))
assert value == expected
assert len(value) == 4
assert not list_like(value[0])
value = excel_compiler.evaluate(AddressRange('Sheet1!A:B'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:B18'))
assert value == expected
assert len(value) == 18
assert len(value[0]) == 2
value = excel_compiler.evaluate(AddressRange('Sheet1!1:2'))
expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D2'))
assert value == expected
assert len(value) == 2
assert len(value[0]) == 4
# now from the text based file
excel_compiler._to_text()
text_excel_compiler = ExcelCompiler._from_text(excel_compiler.filename)
value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:A'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:A18'))
assert value == expected
assert len(value) == 18
assert not list_like(value[0])
value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:1'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D1'))
assert value == expected
assert len(value) == 4
assert not list_like(value[0])
value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:B'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:B18'))
assert len(value) == 18
assert len(value[0]) == 2
assert value == expected
value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:2'))
expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D2'))
assert value == expected
assert len(value) == 2
assert len(value[0]) == 4
def test_list_like(value, expected):
assert list_like(value) == expected
if expected:
assert_list_like(value)
else:
with pytest.raises(TypeError, match='Must be a list like: '):
assert_list_like(value)
def vlookup(lookup_value, table_array, col_index_num, range_lookup=True):
""" Vertical Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param col_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1
if not list_like(table_array):
return NA_ERROR
if col_index_num <= 0:
return '#VALUE!'
if col_index_num > len(table_array[0]):
return REF_ERROR
result_idx = _match(
lookup_value,
[row[0] for row in table_array],
match_type=bool(range_lookup)
)
if isinstance(result_idx, int):
return table_array[result_idx - 1][col_index_num - 1]
else:
# error string
return result_idx
def hlookup(lookup_value, table_array, row_index_num, range_lookup=True):
""" Horizontal Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param row_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f
if not list_like(table_array):
return NA_ERROR
if row_index_num <= 0:
return VALUE_ERROR
if row_index_num > len(table_array[0]):
return REF_ERROR
result_idx = _match(
lookup_value, table_array[0], match_type=bool(range_lookup))
if isinstance(result_idx, int):
return table_array[row_index_num - 1][result_idx - 1]
else:
# error string
return result_idx
def test_list_like(value, expected):
assert list_like(value) == expected
if expected:
assert_list_like(value)
else:
with pytest.raises(TypeError, match='Must be a list like: '):
assert_list_like(value)
def _evaluate(self, address):
"""Evaluate a single cell"""
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.value is None:
if isinstance(cell, _CellRange):
self._evaluate_range(cell.address.address)
elif cell.python_code:
if (self._max_iterations is not None and
cell.iterations > self._max_iterations):
cell.value = 0
else:
cell.iterations += 1
self.log.debug(
"Evaluating: {}, {}".format(cell.address, cell.python_code))
value = self.eval(cell.formula)
self.log.info("Cell %s evaluated to '%s' (%s)" % (
cell.address, value, type(value).__name__))
cell.value = VALUE_ERROR if list_like(value) else value
if isinstance(cell.value, AddressRange):
# If the cell returns a reference, then dereference
return self._evaluate(str(cell.value))
return cell.value
def _evaluate(self, address):
"""Evaluate a single cell"""
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.value is None:
if isinstance(cell, _CellRange):
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug("Evaluating: {}, {}".format(
cell.address, cell.python_code))
if self.eval is None:
self.eval = ExcelFormula.build_eval_context(
self._evaluate, self._evaluate_range, self.log)
value = self.eval(cell.formula)
self.log.info("Cell %s evaluated to '%s' (%s)" %
(cell.address, value, type(value).__name__))
cell.value = VALUE_ERROR if list_like(value) else value
if isinstance(cell.value, AddressRange):
# If the cell returns a reference, then dereference
return self._evaluate(str(cell.value))
return cell.value
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 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 countif(rng, criteria):
# Excel reference: https://support.office.com/en-us/article/
# COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
if not list_like(rng):
rng = ((rng, ), )
valid = find_corresponding_index(rng, criteria)
return len(valid)
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 sumifs(sum_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
if not list_like(sum_range):
sum_range = ((sum_range, ), )
return sum(
_numerics((sum_range[r][c] for r, c in handle_ifs(args, sum_range)),
keep_bools=True))
def index(array, row_num, col_num=None):
# Excel reference: https://support.office.com/en-us/article/
# index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
if not list_like(array):
if array in ERROR_CODES:
return array
else:
return VALUE_ERROR
if not list_like(array[0]):
return VALUE_ERROR
try:
# rectangular array
if row_num and col_num:
return array[row_num - 1][col_num - 1]
elif row_num:
if len(array[0]) == 1:
return array[row_num - 1][0]
elif len(array) == 1:
return array[0][row_num - 1]
elif isinstance(array, np.ndarray):
return array[row_num - 1, :]
else:
return (tuple(array[row_num - 1]), )
elif col_num:
if len(array) == 1:
return array[0][col_num - 1]
elif len(array[0]) == 1:
return array[col_num - 1][0]
elif isinstance(array, np.ndarray):
result = array[:, col_num - 1]
result.shape = result.shape + (1, )
return result
else:
return tuple((r[col_num - 1], ) for r in array)
except IndexError:
pass
return NA_ERROR
def lookup(lookup_value, lookup_array, result_range=None):
"""
There are two ways to use LOOKUP: Vector form and Array form
Vector form: lookup_array is list like (ie: n x 1)
Array form: lookup_array is rectangular (ie: n x m)
First row or column is the lookup vector.
Last row or column is the result vector
The longer dimension is the search dimension
:param lookup_value: value to match (value or cell reference)
:param lookup_array: range of cells being searched.
:param result_range: (optional vector form) values are returned from here
:return: #N/A if not found else value
"""
if not list_like(lookup_array):
return NA_ERROR
height = len(lookup_array)
if list_like(lookup_array[0]):
# rectangular array
assert result_range is None
width = len(lookup_array[0])
# match across the largest dimension
if width <= height:
match_idx = match(lookup_value, tuple(i[0] for i in lookup_array))
result_range = tuple(i[-1] for i in lookup_array)
else:
match_idx = match(lookup_value, lookup_array[0])
result_range = lookup_array[-1]
else:
match_idx = match(lookup_value, lookup_array)
result_range = result_range or lookup_array
if isinstance(match_idx, int):
return result_range[match_idx - 1]
else:
# error string
return match_idx
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):
"""Evaluate a single cell"""
if address not in self.cell_map:
# INDIRECT() and OFFSET() can produce addresses we don't already have loaded
self._gen_graph(address)
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.needs_calc:
if isinstance(cell, _CellRange) or cell.address.is_unbounded_range:
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug(f"Evaluating: {address}, {cell.python_code}")
value = self.eval(cell)
if is_address(value):
# eval produced an address (aka: a reference)
if value.is_range:
# complain as we are not going to do any spilling
self.log.warning(
f"Cell {address} evaluated to '{value}',"
f" truncating to '{value.start}'")
value = value.start
else:
self.log.info(
f"Cell {address} evaluated to address '{value}'")
# fetch the value for this cell, if it exists
ref_addr = value.address
if ref_addr not in self.cell_map and getattr(
self, 'excel', None):
# INDIRECT() can produce addresses we don't already have loaded
self._gen_graph(ref_addr)
value = self.cell_map[ref_addr].value
else:
self.log.info(
f"Cell {cell.address} evaluated to '{value}' ({type(value).__name__})"
)
cell.value = (value[0][0] if list_like(value[0]) else
value[0]) if list_like(value) else value
return cell.value
def averageifs(average_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690
if not list_like(average_range):
average_range = ((average_range, ), )
coords = handle_ifs(args, average_range)
data = _numerics((average_range[r][c] for r, c in coords), keep_bools=True)
if len(data) == 0:
return DIV0
return sum(data) / len(data)
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 minifs(min_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
if not list_like(min_range):
min_range = ((min_range, ), )
try:
return min(
_numerics(
(min_range[r][c] for r, c in handle_ifs(args, min_range)),
keep_bools=True))
except ValueError:
return 0
def maxifs(max_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
if not list_like(max_range):
max_range = ((max_range, ), )
try:
return max(
_numerics(
(max_range[r][c] for r, c in handle_ifs(args, max_range)),
keep_bools=True))
except ValueError:
return 0
def index(array, row_num, col_num=None):
# Excel reference: https://support.office.com/en-us/article/
# index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
if not list_like(array) or not list_like(array[0]):
return VALUE_ERROR
try:
# rectangular array
if row_num and col_num:
return array[row_num - 1][col_num - 1]
elif row_num:
if len(array[0]) == 1:
return array[row_num - 1][0]
elif len(array) == 1:
return array[0][row_num - 1]
elif isinstance(array, np.ndarray):
return array[row_num - 1, :]
else:
return (tuple(array[row_num - 1]),)
elif col_num:
if len(array) == 1:
return array[0][col_num - 1]
elif len(array[0]) == 1:
return array[col_num - 1][0]
elif isinstance(array, np.ndarray):
result = array[:, col_num - 1]
result.shape = result.shape + (1,)
return result
else:
return tuple((r[col_num - 1], ) for r in array)
except IndexError:
pass
return NA_ERROR
def sumifs(sum_range, *args):
# Excel reference: https://support.microsoft.com/en-us/office/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
if not list_like(sum_range):
sum_range = ((sum_range, ), )
coords = handle_ifs(args, sum_range)
# A returned string is an error code
if isinstance(coords, str):
return coords
return sum(_numerics((sum_range[r][c] for r, c in coords),
keep_bools=True))
def trend(Y, X=None, new_X=None, const=None):
# Excel reference: https://support.microsoft.com/en-us/office/
# trend-function-e2f135f0-8827-4096-9873-9a7cf7b51ef1
kwargs = {}
if const is not None:
kwargs['const'] = const
try:
coefs, full_rank = linest_helper(Y, X, **kwargs)
except AssertionError:
return REF_ERROR
except ValueError:
return VALUE_ERROR
if new_X is None:
if X is not None:
new_X = np.array(X)
else:
length = max(len(Y), len(Y[0]))
width = len(coefs) - 1
new_X = np.resize(np.repeat(np.arange(1, length + 1), width),
(length, width))
if list_like(new_X):
new_X = np.array(new_X)
if len(coefs) - 1 not in new_X.shape:
return REF_ERROR
if new_X.shape[1] != len(coefs) - 1:
if full_rank:
result = np.array(
coefs[-2::-1]).transpose() @ new_X + coefs[-1]
else:
result = (coefs[-1], ) * new_X.shape[1]
return result[0] if len(result) == 1 else (tuple(result), )
else:
if full_rank:
result = new_X @ np.array(coefs[-2::-1]) + coefs[-1]
else:
result = (coefs[-1], ) * new_X.shape[0]
return result[0] if len(result) == 1 else tuple(
(x, ) for x in result)
elif len(coefs) != 2:
# new_X is a scaler, this needs to be a single coef fit
return REF_ERROR
elif not full_rank:
return coefs[-1]
else:
return coefs[0] * new_X + coefs[1]
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 list_like(value):
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
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 index(array, row_num, col_num=None, rows=None, cols=None):
# Excel reference: https://support.office.com/en-us/article/
# index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
# A returned string is an error code
if isinstance(array, str):
return array
if row_num in ERROR_CODES:
return row_num
if col_num in ERROR_CODES:
return col_num
try:
if list_like(array[0]):
if rows or cols:
# when we get array formulas out of the worksheet we expand
# them into an index call. If we have rows and cols then
# this is the size of the original range. Excel will expand
# a vector to fill the rectangle.
if 1 == len(array) and row_num <= rows:
row_num = 1
if 1 == len(array[0]) and col_num <= cols:
col_num = 1
# rectangular array
if None not in (row_num, col_num):
return array[row_num - 1][col_num - 1]
elif row_num is not None:
return array[row_num - 1]
elif col_num is not None:
if isinstance(array, np.ndarray):
return array[:, col_num - 1]
else:
return type(array)(row[col_num - 1] for row in array)
elif col_num in (1, None):
return array[row_num - 1]
elif row_num == 1:
return array[col_num - 1]
except IndexError:
pass
return NA_ERROR
def averageifs(average_range, *args):
# Excel reference: https://support.microsoft.com/en-us/office/
# AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690
if not list_like(average_range):
average_range = ((average_range, ), )
coords = handle_ifs(args, average_range)
# A returned string is an error code
if isinstance(coords, str):
return coords
data = _numerics((average_range[r][c] for r, c in coords), keep_bools=True)
if len(data) == 0:
return DIV0
return sum(data) / len(data)
def lookup(lookup_value, lookup_array, result_range=None):
"""
There are two ways to use LOOKUP: Vector form and Array form
Vector form: lookup_array is list like (ie: n x 1)
Array form: lookup_array is rectangular (ie: n x m)
First row or column is the lookup vector.
Last row or column is the result vector
The longer dimension is the search dimension
:param lookup_value: value to match (value or cell reference)
:param lookup_array: range of cells being searched.
:param result_range: (optional vector form) values are returned from here
:return: #N/A if not found else value
"""
if not list_like(lookup_array):
return NA_ERROR
height = len(lookup_array)
width = len(lookup_array[0])
# match across the largest dimension
if width <= height:
match_idx = _match(lookup_value, tuple(i[0] for i in lookup_array))
result = tuple(i[-1] for i in lookup_array)
else:
match_idx = _match(lookup_value, lookup_array[0])
result = lookup_array[-1]
if len(lookup_array) > 1 and len(lookup_array[0]) > 1:
# rectangular array
assert result_range is None
elif result_range:
if len(result_range) > len(result_range[0]):
result = tuple(i[0] for i in result_range)
else:
result = result_range[0]
if isinstance(match_idx, int):
return result[match_idx - 1]
else:
# error string
return match_idx
def minifs(min_range, *args):
# Excel reference: https://support.microsoft.com/en-us/office/
# minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
if not list_like(min_range):
min_range = ((min_range, ), )
try:
coords = handle_ifs(args, min_range)
# A returned string is an error code
if isinstance(coords, str):
return coords
return min(
_numerics((min_range[r][c] for r, c in coords), keep_bools=True))
except ValueError:
return 0
def maxifs(max_range, *args):
# Excel reference: https://support.microsoft.com/en-us/office/
# maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
if not list_like(max_range):
max_range = ((max_range, ), )
try:
coords = handle_ifs(args, max_range)
# A returned string is an error code
if isinstance(coords, str):
return coords
return max(
_numerics((max_range[r][c] for r, c in coords), keep_bools=True))
except ValueError:
return 0
def _evaluate(self, address):
"""Evaluate a single cell"""
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.needs_calc:
if isinstance(cell, _CellRange) or cell.address.is_unbounded_range:
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug(
"Evaluating: {}, {}".format(cell.address, cell.python_code))
value = self.eval(cell)
self.log.info("Cell %s evaluated to '%s' (%s)" % (
cell.address, value, type(value).__name__))
cell.value = VALUE_ERROR if list_like(value) else value
return cell.value
def _evaluate(self, address):
"""Evaluate a single cell"""
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.value is None:
if isinstance(cell, _CellRange):
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug(
"Evaluating: {}, {}".format(cell.address, cell.python_code))
value = self.eval(cell.formula)
self.log.info("Cell %s evaluated to '%s' (%s)" % (
cell.address, value, type(value).__name__))
cell.value = VALUE_ERROR if list_like(value) else value
if isinstance(cell.value, AddressRange):
# If the cell returns a reference, then dereference
return self._evaluate(str(cell.value))
return cell.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 validate_calcs(self, output_addrs=None, sheet=None, verify_tree=True):
"""For each address, calc the value, and verify that it matches
This is a debugging tool which will show which cells evaluate
differently than they do for excel.
:param output_addrs: The cells to evaluate from (defaults to all)
:param sheet: The sheet to evaluate from (defaults to all)
:param verify_tree: Follow the tree to any precedent nodes
:return: dict of addresses with good/bad values that failed to verify
"""
def close_enough(val1, val2):
import pytest
if isinstance(val1, (int, float)) and \
isinstance(val2, (int, float)):
return val2 == pytest.approx(val1)
else:
return val1 == val2
Mismatch = collections.namedtuple('Mismatch', 'original calced formula')
if output_addrs is None:
to_verify = list(self.formula_cells(sheet))
print('Found {} formulas to evaluate'.format(len(to_verify)))
elif list_like(output_addrs):
to_verify = [AddressCell(addr) for addr in flatten(output_addrs)]
else:
to_verify = [AddressCell(output_addrs)]
verified = set()
failed = {}
while to_verify:
addr = to_verify.pop()
if len(to_verify) % 100 == 0:
print("{} formulas left to process".format(len(to_verify)))
try:
self._gen_graph(addr)
cell = self.cell_map[addr.address]
if isinstance(cell, _Cell) and cell.python_code:
original_value = cell.value
if original_value == str(cell.formula):
self.log.debug(
"No Orig data?: {}: {}".format(addr, cell.value))
continue
cell.value = None
self._evaluate(addr.address)
if not (original_value is None or
close_enough(original_value, cell.value)):
failed.setdefault('mismatch', {})[str(addr)] = Mismatch(
original_value, cell.value,
cell.formula.base_formula)
print('{} mismatch {} -> {} {}'.format(
addr, original_value, cell.value,
cell.formula.base_formula))
# do it again to allow easy breakpointing
cell.value = None
self._evaluate(cell.address.address)
verified.add(addr)
if verify_tree: # pragma: no branch
for addr in cell.needed_addresses:
if addr not in verified: # pragma: no branch
to_verify.append(addr)
except Exception as exc:
cell = self.cell_map.get(addr.address, None)
formula = cell and cell.formula.base_formula
exc_str = str(exc)
exc_str_split = exc_str.split('\n')
if 'is not implemented' in exc_str:
exc_str_key = exc_str.split('is not implemented')[0]
exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper()
not_implemented = True
else:
if len(exc_str_split) == 1:
exc_str_key = '{}: {}'.format(
type(exc).__name__, exc_str)
else:
exc_str_key = exc_str_split[-2] # pragma: no cover
not_implemented = exc_str_key.startswith(
'NotImplementedError: ')
if not_implemented:
failed.setdefault('not-implemented', {}).setdefault(
exc_str_key, []).append((str(addr), formula, exc_str))
else:
failed.setdefault('exceptions', {}).setdefault(
exc_str_key, []).append((str(addr), formula, exc_str))
return failed
def validate_calcs(self, output_addrs=None):
"""For each address, calc the value, and verify that it matches
This is a debugging tool which will show which cells evaluate
differently than they do for excel.
:param output_addrs: The cells to evaluate from (defaults to all)
:return: dict of addresses with good/bad values that failed to verify
"""
def close_enough(val1, val2):
import pytest
if isinstance(val1, (int, float)) and \
isinstance(val2, (int, float)):
return val2 == pytest.approx(val1)
else:
return val1 == val2
Mismatch = collections.namedtuple('Mismatch',
'original calced formula')
if output_addrs is None:
to_verify = self._formula_cells
elif list_like(output_addrs):
to_verify = [AddressCell(addr) for addr in flatten(output_addrs)]
else:
to_verify = [AddressCell(output_addrs)]
verified = set()
failed = {}
while to_verify:
addr = to_verify.pop()
try:
self._gen_graph(addr)
cell = self.cell_map[addr.address]
if isinstance(cell, _Cell) and cell.python_code:
original_value = cell.value
if original_value == str(cell.formula):
self.log.debug("No Orig data?: {}: {}".format(
addr, cell.value))
continue
cell.value = None
self._evaluate(addr.address)
# pragma: no branch
if not close_enough(original_value, cell.value):
failed.setdefault('mismatch',
{})[str(addr)] = Mismatch(
original_value, cell.value,
cell.formula.base_formula)
print('{} mismatch {} -> {} {}'.format(
addr, original_value, cell.value,
cell.formula.base_formula))
# do it again to allow easy breakpointing
cell.value = None
self._evaluate(cell.address.address)
verified.add(addr)
for addr in cell.needed_addresses:
if addr not in verified: # pragma: no branch
to_verify.append(addr)
except Exception as exc:
cell = self.cell_map.get(addr.address, None)
formula = cell and cell.formula.base_formula
exc_str = str(exc)
exc_str_split = exc_str.split('\n')
if 'has not been implemented' in exc_str:
exc_str_key = exc_str.split('has not been implemented')[0]
exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper()
not_implemented = True
else:
if len(exc_str_split) == 1:
exc_str_key = '{}: {}'.format(
type(exc).__name__, exc_str)
else:
exc_str_key = exc_str_split[-2] # pragma: no cover
not_implemented = exc_str_key.startswith(
'NotImplementedError: ')
if not_implemented:
failed.setdefault('not-implemented',
{}).setdefault(exc_str_key, []).append(
(str(addr), formula, exc_str))
else:
failed.setdefault('exceptions',
{}).setdefault(exc_str_key, []).append(
(str(addr), formula, exc_str))
return failed
def index(array, row_num, col_num=None):
# Excel reference: https://support.microsoft.com/en-us/office/
# index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
if not list_like(array):
if array in ERROR_CODES:
return array
else:
return VALUE_ERROR
if not list_like(array[0]):
return VALUE_ERROR
if is_address(array[0][0]):
assert len({a for a in flatten(array)}) == 1
_C_ = index.excel_func_meta['name_space']['_C_']
ref_addr = array[0][0].address_at_offset
else:
ref_addr = None
def array_data(row, col):
if ref_addr:
return _C_(ref_addr(row, col).address)
else:
return array[row][col]
try:
# rectangular array
if row_num and col_num:
if row_num < 0 or col_num < 0:
return VALUE_ERROR
else:
return array_data(row_num - 1, col_num - 1)
elif row_num:
if row_num < 0:
return VALUE_ERROR
elif len(array[0]) == 1:
return array_data(row_num - 1, 0)
elif len(array) == 1:
return array_data(0, row_num - 1)
elif isinstance(array, np.ndarray):
return array[row_num - 1, :]
else:
return (tuple(
array_data(row_num - 1, col)
for col in range(len(array[0]))), )
elif col_num:
if col_num < 0:
return VALUE_ERROR
elif len(array) == 1:
return array_data(0, col_num - 1)
elif len(array[0]) == 1:
return array_data(col_num - 1, 0)
elif isinstance(array, np.ndarray):
result = array[:, col_num - 1]
result.shape = result.shape + (1, )
return result
else:
return tuple((array_data(row, col_num - 1), )
for row in range(len(array)))
except IndexError:
return REF_ERROR
else:
return array
def validate_calcs(self,
output_addrs=None,
sheet=None,
verify_tree=True,
tolerance=None,
raise_exceptions=False):
"""For each address, calc the value, and verify that it matches
This is a debugging tool which will show which cells evaluate
differently than they do for excel.
:param output_addrs: The cells to evaluate from (defaults to all)
:param sheet: The sheet to evaluate from (defaults to all)
:param verify_tree: Follow the tree to any precedent nodes
:return: dict of addresses with good/bad values that failed to verify
"""
if output_addrs is None:
to_verify = list(self.formula_cells(sheet))
print(f'Found {len(to_verify)} formulas to evaluate')
elif list_like(output_addrs):
to_verify = [AddressCell(addr) for addr in flatten(output_addrs)]
else:
to_verify = [AddressCell(output_addrs)]
verified = set()
failed = {}
if self.cycles:
iterative_eval_tracker(**self.cycles)
while to_verify:
addr = to_verify.pop()
if len(to_verify) % 100 == 0:
print(f"{len(to_verify)} formulas left to process")
try:
self._gen_graph(addr)
cell = self.cell_map[addr.address]
if isinstance(cell, _Cell) and cell.python_code and (
not cell.address.is_unbounded_range):
original_value = cell.value
if original_value == str(cell.formula):
self.log.debug(f"No Orig data?: {addr}: {cell.value}")
continue
cell.value = None
self.evaluate(addr.address)
if not (original_value is None or cell.close_enough(
original_value, tol=tolerance)):
failed.setdefault('mismatch',
{})[str(addr)] = Mismatch(
original_value, cell.value,
cell.formula.base_formula)
print('{} mismatch {} -> {} {}'.format(
addr, original_value, cell.value,
cell.formula.base_formula))
# do it again to allow easy break-pointing
cell.value = None
self.evaluate(cell.address.address)
verified.add(addr)
if verify_tree: # pragma: no branch
for addr in cell.needed_addresses:
if addr not in verified: # pragma: no branch
to_verify.append(addr)
except Exception as exc:
if raise_exceptions:
raise
cell = self.cell_map.get(addr.address, None)
formula = cell and cell.formula.base_formula
exc_str = str(exc)
exc_str_split = exc_str.split('\n')
if 'is not implemented' in exc_str:
exc_str_key = exc_str.split('is not implemented')[0]
exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper()
not_implemented = True
else:
if len(exc_str_split) == 1:
exc_str_key = f'{type(exc).__name__}: {exc_str}'
else:
exc_str_key = exc_str_split[-2] # pragma: no cover
not_implemented = exc_str_key.startswith(
'NotImplementedError: ')
if not_implemented:
failed.setdefault('not-implemented',
{}).setdefault(exc_str_key, []).append(
(str(addr), formula, exc_str))
else:
failed.setdefault('exceptions',
{}).setdefault(exc_str_key, []).append(
(str(addr), formula, exc_str))
return failed
