excel - Python openpyxl data_only=True returning None

14
votes

I have a simple excel file:

A1 = 200
A2 = 300
A3 = =SUM(A1:A2)

this file works in excel and shows proper value for SUM, but while using openpyxl module for python I cannot get value in data_only=True mode

Python code from shell:

wb = openpyxl.load_workbook('writeFormula.xlsx', data_only = True)
sheet = wb.active
sheet['A3']
<Cell Sheet.A3> # python response
print(sheet['A3'].value)
None # python response

while:

wb2 = openpyxl.load_workbook('writeFormula.xlsx')
sheet2 = wb2.active
sheet2['A3'].value
'=SUM(A1:A2)'  # python response

Any suggestions what am I doing wrong?

4
pythonexcelopenpyxl
Um, you've specified "data only" and you're trying to get the value of a cell that has a formula rather than data? - kindall
Yes, but instead of 500 value I get None. - Michał Nowosielski
Again, you have specified data only. That cell doesn't have data in it. It has a formula. You have told openpxyl to ignore non-data cells. A formula cell is a non-data cell, so it is ignored. The value 500 is not stored in the cell. - kindall
@kindall that's not exactly true: openpyxl will read a cached value if one is available. - Charlie Clark
@kindall data_only = True reads the data in the cell rather than the formula, it doesent ignore the cell - The Dude

4 Answers

20
votes

It depends upon the provenance of the file. data_only=True depends upon the value of the formula being cached by an application like Excel. If, however, the file was created by openpyxl or a similar library, then it's probable that the formula was never evaluated and, thus, no cached value is available and openpyxl will report None as the value.

4
votes

I have replicated the issue with Openpyxl and Python.

I am currently using openpyxl version 2.6.3 and Python 3.7.4. Also I am assuming that you are trying to complete an exercise from ATBSWP by Al Sweigart.

I tried and tested Charlie Clark's answer, considering that Excel may indeed cache values. I opened the spreadsheet in Excel, copied and pasted the formula into the same exact cell, and finally saved the workbook. Upon reopening the workbook in Python with Openpyxl with the data_only=True option, and reading the value of this cell, I saw the proper value, 500, instead of the wrong value, the None type.

I hope this helps.

0
votes

Yes, @Beno is right. If you want to edit the file without touching it, you can make a little "robot" that edits your excel file.

WARNING: This is a recursive way to edit the excel file. These libraries are depend on your machine, make sure you set time.sleep properly before continuing the rest of the code.

For instance, I use time.sleep, subprocess.Popen, and pywinauto.keyboard.send_keys, just add random character to any cell that you set, then save it. Then the data_only=True is working perfectly. Let me know if you don't understand what I mean, I will provide a sample code.

for more info about pywinauto.keyboard: pywinauto.keyboard

Sorry for my bad english.

-1
votes

I ran into the same issue. After reading through this thread I managed to fix it by simply opening the excel file, making a change then saving the file again. What a weird issue.