如何在 Pandas/python 中查看 Excel 电子表格的公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42102674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I see the formulas of an excel spreadsheet in pandas / python?
提问by ThrowAway23948238
I would like to read in an excel spreadsheet to python / pandas, but have the formulae instead of the cell results.
我想在 excel 电子表格中读取 python/pandas，但有公式而不是单元格结果。
For example, if cell A1 is 25, and cell B1 is =A1, I would like my dataframe to show:
例如，如果单元格 A1 是 25，而单元格 B1 是 =A1，我希望我的数据框显示：
25 =A1
Right now it shows:
现在它显示：
25 25
How can I do so?
我该怎么做？
回答by Aleksey Bilogur
OpenPyXLprovides this capacity out-of-the-box. See hereand here. An example:
OpenPyXL提供这种开箱即用的容量。请参阅此处和此处。一个例子：
from openpyxl import load_workbook
import pandas as pd
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_names = wb.get_sheet_names()
name = sheet_names[0]
sheet_ranges = wb[name]
df = pd.DataFrame(sheet_ranges.values)
回答by user5214530
Actually, it is doable. You currently have something like this
其实，这是可行的。你目前有这样的事情
import pandas as pd
import numpy as np
a2_value = "=A1"
data = [1, "=A1", 3, 4]
s = pd.Series(list(data))
writer = pd.ExcelWriter('output.xlsx')
s.to_excel(writer, 'Sheet1', header=False, index=False)
writer.save()
What you can do is actually this:
你可以做的实际上是这样的：
import pandas as pd
import numpy as np
data = [1, '=CONCATENATE("=A1", '')', 3, 4]
s = pd.Series(list(data))
writer = pd.ExcelWriter('output.xlsx')
s.to_excel(writer, 'Sheet1', header=False, index=False)
writer.save()
That's as well formula, but =A1 will be visible instead of its value.
这也是公式，但 =A1 将是可见的，而不是它的值。
回答by Emma Brown
Yes, it is doable. I recently found a package that solves this issue in a quite sophisticated way. It is called portable-spreadsheet (available via pip install portable-spreadsheet). It basically encapsulates xlsxwriter. Simple example:
是的，这是可行的。我最近发现了一个包，它以一种非常复杂的方式解决了这个问题。它称为便携式电子表格（可通过 获得pip install portable-spreadsheet）。它基本上封装了xlsxwriter. 简单的例子：
import portable_spreadsheet as ps
sheet = ps.Spreadsheet.create_new_sheet(5, 5)
# Set values
sheet.iloc[0, 0] = 25 # Set A1
sheet.iloc[1, 0] = sheet.iloc[0, 0] # reference to A1
# Export to Excel
sheet.to_excel('output/sample.xlsx')
