ENH: Allow passing read_only, data_only and keep_links arguments to openpyxl using engine_kwargs

[dweindl]

Previously it was not possible to override the default values for openpyxl.reader.excel.load_workbook's read_only, data_only and keep_links arguments via pandas.read_excel's engine_kwargs (see #55027).

Now these options can be changed via engine_kwargs.

• closes ENH: _openpyxl.py load_workbook allow to modify the read_only, data_only and keep_links parameters using engine_kwargs #55027
• Tests added and passed if fixing a bug or adding a new feature
• All code checks passed.
• Added type annotations to new arguments/methods/functions.
• Added an entry in the latest doc/source/whatsnew/vX.X.X.rst file if fixing a bug or adding a new feature.

[mroeschke]

Could you import this at the top of the file?

[dweindl]

Sure. Done.

[rhshadrach]

I think this is a solid improvement over the current implementation, but it is still odd in relation to the other readers. I believe openpyxl is the only reader where we pass non-default values to the load function of the engine. Still, the values we pass seem like they would be the preferred values in most cases to me. Perhaps it's sufficient to document this oddity?

[dweindl]

I think this is a solid improvement over the current implementation, but it is still odd in relation to the other readers. I believe openpyxl is the only reader where we pass non-default values to the load function of the engine. Still, the values we pass seem like they would be the preferred values in most cases to me. Perhaps it's sufficient to document this oddity?

Thanks for your feedback. I don't find it particularly odd that pandas passes non-default arguments to openpyxl. I think these tools just have somewhat different use cases in mind. Openpyxl seems to focus on modifying excel files, so it makes sense that by default they open files with read_only=False, data_only=False, keep_links=True, where the latter two are required to not break formulas and links when the modified file is saved. For pandas.io.excel._openpyxl.OpenpyxlReader.load_workbook it is only about reading, so choosing the supposedly faster read_only=True seems plausible (although read_only=False seems slightly more robust, e.g. #39001 but also other cases where the results differ). Also external links are irrelevant, and most users would probably expect to get the results of any formulas (data_only=True) rather than the formulas themselves (although with the proposed changes they can get them if they wanted to).

[rhshadrach]

lgtm; @mroeschke - you good here?

[mroeschke]

Thanks @dweindl