pandas 将Excel读取为格式化的 [英] pandas read excel as formatted

问题描述

如何格式化电子表格中的值?我正在处理具有货币格式的电子表格

how do i get the values of a spreadsheet as they are formatted? im working on spreadsheets with a currency format

例如:

ITEM NAME UNIT PRICE
item1     USD 99
item2     SGD 45

，但是术语"USD"和"SGD"是使用excel的格式化功能添加的，而pandas的read_excel函数看不到.我会得到的值，但不是货币名称.我只能按原样处理电子表格，并且鉴于我有各种电子表格，每个电子表格约6-7张纸，所以我希望有一个熊猫(或python)级别的解决方案，而不是excel级别的解决方案.

but the terms 'USD' and 'SGD' were added using the formatting capabilities of excel, and is not seen by the read_excel function of pandas. i would get the values, but not the currency name. i could only work on the spreadsheets as it is, and given that i have various spreadsheets with about 6-7 sheets each, i was hoping to have a pandas (or python)-level solution rather than an excel-level solution.

谢谢大家.

对于丹尼尔，这就是我实现"xlrd"引擎的方式，该引擎似乎什么也没做.

to Daniel, this is how i implemented the 'xlrd' engine, which didn't seem to do anything.

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)

推荐答案

没有什么好方法. pandas不知道数字格式，并且xlrd似乎无法从.xlsx文件读取格式-请参见

There's not any great way to do this. pandas has no knowledge of the number formats, and xlrd doesn't seem to be able to read formats from a .xlsx file - see here

您可以使用openpyxl完成此操作，它至少可以访问数字格式，但是看起来您必须自己基本实现所有解析逻辑.

You could use openpyxl to accomplish this, it at least has access to the number formats, but it looks like you'd have to basically implement all the parsing logic yourself.

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 "#'

这篇关于 pandas 将Excel读取为格式化的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！