2

Using openpyxl, I'm able to read 2 numbers on a sheet, and also able to read their sum by loading the sheet with data_only=True.

However, when I alter the 2 numbers using openpyxl and then try to read the answer using data_only=True, it returns no output. How do I do this?

Aqueous Carlos
  • 445
  • 7
  • 20
oktested
  • 81
  • 1
  • 7
  • From the [docs](https://openpyxl.readthedocs.io/en/stable/usage.html#read-an-existing-workbook) "`data_only` controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet." Notice that neither of those options include *evaluating* the formula. What you want cannot be done with `openpyxl`. – Steven Rumbalski Nov 12 '18 at 23:58
  • @Idlehands afraid that doesn't work. 'Cell' object has no attribute 'value2'. – oktested Nov 13 '18 at 13:17
  • @oktested sorry I was in between Python and VBA so got them mixed up a little. – r.ook Nov 13 '18 at 13:43

3 Answers3

5

Pyopenxl doesn't evaluate formulas. It's awesome at managing an Excel file bit cannot calculate (evaluate) the cells.

PyCel, xlcalculator, Formulas and Schedula are Python libraries which can evaluate cells from an Excel file. These are usually operating system independent and don't need Excel to be installed.

xlwings, PyXll, DataNitro and FlyingKoala all facilitate Excel-Python communications so you can use Python "in"/with Excel. With these solutions, however, you need Excel to be installed.

An example of evaluating a cell from an Excel file using xlcalculator:

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)
bradbase
  • 409
  • 6
  • 9
1

You can have either the value or the formula in openpyxl. It is precisely to avoid the confusion that this kind of edit could introduce that the library works like this. To evaluate the changed formulae you'll need to load the file in an app like MS Excel or LibreOffice that can evaluate the formulae and store the results.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • Nice to get a reply from the man himself. Congrats on the work so far and pls keep going! I'm not changing the formula itself - I just want to use the same formula on a new set of inputs that I'm entering into excel using openpyxl. Basically I have a complex sheet of formulas already and I need openpyxl to repeatedly use those formulas on new inputs, and read the results back to python. I presume this is not possible in openpyxl? This would make openpyxl super-powerful (context: I'm an investment banker from the fixed income space, trying to upgrade my skills with some python). Thnx! – oktested Nov 13 '18 at 13:13
  • You **could** now write your own formula engine with the tokeniser but that way madness lies (dependencies and possible infitite loops). Excel and LibreOffice have optimisations for that, so much better to make the changes in openpyxl and let them do the calculations, especially as you can remote control them. – Charlie Clark Nov 13 '18 at 14:06
  • Apologies - I didn't understand your reply. By "write your own formula engine with the tokeniser", are you referring to making all the formulas in Python instead of excel, so as to leave Excel altogether? Secondly, when you say, "make the changes in openpyxl and let **them** do the calculations", the "them" refers to Excel? Sorry I'm not clear what your recommendation is. – oktested Nov 14 '18 at 04:02
1

openpyxl data_only flag set to true only get the formula evaluated value when load the file. Even more, the data_only can only get the value if some tool such as MS Excel already evaluated the formula and stored the cached value, or openpyxl will get None. I found it from this answer

cuble
  • 306
  • 2
  • 7