Get formula from Excel cell with python xlrd

Question

I have to port an algorithm from an Excel sheet to python code but I have to reverse engineer the algorithm from the Excel file.

The Excel sheet is quite complicated, it contains many cells in which there are formulas that refer to other cells (that can also contains a formula or a constant).

My idea is to analyze with a python script the sheet building a sort of table of dependencies between cells, that is:

A1 depends on B4,C5,E7 formula: "=sqrt(B4)+C5*E7"
A2 depends on B5,C6 formula: "=sin(B5)*C6"
...

The xlrd python module allows to read an XLS workbook but at the moment I can access to the value of a cell, not the formula.

For example, with the following code I can get simply the value of a cell:

import xlrd

#open the .xls file
xlsname="test.xls"
book = xlrd.open_workbook(xlsname)

#build a dictionary of the names->sheets of the book
sd={}
for s in book.sheets():
    sd[s.name]=s

#obtain Sheet "Foglio 1" from sheet names dictionary
sheet=sd["Foglio 1"]

#print value of the cell J141
print sheet.cell(142,9)

Anyway, It seems to have no way to get the formul from the Cell object returned by the .cell(...) method. In documentation they say that it is possible to get a string version of the formula (in english because there is no information about function name translation stored in the Excel file). They speak about formulas (expressions) in the Name and Operand classes, anyway I cannot understand how to get the instances of these classes by the Cell class instance that must contains them.

Could you suggest a code snippet that gets the formula text from a cell?

score 25 · Accepted Answer · answered Jan 14 '11 at 20:16

[Dis]claimer: I'm the author/maintainer of xlrd.

The documentation references to formula text are about "name" formulas; read the section "Named references, constants, formulas, and macros" near the start of the docs. These formulas are associated sheet-wide or book-wide to a name; they are not associated with individual cells. Examples: PI maps to =22/7, SALES maps to =Mktng!$A$2:$Z$99. The name-formula decompiler was written to support inspection of the simpler and/or commonly found usages of defined names.

Formulas in general are of several kinds: cell, shared, and array (all associated with a cell, directly or indirectly), name, data validation, and conditional formatting.

Decompiling general formulas from bytecode to text is a "work-in-progress", slowly. Note that supposing it were available, you would then need to parse the text formula to extract the cell references. Parsing Excel formulas correctly is not an easy job; as with HTML, using regexes looks easy but doesn't work. It would be better to extract the references directly from the formula bytecode.

Also note that cell-based formulas can refer to names, and name formulas can refer both to cells and to other names. So it would be necessary to extract both cell and name references from both cell-based and name formulas. It may be useful to you to have info on shared formulas available; otherwise having parsed the following:

B2 =A2
B3 =A3+B2
B4 =A4+B3
B5 =A5+B4
...
B60 =A60+B59

you would need to deduce the similarity between the B3:B60 formulas yourself.

In any case, none of the above is likely to be available any time soon -- xlrd priorities lie elsewhere.

There are any example of the use of the Name Class of xlrd module? — Trimax, Mar 19 '14 at 16:39

score 14 · Answer 2 · edited Jan 13 '20 at 05:43

Update: I have gone and implemented a little library to do exactly what you describe: extracting the cells & dependencies from an Excel spreadsheet and converting them to python code. Code is on github, patches welcome :)

Just to add that you can always interact with excel using win32com (not very fast but it works). This does allow you to get the formula. A ~~tutorial can be found here~~ [cached copy] and details can be found ~~in this chapter~~ [cached copy].

Essentially you just do:

app.ActiveWorkbook.ActiveSheet.Cells(r,c).Formula

As for building a table of cell dependencies, a tricky thing is parsing the excel expressions. If I remember correctly the Trace code you mentioned does not always do this correctly. The best I have seen is the algorithm by E. W. Bachtal, of which a python implementation is available which works well.

mkultra · Answer 3 · 2016-11-13T18:05:38.870

So I know this is a very old post, but I found a decent way of getting the formulas from all the sheets in a workbook as well as having the newly created workbook retain all the formatting.

First step is to save a copy of your .xlsx file as .xls -- Use the .xls as the filename in the code below

Using Python 2.7

from lxml import etree
from StringIO import StringIO
import xlsxwriter
import subprocess
from xlrd import open_workbook
from xlutils.copy import copy
from xlsxwriter.utility import xl_cell_to_rowcol
import os



file_name = '<YOUR-FILE-HERE>'
dir_path = os.path.dirname(os.path.realpath(file_name))

subprocess.call(["unzip",str(file_name+"x"),"-d","file_xml"])


xml_sheet_names = dict()

