使用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类实例来获取这些类的实例。你能提供一个代码片段，从单元格中获取公式文本吗？

- alexroat

Answer 1

[免责声明]: 我是 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 的优先级在其他方面。

- John Machin

---

xlrd模块的Name类有使用示例吗？ - Trimax

Answer 2

更新：我已经实现了一个小型库，完全符合您所描述的要求：从Excel电子表格中提取单元格和依赖关系，并将它们转换为Python代码。 代码在Github上，欢迎提交补丁 :)

---

还要补充一点，您可以始终使用win32com与Excel进行交互（虽然速度不是很快，但能够工作）。这确实可以让您获取公式。一个教程可以在此找到 [缓存副本]，详细信息可以在[缓存副本]的[本章节]找到。

基本上，您只需要执行：

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

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

- dgorissen

Answer 3

我知道这是一个非常老的帖子，但我找到了一个不错的方法，可以获取工作簿中所有工作表的公式，并使新创建的工作簿保留所有格式。

第一步是将你的 .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),"")',

- mkultra

Answer 4

看起来现在使用xlrd做你想要的事情似乎是不可能的。你可以查看这篇帖子，详细描述了为什么实现你所需的功能如此困难。

请注意，开发团队在python-excel谷歌组提供了出色的支持。

- Steve

---

当然，拥有一个Excel解析器是很棒的，但我真的希望能够获得公式引用信息。无论如何，我在VB宏中找到了一个依赖跟踪器，并且可以使用graphwiz在http://www.christopherteh.com/trace/上创建依赖关系图。最好有一个依赖表，以便更快地分析和将算法移植到Python中。 - alexroat

Answer 5

耶！使用win32com对我很有效。

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

- Kairat Koibagarov

---

是的，但这不是主题。在这里，您不是使用xldr，而是使用本机Excel COM（因此需要安装MS Office）。 - alexroat

Answer 6

你可以使用 cell.data_type 来判断单元格是否包含公式。如果返回'f'，那就表示它是一个公式。

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) 

以上代码的结果是：

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

- Prakhar Gurawa

Answer 7

我知道这篇文章有点晚了，但是还有一个建议没有被提到。从工作表中剪切所有条目，并使用“粘贴特殊”（OpenOffice）进行粘贴。这将把公式转换为数字，因此无需额外的编程，对于小型工作簿来说，这是一个合理的解决方案。

- Erik