Openpyxl - How To Return Both The Calculated And Formula Components Of A Cell For Any Given Workbook

At work we use Excel docs to perform analyses on samples, and one worksheet in the workbook is the 'Top Sheet', where all the important parts of the data are collated into a nice t

Solution 1:

It is possible by modifying openpyxl's code. Note that I've made these changes on openpyxl 2.2.5 which is not the latest version. Therefore line numbers will probably be different for you.

This is a quick monkey patching and most probably not the best way of doing this but it should get you going.

Note that these changes won't make openpyxl to re-calculate the values if you modify a formula (as Charlie Clark said in his answer). This will simply cause load_workbook to retrieve both the cells' values and formulas instead of the need to call it twice.

\openpyxl\cell\cell.py:

line 84:

Add 'formula' to __slots__:

 __slots__ =  (
        'column',
        'row',
        'coordinate',
        '_value',
        'formula',      
        'data_type',
        'parent',
        'xf_index',
        '_hyperlink_rel',
        '_comment')

line 111: Change Cell's __init__ to accept formula arg with default argument None:

def__init__(self, worksheet, column, row, value=None, formula=None, fontId=0,
                 fillId=0, borderId=0, alignmentId=0, protectionId=0, numFmtId=0,
                 pivotButton=None, quotePrefix=None, xfId=None):

And initialize it in __init__ body:

self.formula = formula

\openpyxl\reader\worksheet.py

line 111:

We don't really care about data_only anymore, so change the line from if formula is not None and not self.data_only: to if formula is not None:.

lines 113 - 116:

Change the following lines from

if formula.text:
    value = "=" + formula.textelse:
    value = "="

to

if formula.text:
    _formula = "=" + formula.textelse:
    _formula = "="

A few lines bellow these lines you should see

else:
    cell._value=value
    cell.data_type=data_type

Immediately below these lines add:

try:
    cell.formula = _formula
except UnboundLocalError:
    pass

That's it! let's test it:

I created a new xlsx file. Cell A1 has the formula =1+1 and cell A2 has no formula, just the plain value of 2.

wb = load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

cell = sheet.cell(row=1, column=1)
print(cell.value)
print(cell.formula)

>>2>> =1+1

cell = sheet.cell(row=2, column=1)
print(cell.value)
print(cell.formula)

>>2>> None

Note that this will work regardless of the value of data_only that was passed to open_workbook.