如何使用openpyxl从python中的xlsx文件中读取货币符号? [英] How to read a currency symbol from an xlsx file in python using openpyxl?

问题描述

我有一个 .xlsx 文件，其中包含一个组织的国际员工的工资信息.我正在提取他们的一些细节.除了他们的薪水外，一切都很好.

I have an .xlsx file that contains the salary information of international workforce of an organization. I'm extracting some of the their details. All goes fine except for their salaries.

薪水值看起来像:£10,000 或 €34000 等.在 excel 文件中，符号是通过 Format Cell 选项添加的.但我的代码只读取实际数字而没有货币符号.

The salary values look like: £10,000 or €34000 and etc. In the excel file, the symbol was added via the Format Cell option. But my code reads only the actual numbers without the currency symbol.

下面是我的代码:

import openpyxl

wb = openpyxl.load_workbook("International_Employees.xlsx")
for ws in wb:
    for row in iter_rows():
        for cell in row:
            if str(cell.value) == 'SALARY':
                salary = "{1}".format(cell.value, cell.offset(0,1).value)
                print(salary)

输出很简单:10000 而不是 £10,000 或 £10000

The output is simply: 10000 instead of £10,000 or £10000

如何使用 openpyxl 告诉程序也读取符号?

How do I tell the program to read the symbol as well using openpyxl?

推荐答案

您可以使用 number_format 属性来查找，以下是您如何实现该方案的示例:

You could use the number_format property to find out, here is an example of how you could implement that:

c = ws['A1']
fmt = c.number_format
print(fmt) # >>> for me: #,##0.00\ "€"

symbol = re.search(r"[€£]", fmt)
if not symbol:
    print("no currency")
else:
    print(f"{c.value} {symbol.group(0)}") # >>> 1000 €

更新:

更新 2:

for ws in wb:
    for row in iter_rows():
        for cell in row:
            print(cell.value, cell.number_format)

这篇关于如何使用openpyxl从python中的xlsx文件中读取货币符号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！