Function Evaluation

[Len Wanger]

I've seen a number of questions where people ask for OpenPyxl to calculate formulas. For fun I took a crack at evaluating functions. To be clear, this is emulating Excel functions in Python, not using Excel to calculate them. It's pretty basic and only supports a handful of functions right now (ABS, SUM, AVERAGE, MIN, MAX, AND, OR, and IF). It's pretty easy to add new functions, but it would be a lot of work to fill out full emulation of all of the Excel functions! Some other caveats: It only handles basic cell and range references (no names, references to other sheets, etc.), and no circular reference checking. But it works pretty well (see the attached spreadsheet for some examples).

One other big caveat -- this isn't a safe way to do this. It would be pretty easy for someone to do code injection to do malicious things (e.g. write a formula like: = os.execv('...')

Thoughts?

Len

[Charlie Clark]

For the reasons you list and others we have no plans to provide formula
evaluation in openpyxl. If you want to do this you might want to look at
Pycel https://github.com/dgorissen/pycel (note the licence is incompatible
with openpyxl) which also does the hard work of building any relevant
graphs.

It should also be noted that openpyxl does include a formula tokeniser
which can decompose complex cell formulae into their constituent parts.
This is used externally to unpack "shared formulae" as in the bug report
you submitted. See http://openpyxl.readthedocs.io/en/latest/formula.html
for details.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Len Wanger]

Thanks Charlie, the formula evaluation code I wrote is using the tokenizer from openpyxl.

Len

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/HNam8aBS2to/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.