def cell2code(cell, named_ranges): """Generate python code for the given cell""" if cell.formula: debug = False # if 'OFFSET' in cell.formula or 'INDEX' in cell.formula: # debug = True # if debug: # print 'FORMULA', cell.formula ref = parse_cell_address( cell.address()) if not cell.is_named_range else None sheet = cell.sheet e = shunting_yard(cell.formula, named_ranges, ref=ref, tokenize_range=False) ast, root = build_ast(e, debug=debug) code = root.emit(ast, context=sheet) # print 'CODE', code, ref else: ast = None if isinstance(cell.value, str): code = '"' + cell.value.replace('"', r'\"') + '"' else: code = str(cell.value) return code, ast
def cell2code(cell, named_ranges): """Generate python code for the given cell""" if cell.formula: debug = False # if 'OFFSET' in cell.formula or 'INDEX' in cell.formula: # debug = True # if debug: # print 'FORMULA', cell.formula ref = parse_cell_address(cell.address()) if not cell.is_named_range else None sheet = cell.sheet e = shunting_yard(cell.formula, named_ranges, ref=ref, tokenize_range = False) ast,root = build_ast(e, debug = debug) code = root.emit(ast, context=sheet) # print 'CODE', code, ref else: ast = None if isinstance(cell.value, unicode): code = u'u"' + cell.value.replace(u'"', u'\\"') + u'"' elif isinstance(cell.value, str): raise RuntimeError("Got unexpected non-unicode str") else: code = str(cell.value) return code,ast
def clean_volatile(self, volatiles): print '___### Cleaning Volatiles ###___' 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) evaluate all volatiles for formula, address, sheet in volatiles: if sheet: parsed = parse_cell_address(address) else: parsed = "" e = shunting_yard(formula, self.named_ranges, ref=parsed) ast,root = build_ast(e) code = root.emit(ast) cell = {"formula": formula, "address": address, "sheet": sheet} replacements = self.eval_volatiles_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 find_volatile_arguments(self, outputs = None): # 1) gather all occurence of volatile all_volatiles = set() # if outputs is None: # 1.1) from all cells for volatile in self.volatiles: cell = self.cellmap[volatile] if cell.formula: all_volatiles.add((cell.formula, cell.address(), cell.sheet if cell.sheet is not None else None)) else: raise Exception('Volatiles should always have a formula') # 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.volatiles: # if cell.formula: # all_volatiles.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_iter(cell): # climb up the tree # todo.append(parent) # done.add(cell) print "Total number of volatiles ", len(all_volatiles) # 2) extract the arguments from these volatiles done = set() volatile_arguments = {} for formula, address, sheet in all_volatiles: 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_volatile_arguments_from_ast(ast, root, sheet))): if a in volatile_arguments: volatile_arguments[a].append((formula, address, sheet)) else: volatile_arguments[a] = [(formula, address, sheet)] done.add(formula) return volatile_arguments, all_volatiles
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 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 build_code(formula): ref = None sheet = None if ref_cell is not None: sheet = ref_cell.sheet if not ref_cell.is_named_range: ref = parse_cell_address(ref_cell.address()) e = shunting_yard(formula, names, ref=ref, tokenize_range=False) debug = False ast, root = build_ast(e, debug=debug) code = root.emit(ast, context=sheet, pointer=True) return code
def build_code(formula): ref = None sheet = None if ref_cell is not None: sheet = ref_cell.sheet if not ref_cell.is_named_range: ref = parse_cell_address(ref_cell.address()) e = shunting_yard(formula, names, ref = ref, tokenize_range = False) debug = False ast,root = build_ast(e, debug = debug) code = root.emit(ast, context = sheet, pointer = True) return code
def cell2code(cell, named_ranges, debug = False): """Generate python code for the given cell""" if cell.formula: # if 'OFFSET' in cell.formula or 'INDEX' in cell.formula: # debug = True # if debug: # print 'FORMULA', cell.formula ref = parse_cell_address(cell.address()) if not cell.is_named_range else None sheet = cell.sheet e = shunting_yard(cell.formula, named_ranges, ref=ref, debug = debug) ast,root = build_ast(e) code = root.emit(ast, context=sheet) # print 'CODE', code, ref else: ast = None code = str('"' + cell.value.encode('utf-8') + '"' if isinstance(cell.value,unicode) else cell.value) return code,ast
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 self.named_ranges.items(): if pointer_name in v: all_pointers.add((v, k, None)) for k, cell in 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 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