ENH: infer read_excel engine from a workbook object (GH#46352)

@@ -160,6 +160,7 @@ Other enhancements - Added :class:`NamedFunc`, an alias to :class:`NamedAgg` for a more semantically accurate name when used with non-aggregation functions; either can accept arbitrary functions (:issue:`65164`) - :meth:`ExtensionArray.map` now calls :meth:`ExtensionArray._cast_pointwise_result` to retain the dtype backend, e.g. Arrow-backed arrays now preserve their Arrow dtype through ``map`` (:issue:`57189`, :issue:`62164`) - :func:`read_csv` now supports ``dtype="complex64"`` and ``dtype="complex128"`` with the C engine, enabling round-tripping of complex-number columns written by :meth:`DataFrame.to_csv` (:issue:`9379`)+- :func:`read_excel` and :class:`ExcelFile` now infer ``engine`` from a workbook object created by a supported engine's library, such as an ``openpyxl.Workbook``, instead of raising (:issue:`46352`) - :func:`to_datetime` and ``strptime`` parsing now support the ``%N`` directive for matching exactly 9 digits representing nanoseconds, providing symmetry with ``strftime`` formatting (:issue:`65863`) - :meth:`DataFrame.select_dtypes` can now select ``datetime64`` and ``timedelta64`` columns by a specific resolution (e.g. ``"datetime64[us]"``, ``np.dtype("timedelta64[ms]")``), which previously raised; a unit-specific spec matches only columns with exactly that resolution, while a unitless ``"datetime64"``/``"timedelta64"`` still matches every resolution (:issue:`40234`) - :meth:`Series.round` now works on third-party numeric :class:`ExtensionArray` types via a default :meth:`ExtensionArray.round` (:issue:`49387`)

@@ -206,7 +206,7 @@ def read_excel(      Parameters     -----------    io : str, ExcelFile, xlrd.Book, path object, or file-like object+    io : str, ExcelFile, workbook object, path object, or file-like object         Any valid string path is acceptable. The string could be a URL. Valid         URL schemes include http, ftp, s3, and file. For file URLs, a host is         expected. A local file could be: ``file://localhost/path/to/table.xlsx``.@@ -221,6 +221,9 @@ def read_excel(         such as a file handle (e.g. via builtin ``open`` function)         or ``StringIO``. +        A workbook object from a supported engine's library, such as an+        ``openpyxl.Workbook``, is read with that engine.+     sheet_name : str, int, list, or None, default 0         Strings are used for sheet names. Integers are used in zero-indexed         sheet positions (chart sheets do not count as a sheet position).@@ -291,7 +294,6 @@ def read_excel(         of dtype conversion.         If you use ``None``, it will infer the dtype of each column based on the data.     engine : {'openpyxl', 'calamine', 'odf', 'pyxlsb', 'xlrd'}, default None-        If io is not a buffer or path, this must be set to identify io.         Engine compatibility :          - ``openpyxl`` supports newer Excel file formats.@@ -303,7 +305,8 @@ def read_excel(          When ``engine=None``, the following logic will be used to determine the engine: -        - If ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),+        - If ``io`` is a workbook object, the engine that created it will be used.+        - Otherwise if ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),           then `odf <https://pypi.org/project/odfpy/>`_ will be used.         - Otherwise if ``path_or_buffer`` is an xls format, ``xlrd`` will be used.         - Otherwise if ``path_or_buffer`` is in xlsb format, ``pyxlsb`` will be used.@@ -1538,6 +1541,29 @@ def inspect_excel_format(         return "zip"  +# GH#46352 - engine name -> (module, class) of the workbook objects it reads+_WORKBOOK_CLASSES = {+    "xlrd": ("xlrd", "Book"),+    "openpyxl": ("openpyxl", "Workbook"),+    "odf": ("odf.opendocument", "OpenDocument"),+    "pyxlsb": ("pyxlsb", "Workbook"),+    "calamine": ("python_calamine", "CalamineWorkbook"),+}+++def _infer_engine_from_workbook(obj) -> str | None:+    """+    Return the engine whose library created the workbook ``obj``, if any.+    """+    for engine, (module_name, class_name) in _WORKBOOK_CLASSES.items():+        # GH#56692 - avoid importing optional dependencies; such a workbook+        # can only exist if the caller already imported its module.+        module = sys.modules.get(module_name)+        if module is not None and isinstance(obj, getattr(module, class_name)):+            return engine+    return None++ @set_module("pandas") class ExcelFile:     """@@ -1548,11 +1574,10 @@ class ExcelFile:     Parameters     ----------     path_or_buffer : str, bytes, pathlib.Path,-        A file-like object, xlrd workbook or openpyxl workbook.+        A file-like object or a workbook object from a supported engine.         If a string or path object, expected to be a path to a         .xls, .xlsx, .xlsb, .xlsm, .odf, .ods, or .odt file.     engine : str, default None-        If io is not a buffer or path, this must be set to identify io.         Supported engines: ``xlrd``, ``openpyxl``, ``odf``, ``pyxlsb``, ``calamine``         Engine compatibility : @@ -1568,7 +1593,9 @@ class ExcelFile:         When ``engine=None``, the following logic will be         used to determine the engine: -        - If ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),+        - If ``path_or_buffer`` is a workbook object, the engine that created it+            will be used.+        - Otherwise if ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),             then `odf <https://pypi.org/project/odfpy/>`_ will be used.         - Otherwise if ``path_or_buffer`` is an xls format,             ``xlrd`` will be used.@@ -1645,53 +1672,42 @@ def __init__(         self._io = stringify_path(path_or_buffer)          if engine is None:-            # Only determine ext if it is needed-            ext: str | None = None--            # GH#56692 - avoid importing xlrd; a Book only exists if the caller-            # already imported it. GH#68086 - must precede inspect_excel_format,-            # which seeks.-            xlrd = sys.modules.get("xlrd")-            if xlrd is not None and isinstance(path_or_buffer, xlrd.Book):-                ext = "xls"-                engine = "xlrd"+            # GH#68086 - must precede inspect_excel_format, which seeks.+            engine = _infer_engine_from_workbook(path_or_buffer) +        if engine is None:+            ext = inspect_excel_format(+                content_or_path=path_or_buffer, storage_options=storage_options+            )             if ext is None:-                ext = inspect_excel_format(-                    content_or_path=path_or_buffer, storage_options=storage_options+                raise ValueError(+                    "Excel file format cannot be determined, you must specify "+                    "an engine manually."                 )-                if ext is None:-                    raise ValueError(-                        "Excel file format cannot be determined, you must specify "-                        "an engine manually."-                    ) -            if engine is None:-                engine = config["io"]["excel"][ext]["reader"]-                if engine == "auto":-                    engine = get_default_engine(ext, mode="reader")-                    # GH#56542 - the calamine engine will become the default-                    # for xlsx/xlsm. Warn while calamine is available so the-                    # switch is actionable; an explicit engine or the-                    # io.excel.xlsx.reader option silences this. xlsm files-                    # are format-sniffed as xlsx, so ext is never "xlsm" here.-                    if (-                        ext == "xlsx"-                        and engine == "openpyxl"-                        and import_optional_dependency(-                            "python_calamine", errors="ignore"-                        )-                        is not None-                    ):-                        warnings.warn(-                            "The default engine for reading 'xlsx' files "-                            "will change from 'openpyxl' to 'calamine' in a "-                            "future version. Pass engine='openpyxl' (or set "-                            "the 'io.excel.xlsx.reader' option) to keep the "-                            "current engine and silence this warning.",-                            Pandas4Warning,-                            stacklevel=find_stack_level(),-                        )+            engine = config["io"]["excel"][ext]["reader"]+            if engine == "auto":+                engine = get_default_engine(ext, mode="reader")+                # GH#56542 - the calamine engine will become the default+                # for xlsx/xlsm. Warn while calamine is available so the+                # switch is actionable; an explicit engine or the+                # io.excel.xlsx.reader option silences this. xlsm files+                # are format-sniffed as xlsx, so ext is never "xlsm" here.+                if (+                    ext == "xlsx"+                    and engine == "openpyxl"+                    and import_optional_dependency("python_calamine", errors="ignore")+                    is not None+                ):+                    warnings.warn(+                        "The default engine for reading 'xlsx' files "+                        "will change from 'openpyxl' to 'calamine' in a "+                        "future version. Pass engine='openpyxl' (or set "+                        "the 'io.excel.xlsx.reader' option) to keep the "+                        "current engine and silence this warning.",+                        Pandas4Warning,+                        stacklevel=find_stack_level(),+                    )          assert engine is not None         self.engine = engine

@@ -1890,3 +1890,33 @@ def test_pyxlsb_engine_deprecated(datapath):         Pandas4Warning, match="pyxlsb engine is deprecated"     ):         pd.read_excel(path, engine="pyxlsb")+++@pytest.mark.filterwarnings(+    "ignore:The (xlrd|pyxlsb) engine is deprecated:pandas.errors.Pandas4Warning"+)+@pytest.mark.parametrize(+    "engine, module_name, read_ext, load",+    [+        ("xlrd", "xlrd", ".xls", lambda mod, path: mod.open_workbook(path)),+        ("openpyxl", "openpyxl", ".xlsx", lambda mod, path: mod.load_workbook(path)),+        ("odf", "odf.opendocument", ".ods", lambda mod, path: mod.load(path)),+        ("pyxlsb", "pyxlsb", ".xlsb", lambda mod, path: mod.open_workbook(path)),+        (+            "calamine",+            "python_calamine",+            ".xlsx",+            lambda mod, path: mod.CalamineWorkbook.from_path(path),+        ),+    ],+)+def test_read_workbook_infers_engine(datapath, engine, module_name, read_ext, load):+    # GH#46352+    module = pytest.importorskip(module_name)+    path = datapath("io", "data", "excel", f"test1{read_ext}")+    expected = pd.read_excel(path, engine=engine, index_col=0)++    with pd.ExcelFile(load(module, path)) as xl:+        assert xl.engine == engine+    result = pd.read_excel(load(module, path), index_col=0)+    tm.assert_frame_equal(result, expected)