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
