第三天 解析公式

openpyxl对单元格内的公式支持有限。openpyxl.formula包包含了一个 Tokenizer类，它可以将公式分割成构成该公式的token。用法如下：

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

运行结果
如上所示，token有三个有意思的属性：

• 值(value)：公式子串产生的```token``

• 类型(type):
    Token.LITERAL：当单元格不包含公式时，默认为常量
    Token.OPERAND：公式中的任意值
    Token.FUNC：函数调用被分解为多个token：opener，参数，closer;函数名和圆括号构成了一个FUNC token
    Token.ARRAY：由花括号构成{和}构成
    …

• 子类型：一些上述的类型使用子类型提供额外的信息

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.