shift default excel read engine from xlrd to openpyxl

[leo4183]

since v2.0.0, xlrd no longer supports excel files other than ".xls". manually specify pd.read_excel engine (to openpyxl and etc) is a bit annoying (otherwise pandas would complain about the missing xlrd module). is it possible to shift the default engine to sth more commonly used (eg. openpyxl )

[rhshadrach]

Thanks for raising this, I was not aware of the recent changes to xlrd. In pandas 1.2, if you have openpyxl installed it will be used as the default for reading non-xls files. See https://pandas.pydata.org/pandas-docs/dev/whatsnew/v1.2.0.html for more info.

With the change to xlrd, I think the messages in the whatsnew and potentially warning messages need to be updated. cc @jorisvandenbossche @jreback

[jorisvandenbossche]

@rhshadrach see #28547 (comment) and my answer below. I think we indeed should check for this version in pandas, and update the error message (and whatsnew message) to make it clearer.

[cjw296]

I'm sorry for causing you folk extra work :-(

[simonjayhawkins]

@rhshadrach see #28547 (comment) and my answer below. I think we indeed should check for this version in pandas, and update the error message (and whatsnew message) to make it clearer.

added the blocker label.