BUG: Roundtrip with openpyxl and datetime precision

[rhshadrach]

The test tests.io.excel.test_writers.test_datetimes fails with openpyxl (now the default, xref #35029):

path = "test.xlsx"
datetimes = [
    datetime(2013, 1, 13, 1, 2, 3),
    datetime(2013, 1, 13, 2, 45, 56),
    datetime(2013, 1, 13, 4, 29, 49),  # <---
    datetime(2013, 1, 13, 6, 13, 42),
    datetime(2013, 1, 13, 7, 57, 35),  # <---
    datetime(2013, 1, 13, 9, 41, 28),
    datetime(2013, 1, 13, 11, 25, 21),
    datetime(2013, 1, 13, 13, 9, 14),
    datetime(2013, 1, 13, 14, 53, 7),
    datetime(2013, 1, 13, 16, 37, 0),
    datetime(2013, 1, 13, 18, 20, 52),
]
write_frame = DataFrame({"A": datetimes})
write_frame.to_excel(path, "Sheet1")
read_frame = pd.read_excel(path, sheet_name="Sheet1", header=0, engine="openpyxl")
tm.assert_series_equal(write_frame["A"], read_frame["A"])

The highlighted lines give

 2013-01-13 04:29:48.999999
 2013-01-13 07:57:35.000001

With these two lines removed, the results are of the form e.g. 2013-01-13 11:25:21 and the test passes.

[rhshadrach]

This is an excel issue - datetimes are stored as floats. It seems odf/xlrd have logic to handle precision issues encountered whereas openpyxl does not. We could implement something similar here, but it seems best to me leave as-is.

https://stackoverflow.com/questions/57530408/python-reads-inaccurately-from-excel-date-time-field

[pgp]

Related issue on openpyxl:
https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1630

[mroeschke]

Agreed looks like an upstream issue and appears to have a related bug report. Closing