Python openpyxl data_only=True returning None

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.

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. for more info about pywinauto.keyboard: pywinauto.keyboard

# import these stuff
import subprocess
from pywinauto.keyboard import send_keys
import time
import pygetwindow as gw
import pywinauto

excel_path = r"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"
excel_file_path = r"D:\test.xlsx"

def focus_to_window(window_title=None): # function to focus to window. https://stackoverflow.com/a/65623513/8903813
    window = gw.getWindowsWithTitle(window_title)[0]
    if not window.isActive:
        pywinauto.application.Application().connect(handle=window._hWnd).top_window().set_focus()

subprocess.Popen([excel_path, excel_file_path])
time.sleep(1.5) # wait excel to open. Depends on your machine, set it propoerly
focus_to_window("Excel") # focus to that opened file
send_keys('%{F3}') # excel's name box | ALT+F3
send_keys('AA1{ENTER}') # whatever cell do you want to insert somthing | Type 'AA1' then press Enter
send_keys('Stackoverflow.com') # put whatever you want | Type 'Stackoverflow.com'
send_keys('^s') # save | CTRL+S
send_keys('%{F4}') # exit | ALT+F4
print("Done")

Sorry for my bad english.

I have suggestion to this problem. Convert xlsx file to csv :). You will still have the original xlsx file. The conversion is done by libreoffice (it is that subprocess.call() line).You can use also Pandas for this as a more pythonic way.

from subprocess import call
from openpyxl import load_workbook
from csv import reader

filename="test"

wb = load_workbook(filename+".xlsx")
spread_range = wb['Sheet1']

#what ever function there is in A1 cell to be evaluated
print(spread_range.cell(row=1,column=1).value) 

wb.close()

#this line can be done with subprocess or os.system()
#libreoffice --headless --convert-to csv $filename --outdir $outdir
call("libreoffice --headless --convert-to csv "+filename+".xlsx", shell=True)

with open(filename+".csv", newline='') as f:
    reader = reader(f)
    data = list(reader)

print(data[0][0])

or

# importing pandas as pd
import pandas as pd
  
# read an excel file and convert 
# into a dataframe object
df = pd.DataFrame(pd.read_excel("Test.xlsx"))
  
# show the dataframe
df

I hope this helps somebody :-)

As others already mentioned, Openpyxl only reads cashed formula value in data_only mode. I have used PyWin32 to open and save each XLSX file before it's processed by Openpyxl to read the formulas result value. This works for me well, as I don't process large files. This solution will work only if you have MS Excel installed on your PC.

import os
import win32com.client
from openpyxl import load_workbook

# Opening and saving XLSX file, so results for each stored formula can be evaluated and cashed so OpenPyXL can read them.
excel_file = os.path.join(path, file)
excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
excel.DisplayAlerts = False # disabling prompts to overwrite existing file
excel.Workbooks.Open(excel_file )
excel.ActiveWorkbook.SaveAs(excel_file, FileFormat=51, ConflictResolution=2)
excel.DisplayAlerts = True # enabling prompts
excel.ActiveWorkbook.Close()

wb = load_workbook(excel_file)
# read your formula values with openpyxl and do other stuff here

I had the same issue. This may not be the most elegant solution, but this is what worked for me:

import xlwings
from openpyxl import load_workbook

excel_app = xlwings.App(visible=False)
excel_book = excel_app.books.open('writeFormula.xlsx')
excel_book.save()
excel_book.close()
excel_app.quit()

workbook = load_workbook(filename='writeFormula.xlsx', data_only=True)

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.