with open_workbook(file_name,formatting_info=True) as rb:
    wb = copy(rb)
    workbook_names_list = rb.sheet_names()
    for i,name in enumerate(workbook_names_list):
        xml_sheet_names[name] = "sheet"+str(i+1)

sheet_formulas = dict()
for i, k in enumerate(workbook_names_list):
    xmlFile = os.path.join(dir_path,"file_xml/xl/worksheets/{}.xml".format(xml_sheet_names[k]))
    with open(xmlFile) as f:
        xml = f.read()

    tree = etree.parse(StringIO(xml))
    context = etree.iterparse(StringIO(xml))

    sheet_formulas[k] = dict()
    for _, elem in context:
        if elem.tag.split("}")[1]=='f':
            cell_key = elem.getparent().get(key="r")
            cell_formula = elem.text
            sheet_formulas[k][cell_key] = str("="+cell_formula)

sheet_formulas

Structure of Dictionary 'sheet_formulas'

{'Worksheet_Name': {'A1_cell_reference':'cell_formula'}}

Example results:

{u'CY16': {'A1': '=Data!B5',
  'B1': '=Data!B1',
  'B10': '=IFERROR(Data!B12,"")',
  'B11': '=IFERROR(SUM(B9:B10),"")',

score 2 · Answer 4 · answered Jan 14 '11 at 13:47

2

It seems that it is impossible now to do what you want with xlrd. You can have a look at this post for the detailed description of why it is so difficult to implement the functionality you need.

Note that the developping team does a great job for support at the python-excel google group.

answered Jan 14 '11 at 13:47

Steve

234
3
8

Of course, the availability of an Excel parser is a great stuff yet but i really hope to get even the formula references informations. Anyway, I found a dependency tracer written in VB macro and that creates a graph of dependency using graphwiz on http://www.christopherteh.com/trace/ . It would be better to have a table of dependency that allows a faster analysis and porting of the algorithm in python. – alexroat Jan 14 '11 at 14:31

score 0 · Answer 5 · answered Nov 28 '19 at 04:44

0

Ye! With win32com it's works for me.

import    win32com.client
Excel = win32com.client.Dispatch("Excel.Application")

# python -m pip install pywin32
file=r'path Excel file'
wb = Excel.Workbooks.Open(file)
sheet = wb.ActiveSheet

#Get value
val = sheet.Cells(1,1).value
# Get Formula
sheet.Cells(6,2).Formula

answered Nov 28 '19 at 04:44

Kairat Koibagarov

1,385
15
9

yes but it is out of topic. Here you're not using xldr, you're using native Excel COM (so you need MS Office installed) – alexroat Dec 06 '19 at 14:48

score 0 · Answer 6 · answered Aug 22 '23 at 08:46

You can use cell.data_type to find whether a cell contains a formula or not. If it returns 'f' that means it is a formula.

import openpyxl
wb = openpyxl.load_workbook(os.path.join(folder_name,filename),data_only=False)
ws = wb['Sheet1']
all_rows = list(ws.rows)
print(f"Found {len(all_rows)} rows of data.")
print("\nFirst rows of data:")
for row in all_rows[:5]:
    print(row)
    
for cell in all_rows[1]:
    print(cell.data_type,cell.value)

The result for above code is:

Found 40 rows of data.

First rows of data:
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>, <Cell 'Sheet1'.F1>, <Cell 'Sheet1'.G1>, <Cell 'Sheet1'.H1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>, <Cell 'Sheet1'.F2>, <Cell 'Sheet1'.G2>, <Cell 'Sheet1'.H2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.F3>, <Cell 'Sheet1'.G3>, <Cell 'Sheet1'.H3>)
(<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.E4>, <Cell 'Sheet1'.F4>, <Cell 'Sheet1'.G4>, <Cell 'Sheet1'.H4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>, <Cell 'Sheet1'.E5>, <Cell 'Sheet1'.F5>, <Cell 'Sheet1'.G5>, <Cell 'Sheet1'.H5>)
s John Doe
n 30
s Male
s Manager
n 60000
f =IF(AND(C2="Male", D2="Manager"), E2*1.1,
 IF(AND(C2="Female", D2="Director"), E2*1.15,
 E2*1.05))
n 1
s MMaCODE1

score -1 · Answer 7 · answered Jul 27 '13 at 13:10

I know this post is a little late but there's one suggestion that hasn't been covered here. Cut all the entries from the worksheet and paste using paste special (OpenOffice). This will convert the formulas to numbers so there's no need for additional programming and this is a reasonable solution for small workbooks.

Get formula from Excel cell with python xlrd

7 Answers7

Linked

Related