using openpyxl Tokenizer to parse Excel IF statements

I found this doc on Tokenizer, but it doesn't say how to use the formula and actually execute it (seems pretty useless without that).

I have some simple IF cases I need to handle:

IF

formula = '''IF(size<80000,size/80000*(1.85-1.65)+1.65,IF(size<160000
(size-80000)/80000*(1.65-1.45)+1.45,IF(size<280000,(size-160000)/
120000*(1.45-1.05)+1.05,IF(size<800000,(size-280000)/520000*(1.05-0.7)+0.7,0))))'''

from openpyxl.formula import Tokenizer
tok = Tokenizer(formula)


I can see the methods for tok:

tok

print([d for d in dir(tok) if '__' not in d])
['ERROR_CODES', 'SN_RE', 'STRING_REGEXES', 'TOKEN_ENDERS', 'WSPACE_RE', '_parse', '_parse_brackets', '_parse_closer', '_parse_error', '_parse_opener', '_parse_operator', '_parse_separator', '_parse_string', '_parse_whitespace', 'assert_empty_token', 'check_scientific_notation', 'formula', 'items', 'offset', 'render', 'save_token', 'token', 'token_stack']


If I wanted to pass in size=100 to this tok instance and execute it to get a returned result, how do I do that?

size=100

tok

There is the obvious way of building an AST with the parsed tokens, but I'm curious if it is possible to write the formula to a an Excel doc in memory and execute the function that way, if that is easier. I do not have an Excel doc for this. I just have lots of formula strings.

1 Answer
1

openpyxl never evaluates formulae. The tokenizer is solely used for unpacking and reassigning shared formulae. Other libraries do exist for evaluating formulae but you are generally better off passing the file to an application such as MS Excel or OpenOffice or LibreOffice for evaluation as these contain optimisations for the calculation, including parallelisation.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.