Parsing Formulas

openpyxl supports limited parsing of formulas embedded in cells. The openpyxl.formula package contains a Tokenizer class to break formulas into their constituent tokens. Usage is as follows:

>>> from openpyxl.formula import Tokenizer
>>> tok = Tokenizer("""=IF($A$1,"then True",MAX(DEFAULT_VAL,'Sheet 2'!B1))""")
>>> print("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
         IF(       FUNC     OPEN
        $A$1    OPERAND    RANGE
           ,        SEP      ARG
 "then True"    OPERAND     TEXT
           ,        SEP      ARG
        MAX(       FUNC     OPEN
 DEFAULT_VAL    OPERAND    RANGE
           ,        SEP      ARG
'Sheet 2'!B1    OPERAND    RANGE
           )       FUNC    CLOSE
           )       FUNC    CLOSE

As shown above, tokens have three attributes of interest:

  • .value: The substring of the formula that produced this token

  • .type: The type of token this represents. Can be one of

    • Token.LITERAL: If the cell does not contain a formula, its value is represented by a single LITERAL token.

    • Token.OPERAND: A generic term for any value in the Excel formula. (See .subtype below for more details).

    • Token.FUNC: Function calls are broken up into tokens for the opener (e.g., SUM(), followed by the arguments, followed by the closer (i.e., )). The function name and opening parenthesis together form one FUNC token, and the matching parenthesis forms another FUNC token.

    • Token.ARRAY: Array literals (enclosed between curly braces) get two ARRAY tokens each, one for the opening { and one for the closing }.

    • Token.PAREN: When used for grouping subexpressions (and not to denote function calls), parentheses are tokenized as PAREN tokens (one per character).

    • Token.SEP: These tokens are created from either commas (,) or semicolons (;). Commas create SEP tokens when they are used to separate function arguments (e.g., SUM(a,b)) or when they are used to separate array elements (e.g., {a,b}). (They have another use as an infix operator for joining ranges). Semicolons are always used to separate rows in an array literal, so always create SEP tokens.

    • Token.OP_PRE: Designates a prefix unary operator. Its value is always + or -

    • Token.OP_IN: Designates an infix binary operator. Possible values are >=, <=, <>, =, >, <, *, /, +, -, ^, or &.

    • Token.OP_POST: Designates a postfix unary operator. Its value is always %.

    • Token.WSPACE: Created for any whitespace encountered. Its value is always a single space, regardless of how much whitespace is found.

  • .subtype: Some of the token types above use the subtype to provide additional information about the token. Possible subtypes are:

    • Token.TEXT, Token.NUMBER, Token.LOGICAL, Token.ERROR, Token.RANGE: these subtypes describe the various forms of OPERAND found in formulae. LOGICAL is either TRUE or FALSE, RANGE is either a named range or a direct reference to another range. TEXT, NUMBER, and ERROR all refer to literal values in the formula

    • Token.OPEN and Token.CLOSE: these two subtypes are used by PAREN, FUNC, and ARRAY, to describe whether the token is opening a new subexpression or closing it.

    • Token.ARG and Token.ROW: are used by the SEP tokens, to distinguish between the comma and semicolon. Commas produce tokens of subtype ARG whereas semicolons produce tokens of subtype ROW

Translating formulae from one location to another

It is possible to translate (in the mathematical sense) formulae from one location to another using the openpyxl.formulas.translate.Translator class. For example, there a range of cells B2:E7 with a sum of each row in column F:

>>> from openpyxl.formula.translate import Translator
>>> ws['F2'] = "=SUM(B2:E2)"
>>> # move the formula one colum to the right
>>> ws['G2'] = Translator("=SUM(B2:E2)", origin="F2").translate_formula("G2")
>>> ws['G2'].value
'=SUM(C2:F2)'

Note

This is limited to the same general restrictions of formulae: A1 cell-references only and no support for defined names.