Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl

Question

I am printing some formula in one of the Excel sheets:

wsOld.cell(row = 1, column = 1).value = "=B3=B4" 

But I cannot use its result in implementing some other logic, as:

if((wsOld.cell(row=1, column=1).value)='true'):     # copy the 1st row to another sheet 

Even when I am trying to print the result in the command line, I end up printing the formula:

>>> print(wsOld.cell(row=1, column=1)) >>> =B3=B4 

How can I get the result of the formula in a cell and not the formula itself?

Answer 1

openpyxl support either the formula or the value of the formula. You can select which using the data_only flag when opening a workbook. However, openpyxl does not and will not calculate the result of a formula. There are libraries out there like pycel which purport to do this.

Answer 2

xlwings, PyXll FlyingKoala, DataNitro all use Excel as the interface to using Python.

If you want to use a Python library you can try PyCel, xlcalculator, Formulas and Schedula.

I am the project owner of xlcalculator.

xlcalculator uses openpyxl to read Excel files and adds functionality which translates Excel formulas into Python.

An example using xlcalculator with an Excel file:

from xlcalculator import ModelCompiler from xlcalculator import Model from xlcalculator import Evaluator  filename = r'use_case_01.xlsm' compiler = ModelCompiler() new_model = compiler.read_and_parse_archive(filename) evaluator = Evaluator(new_model) val1 = evaluator.evaluate('First!A2') print("value 'evaluated' for First!A2:", val1) 

An example using xlcalculator with a dict;

input_dict = {     "B4": 0.95,     "B2": 1000,     "B19": 0.001,     "B20": 4,     # B21     "B22": 1,     "B23": 2,     "B24": 3,     "B25": "=B2*B4",     "B26": 5,     "B27": 6,     "B28": "=B19*B20*B22",     "C22": "=SUM(B22:B28)",   }  from xlcalculator import ModelCompiler from xlcalculator import Model from xlcalculator import Evaluator  compiler = ModelCompiler() my_model = compiler.read_and_parse_dict(input_dict) evaluator = Evaluator(my_model)  for formula in my_model.formulae:     print("Formula", formula, "evaluates to", evaluator.evaluate(formula))  # cells need a sheet and Sheet1 is default. evaluator.set_cell_value("Sheet1!B22", 100) print("Formula B28 now evaluates to", evaluator.evaluate("Sheet1!B28")) print("Formula C22 now evaluates to", evaluator.evaluate("Sheet1!C22"))