python - 使用 openpyxl 读取为时间日期的 float 数值

Question

我有一个 Excel 电子表格，其中有一个字段包含小的 %f.2 值，例如 1.2、1.07、2.3 等，出于某种原因，openpyxl 将这些单元格读取为 1900 年的日期。我已经多次看到这个问题被提出，但通常这些用户期待一个约会并且得到一个虚假的约会。我期待一个值，通常 x<10.0，我得到大约 30-40% 的“坏”数据(读取为时间日期)，而其他时间则读取为数值。

我正在使用迭代器，所以我调用了一个简单的 ws.iter_rows() 来一次提取一行数据。我试图将其“强制转换”为先前创建的包含数值的变量，但这并没有多大用处。

有没有人对如何克服这个偶发问题有建议。如果这是一个已知错误，是否有任何已知的解决方法？

我发现如果我将文件存储为 csv，然后将其重新打开为 csv，然后将其重新存储为 xlsx，我将得到一个我可以正确读取的文件。虽然这有助于调试代码，但我需要一个我的客户可以使用而无需跳过这些步骤的解决方案。

我认为，如果该列的格式不正确，它将应用于所有元素，因此间歇性出现这种情况会让人感到困惑。

import openpyxl
from openpyxl import load_workbook

# Source workbook - wb

wb = load_workbook(filename = r'C:\data\TEST.xlsx' , use_iterators = True)
ws = wb.get_sheet_by_name(name ='QuoteFile ')

for row in ws.iter_rows():
        print(row[0].internal_value ,row[3].internal_value ,row[4].internal_value         ,row[5].internal_value)


print('Done')

这是我从 excel 表中看到的输入

20015   2.13    1.2 08/01/11
20015   5.03    1.2 08/01/11
20015   5.03    1.2 08/01/11
20015   5.51    1.2 08/01/11
20015   8.13    1.2 08/01/11
20015   5.60    1.2 08/01/11
20015   5.03    1.2 08/01/11
20015   1.50    1.2 08/01/11
20015   1.50    1.2 08/01/11
20015   1.50    1.2 08/01/11
20015   1.50    1.2 08/01/11
20015   1.50    1.2 08/01/11
20015   1.50    1.2 08/01/11

这是我的输出，您可以看到前七行将第二个字段指示为 1900 年的日期，而第 8-13 行将该字段正确显示为数字字段:

20015.0 1900-01-02 03:07:12 1.2 2011-08-01 00:00:00
20015.0 1900-01-05 00:43:12 1.2 2011-08-01 00:00:00
20015.0 1900-01-05 00:43:12 1.2 2011-08-01 00:00:00
20015.0 1900-01-05 12:14:24 1.2 2011-08-01 00:00:00
20015.0 1900-01-08 03:07:12 1.2 2011-08-01 00:00:00
20015.0 1900-01-05 14:24:00 1.2 2011-08-01 00:00:00
20015.0 1900-01-05 00:43:12 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00
20015.0 1.5 1.2 2011-08-01 00:00:00

使用 python 3.3 和 openpyxl 1.6.2

Answer 1

免责声明:我不知道如何使用 openpyxl。然而，您通常只需要担心 datetime 模块。

如果您知道哪些行应该是数字，您可以尝试这样的代码将 Excel 日期格式转换为 float ，如果是数字则忽略它:

import datetime
import openpyxl
from openpyxl import load_workbook

# Source workbook - wb

wb = load_workbook(filename = r'C:\data\TEST.xlsx' , use_iterators=True)
ws = wb.get_sheet_by_name(name='QuoteFile ')

If val's a number, return it. Otherwise, take the difference between the datetime
and 1899-12-31 00:00:00. The way the datetimes work is they're internally a float,
being the number of days since the start of 1900. We get the number of seconds in
the delta (done through subtraction) and divide that by 86400 (the number of seconds
in a day).
def forcefloat(val):
    """If val's a number, return it. Otherwise, take the difference between the
    datetime and 1899-12-31 00:00:00. The way the datetimes work is they're
    internally a float, being the number of days since the start of 1900.
    We get the number of seconds in the delta (done through subtraction)
    and divide that by 86400 (the number of seconds in a day)."""
    if isinstance(val, (int, float)):
        return val
    assert isinstance(val, datetime.datetime)
    return (val - datetime.datetime(1899,12,31,0,0,0)).total_seconds() / 86400

for row in ws.iter_rows():
        print(
            row[0].internal_value,
            forcefloat(row[3].internal_value),
            row[4].internal_value,
            row[5].internal_value,
        )

print('Done')

不完全是最优雅的解决方案，但它确实有效。