1

I have an .xlsx file which I edit in another Python script which inputs formulas into blocks of cells. When I view the sheet with Excel, I have no problem seeing the values which result from the formulas. When I try to access it again, data_only=True or not, I get the wrong results when I try to read the values.

With this code (data_only=False):

wb = load_workbook(filename='Week of August 26, 2019.xlsx')
ws = wb['Weekly Stats']

for x in range(2,15):
    for y in range(38,41):
        print(ws.cell(row=y,column=x).value)

I end up with a long list of formulas:

...
=SUM(I3,I10,I17,I24,I31)
=SUM(I4,I11,I18,I25,I32)
=SUM(I5,I12,I19,I26,I33)
=SUM(I6,I13,I20,I27,I34)
...

If I run it with data_only=True, then I get a long list like:

None
None
None
...

Which is not what the Excel shows me when I open up the file itself. I don't have this issue when I manually input the formulas into excel, but if I put in the formulas using openpyxl, openpyxl has a hard time reading those values.

I've tried messing around with different combinations of data_only=True for both when I edit the Excel sheet and when I read it. It all leads to the same results. Here is what the Excel sheet looks like (formula for one of the cells is in the top section).

Any help is greatly appreciated!

EDIT: I noticed another similar question with a similar issue, but in my case, I'm only editing an existing file, not creating a new one that I'm trying to read from (a suggested answer from the other question.)

Even if I am reading from an openpyxl-created workbook, is there any way "refresh" or evaluate my formulas so that I can get the data from them? The file is saved before it's accessed again, so I would think there shouldn't be an issue?

Chas F
  • 11
  • 2
  • openpyxl just does not support formulas (or they upgraded it without me noticing). – Jean-François Fabre Oct 18 '19 at 08:03
  • @Jean-FrançoisFabre so then how would I go about editing in formulas through openpyxl such that I can access the data in a later python script? – Chas F Oct 18 '19 at 08:15
  • After looking into more similar questions, I found that the formulas have to be evaluated via Excel before openpyxl can read the value that's generated. When I ran my scripts and then opened and saved the file, I was able to read the values. Still trying to figure out how to do that in an automated fashion. I could run a batch file that would could open the files, but I don't know about saving them, not to mention that seems extremely inefficient and unnecessary, but I'm blanking on other options... – Chas F Oct 18 '19 at 08:35

0 Answers0