def cell2code(self, cell, sheet):
"""Generate python code for the given cell"""
if cell.formula:
e = shunting_yard(cell.formula or str(cell.value), self.named_ranges, cell.address())
ast,root = build_ast(e)
code = root.emit(ast, context=sheet)
else:
ast = None
code = str('"' + cell.value + '"' if isinstance(cell.value,unicode) else cell.value)
return code,ast
def cell2code(self,cell):
"""Generate python code for the given cell"""
if cell.formula:
e = shunting_yard(cell.formula or str(cell.value))
ast,root = build_ast(e)
code = root.emit(ast,context=Context(cell,self.excel))
else:
ast = None
code = str('"' + cell.value + '"' if isinstance(cell.value,unicode) else cell.value)
return code,ast
def cell2code(self, cell):
"""Generate python code for the given cell"""
if cell.formula:
e = shunting_yard(cell.formula or str(cell.value), self.names)
ast, root = build_ast(e)
code = root.emit(ast, context=Context(cell, self.excel))
else:
ast = None
code = str('"' + cell.value +
'"' if isinstance(cell.value, unicode) else cell.value)
return code, ast
def parse_conditions(conds):
conds = encode_conditions(conds)
python_inputs = []
for i in conds:
print "**************************************************"
print "Formula: ", i
e = shunting_yard(i)
# print "RPN: ", "|".join([str(x) for x in e])
G, root = build_ast(e)
python_inputs += [root.emit(G, context=None)]
print "Python code: ", root.emit(G, context=None)
print "**************************************************"
return decode_conditions(python_inputs)
# E. W. Bachtal's test formulae
'=IF("a"={"a","b";"c",#N/A;-1,TRUE}, "yes", "no") & " more ""test"" text"',
#'=+ AName- (-+-+-2^6) = {"A","B"} + @SUM(R1C1) + (@ERROR.TYPE(#VALUE!) = 2)',
'=IF(R13C3>DATE(2002,1,6),0,IF(ISERROR(R[41]C[2]),0,IF(R13C3>=R[41]C[2],0, IF(AND(R[23]C[11]>=55,R[24]C[11]>=20),R53C3,0))))',
'=IF(R[39]C[11]>65,R[25]C[42],ROUND((R[11]C[11]*IF(OR(AND(R[39]C[11]>=55, ' +
'R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")),R[44]C[11],R[43]C[11]))+(R[14]C[11] ' +
'*IF(OR(AND(R[39]C[11]>=55,R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")), ' +
'R[45]C[11],R[43]C[11])),0))',
'=(propellor_charts!B22*(propellor_charts!E21+propellor_charts!D21*(engine_data!O16*D70+engine_data!P16)+propellor_charts!C21*(engine_data!O16*D70+engine_data!P16)^2+propellor_charts!B21*(engine_data!O16*D70+engine_data!P16)^3)^2)^(1/3)*(1*D70/5.33E-18)^(2/3)*0.0000000001*28.3495231*9.81/1000',
'=(3600/1000)*E40*(E8/E39)*(E15/E19)*LN(E54/(E54-E48))',
'=IF(P5=1.0,"NA",IF(P5=2.0,"A",IF(P5=3.0,"B",IF(P5=4.0,"C",IF(P5=5.0,"D",IF(P5=6.0,"E",IF(P5=7.0,"F",IF(P5=8.0,"G"))))))))',
'=LINEST(X5:X32,W5:W32^{1,2,3})',
'=IF(configurations!$G$22=3,sizing!$C$303,M14)',
'=0.000001042*E226^3-0.00004777*E226^2+0.0007646*E226-0.00075',
'=LINEST(G2:G17,E2:E17,FALSE)',
'=IF(AI119="","",E119)',
'=LINEST(B32:(INDEX(B32:B119,MATCH(0,B32:B119,-1),1)),(F32:(INDEX(B32:F119,MATCH(0,B32:B119,-1),5)))^{1,2,3,4})',
]
for i in inputs:
print "**************************************************"
print "Formula: ", i
e = shunting_yard(i);
print "RPN: ", "|".join([str(x) for x in e])
G,root = build_ast(e)
print "Python code: ", root.emit(G,context=None)
print "**************************************************"
'R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")),R[44]C[11],R[43]C[11]))+(R[14]C[11] '
+
'*IF(OR(AND(R[39]C[11]>=55,R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")), '
+ 'R[45]C[11],R[43]C[11])),0))',
'=(propellor_charts!B22*(propellor_charts!E21+propellor_charts!D21*(engine_data!O16*D70+engine_data!P16)+propellor_charts!C21*(engine_data!O16*D70+engine_data!P16)^2+propellor_charts!B21*(engine_data!O16*D70+engine_data!P16)^3)^2)^(1/3)*(1*D70/5.33E-18)^(2/3)*0.0000000001*28.3495231*9.81/1000',
'=(3600/1000)*E40*(E8/E39)*(E15/E19)*LN(E54/(E54-E48))',
'=IF(P5=1.0,"NA",IF(P5=2.0,"A",IF(P5=3.0,"B",IF(P5=4.0,"C",IF(P5=5.0,"D",IF(P5=6.0,"E",IF(P5=7.0,"F",IF(P5=8.0,"G"))))))))',
'=LINEST(X5:X32,W5:W32^{1,2,3})',
'=IF(configurations!$G$22=3,sizing!$C$303,M14)',
'=0.000001042*E226^3-0.00004777*E226^2+0.0007646*E226-0.00075',
'=LINEST(G2:G17,E2:E17,FALSE)',
'=IF(AI119="","",E119)',
'=LINEST(B32:(INDEX(B32:B119,MATCH(0,B32:B119,-1),1)),(F32:(INDEX(B32:F119,MATCH(0,B32:B119,-1),5)))^{1,2,3,4})',
'=IFERROR(10,11)',
'=MATCH(InputData!G15,InputData!L5:DG5,0)',
'=IFERROR(IF(InputData!G14>=InputData!G15,0,AVERAGE(L52:OFFSET(K52,0,MATCH(InputData!G15,InputData!L5:DG5,0)))),0%)'
'=OFFSET(K52,0,MATCH(InputData!G15,InputData!L5:DG5,0))'
]
for i in inputs:
print "**************************************************"
print "Formula: ", i
e = shunting_yard(i, {})
print "RPN: ", "|".join([str(x) for x in e])
G, root = build_ast(e)
print "Python code: ", root.emit(G, context=None)
print "**************************************************"
def all_cell_coordinates(formula):
rpn = shunting_yard(formula)
for node in rpn:
if node.token.ttype == 'operand' and node.token.tsubtype == 'range':
yield node.token.tvalue
def clean_volatile(self):
all_volatiles = []
for volatile_name in self.volatile_to_remove:
for k,v in self.named_ranges.items():
if volatile_name in v:
all_volatiles.append({"formula":v, "address": k, "sheet": None})
for k,cell in self.cellmap.items():
if cell.formula and volatile_name in cell.formula:
all_volatiles.append({"formula":cell.formula, "address": cell.address(), "sheet": cell.sheet})
print "%s %s to parse" % (str(len(all_volatiles)), volatile_name)
cache = {} # formula => new_formula
new_named_ranges = self.named_ranges.copy()
new_cells = self.cellmap.copy()
for cell in all_volatiles:
if cell["formula"] in cache:
new_formula = cache[cell["formula"]]
else:
if cell["sheet"]:
parsed = parse_cell_address(cell["address"])
else:
parsed = ""
e = shunting_yard(cell["formula"], self.named_ranges, ref=parsed, tokenize_range = True)
ast,root = build_ast(e)
code = root.emit(ast)
replacements = self.eval_volatiles_from_ast(ast, root, cell["sheet"])
# print replacements
new_formula = cell["formula"]
if type(replacements) == list:
for repl in replacements:
if type(repl["value"]) == ExcelError:
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
cache[cell["formula"]] = new_formula
if "OFFSET" in new_formula:
print "====================================="
print cell["address"], cell["formula"].replace(" ","")
print "-------------------------------------"
print new_formula
print replacements
if cell["address"] in new_named_ranges:
new_named_ranges[cell["address"]] = new_formula
old_cell = new_cells[cell["address"]]
new_cells[cell["address"]] = Cell(old_cell.address(), old_cell.sheet, value=old_cell.value, formula=new_formula, is_named_range=old_cell.is_named_range, always_eval=old_cell.always_eval)
#print 'ALLEZ', new_named_ranges["IA_EconLimitOverride_ProjectList"]
#print new_cells["IA_EconLimitOverride_ProjectList"].formula
return new_cells, new_named_ranges
#'=+ AName- (-+-+-2^6) = {"A","B"} + @SUM(R1C1) + (@ERROR.TYPE(#VALUE!) = 2)',
'=IF(R13C3>DATE(2002,1,6),0,IF(ISERROR(R[41]C[2]),0,IF(R13C3>=R[41]C[2],0, IF(AND(R[23]C[11]>=55,R[24]C[11]>=20),R53C3,0))))',
'=IF(R[39]C[11]>65,R[25]C[42],ROUND((R[11]C[11]*IF(OR(AND(R[39]C[11]>=55, '
+
'R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")),R[44]C[11],R[43]C[11]))+(R[14]C[11] '
+
'*IF(OR(AND(R[39]C[11]>=55,R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")), '
+ 'R[45]C[11],R[43]C[11])),0))',
'=(propellor_charts!B22*(propellor_charts!E21+propellor_charts!D21*(engine_data!O16*D70+engine_data!P16)+propellor_charts!C21*(engine_data!O16*D70+engine_data!P16)^2+propellor_charts!B21*(engine_data!O16*D70+engine_data!P16)^3)^2)^(1/3)*(1*D70/5.33E-18)^(2/3)*0.0000000001*28.3495231*9.81/1000',
'=(3600/1000)*E40*(E8/E39)*(E15/E19)*LN(E54/(E54-E48))',
'=IF(P5=1.0,"NA",IF(P5=2.0,"A",IF(P5=3.0,"B",IF(P5=4.0,"C",IF(P5=5.0,"D",IF(P5=6.0,"E",IF(P5=7.0,"F",IF(P5=8.0,"G"))))))))',
'=LINEST(X5:X32,W5:W32^{1,2,3})',
'=IF(configurations!$G$22=3,sizing!$C$303,M14)',
'=0.000001042*E226^3-0.00004777*E226^2+0.0007646*E226-0.00075',
'=LINEST(G2:G17,E2:E17,FALSE)',
'=IF(AI119="","",E119)',
'=LINEST(B32:(INDEX(B32:B119,MATCH(0,B32:B119,-1),1)),(F32:(INDEX(B32:F119,MATCH(0,B32:B119,-1),5)))^{1,2,3,4})',
]
for i in inputs:
print "**************************************************"
print "Formula: ", i
e = shunting_yard(i)
print "RPN: ", "|".join([str(x) for x in e])
G, root = build_ast(e)
print "Python code: ", root.emit(G, context=None)
print "**************************************************"
'=IF(R[39]C[11]>65,R[25]C[42],ROUND((R[11]C[11]*IF(OR(AND(R[39]C[11]>=55, ' +
'R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")),R[44]C[11],R[43]C[11]))+(R[14]C[11] ' +
'*IF(OR(AND(R[39]C[11]>=55,R[40]C[11]>=20),AND(R[40]C[11]>=20,R11C3="YES")), ' +
'R[45]C[11],R[43]C[11])),0))',
'=(propellor_charts!B22*(propellor_charts!E21+propellor_charts!D21*(engine_data!O16*D70+engine_data!P16)+propellor_charts!C21*(engine_data!O16*D70+engine_data!P16)^2+propellor_charts!B21*(engine_data!O16*D70+engine_data!P16)^3)^2)^(1/3)*(1*D70/5.33E-18)^(2/3)*0.0000000001*28.3495231*9.81/1000',
'=(3600/1000)*E40*(E8/E39)*(E15/E19)*LN(E54/(E54-E48))',
'=IF(P5=1.0,"NA",IF(P5=2.0,"A",IF(P5=3.0,"B",IF(P5=4.0,"C",IF(P5=5.0,"D",IF(P5=6.0,"E",IF(P5=7.0,"F",IF(P5=8.0,"G"))))))))',
'=LINEST(X5:X32,W5:W32^{1,2,3})',
'=IF(configurations!$G$22=3,sizing!$C$303,M14)',
'=0.000001042*E226^3-0.00004777*E226^2+0.0007646*E226-0.00075',
'=LINEST(G2:G17,E2:E17,FALSE)',
'=IF(AI119="","",E119)',
'=LINEST(B32:(INDEX(B32:B119,MATCH(0,B32:B119,-1),1)),(F32:(INDEX(B32:F119,MATCH(0,B32:B119,-1),5)))^{1,2,3,4})',
'=IFERROR(10,11)',
'=MATCH(InputData!G15,InputData!L5:DG5,0)',
'=IFERROR(IF(InputData!G14>=InputData!G15,0,AVERAGE(L52:OFFSET(K52,0,MATCH(InputData!G15,InputData!L5:DG5,0)))),0%)'
'=OFFSET(K52,0,MATCH(InputData!G15,InputData!L5:DG5,0))'
]
for i in inputs:
print "**************************************************"
print "Formula: ", i
e = shunting_yard(i, {});
print "RPN: ", "|".join([str(x) for x in e])
G,root = build_ast(e)
print "Python code: ", root.emit(G,context=None)
print "**************************************************"
