1

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.

EliSquared
  • 1,409
  • 5
  • 20
  • 44
  • The documentation is very clear on this: openpyxl does not evaluate formulae. http://openpyxl.readthedocs.io/en/latest/usage.html#using-formulae If you need this then you can pump the file through Excel or a headless LibreOffice. – Charlie Clark May 08 '17 at 17:37
  • I actually just ended up adding one more step to the open and close process using xlwings. I now save my workbook using openpyxl and then reopen the workbook with xlwings, save it, and then close the workbook. – EliSquared May 08 '17 at 17:42

1 Answers1

1

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.

Antoine
  • 3,880
  • 2
  • 26
  • 44
  • Hmm, so the files that I am opening were actually created by openpyxl by copying worksheets, values and equations from another workbook and then saving the workbook to a new location. Is there anyway to calculate the equations on save or is it the same problem? Alternatively, if I work to create an Excel macro that iterated through the files, opening them and then saving them, then this would reevaluate the equations correctly right? – EliSquared May 08 '17 at 15:13
  • 1
    Yes absolutely this is your problem right there, Excel never opened the files. Programmatically opening Excel and closing it would be a solution. It's the same problem, openpyxl is simply not equipped with all the logic needed to parse and compute Excel's formulae. – Antoine May 08 '17 at 15:14
  • 1
    Ok thanks. I will have to look into either using xlwings or just making a separate macro. – EliSquared May 08 '17 at 15:18