4

I have a problem with reading value of formula from xslx file. For getting value i use openpyxl, but when i want to get value i see "None". This is my code:

from openpyxl import *
wb = load_workbook('output.xlsx', data_only=True)
sh = wb["Sheet1"]
val=(sh['C5'].value)

File output.xlsx contains formula "C5=A1+B1", cell C5=2, but i can't get this value. Anybody help me. May be i need other library for reading the value of the formula from xslx file. May be exist a sample how do it? I know that it's possible to convert this file into other format for reading, but it is not applicable for this task.

Ivan_47
  • 463
  • 1
  • 5
  • 18

2 Answers2

5

There are a number of solutions to get the value from an Excel cell. It all depends on the environment you are in to get the evaluated cell value.

If you have used Excel to create the xls or xlsx file usually there's a value in a cell. It's not guaranteed because it is possible to turn off re-calc on save, but is usual. And, if someone has turned off re-calc on save the value may not be correct.

If the xls or xlsx file has been created by a non-Excel library (eg; openpyxl, xlwrt), unless you've expressly set the value of the cell, it may not have one.

Thanks to the Python universe there are options.

Pycel, xlcalculator, DataNitro, Formulas and Schedula can read an Excel file and evaluate the formula for a cell. None of these solutions need Excel installed. Most are likely to be OS independant.

  • I can't find a datanitro link

disclaimer: I am the project owner of xlcalculator.

An example using xlcalculator:

from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator

filename = r'output.xlsx'
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(new_model)
val1 = evaluator.evaluate('Sheet1!C5')
print("value 'evaluated' for Sheet1!C5:", val1)
bradbase
  • 409
  • 6
  • 9
1

I came across this problem yesterday and found a few posts on stackoverflow talking about it. Please check Charlie Clark's post on Read Excel cell value and not the formula computing it -openpyxl.

"openpyxl does not evaluate formulae. When you open an Excel file with openpyxl you have the choice either to read the formulae or the last calculated value. If, as you indicate, the formula is dependent upon add-ins then the cached value can never be accurate. As add-ins outside the file specification they will never be supported. Instead you might want to look at something like xlwings which can interact with the Excel runtime. "

Using xlwings to read the value of a formula cell seems to be a solution but it doesn't work for me because xlwings only works on Mac OS and Windows.

Community
  • 1
  • 1
joewhitedelux
  • 169
  • 1
  • 2
  • 9