Keep The Nature Of Array Formulas When Using Openpyxl

Iam working on a excel workbook that needs to find median of all column values where another column value is a specific string using openpyxl. For this purpose, I have used MEDIAN

Solution 1:

openpyxl supports limited parsing of formulas embedded in cells. The openpyxl.formula package contains a Tokenizer class to break formulas into their constituent tokens. A token in an Excel formula.

Tokens have three attributes:

• value: The string value parsed that led to this token (The actual formula, in our Median-IF formula as you would write in excel.)
• type: A string identifying the type of token
• subtype: A string identifying subtype of the token (optional, and defaults to “”)

Your Median-If formula could be different, the code below is for reference only. It suggest how to parse (or say, validate) the array formulas using openpyxl. Usage is as follows:

>>>from openpyxl.formula import Tokenizer>>>tok = Tokenizer("""{=MEDIAN(IF($B$1:$B$6="1234",$A$1:$A$6,""))}""")>>>tok.type = Token.ARRAY>>>tok.parse()

Kindly note that openpyxl support either the formula or the value of the formula which one can select using the data_only flag when opening a workbook. However, openpyxl does not and will not calculate the result of a formula. Use either of the following for a greater control to Excel in Python:

1. pycel
2. xlwings

Attribution: openpyxl documentation (Tokenizer)