ENH: _openpyxl.py load_workbook allow to modify the read_only, data_only and keep_links parameters using engine_kwargs

[antonio-castellon]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

Dear Developers,

I was updating my libraries using Pandas 2.1.0 and Openpyxl 3.1.2 as engine and I found a missing point, months ago it was added the opportunity to include in the engine_kwargs for this lib the keep_vba parameter but, the other parameters were stablished /forced by default. If we want to change the behaviour we cannot doing it from pandas, that's why I;m requesting to include all these parameters as well not only the VBA.

Feature Description

the current definition on _openpyxl.py file is:

def load_workbook(
        self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
    ) -> Workbook:
        from openpyxl import load_workbook

        return load_workbook(
            filepath_or_buffer,
            read_only=True,
            data_only=True,
            keep_links=False,
            **engine_kwargs,
        )

Alternative Solutions

and it must be just 👍
` def load_workbook(
self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
) -> Workbook:
from openpyxl import load_workbook

    return load_workbook(
        filepath_or_buffer,
        **engine_kwargs,
    )`

Additional Context

Many thanks for your support,

[dweindl]

I second that. It would be very helpful if one could change in particular the read_only option to openpyxl.reader.excel.load_workbook. I encountered xlsx files that aren't read correctly with the hard-coded read_only=True, but work with read_only=False.

Currently (pandas==2.1.2) pandas.read_excel(..., engine_kwargs={"read_only": False}) fails with openpyxl.reader.excel.load_workbook() got multiple values for keyword argument 'read_only'.

[dweindl]

take