使用python xlrd获取Excel单元格公式

Question

我需要把一个算法从Excel表格转移到Python代码，但我得先从Excel文件中逆向工程这个算法。

这个Excel表格相当复杂，里面有很多单元格，这些单元格里有公式，而这些公式还可能引用其他单元格（那些单元格也可能有公式或者常量）。

我的想法是用一个Python脚本来分析这个表格，建立一个单元格之间依赖关系的表格，也就是说：

A1依赖于B4、C5、E7，公式是："=sqrt(B4)+C5*E7"
A2依赖于B5、C6，公式是："=sin(B5)*C6"
...

xlrd这个Python模块可以读取XLS工作簿，但目前我只能获取单元格的值，而不能获取公式。

比如，使用下面的代码我可以简单地获取一个单元格的值：

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)

不过，似乎没有办法从通过.cell(...)方法返回的单元格对象中获取公式。在文档中，他们说可以获取公式的字符串版本（是英文的，因为Excel文件中没有存储函数名称翻译的信息）。他们提到在Name和Operand类中有公式（表达式），但我不明白如何通过Cell类的实例获取这些类的实例。

你能给我一个代码示例，帮助我从单元格中获取公式文本吗？

Answer 1

我知道这个帖子很老了，但我找到了一种不错的方法，可以从工作簿中的所有表格获取公式，同时让新创建的工作簿保留所有的格式。

第一步是把你的 .xlsx 文件另存为 .xls 格式——在下面的代码中使用 .xls 作为文件名。

使用 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

字典 'sheet_formulas' 的结构

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

示例结果：

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

Answer 2

更新: 我已经实现了一个小库，正好可以做你所描述的事情：从Excel表格中提取单元格和依赖关系，并将它们转换成Python代码。代码可以在GitHub上找到，欢迎提交补丁 :)

---

另外，你可以使用win32com与Excel进行交互（虽然速度不快，但可以用）。这样你就可以获取公式了。这里有一个教程 [缓存副本]，详细信息可以在这一章 [缓存副本]中找到。

基本上，你只需要这样做：

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

至于构建单元格依赖关系的表格，解析Excel表达式是个棘手的事情。如果我没记错的话，你提到的Trace代码并不总是能正确处理这个问题。我见过的最好的方法是E. W. Bachtal的算法，有一个Python实现，效果很好。

Answer 3

[免责声明]：我是 xlrd 的作者和维护者。

文档中提到的公式文本是关于“名称”公式的；可以查看文档开头的“命名引用、常量、公式和宏”部分。这些公式是与整个工作表或整个工作簿关联的名称，而不是与单个单元格关联的。例如：PI 对应于 =22/7，SALES 对应于 =Mktng!$A$2:$Z$99。名称公式的反编译工具是为了支持检查一些简单的和常见的定义名称的用法。

公式一般有几种类型：单元格公式、共享公式和数组公式（这些都直接或间接与单元格相关），还有名称公式、数据验证和条件格式。

将一般公式从字节码反编译成文本的工作还在进行中，进展缓慢。需要注意的是，如果这个功能可用，你还需要解析文本公式以提取单元格引用。正确解析Excel公式并不简单；就像处理HTML一样，使用正则表达式看起来简单，但实际上并不奏效。直接从公式的字节码中提取引用会更好。

另外，单元格公式可以引用名称，而名称公式可以同时引用单元格和其他名称。因此，有必要从单元格公式和名称公式中提取单元格和名称的引用。如果你能获取共享公式的信息，那会很有用；否则，解析以下内容后：

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

你需要自己推断 B3:B60 公式之间的相似性。

无论如何，上述内容在短时间内都不太可能可用——xlrd 的优先事项在其他地方。