使用 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(...) 返回的 Cell 对象中获取公式方法。 在文档中，他们说可以获取公式的字符串版本（英文，因为 Excel 文件中没有存储有关函数名称翻译的信息）。他们谈论 Name 和 Operand 类中的公式（表达式），无论如何我无法理解如何通过 Cell 获取这些类的实例必须包含它们的类实例。

您能否建议一个从单元格获取公式文本的代码片段？

Answer 1

[Dis]声明者：我是 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优先级在其他地方。

Answer 2

更新：我已经实现了一个小库来完全按照您的描述进行操作：提取单元格和数据。来自 Excel 电子表格的依赖项并将其转换为 Python 代码。代码 在 github 上，欢迎补丁:)

---

只是补充一下，您始终可以使用 win32com （不是很快，但它可以工作）。这确实可以让您获得公式。 可以在此处找到教程 [缓存副本]，详细信息可参见本章 [缓存副本]。

本质上你只需要做：

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

至于构建单元格依赖关系表，一件棘手的事情是解析 Excel 表达式。如果我没记错的话，您提到的跟踪代码并不总是正确执行此操作。我见过的最好的是EW Bachtal 的算法，其中有一个 python 实现，效果很好。

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),"")',

Answer 4

看来现在用xlrd做你想做的事情是不可能的了。您可以查看 这篇文章详细说明了为什么实现您需要的功能如此困难。

请注意，开发团队在 python-excel google 小组的支持方面做得非常出色。

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

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