[Python] How to make pandas.read_excel with engine='openpyxl' behave like it did with xlrd,...

We have a process that reads data in from an Excel .xlsx spreadsheet into a pandas DataFrame. While trying to upgrade to the latest version (1.2.1) of pandas, I saw the following in the doc for pandas read_excel function for the engine argument:

• “openpyxl” supports newer Excel file formats.

Changed in version 1.2.0: The engine xlrd now only supports old-style .xls files.
​

So, I added engine='openpyxl' to my read_excel function call and started to see strange, new behavior, whereby datetime values now were showing nanoseconds by default, which wasn't the case with xlrd. On top of that, I was seeing datetimes a bit off from the expected value seen in Excel by a few nanoseconds. I saw the same thing with pandas 1.2.1 and also 1.1.4.

For the following Excel data (the raw values show as 44098.0416666667 for the 9/24 date and 44083.6847222222 for both 9/9 dates)



I'm seeing the following behavior:

>>> import pandas as pd
>>> pd.read_excel('~/testDatetimeNanos.xlsx')
TestDate
0 2020-09-24 01:00:00
1 2020-09-09 16:26:00
2 2020-09-09 16:26:00
>>> pd.read_excel('~/testDatetimeNanos.xlsx', engine='openpyxl')
TestDate
0 2020-09-24 01:00:00.000003
1 2020-09-09 16:25:59.999998
2 2020-09-09 16:26:00.000000


I'm wondering if there's a way to use the new openpyxl engine so that its behavior is consistent with the old xlrd engine...?
Also, wondering if I may have stumbled onto a bug (update: submitted bug report).

As of openpyxl ≥3.0.7, the bug has been fixed (Aug 2021)

Continue reading...