BUG: date_format and datetime_format arguments to ExcelWriter are currently ignored by default openpyxl engine

[gaibo]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the master branch of pandas.

Reproducible Example

import pandas as pd
with pd.ExcelWriter('TEST.xlsx', datetime_format='YYYY-MM-DD') as writer:
    test_df = pd.DataFrame([9000]*10, index=[pd.Timestamp('2021-11-02')]*10)
    test_df.to_excel(writer)

Issue Description

In pandas version 0.24.2, the above example would output nicely formatted dates. In pandas version 1.3.4, it outputs YYYY-MM-DD HH:MM:SS format, ignoring the datetime_format field.
This is probably as simple as openpyxl becoming the new default engine but not being configured for date formatting.

Expected Behavior

In written Excel sheet, YYYY-MM-DD format dates instead of YYYY-MM-DD HH:MM:SS format dates.

Installed Versions

INSTALLED VERSIONS

commit : 73c6825
python : 3.9.7.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19042
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 13, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252
pandas : 1.3.3
numpy : 1.20.3
pytz : 2021.1
dateutil : 2.8.2
pip : 21.2.4
setuptools : 58.0.4
Cython : None
pytest : 6.2.4
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : 1.3.2
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.4.3
numexpr : 2.7.3
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : 1.7.1
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[asishm]

This is probably as simple as openpyxl becoming the new default engine but not being configured for date formatting.

The default engine for writing hasn't changed - it's still xlsxwriter, but in this case you don't have xlsxwriter so it uses openpyxl. If you install xlsxwriter, you'll see the previous behavior.

as for the issue - it seems to be because these (date_format, datetime_format) args are not being propagated to the base class

pandas/pandas/io/excel/_openpyxl.py

Lines 31 to 58 in 2e29e11

	class OpenpyxlWriter(ExcelWriter):
	engine = "openpyxl"
	supported_extensions = (".xlsx", ".xlsm")
	def __init__(
	self,
	path,
	engine=None,
	date_format=None,
	datetime_format=None,
	mode: str = "w",
	storage_options: StorageOptions = None,
	if_sheet_exists: str | None = None,
	engine_kwargs: dict[str, Any] | None = None,
	**kwargs,
	):
	# Use the openpyxl module as the Excel writer.
	from openpyxl.workbook import Workbook
	engine_kwargs = combine_kwargs(engine_kwargs, kwargs)
	super().__init__(
	path,
	mode=mode,
	storage_options=storage_options,
	if_sheet_exists=if_sheet_exists,
	engine_kwargs=engine_kwargs,
	)

[gaibo]

The default engine for writing hasn't changed - it's still xlsxwriter, but in this case you don't have xlsxwriter so it uses openpyxl. If you install xlsxwriter, you'll see the previous behavior.

Hey thanks for the quick response! You are absolutely right, I somehow missed the following in ExcelWriter class while glancing at source yesterday:
Default is to use : * xlwt for xls * xlsxwriter for xlsx if xlsxwriter is installed otherwise openpyxl * odf for ods. See DataFrame.to_excel for typical usage.
I CTRL-F'd for "engine" and read the defaults for the reader instead. Well, this solved my use case haha but hope someone is up for the actual issue.

[sebastian-de]

The above example works fine for me until v1.2.5.
Since v1.3.0 date_format and datetime_format are ignored. I suspect this is due to PR #40430
In 1.3.x, XlsxWriter super().init() has

            date_format=date_format,
            datetime_format=datetime_format,

which is missing in OpenpyxlWriter super().init()
I'm off to a short holiday, but next week I'll try if adding these lines to OpenpyxlWriter fixes the problem.

[driedwater]

take