def update_shared_formulas(cell, shared_formulas):
new_formula = []
expression = next((formula["formula"] for formula in shared_formulas if formula[
"si"] == cell.shared_index), None)
host_address = next((formula["address"] for formula in shared_formulas if formula[
"si"] == cell.shared_index), None)
client_address = cell.address
p = ExcelParser()
p.parse(expression)
offset = compute_offset(host_address, client_address)
for t in p.tokens.items: # Iterate over the tokens
if t.ttype == "operand" and t.tsubtype == "range":
# If operand-range is a range
if check_address(t.tvalue, ":") == True:
# split the range
new_range = []
for ad in t.tvalue.split(":"):
updated_address = offset_cell(ad, offset)
new_range.append(updated_address)
new_formula.append(":".join(new_range))
else: # If operand-range is just a cell
updated_address = offset_cell(t.tvalue, offset)
new_formula.append(updated_address)
else: # If not a range
new_formula.append(t.tvalue)
return ''.join(new_formula)
def update_shared_formulas(cell, shared_formulas):
new_formula = []
expression = next(
(formula["formula"]
for formula in shared_formulas if formula["si"] == cell.shared_index),
None)
host_address = next(
(formula["address"]
for formula in shared_formulas if formula["si"] == cell.shared_index),
None)
client_address = cell.address
p = ExcelParser()
p.parse(expression)
offset = compute_offset(host_address, client_address)
for t in p.tokens.items: # Iterate over the tokens
if t.ttype == "operand" and t.tsubtype == "range":
# If operand-range is a range
if check_address(t.tvalue, ":") == True:
# split the range
new_range = []
for ad in t.tvalue.split(":"):
updated_address = offset_cell(ad, offset)
new_range.append(updated_address)
new_formula.append(":".join(new_range))
else: # If operand-range is just a cell
updated_address = offset_cell(t.tvalue, offset)
new_formula.append(updated_address)
else: # If not a range
new_formula.append(t.tvalue)
return ''.join(new_formula)
def shunting_yard(expression):
"""
Tokenize an excel formula expression into reverse polish notation
Core algorithm taken from wikipedia with varargs extensions from
http://www.kallisti.net.nz/blog/2008/02/extension-to-the-shunting-yard-algorithm-to-allow-variable-numbers-of-arguments-to-functions/
"""
#remove leading =
if expression.startswith('='):
expression = expression[1:]
p = ExcelParser();
p.parse(expression)
# insert tokens for '(' and ')', to make things clearer below
tokens = []
for t in p.tokens.items:
if t.ttype == "function" and t.tsubtype == "start":
t.tsubtype = ""
tokens.append(t)
tokens.append(f_token('(','arglist','start'))
elif t.ttype == "function" and t.tsubtype == "stop":
tokens.append(f_token(')','arglist','stop'))
elif t.ttype == "subexpression" and t.tsubtype == "start":
t.tvalue = '('
tokens.append(t)
elif t.ttype == "subexpression" and t.tsubtype == "stop":
t.tvalue = ')'
tokens.append(t)
else:
tokens.append(t)
#print "tokens: ", "|".join([x.tvalue for x in tokens])
#http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010078886.aspx
operators = {}
operators[':'] = Operator(':',8,'left')
operators[''] = Operator(' ',8,'left')
operators[','] = Operator(',',8,'left')
operators['u-'] = Operator('u-',7,'left') #unary negation
operators['%'] = Operator('%',6,'left')
operators['^'] = Operator('^',5,'left')
operators['*'] = Operator('*',4,'left')
operators['/'] = Operator('/',4,'left')
operators['+'] = Operator('+',3,'left')
operators['-'] = Operator('-',3,'left')
operators['&'] = Operator('&',2,'left')
operators['='] = Operator('=',1,'left')
operators['<'] = Operator('<',1,'left')
operators['>'] = Operator('>',1,'left')
operators['<='] = Operator('<=',1,'left')
operators['>='] = Operator('>=',1,'left')
operators['<>'] = Operator('<>',1,'left')
output = collections.deque()
stack = []
were_values = []
arg_count = []
for t in tokens:
if t.ttype == "operand":
output.append(create_node(t))
if were_values:
were_values.pop()
were_values.append(True)
elif t.ttype == "function":
stack.append(t)
arg_count.append(0)
if were_values:
were_values.pop()
were_values.append(True)
were_values.append(False)
elif t.ttype == "argument":
while stack and (stack[-1].tsubtype != "start"):
output.append(create_node(stack.pop()))
if were_values.pop(): arg_count[-1] += 1
were_values.append(False)
if not len(stack):
raise Exception("Mismatched or misplaced parentheses")
elif t.ttype.startswith('operator'):
if t.ttype.endswith('-prefix') and t.tvalue =="-":
o1 = operators['u-']
else:
o1 = operators[t.tvalue]
while stack and stack[-1].ttype.startswith('operator'):
if stack[-1].ttype.endswith('-prefix') and stack[-1].tvalue =="-":
o2 = operators['u-']
else:
o2 = operators[stack[-1].tvalue]
if ( (o1.associativity == "left" and o1.precedence <= o2.precedence)
or
(o1.associativity == "right" and o1.precedence < o2.precedence) ):
output.append(create_node(stack.pop()))
else:
break
stack.append(t)
elif t.tsubtype == "start":
stack.append(t)
elif t.tsubtype == "stop":
while stack and stack[-1].tsubtype != "start":
output.append(create_node(stack.pop()))
if not stack:
raise Exception("Mismatched or misplaced parentheses")
stack.pop()
if stack and stack[-1].ttype == "function":
f = create_node(stack.pop())
a = arg_count.pop()
w = were_values.pop()
if w: a += 1
f.num_args = a
#print f, "has ",a," args"
output.append(f)
while stack:
if stack[-1].tsubtype == "start" or stack[-1].tsubtype == "stop":
raise Exception("Mismatched or misplaced parentheses")
output.append(create_node(stack.pop()))
#print "Stack is: ", "|".join(stack)
#print "Ouput is: ", "|".join([x.tvalue for x in output])
# convert to list
result = [x for x in output]
return result
def shunting_yard(expression, names):
"""
Tokenize an excel formula expression into reverse polish notation
Core algorithm taken from wikipedia with varargs extensions from
http://www.kallisti.net.nz/blog/2008/02/extension-to-the-shunting-yard-algorithm-to-allow-variable-numbers-of-arguments-to-functions/
"""
#remove leading =
if expression.startswith('='):
expression = expression[1:]
#remove %
expression = expression.replace("%", "")
p = ExcelParser()
p.parse(expression)
# insert tokens for '(' and ')', to make things clearer below
tokens = []
for t in p.tokens.items:
if t.ttype == "function" and t.tsubtype == "start":
t.tsubtype = ""
tokens.append(t)
tokens.append(f_token('(', 'arglist', 'start'))
elif t.ttype == "function" and t.tsubtype == "stop":
tokens.append(f_token(')', 'arglist', 'stop'))
elif t.ttype == "subexpression" and t.tsubtype == "start":
t.tvalue = '('
tokens.append(t)
elif t.ttype == "subexpression" and t.tsubtype == "stop":
t.tvalue = ')'
tokens.append(t)
else:
tokens.append(t)
# print "tokens: ", "|".join([x.tvalue for x in tokens])
# replace variables
for t in tokens:
k = t.tvalue
if k in names.keys():
t.tvalue = names[k]
# for t in tokens:
# print t.tvalue, t.ttype, t.tsubtype
#print "==> ", "".join([t.tvalue for t in tokens])
#http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010078886.aspx
operators = {}
operators[':'] = Operator(':', 8, 'left')
operators[''] = Operator(' ', 8, 'left')
operators[','] = Operator(',', 8, 'left')
operators['u-'] = Operator('u-', 7, 'left') #unary negation
operators['%'] = Operator('%', 6, 'left')
operators['^'] = Operator('^', 5, 'left')
operators['*'] = Operator('*', 4, 'left')
operators['/'] = Operator('/', 4, 'left')
operators['+'] = Operator('+', 3, 'left')
operators['-'] = Operator('-', 3, 'left')
operators['&'] = Operator('&', 2, 'left')
operators['='] = Operator('=', 1, 'left')
operators['<'] = Operator('<', 1, 'left')
operators['>'] = Operator('>', 1, 'left')
operators['<='] = Operator('<=', 1, 'left')
operators['>='] = Operator('>=', 1, 'left')
operators['<>'] = Operator('<>', 1, 'left')
output = collections.deque()
stack = []
were_values = []
arg_count = []
for t in tokens:
if t.ttype == "operand":
output.append(create_node(t))
if were_values:
were_values.pop()
were_values.append(True)
elif t.ttype == "function":
stack.append(t)
arg_count.append(0)
if were_values:
were_values.pop()
were_values.append(True)
were_values.append(False)
elif t.ttype == "argument":
while stack and (stack[-1].tsubtype != "start"):
output.append(create_node(stack.pop()))
if were_values.pop(): arg_count[-1] += 1
were_values.append(False)
if not len(stack):
raise Exception("Mismatched or misplaced parentheses")
elif t.ttype.startswith('operator'):
if t.ttype.endswith('-prefix') and t.tvalue == "-":
o1 = operators['u-']
else:
o1 = operators[t.tvalue]
while stack and stack[-1].ttype.startswith('operator'):
if stack[-1].ttype.endswith(
'-prefix') and stack[-1].tvalue == "-":
o2 = operators['u-']
else:
o2 = operators[stack[-1].tvalue]
if ((o1.associativity == "left"
and o1.precedence <= o2.precedence)
or (o1.associativity == "right"
and o1.precedence < o2.precedence)):
output.append(create_node(stack.pop()))
else:
break
stack.append(t)
elif t.tsubtype == "start":
stack.append(t)
elif t.tsubtype == "stop":
while stack and stack[-1].tsubtype != "start":
output.append(create_node(stack.pop()))
if not stack:
raise Exception("Mismatched or misplaced parentheses")
stack.pop()
if stack and stack[-1].ttype == "function":
f = create_node(stack.pop())
a = arg_count.pop()
w = were_values.pop()
if w: a += 1
f.num_args = a
#print f, "has ",a," args"
output.append(f)
while stack:
if stack[-1].tsubtype == "start" or stack[-1].tsubtype == "stop":
raise Exception("Mismatched or misplaced parentheses")
output.append(create_node(stack.pop()))
#print "Stack is: ", "|".join(stack)
#print "Output is: ", "|".join([x.tvalue for x in output])
# convert to list
result = [x for x in output]
return result
def shunting_yard(expression, named_ranges, ref = '', tokenize_range = False):
"""
Tokenize an excel formula expression into reverse polish notation
Core algorithm taken from wikipedia with varargs extensions from
http://www.kallisti.net.nz/blog/2008/02/extension-to-the-shunting-yard-algorithm-to-allow-variable-numbers-of-arguments-to-functions/
The ref is the cell address which is passed down to the actual compiled python code.
Range basic operations signature require this reference, so it has to be written during OperatorNode.emit()
https://github.com/iOiurson/koala/blob/master/koala/ast/graph.py#L292.
This is needed because Excel range basic operations (+, -, * ...) are applied on matching cells.
Example:
Cell C2 has the following formula 'A1:A3 + B1:B3'.
The output will actually be A2 + B2, because the formula is relative to cell C2.
"""
#remove leading =
if expression.startswith('='):
expression = expression[1:]
p = ExcelParser(tokenize_range = tokenize_range);
p.parse(expression)
# insert tokens for '(' and ')', to make things clearer below
tokens = []
for t in p.tokens.items:
if t.ttype == "function" and t.tsubtype == "start":
t.tsubtype = ""
tokens.append(t)
tokens.append(f_token('(','arglist','start'))
elif t.ttype == "function" and t.tsubtype == "stop":
tokens.append(f_token(')','arglist','stop'))
elif t.ttype == "subexpression" and t.tsubtype == "start":
t.tvalue = '('
tokens.append(t)
elif t.ttype == "subexpression" and t.tsubtype == "stop":
t.tvalue = ')'
tokens.append(t)
elif t.ttype == "operand" and t.tsubtype == "range" and t.tvalue in named_ranges:
t.tsubtype = "named_range"
tokens.append(t)
else:
tokens.append(t)
# print "==> ", "".join([t.tvalue for t in tokens])
#http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010078886.aspx
operators = {}
operators[':'] = Operator(':',8,'left')
operators[''] = Operator(' ',8,'left')
operators[','] = Operator(',',8,'left')
operators['u-'] = Operator('u-',7,'left') #unary negation
operators['%'] = Operator('%',6,'left')
operators['^'] = Operator('^',5,'left')
operators['*'] = Operator('*',4,'left')
operators['/'] = Operator('/',4,'left')
operators['+'] = Operator('+',3,'left')
operators['-'] = Operator('-',3,'left')
operators['&'] = Operator('&',2,'left')
operators['='] = Operator('=',1,'left')
operators['<'] = Operator('<',1,'left')
operators['>'] = Operator('>',1,'left')
operators['<='] = Operator('<=',1,'left')
operators['>='] = Operator('>=',1,'left')
operators['<>'] = Operator('<>',1,'left')
output = collections.deque()
stack = []
were_values = []
arg_count = []
for t in tokens:
if t.ttype == "operand":
output.append(create_node(t, ref))
if were_values:
were_values.pop()
were_values.append(True)
elif t.ttype == "function":
stack.append(t)
arg_count.append(0)
if were_values:
were_values.pop()
were_values.append(True)
were_values.append(False)
elif t.ttype == "argument":
while stack and (stack[-1].tsubtype != "start"):
output.append(create_node(stack.pop(), ref))
if were_values.pop(): arg_count[-1] += 1
were_values.append(False)
if not len(stack):
raise Exception("Mismatched or misplaced parentheses")
elif t.ttype.startswith('operator'):
if t.ttype.endswith('-prefix') and t.tvalue =="-":
o1 = operators['u-']
else:
o1 = operators[t.tvalue]
while stack and stack[-1].ttype.startswith('operator'):
if stack[-1].ttype.endswith('-prefix') and stack[-1].tvalue =="-":
o2 = operators['u-']
else:
o2 = operators[stack[-1].tvalue]
if ( (o1.associativity == "left" and o1.precedence <= o2.precedence)
or
(o1.associativity == "right" and o1.precedence < o2.precedence) ):
output.append(create_node(stack.pop(), ref))
else:
break
stack.append(t)
elif t.tsubtype == "start":
stack.append(t)
elif t.tsubtype == "stop":
while stack and stack[-1].tsubtype != "start":
output.append(create_node(stack.pop(), ref))
if not stack:
raise Exception("Mismatched or misplaced parentheses")
stack.pop()
if stack and stack[-1].ttype == "function":
f = create_node(stack.pop(), ref)
a = arg_count.pop()
w = were_values.pop()
if w: a += 1
f.num_args = a
#print f, "has ",a," args"
output.append(f)
while stack:
if stack[-1].tsubtype == "start" or stack[-1].tsubtype == "stop":
raise Exception("Mismatched or misplaced parentheses")
output.append(create_node(stack.pop(), ref))
#print "Stack is: ", "|".join(stack)
#print "Output is: ", "|".join([x.tvalue for x in output])
# convert to list
return [x for x in output]
def shunting_yard(expression):
"""
Tokenize an excel formula expression into reverse polish notation
Core algorithm taken from wikipedia with varargs extensions from
http://www.kallisti.net.nz/blog/2008/02/extension-to-the-shunting-yard-algorithm-to-allow-variable-numbers-of-arguments-to-functions/
"""
# remove leading =
if expression.startswith("="):
expression = expression[1:]
p = ExcelParser()
p.parse(expression)
# insert tokens for '(' and ')', to make things clearer below
tokens = []
for t in p.tokens.items:
if t.ttype == "function" and t.tsubtype == "start":
t.tsubtype = ""
tokens.append(t)
tokens.append(f_token("(", "arglist", "start"))
elif t.ttype == "function" and t.tsubtype == "stop":
tokens.append(f_token(")", "arglist", "stop"))
elif t.ttype == "subexpression" and t.tsubtype == "start":
t.tvalue = "("
tokens.append(t)
elif t.ttype == "subexpression" and t.tsubtype == "stop":
t.tvalue = ")"
tokens.append(t)
else:
tokens.append(t)
# print "tokens: ", "|".join([x.tvalue for x in tokens])
# http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010078886.aspx
operators = {}
operators[":"] = Operator(":", 8, "left")
operators[""] = Operator(" ", 8, "left")
operators[","] = Operator(",", 8, "left")
operators["u-"] = Operator("u-", 7, "left") # unary negation
operators["%"] = Operator("%", 6, "left")
operators["^"] = Operator("^", 5, "left")
operators["*"] = Operator("*", 4, "left")
operators["/"] = Operator("/", 4, "left")
operators["+"] = Operator("+", 3, "left")
operators["-"] = Operator("-", 3, "left")
operators["&"] = Operator("&", 2, "left")
operators["="] = Operator("=", 1, "left")
operators["<"] = Operator("<", 1, "left")
operators[">"] = Operator(">", 1, "left")
operators["<="] = Operator("<=", 1, "left")
operators[">="] = Operator(">=", 1, "left")
operators["<>"] = Operator("<>", 1, "left")
output = collections.deque()
stack = []
were_values = []
arg_count = []
for t in tokens:
if t.ttype == "operand":
output.append(create_node(t))
if were_values:
were_values.pop()
were_values.append(True)
elif t.ttype == "function":
stack.append(t)
arg_count.append(0)
if were_values:
were_values.pop()
were_values.append(True)
were_values.append(False)
elif t.ttype == "argument":
while stack and (stack[-1].tsubtype != "start"):
output.append(create_node(stack.pop()))
if were_values.pop():
arg_count[-1] += 1
were_values.append(False)
if not len(stack):
raise Exception("Mismatched or misplaced parentheses")
elif t.ttype.startswith("operator"):
if t.ttype.endswith("-prefix") and t.tvalue == "-":
o1 = operators["u-"]
else:
o1 = operators[t.tvalue]
while stack and stack[-1].ttype.startswith("operator"):
if stack[-1].ttype.endswith("-prefix") and stack[-1].tvalue == "-":
o2 = operators["u-"]
else:
o2 = operators[stack[-1].tvalue]
if (o1.associativity == "left" and o1.precedence <= o2.precedence) or (
o1.associativity == "right" and o1.precedence < o2.precedence
):
output.append(create_node(stack.pop()))
else:
break
stack.append(t)
elif t.tsubtype == "start":
stack.append(t)
elif t.tsubtype == "stop":
while stack and stack[-1].tsubtype != "start":
output.append(create_node(stack.pop()))
if not stack:
raise Exception("Mismatched or misplaced parentheses")
stack.pop()
if stack and stack[-1].ttype == "function":
f = create_node(stack.pop())
a = arg_count.pop()
w = were_values.pop()
if w:
a += 1
f.num_args = a
# print f, "has ",a," args"
output.append(f)
while stack:
if stack[-1].tsubtype == "start" or stack[-1].tsubtype == "stop":
raise Exception("Mismatched or misplaced parentheses")
output.append(create_node(stack.pop()))
# print "Stack is: ", "|".join(stack)
# print "Ouput is: ", "|".join([x.tvalue for x in output])
# convert to list
result = [x for x in output]
return result