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, 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
