1

I try with Python 3.4 to get the value of cell C1 of an Excel file. cell C1 is a formula: =A1+B1. Note: A1 and B1 value are changing, thus the return value in C1 has to change automatically accordingly.

In Python 3.4, I use following codes:

import openpyxl
from openpyxl import Workbook

wb = openpyxl.load_workbook('test.xlsx')
sheet1 = wb['Sheet1']
C1Value = sheet1['C1'].value
print('C1: ', C1Value)

======

When running the python program, I obtain: C1: =A1+B1

What I want to obtain is:

A1=1, B1=2 then C1: 3 (instead of C1: =A1+B1)

and A1=10, B1=20 then automatically C1: 30 (instead of C1: =A1+B1)

Any help, idea would be greatly appreciated. Thanks.

Stan
  • 19
  • 2
  • 1
    Possible duplicate of [Read Excel cell value and not the formula computing it -openpyxl](http://stackoverflow.com/q/28517508/1072229) – Grisha Levit Jan 17 '17 at 03:44
  • 1
    Possible duplicate of [openpyxl error: "could not convert string to float"](http://stackoverflow.com/questions/41449712/openpyxl-error-could-not-convert-string-to-float) – e4c5 Jan 17 '17 at 03:44

2 Answers2

1

A very quick look on the openpyxl docs and you might find this page saying

openpyxl never evaluates formula

Before going any further, it should be noted that the possible duplicate links have answers pertaining to other modules, and these are usually the accepted answers. With those modules you don't have to implement your own parsing. Which can be anywhere from challenging to near impossible for more complex systems.

If you're doing pretty simple stuff, it's not terribly difficult with the Tokenizer class. Usage:

>>> from openpyxl.formula import Tokenizer
>>> tok = Tokenizer("""=IF($A$1,"then True",MAX(DEFAULT_VAL,'Sheet    2'!B1))""")
>>> print("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
         IF(       FUNC     OPEN
        $A$1    OPERAND    RANGE
           ,        SEP      ARG
 "then True"    OPERAND     TEXT
           ,        SEP      ARG
        MAX(       FUNC     OPEN
 DEFAULT_VAL    OPERAND    RANGE
           ,        SEP      ARG
'Sheet 2'!B1    OPERAND    RANGE
           )       FUNC    CLOSE
           )       FUNC    CLOSE

Pay special attention, everything is a string, so evaluating formulas is best done with eval()

So if you edited your code a bit (new/changed lines marked with #),

import openpyxl
from openpyxl import Workbook
from openpyxl.formula import Tokenizer     #

wb = openpyxl.load_workbook('test.xlsx')
sheet1 = wb['Sheet1']
C1Formula = sheet1['C1'].value             #
C1Token = Tokenizer(C1Formula)             #
C1Value = handle_token(sheet1, C1Token)    #
print('C1: ', C1Value)

Where handle_token() is up to you for how complicated you wanted it. A couple simple and perhaps hack-y examples for C1: =A1 + B1 and other binary operators: (these don't work for ^ as python uses that for bitwise XOR)

def handle_token(sheet, token):
    formula = ""
    for t in token.items:
        if t.type is 'OPERAND':
            formula += str(sheet[t.value])
        else:
            formula += str(t.value)
    return eval(formula)

Or if you prefer (this can be converted to a one-liner, not recommended) using a generator:

def handle_token(sheet, token):
    parsed = str(sheet[t.value].value) if t.type is 'OPERAND' else t.value for t in token.items
    formula = "".join(parsed)
    return eval(formula)
Mxt
  • 166
  • 2
  • 17
  • 2
    It work like a charm by using: wb = openpyxl.load_workbook('test.xlsx', data_only=True) – Stan Jan 17 '17 at 12:47
0

you need to add data_only=True when calling load_workbook, like this:

wb = openpyxl.load_workbook('test.xlsx', data_only=True)
nmz787
  • 1,960
  • 1
  • 21
  • 35