I am trying to read data from equations that reference internal cells (the references are both to cells on the same worksheet, and also to cells on other worksheets) using openpyxl, but when I try and read the values from equations I get a value of 'None'. If I open up the workbook first, I get a Message Box about updating links. If I hit cancel and then save the workbook, I can read the values with the equations. I don't want to manually open every workbook and save it. Is there a way to get the equations for internal references to be properly evaluated when openpyxl loads the workbook?
Here is how I load the workbook:
import openpyxl
wbPath = [workbookpath]
workbook = openpyxl.load_workbook(wbPath, data_only=True)
*Edit: just wanted to add the code I use to get the equations to update with openpyxl and xlwings package:
import xlwings as xw
import openpyxl
#Open and save workbook with openpyxl
wbPath = [WorkbookPath]
wb = openpyxl.load_workbook(wbPath)
wb.save(wbPath)
#Use xlwings to open and compute equations
wb = xw.Book(wbPath)
app = xw.apps.active
wb.save(wbPath)
app.quit()
This will cause all the equations to regenerate and still allows you to use openpyxl for the rest of the workbook manipulation.
Unfortunately no, there is no way of doing this with openpyxl. All openpyxl does is read the raw data from the excel file, and it so happens that Excel will save a "cached" version of the computation when the file is opened/saved. openpyxl can exploit this and return that data as of the last time it was computed, but it stays "dumb" in the way that it does not re-compute or re-evaluates formulae, etc.
If you want to evaluate formulae you'll have to either find or write a basic parser, or you might want to look at something like xlwings which can interact with Excel at runtime.