def init_tracker():
# init the tracker
iterative_eval_tracker(iterations=100, tolerance=0.001)
assert iterative_eval_tracker.ns.iteration_number == 0
assert iterative_eval_tracker.ns.iterations == 100
assert iterative_eval_tracker.ns.tolerance == 0.001
assert iterative_eval_tracker.tolerance == 0.001
assert iterative_eval_tracker.done
def _evaluate_iterative(self, address, iterations=None, tolerance=None):
""" evaluate a cell or cells in a spreadsheet with cycles
reference: https://support.microsoft.com/en-us/office/
8540bd0f-6e97-4483-bcf7-1b49cd50d123
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:param iterations: maximum number of iterations to compute. If not
specified use the value from the workbook.
:param tolerance: maximum change, if any calculated value changes by
more than this, another iteration will be performed. If not
specified use the value from the workbook.
:return: evaluated value/values
"""
iterations = iterations or self.cycles['iterations'] or 10000
tolerance = tolerance or self.cycles['tolerance'] or 0.01
progress_tracker = iterative_eval_tracker(iterations, tolerance)
while True:
progress_tracker.inc_iteration_number()
results = self._evaluate_non_iterative(address)
if progress_tracker.done:
return results
def test_iterative_eval_tracker():
assert isinstance(iterative_eval_tracker.ns.todo, set)
# init the tracker
iterative_eval_tracker(iterations=100, tolerance=0.001)
assert iterative_eval_tracker.ns.iteration_number == 0
assert iterative_eval_tracker.ns.iterations == 100
assert iterative_eval_tracker.ns.tolerance == 0.001
assert iterative_eval_tracker.tolerance == 0.001
assert iterative_eval_tracker.done
# test done if no WIP
iterative_eval_tracker.wip(1)
assert iterative_eval_tracker.ns.todo == {1}
assert not iterative_eval_tracker.done
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.done
# init the tracker
iterative_eval_tracker(iterations=2, tolerance=5)
assert iterative_eval_tracker.ns.iteration_number == 0
assert iterative_eval_tracker.ns.iterations == 2
assert iterative_eval_tracker.ns.tolerance == 5
# test done if max iters exceeded
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.ns.iteration_number == 1
iterative_eval_tracker.wip(1)
assert not iterative_eval_tracker.done
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.ns.iteration_number == 2
iterative_eval_tracker.wip(1)
assert iterative_eval_tracker.done
# check calced / iscalced
assert not iterative_eval_tracker.is_calced(1)
iterative_eval_tracker.calced(1)
assert iterative_eval_tracker.is_calced(1)
iterative_eval_tracker.inc_iteration_number()
assert not iterative_eval_tracker.is_calced(1)
def _evaluate_iterative(self, address, iterations=100, tolerance=0.001):
""" evaluate a cell or cells in a spreadsheet with cycles
reference: https://support.office.com/en-us/article/
8540bd0f-6e97-4483-bcf7-1b49cd50d123
:param address: str, AddressRange, AddressCell or a tuple or list
or iterable of these three
:param iterations: maximum number of iterations to compute
:param tolerance: maximum change, if any calculated value changes by
more than this, another iteration will be performed
:return: evaluated value/values
"""
progress_tracker = iterative_eval_tracker(iterations, tolerance)
while True:
progress_tracker.inc_iteration_number()
results = self._evaluate_non_iterative(address)
if progress_tracker.done:
return results
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
def test_iterative_eval_tracker():
assert isinstance(iterative_eval_tracker.ns.todo, set)
def init_tracker():
# init the tracker
iterative_eval_tracker(iterations=100, tolerance=0.001)
assert iterative_eval_tracker.ns.iteration_number == 0
assert iterative_eval_tracker.ns.iterations == 100
assert iterative_eval_tracker.ns.tolerance == 0.001
assert iterative_eval_tracker.tolerance == 0.001
assert iterative_eval_tracker.done
def do_test_tracker():
# test done if no WIP
iterative_eval_tracker.wip(1)
assert iterative_eval_tracker.ns.todo == {1}
assert not iterative_eval_tracker.done
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.done
init_tracker()
do_test_tracker()
# init the tracker
iterative_eval_tracker(iterations=2, tolerance=5)
assert iterative_eval_tracker.ns.iteration_number == 0
assert iterative_eval_tracker.ns.iterations == 2
assert iterative_eval_tracker.ns.tolerance == 5
# test done if max iters exceeded
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.ns.iteration_number == 1
iterative_eval_tracker.wip(1)
assert not iterative_eval_tracker.done
iterative_eval_tracker.inc_iteration_number()
assert iterative_eval_tracker.ns.iteration_number == 2
iterative_eval_tracker.wip(1)
assert iterative_eval_tracker.done
# check calced / iscalced
assert not iterative_eval_tracker.is_calced(1)
iterative_eval_tracker.calced(1)
assert iterative_eval_tracker.is_calced(1)
iterative_eval_tracker.inc_iteration_number()
assert not iterative_eval_tracker.is_calced(1)
class AThread(threading.Thread):
def run(self):
try:
init_tracker()
import time
time.sleep(0.1)
do_test_tracker()
self.result = True
except: # noqa: E722
self.result = False
thread = AThread()
thread.start()
init_tracker()
do_test_tracker()
thread.join()
assert thread.result