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