Pandas读取格式化的Excel文件

Question

我如何获取电子表格中按格式显示的值？我正在处理货币格式的电子表格。

比如这个：

ITEM NAME UNIT PRICE
item1     USD 99
item2     SGD 45

但是，使用 Excel 的格式化功能添加的“USD”和“SGD”这些术语并未被 pandas 的 read_excel 函数看到。我可以获取数值，但无法获得货币名称。我只能按照现有的电子表格进行操作，考虑到我有几个包含大约 6-7 个工作表的电子表格，我希望有一个 Pandas（或 Python）层面的解决方案，而不是基于 Excel 的解决方案。

谢谢大家。

对于丹尼尔，这就是我实现“xlrd”引擎的方式，但似乎没有起到任何作用。

excel = pd.ExcelFile('itemlist.xlsx', sheetname=None)
master = pd.DataFrame(None)

for sheet in excel.sheet_names:
    df = pd.read_excel(excel,sheet,header=2, engine='xlrd')
    master=master.append(df)

- carlo

---

pandas 内部使用 xlrd，它可以解析格式信息。 - Daniel

@Daniel编辑了我的帖子，展示了我如何实现xlrd引擎，尽管我似乎还缺少一些东西。 - carlo

Answer 1

没有一种很好的方法可以做到这一点。pandas 不了解数字格式，而 xlrd 似乎无法从 .xlsx 文件中读取格式 - 详见这里

你可以使用 openpyxl 来完成这个任务，它至少可以访问数字格式，但是看起来你需要自己实现所有的解析逻辑。

In [26]: from openpyxl import load_workbook

In [27]: wb = load_workbook('temp.xlsx')

In [28]: ws = wb.worksheets[0]

In [29]: ws.cell("B2")  # numeric value = 4, formatted as "USD 4"
Out[29]: <Cell Sheet1.B2>

In [30]: ws.cell("B2").value
Out[30]: 4

In [31]: ws.cell("B2").number_format
Out[31]: '"USD "#'

- chrisb

Answer 2

对于有同样问题的任何人，我通过将每个工作表分离为CSV文件来解决了它，使用了这个答案。

它保留了电子表格中显示的货币细节，虽然有点繁琐，但总之完成了任务。

- carlo

---

这个答案对我帮助很大！最终我使用命令行上的 unoconv 批量将所有的 Excel 文件（超过 1k！）转换成 CSV 文件，然后再用 python 处理它们。 - EA Bubnoff

Answer 3

老实说，xlrd 允许您访问单元格格式信息。

book = xlrd.open_workbook("tmp/test.xls", formatting_info=True)  
xf_index = book.sheet_by_index(0).cell_xf_index(5,7)  
xf = book.xf_list[xf_index]  
fmt = book.format_map[xf.format_key]  
print(fmt.format_str, fmt.type)  

- Roman Suprotkin

---

这仅适用于“.xls”文件格式，而不适用于“.xlsx”文件格式。 - Malgo

@Malgo 你说得对。没有一个库可以同时处理这两种格式。你需要为每种格式单独实现它。我只是展示了如何为“.xls”格式实现它。 - Roman Suprotkin