from __future__ import absolute_import, print_function # cython: profile=True from koala.Range import get_cell_address, parse_cell_address from koala.ast import * from koala.reader import read_archive, read_named_ranges, read_cells # This import equivalent functions defined in Excel. from koala.excellib import * from openpyxl.formula.translate import Translator from koala.serializer import * from koala.tokenizer import reverse_rpn from koala.utils import * import warnings import os.path import networkx from networkx.readwrite import json_graph from openpyxl.compat import unicode class Spreadsheet(object): def __init__(self, file=None, ignore_sheets=[], ignore_hidden=False, debug=False): # print("___### Initializing Excel Compiler ###___") if file is None: # create empty version of this object self.cells = None # precursor for cellmap: dict that link addresses (str) to Cell objects. self.named_ranges = {} self.pointers = set() # set listing the pointers self.debug = None # boolean seeds = [] cellmap, G = graph_from_seeds(seeds, self) self.G = G # DiGraph object that represents the view of the Spreadsheet self.cellmap = cellmap # dict that link addresses (str) to Cell objects. self.addr_to_name = None self.addr_to_range = None self.outputs = None self.inputs = None self.save_history = None self.history = None self.count = None self.range = RangeFactory(cellmap) self.pointer_to_remove = None self.pointers_to_reset = set() self.reset_buffer = None self.fixed_cells = {} else: # fill in what the ExcelCompiler used to do super(Spreadsheet, self).__init__() # generate an empty spreadsheet # Decompose subfiles structure in zip file if hasattr(file, 'read'): # file-like object archive = read_archive(file) else: # assume file path archive = read_archive(os.path.abspath(file)) # Parse cells self.cells = read_cells(archive, ignore_sheets, ignore_hidden) # Parse named_range { name (ExampleName) -> address (Sheet!A1:A10)} self.named_ranges = read_named_ranges(archive) self.range = RangeFactory(self.cells) self.pointers = set() self.debug = debug # now add the stuff what was originally done by the Spreadsheet self.gen_graph() def clean_pointer(self): spreadsheet = Spreadsheet() sp = spreadsheet.build_spreadsheet(networkx.DiGraph(),self.cells, self.named_ranges, debug = self.debug) cleaned_cells, cleaned_ranged_names = sp.clean_pointer() self.cells = cleaned_cells self.named_ranges = cleaned_ranged_names self.pointers = set() def gen_graph(self, outputs=[], inputs=[]): """ Generate the contents of the Spreadsheet from the read cells in the binary files. Specifically this function generates the graph. :param outputs: can be used to specify the outputs. All not affected cells are removed from the graph. :param inputs: can be used to specify the inputs. All not affected cells are removed from the graph. """ # print('___### Generating Graph ###___') if len(outputs) == 0: preseeds = set(list(flatten(self.cells.keys())) + list(self.named_ranges.keys())) # to have unicity else: preseeds = set(outputs) preseeds = list(preseeds) # to be able to modify the list seeds = [] for o in preseeds: if o in self.named_ranges: reference = self.named_ranges[o] if is_range(reference): if 'OFFSET' in reference or 'INDEX' in reference: start_end = prepare_pointer(reference, self.named_ranges) rng = self.range(start_end) self.pointers.add(o) else: rng = self.range(reference) for address in rng.addresses: # this is avoid pruning deletion preseeds.append(address) virtual_cell = Cell(o, None, value = rng, formula = reference, is_range = True, is_named_range = True ) seeds.append(virtual_cell) else: # might need to be changed to actual cells Cell, not a copy if 'OFFSET' in reference or 'INDEX' in reference: self.pointers.add(o) value = self.cells[reference].value if reference in self.cells else None virtual_cell = Cell(o, None, value = value, formula = reference, is_range = False, is_named_range = True) seeds.append(virtual_cell) else: if is_range(o): rng = self.range(o) for address in rng.addresses: # this is avoid pruning deletion preseeds.append(address) virtual_cell = Cell(o, None, value = rng, formula = o, is_range = True, is_named_range = True ) seeds.append(virtual_cell) else: seeds.append(self.cells[o]) seeds = set(seeds) # print("Seeds %s cells" % len(seeds)) outputs = set(preseeds) if len(outputs) > 0 else [] # seeds and outputs are the same when you don't specify outputs cellmap, G = graph_from_seeds(seeds, self) if len(inputs) != 0: # otherwise, we'll set inputs to cellmap inside Spreadsheet inputs = list(set(inputs)) # add inputs that are outside of calculation chain for i in inputs: if i not in cellmap: if i in self.named_ranges: reference = self.named_ranges[i] if is_range(reference): rng = self.range(reference) for address in rng.addresses: # this is avoid pruning deletion inputs.append(address) virtual_cell = Cell(i, None, value = rng, formula = reference, is_range = True, is_named_range = True ) cellmap[i] = virtual_cell G.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: # might need to be changed to actual cells Cell, not a copy virtual_cell = Cell(i, None, value = self.cells[reference].value, formula = reference, is_range = False, is_named_range = True) cellmap[i] = virtual_cell G.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: if is_range(i): rng = self.range(i) for address in rng.addresses: # this is avoid pruning deletion inputs.append(address) virtual_cell = Cell(i, None, value = rng, formula = o, is_range = True, is_named_range = True ) cellmap[i] = virtual_cell G.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: cellmap[i] = self.cells[i] G.add_node(self.cells[i]) # edges are not needed here since the input here is not in the calculation chain inputs = set(inputs) # print("Graph construction done, %s nodes, %s edges, %s cellmap entries" % (len(G.nodes()),len(G.edges()),len(cellmap))) # undirected = networkx.Graph(G) # print "Number of connected components %s", str(number_connected_components(undirected)) if inputs == [] and outputs == []: self.build_spreadsheet(G, cellmap, self.named_ranges, pointers = self.pointers, outputs = outputs, inputs = inputs, debug = self.debug) else: sp = Spreadsheet() sp.build_spreadsheet(G, cellmap, self.named_ranges, pointers = self.pointers, outputs = outputs, inputs = inputs, debug = self.debug) return sp def build_spreadsheet(self, G, cellmap, named_ranges, pointers = set(), outputs = set(), inputs = set(), debug = False): """ Writes the elements created by gen_graph to the object :param G: :param cellmap: :param named_ranges: :param pointers: :param outputs: :param inputs: :param debug: """ self.G = G self.cellmap = cellmap self.named_ranges = named_ranges addr_to_name = {} for name in named_ranges: addr_to_name[named_ranges[name]] = name self.addr_to_name = addr_to_name addr_to_range = {} for c in list(self.cellmap.values()): if c.is_range and len(list(c.range.keys())) != 0: # could be better, but can't check on Exception types here... addr = c.address() if c.is_named_range else c.range.name for cell in c.range.addresses: if cell not in addr_to_range: addr_to_range[cell] = [addr] else: addr_to_range[cell].append(addr) self.addr_to_range = addr_to_range self.outputs = outputs self.inputs = inputs self.save_history = False self.history = dict() self.count = 0 self.pointer_to_remove = ["INDEX", "OFFSET"] self.pointers = pointers self.pointers_to_reset = pointers self.range = RangeFactory(cellmap) self.reset_buffer = set() self.debug = debug self.fixed_cells = {} # make sure that all cells that don't have a value defined are updated. for cell in self.cellmap.values(): if cell.value is None and cell.formula is not None: cell.needs_update = True def activate_history(self): self.save_history = True def add_cell(self, cell, value = None): """ Depricated, see cell_add(). """ if type(cell) != Cell: cell = Cell(cell, None, value = value, formula = None, is_range = False, is_named_range = False) # previously reset was used to only reset one cell. Capture this behaviour. warnings.warn( "xxx_cell functions are depricated and replaced by cell_xxx functions. Please use those functions instead. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) self.cell_add(cell=cell) def cell_add(self, address=None, cell=None, value=None, formula=None): """ Adds a cell to the Spreadsheet. Either the cell argument can be specified, or any combination of the other arguments. :param address: the address of the cell :param cell: a Cell object to add :param value: (optional) a new value for the cell. In this case, the first argument cell is processed as an address. :param formula: """ if cell is None: cell = Cell(address, value=value, formula=formula) if address in self.cellmap: raise Exception('Cell %s already in cellmap' % address) cellmap, G = graph_from_seeds([cell], self) self.cellmap = cellmap self.G = G print("Graph construction updated, %s nodes, %s edges, %s cellmap entries" % (len(G.nodes()),len(G.edges()),len(cellmap))) def set_formula(self, addr, formula): # previously set_formula was used. Capture this behaviour. warnings.warn( "This function is depricated and will be replaced by cell_set_formula. Please use this function instead. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) return self.cell_set_formula(addr, formula) def cell_set_formula(self, address, formula): """ Set the formula of a cell. :param address: the address of a cell :param formula: the new formula """ if address in self.cellmap: cell = self.cellmap[address] else: raise Exception('Cell %s not in cellmap' % address) seeds = [cell] if cell.is_range: for index, c in enumerate(cell.range.cells): # for each cell of the range, translate the formula if index == 0: c.formula = formula translator = Translator(unicode('=' + formula), c.address().split('!')[1]) # the Translator needs a reference without sheet else: translated = translator.translate_formula(c.address().split('!')[1]) # the Translator needs a reference without sheet c.formula = translated[1:] # to get rid of the '=' seeds.append(c) else: cell.formula = formula cellmap, G = graph_from_seeds(seeds, self) self.cellmap = cellmap self.G = G should_eval = self.cellmap[address].should_eval self.cellmap[address].should_eval = 'always' self.evaluate(address) self.cellmap[address].should_eval = should_eval print("Graph construction updated, %s nodes, %s edges, %s cellmap entries" % (len(G.nodes()),len(G.edges()),len(cellmap))) def prune_graph(self, *args): print('___### Pruning Graph ###___') G = self.G # get all the cells impacted by inputs dependencies = set() for input_address in self.inputs: child = self.cellmap[input_address] if child == None: print("Not found ", input_address) continue g = make_subgraph(G, child, "descending") dependencies = dependencies.union(g.nodes()) # print "%s cells depending on inputs" % str(len(dependencies)) # prune the graph and set all cell independent of input to const subgraph = networkx.DiGraph() new_cellmap = {} for output_address in self.outputs: new_cellmap[output_address] = self.cellmap[output_address] seed = self.cellmap[output_address] todo = [(seed,n) for n in G.predecessors(seed)] done = set(todo) while len(todo) > 0: current, pred = todo.pop() # print "===========================" # print current.address(), pred.address() if current in dependencies: if pred in dependencies or isinstance(pred.value, RangeCore) or pred.is_named_range: subgraph.add_edge(pred, current) new_cellmap[pred.address()] = pred new_cellmap[current.address()] = current nexts = G.predecessors(pred) for n in nexts: if (pred,n) not in done: todo += [(pred,n)] done.add((pred,n)) else: if pred.address() not in new_cellmap: const_node = Cell(pred.address(), pred.sheet, value = pred.range if pred.is_range else pred.value, formula=None, is_range = isinstance(pred.range, RangeCore), is_named_range=pred.is_named_range, should_eval=pred.should_eval) # pystr,ast = cell2code(self.named_ranges, const_node, pred.sheet) # const_node.python_expression = pystr # const_node.compile() new_cellmap[pred.address()] = const_node const_node = new_cellmap[pred.address()] subgraph.add_edge(const_node, current) else: # case of range independant of input, we add all children as const if pred.address() not in new_cellmap: const_node = Cell(pred.address(), pred.sheet, value = pred.range if pred.is_range else pred.value, formula=None, is_range = pred.is_range, is_named_range=pred.is_named_range, should_eval=pred.should_eval) # pystr,ast = cell2code(self.named_ranges, const_node, pred.sheet) # const_node.python_expression = pystr # const_node.compile() new_cellmap[pred.address()] = const_node const_node = new_cellmap[pred.address()] subgraph.add_edge(const_node, current) print("Graph pruning done, %s nodes, %s edges, %s cellmap entries" % (len(subgraph.nodes()),len(subgraph.edges()),len(new_cellmap))) undirected = networkx.Graph(subgraph) # print "Number of connected components %s", str(number_connected_components(undirected)) # print map(lambda x: x.address(), subgraph.nodes()) # add back inputs that have been pruned because they are outside of calculation chain for i in self.inputs: if i not in new_cellmap: if i in self.named_ranges: reference = self.named_ranges[i] if is_range(reference): rng = self.Range(reference) virtual_cell = Cell(i, None, value = rng, formula = reference, is_range = True, is_named_range = True ) new_cellmap[i] = virtual_cell subgraph.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: # might need to be changed to actual self.cells Cell, not a copy virtual_cell = Cell(i, None, value = self.cellmap[reference].value, formula = reference, is_range = False, is_named_range = True) new_cellmap[i] = virtual_cell subgraph.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: if is_range(i): rng = self.Range(i) virtual_cell = Cell(i, None, value = rng, formula = o, is_range = True, is_named_range = True ) new_cellmap[i] = virtual_cell subgraph.add_node(virtual_cell) # edges are not needed here since the input here is not in the calculation chain else: new_cellmap[i] = self.cellmap[i] subgraph.add_node(self.cellmap[i]) # edges are not needed here since the input here is not in the calculation chain spreadsheet = Spreadsheet() return spreadsheet.build_spreadsheet(subgraph, new_cellmap, self.named_ranges, self.pointers, self.outputs, self.inputs, debug = self.debug) def clean_pointer(self): print('___### Cleaning Pointers ###___') new_named_ranges = self.named_ranges.copy() new_cells = self.cellmap.copy() ### 1) create ranges for n in self.named_ranges: reference = self.named_ranges[n] if is_range(reference): if 'OFFSET' not in reference: my_range = self.Range(reference) self.cellmap[n] = Cell(n, None, value = my_range, formula = reference, is_range = True, is_named_range = True ) else: self.cellmap[n] = Cell(n, None, value = None, formula = reference, is_range = False, is_named_range = True ) else: if reference in self.cellmap: self.cellmap[n] = Cell(n, None, value = self.cellmap[reference].value, formula = reference, is_range = False, is_named_range = True ) else: self.cellmap[n] = Cell(n, None, value = None, formula = reference, is_range = False, is_named_range = True ) ### 2) gather all occurence of pointer functions in cells or named_range all_pointers = set() for pointer_name in self.pointer_to_remove: for k,v in list(self.named_ranges.items()): if pointer_name in v: all_pointers.add((v, k, None)) for k,cell in list(self.cellmap.items()): if cell.formula and pointer_name in cell.formula: all_pointers.add((cell.formula, cell.address(), cell.sheet)) # print "%s %s to parse" % (str(len(all_pointers)), pointer_name) ### 3) evaluate all pointers for formula, address, sheet in all_pointers: if sheet: parsed = parse_cell_address(address) else: parsed = "" e = shunting_yard(formula, self.named_ranges, ref=parsed, tokenize_range = True) ast,root = build_ast(e) code = root.emit(ast) cell = {"formula": formula, "address": address, "sheet": sheet} replacements = self.eval_pointers_from_ast(ast, root, cell) new_formula = formula if type(replacements) == list: for repl in replacements: if type(repl["value"]) == ExcelError: if self.debug: print('WARNING: Excel error found => replacing with #N/A') repl["value"] = "#N/A" if repl["expression_type"] == "value": new_formula = new_formula.replace(repl["formula"], str(repl["value"])) else: new_formula = new_formula.replace(repl["formula"], repl["value"]) else: new_formula = None if address in new_named_ranges: new_named_ranges[address] = new_formula else: old_cell = self.cellmap[address] new_cells[address] = Cell(old_cell.address(), old_cell.sheet, value=old_cell.value, formula=new_formula, is_range = old_cell.is_range, is_named_range=old_cell.is_named_range, should_eval=old_cell.should_eval) return new_cells, new_named_ranges def print_value_ast(self, ast,node,indent): print("%s %s %s %s" % (" "*indent, str(node.token.tvalue), str(node.token.ttype), str(node.token.tsubtype))) for c in node.children(ast): self.print_value_ast(ast, c, indent+1) def eval_pointers_from_ast(self, ast, node, cell): results = [] context = cell["sheet"] if (node.token.tvalue == "INDEX" or node.token.tvalue == "OFFSET"): pointer_string = reverse_rpn(node, ast) expression = node.emit(ast, context=context) if expression.startswith("self.eval_ref"): expression_type = "value" else: expression_type = "formula" try: pointer_value = eval(expression) except Exception as e: if self.debug: print('EXCEPTION raised in eval_pointers: EXPR', expression, cell["address"]) raise Exception("Problem evalling: %s for %s, %s" % (e, cell["address"], expression)) return {"formula":pointer_string, "value": pointer_value, "expression_type": expression_type} else: for c in node.children(ast): results.append(self.eval_pointers_from_ast(ast, c, cell)) return list(flatten(results, only_lists = True)) def detect_alive(self, inputs = None, outputs = None): pointer_arguments = self.find_pointer_arguments(outputs) if inputs is None: inputs = self.inputs # go down the tree and list all cells that are pointer arguments todo = [self.cellmap[input] for input in inputs] done = set() alive = set() while len(todo) > 0: cell = todo.pop() if cell not in done: if cell.address() in pointer_arguments: alive.add(cell.address()) for child in self.G.successors(cell): todo.append(child) done.add(cell) self.pointers_to_reset = alive return alive def find_pointer_arguments(self, outputs = None): # 1) gather all occurence of pointer all_pointers = set() if outputs is None: # 1.1) from all cells for pointer_name in self.pointer_to_remove: for k, cell in list(self.cellmap.items()): if cell.formula and pointer_name in cell.formula: all_pointers.add((cell.formula, cell.address(), cell.sheet)) else: # 1.2) from the outputs while climbing up the tree todo = [self.cellmap[output] for output in outputs] done = set() while len(todo) > 0: cell = todo.pop() if cell not in done: if cell.address() in self.pointers: if cell.formula: all_pointers.add((cell.formula, cell.address(), cell.sheet if cell.sheet is not None else None)) else: raise Exception('Volatiles should always have a formula') for parent in self.G.predecessors(cell): # climb up the tree todo.append(parent) done.add(cell) # 2) extract the arguments from these pointers done = set() pointer_arguments = set() #print 'All vol %i / %i' % (len(all_pointers), len(self.pointers)) for formula, address, sheet in all_pointers: if formula not in done: if sheet: parsed = parse_cell_address(address) else: parsed = "" e = shunting_yard(formula, self.named_ranges, ref=parsed, tokenize_range = True) ast,root = build_ast(e) code = root.emit(ast) for a in list(flatten(self.get_pointer_arguments_from_ast(ast, root, sheet))): pointer_arguments.add(a) done.add(formula) return pointer_arguments def get_arguments_from_ast(self, ast, node, sheet): arguments = [] for c in node.children(ast): if c.tvalue == ":": arg_range = reverse_rpn(c, ast) for elem in resolve_range(arg_range, False, sheet)[0]: arguments += [elem] if c.ttype == "operand": if not is_number(c.tvalue): if sheet is not None and "!" not in c.tvalue and c.tvalue not in self.named_ranges: arguments += [sheet + "!" + c.tvalue] else: arguments += [c.tvalue] else: arguments += [self.get_arguments_from_ast(ast, c, sheet)] return arguments def get_pointer_arguments_from_ast(self, ast, node, sheet): arguments = [] if node.token.tvalue in self.pointer_to_remove: for c in node.children(ast)[1:]: if c.ttype == "operand": if not is_number(c.tvalue): if sheet is not None and "!" not in c.tvalue and c.tvalue not in self.named_ranges: arguments += [sheet + "!" + c.tvalue] else: arguments += [c.tvalue] else: arguments += [self.get_arguments_from_ast(ast, c, sheet)] else: for c in node.children(ast): arguments += [self.get_pointer_arguments_from_ast(ast, c, sheet)] return arguments def dump_json(self, fname): dump_json(self, fname) def dump(self, fname): dump(self, fname) @staticmethod def load(fname): spreadsheet = Spreadsheet() spreadsheet.build_spreadsheet(*load(fname)) return spreadsheet @staticmethod def load_json(fname): data = load_json(fname) return Spreadsheet.from_dict(data) def set_value(self, address, val): # previously set_value was used. Capture this behaviour. warnings.warn( "This function is depricated and will be replaced by cell_set_value. Please use this function instead. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) return self.cell_set_value(address, val) def cell_set_value(self, address, value): """ Set the value of a cell :param address: the address of a cell :param value: the new value """ self.reset_buffer = set() if address in self.named_ranges.keys(): # if cell is named range get real address address = self.named_ranges[address] try: address = address.replace('$', '') address = address.replace("'", '') cell = self.cellmap[address] # when you set a value on cell, its should_eval flag is set to 'never' so its formula is not used until set free again => sp.activate_formula() self.fix_cell(address) # case where the address refers to a range if cell.is_range: cells_to_set = [] if not isinstance(value, list): value = [value] * len(cells_to_set) self.cell_reset(cell.address()) cell.range.values = value # case where the address refers to a single value else: if address in self.named_ranges: # if the cell is a named range, we need to update and fix the reference cell ref_address = self.named_ranges[address] if ref_address in self.cellmap: ref_cell = self.cellmap[ref_address] else: ref_cell = Cell( ref_address, None, value=value, formula=None, is_range=False, is_named_range=False) self.cell_add(cell=ref_cell) ref_cell.value = value if cell.value != value: if cell.value is None: cell.value = 'notNone' # hack to avoid the direct return in reset() when value is None # reset the node + its dependencies self.cell_reset(cell.address()) # set the value cell.value = value for vol in self.pointers_to_reset: # reset all pointers self.cell_reset(self.cellmap[vol].address()) except KeyError: raise Exception('Cell %s not in cellmap' % address) def reset(self, depricated=None): """ Resets all the cells in a spreadsheet and indicates that an update is required. :return: nothing """ # previously reset was used to only reset one cell. Capture this behaviour. if depricated is not None: warnings.warn( "reset() is used to reset the full spreadsheet, cell_reset() should be used to reset only one cell. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) self.cell_reset(depricated.address()) for cell in self.cellmap.values: self.cell_reset(cell.address()) return def cell_reset(self, address): """ Resets the value of the cell and indicates that an update is required. Also resets all of its dependents. :param address: the address of the cell to be reset. :return: nothing """ if address in self.cellmap: cell = self.cellmap[address] else: return if cell.value is None and address not in self.named_ranges: return # check if cell has to be reset if cell.value is None and cell.need_update: return # update cells if cell.should_eval != 'never': if not cell.is_range: cell.value = None self.reset_buffer.add(cell) cell.need_update = True for child in self.G.successors(cell): if child not in self.reset_buffer: self.cell_reset(child.address()) def fix_cell(self, address): warnings.warn( "xxx_cell functions are depricated and replaced by cell_xxx functions. Please use those functions instead. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) return self.cell_fix(address) def cell_fix(self, address): """ Fix the value of a cell :param address: the address of the cell """ try: if address not in self.fixed_cells: cell = self.cellmap[address] self.fixed_cells[address] = cell.should_eval cell.should_eval = 'never' except KeyError: raise Exception('Cell %s not in cellmap' % address) def free_cell(self, address=None): warnings.warn( "xxx_cell functions are depricated and replaced by cell_xxx functions. Please use those functions instead. " "This behaviour will be removed in a future version.", PendingDeprecationWarning ) return self.cell_free(address) def cell_free(self, address=None): """ Free the cell (opposite of fix) :param address: the address of the cell """ if address is None: for addr in self.fixed_cells: cell = self.cellmap[addr] cell.should_eval = 'always' # this is to be able to correctly reinitiliaze the value if cell.python_expression is not None: self.eval_ref(addr) cell.should_eval = self.fixed_cells[addr] self.fixed_cells = {} else: try: cell = self.cellmap[address] cell.should_eval = 'always' # this is to be able to correctly reinitiliaze the value if cell.python_expression is not None: self.eval_ref(address) cell.should_eval = self.fixed_cells[address] self.fixed_cells.pop(address, None) except KeyError: raise Exception('Cell %s not in cellmap' % address) def print_value_tree(self,addr,indent): cell = self.cellmap[addr] print("%s %s = %s" % (" "*indent,addr,cell.value)) for c in self.G.predecessors_iter(cell): self.print_value_tree(c.address(), indent+1) def build_pointer(self, pointer): if not isinstance(pointer, RangeCore): vol_range = self.cellmap[pointer].range else: vol_range = pointer start = eval(vol_range.reference['start']) end = eval(vol_range.reference['end']) vol_range.build('%s:%s' % (start, end), debug = True) def build_pointers(self): for pointer in self.pointers: vol_range = self.cellmap[pointer].range start = eval(vol_range.reference['start']) end = eval(vol_range.reference['end']) vol_range.build('%s:%s' % (start, end), debug = True) def eval_ref(self, addr1, addr2 = None, ref = None): debug = False if isinstance(addr1, ExcelError): return addr1 elif isinstance(addr2, ExcelError): return addr2 else: if addr1 in self.cellmap: cell1 = self.cellmap[addr1] else: if self.debug: print('WARNING in eval_ref: address %s not found in cellmap, returning #NULL' % addr1) return ExcelError('#NULL', 'Cell %s is empty' % addr1) if addr2 == None: if cell1.is_range: if cell1.range.is_pointer: self.build_pointer(cell1.range) # print 'NEED UPDATE', cell1.need_update associated_addr = RangeCore.find_associated_cell(ref, cell1.range) if associated_addr: # if range is associated to ref, no need to return/update all range return self.evaluate(associated_addr) else: range_name = cell1.address() if cell1.need_update: self.update_range(cell1.range) range_need_update = True for c in self.G.successors(cell1): # if a parent doesnt need update, then cell1 doesnt need update if not c.need_update: range_need_update = False break cell1.need_update = range_need_update return cell1.range else: return cell1.range elif addr1 in self.named_ranges or not is_range(addr1): value = self.evaluate(addr1) return value else: # addr1 = Sheet1!A1:A2 or Sheet1!A1:Sheet1!A2 addr1, addr2 = addr1.split(':') if '!' in addr1: sheet = addr1.split('!')[0] else: sheet = None if '!' in addr2: addr2 = addr2.split('!')[1] return self.Range('%s:%s' % (addr1, addr2)) else: # addr1 = Sheet1!A1, addr2 = Sheet1!A2 if '!' in addr1: sheet = addr1.split('!')[0] else: sheet = None if '!' in addr2: addr2 = addr2.split('!')[1] return self.Range('%s:%s' % (addr1, addr2)) def update_range(self, range): # This function loops through its Cell references to evaluate the ones that need so # This uses Spreadsheet.pending dictionary, that holds the addresses of the Cells that are being calculated debug = False for index, key in enumerate(range.order): addr = get_cell_address(range.sheet, key) if self.cellmap[addr].need_update or self.cellmap[addr].value is None: self.evaluate(addr) def evaluate(self, cell, is_addr=True): if isinstance(cell, Cell): is_addr = False if is_addr: address = cell else: address = cell.address return self.cell_evaluate(address) def cell_evaluate(self, address): """ Evaluate the cell. :param address: the address of the cell :return: """ try: cell = self.cellmap[address] except: if self.debug: print('WARNING: Empty cell at ' + address) return ExcelError('#NULL', 'Cell %s is empty' % address) # no formula, fixed value if cell.should_eval == 'normal' and not cell.need_update and cell.value is not None or not cell.formula or cell.should_eval == 'never': return cell.value if cell.value != '' else None try: if cell.is_range: for child in cell.range.cells: self.evaluate(child.address()) elif cell.compiled_expression != None: vv = eval(cell.compiled_expression) if isinstance(vv, RangeCore): # this should mean that vv is the result of RangeCore.apply_all, but with only one value inside cell.value = vv.values[0] else: cell.value = vv if vv != '' else None else: cell.value = 0 cell.need_update = False # DEBUG: saving differences if self.save_history: if cell.address() in self.history: ori_value = self.history[cell.address()]['original'] if 'new' not in list(self.history[cell.address()].keys()): if type(ori_value) == list and type(cell.value) == list \ and all([not is_almost_equal(x_y[0], x_y[1]) for x_y in zip(ori_value, cell.value)]) \ or not is_almost_equal(ori_value, cell.value): self.count += 1 self.history[cell.address()]['formula'] = str(cell.formula) self.history[cell.address()]['priority'] = self.count self.history[cell.address()]['python'] = str(cell.python_expression) if self.count == 1: self.history['ROOT_DIFF'] = self.history[cell.address()] self.history['ROOT_DIFF']['cell'] = cell.address() self.history[cell.address()]['new'] = str(cell.value) else: if isinstance(cell.value, ExcelError): self.history[cell.address()] = {'new': str(cell.value), 'error': str(cell.value.info)} else: self.history[cell.address()] = {'new': str(cell.value)} except Exception as e: if str(e).startswith("Problem evalling"): raise e else: raise Exception("Problem evalling: %s for %s, %s" % (e,cell.address(),cell.python_expression)) return cell.value def asdict(self): data = json_graph.node_link_data(self.G) def cell_to_dict(cell): if isinstance(cell.range, RangeCore): range = cell.range value = { "cells": range.addresses, "values": range.values, "nrows": range.nrows, "ncols": range.ncols } else: value = cell.value node = { "address": cell.address(), "formula": cell.formula, "value": value, "python_expression": cell.python_expression, "is_named_range": cell.is_named_range, "should_eval": cell.should_eval } return node # save nodes as simple objects nodes = [] for node in data["nodes"]: cell = node["id"] nodes.append(cell.asdict()) links = [] for el in data['links']: link = {key: cell.address() for key, cell in el.items()} links.append(link) data["nodes"] = nodes data["links"] = links data["outputs"] = self.outputs data["inputs"] = self.inputs data["named_ranges"] = self.named_ranges return data @staticmethod def from_dict(input_data): data = dict(input_data) nodes = list( map(Cell.from_dict, filter( lambda item: not isinstance(item['value'], dict), data['nodes']))) cellmap = {n.address(): n for n in nodes} def cell_from_dict(d): return Cell.from_dict(d, cellmap=cellmap) nodes.extend( list( map(cell_from_dict, filter( lambda item: isinstance(item['value'], dict), data['nodes'])))) data["nodes"] = [{'id': node} for node in nodes] links = [] idmap = { node.address(): node for node in nodes } for el in data['links']: source_address = el['source'] target_address = el['target'] link = { 'source': idmap[source_address], 'target': idmap[target_address], } links.append(link) data['links'] = links G = json_graph.node_link_graph(data) cellmap = {n.address(): n for n in G.nodes()} named_ranges = data["named_ranges"] inputs = data["inputs"] outputs = data["outputs"] spreadsheet = Spreadsheet() spreadsheet.build_spreadsheet( G, cellmap, named_ranges, inputs=inputs, outputs=outputs) return spreadsheet