2

It is said that argument data_only=True helps read data in cell from excel. enter image description here
I want to get the value in cell B10 which contains a simple formula,upload the data file in dropbox ,please download it and have a try with below codes:
please download and have a try

dest_fn = "valuation.xlsx"
from openpyxl import load_workbook
wb = load_workbook(dest_fn, data_only=True)
ws = wb["valuation"]
print(ws['B10'].value)
#it shows nothing!

How can read the value in the cell then?
Are there some other python lib which can read the value in cell containing a formula?

showkey
  • 482
  • 42
  • 140
  • 295
  • Openpyxl does not evaluate formulas, however it does have [some support](https://openpyxl.readthedocs.io/en/stable/formula.html) that could help you to implement formula evaluation yourself – Dima Chubarov Aug 08 '21 at 11:07
  • Possible duplicate https://stackoverflow.com/q/36116162/1328439 – Dima Chubarov Aug 08 '21 at 11:16

1 Answers1

0

Openpyxl can only read the value of a calculation if another application or library has performed the calculation and stored the value.

If you look at the source for this file it's clear that this has not happened:

    <x:c r="B10">
      <x:f>sum(B9:H9)</x:f>
      <x:v />
    </x:c>

This is why openpyxl returns None as the value. In fact, it looks like the file was probably created with openpyxl. Unfortunately, there is no magic. You'll need to open and then save the file in Excel or OpenOffice first.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55