Repository: pandas-dev/pandas
Pull request: DEPR: Adjust read excel behavior for xlrd >= 2.0
- [x] closes #38424
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Alternative to #38522. I've been testing this locally using both xlrd 1.2.0 and 2.0.1.

One test fails because we used to default to xlrd but now default to openpyxl, it's not clear to me if this test should be passing with openpyxl.

cc @cjw296, @jreback, @jorisvandenbossche 



Problem statement:
shift default excel read engine from xlrd to openpyxl
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 )

Hints from issue discussion:
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 
@rhshadrach see https://github.com/pandas-dev/pandas/issues/28547#issuecomment-743129783 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.
I'm sorry for causing you folk extra work :-(
> @rhshadrach see [#28547 (comment)](https://github.com/pandas-dev/pandas/issues/28547#issuecomment-743129783) 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.
@jorisvandenbossche My understanding is that, other than ods-streams, only xlrd can read buffer-like values (i.e. non-path) of `path_or_buffer` in `read_excel`. As such, one thought is (roughly) to define:

    is_xlrd_only = "(non-ods buffer) or (xls-type extension) or (xlrd.Book instance)"

Then:

* Emit a FutureWarning if:
  - engine was passed as None;
  - not is_xlrd_only;
  - openpyxl is not installed (as would use openpyxl); and
  - and xlrd_version < 2

* Emit a ValueError anytime (even if engine was not passed as None):
  - engine was determined to be "xlrd";
  - not is_xlrd_only; and
  - xlrd_version >= 2

One case I want to highlight is when read_excel is passed a buffer to a non-xls/ods format. With the logic above, we'd be passing this onto xlrd without warning. I don't know if there is a good way to tell if the buffer can handled by xlrd.
>  only xlrd can read buffer-like values (i.e. non-path)

But only if it is a buffer of a file type it does support? Eg with xlrd >= 2.0, it won't read a buffer-like of an xlsx file?

> Emit a ValueError anytime (even if engine was not passed as None): ....

Yes, that sounds correct, because for those conditions, `xlrd` (>= 2.0) would error anyway (I think), and so we want to raise the error, so we can give it a pandas-specific error message.

> One case I want to highlight is when read_excel is passed a buffer to a non-xls/ods format. With the logic above, we'd be passing this onto xlrd without warning. I don't know if there is a good way to tell if the buffer can handled by xlrd.

Yeah, see @cjw296's comment here: https://github.com/pandas-dev/pandas/issues/28547#issuecomment-743113883. He mentions an `inspect_format` which might help here. That could maybe be used if xlrd is installed, and if it's version >= 2.0, so we can check if the buffer is readable by xlrd or not.

Thanks @jorisvandenbossche, I had missed the inspect_format part. I think that solves the part where I had reservations.
@rhshadrach - I'm the maintainer of xlrd, so I wanted to be clear on the intention here: xlrd should *only* be used for `.xls` files, please can you emit a warning whenever it is used for anything else, no matter what version of xlrd is installed?

 It **absolutely should not** be used for opening `.xlsx` files, or any other form of zip/xml files: there are security vulnerabilities lurking, it will break on Python 3.9 and it will fail at parsing some files which `openpyxl` will do a much better job of.

Please, I'm begging you, do not put in hacks which try and suggest users can get away with "sticking with xlrd 1.2" or whatever...
> please can you emit a warning whenever it is used for anything else?

That's already the case on master / on 1.2rc, see https://github.com/pandas-dev/pandas/pull/35029

The whatsnew notice about this (the first warning box in the 1.2 release notes) can be seen here: https://pandas.pydata.org/docs/dev/whatsnew/v1.2.0.html 
Specific feedback on the messaging is certainly welcome.
Actually, re-reading that, I suppose we do *not* raise a warning when you manually specify `engine="xlrd"` but are reading a non-xls file. This is not the default (the default of `engine=None` will either use openpyxl if installed, or otherwise use xlrd *with* warning), and in the past we always used xlrd automatically in this case, so I don't expect many users to manually specify this, but that's still a case where we should warn as well, then.

Commit message: Fix test to handle zh_CN.utf8

Patch to review:
diff --git a/doc/source/whatsnew/v1.2.0.rst b/doc/source/whatsnew/v1.2.0.rst
index e2521cedb64cc..7e91ecf33eb9b 100644
--- a/doc/source/whatsnew/v1.2.0.rst
+++ b/doc/source/whatsnew/v1.2.0.rst
@@ -10,23 +10,19 @@ including other versions of pandas.
 
 .. warning::
 
-   The packages `xlrd <https://xlrd.readthedocs.io/en/latest/>`_ for reading excel
-   files and `xlwt <https://xlwt.readthedocs.io/en/latest/>`_ for
-   writing excel files are no longer maintained. These are the only engines in pandas
+   The package `xlrd <https://xlrd.readthedocs.io/en/latest/>`_ for reading excel
+   files now only supports the xls format. The package
+   `xlwt <https://xlwt.readthedocs.io/en/latest/>`_ for writing excel files
+   is no longer maintained. These are the only engines in pandas
    that support the xls format.
 
    Previously, the default argument ``engine=None`` to ``pd.read_excel``
    would result in using the ``xlrd`` engine in many cases. If
    `openpyxl <https://openpyxl.readthedocs.io/en/stable/>`_  is installed,
    many of these cases will now default to using the ``openpyxl`` engine.
-   See the :func:`read_excel` documentation for more details. Attempting to read
-   ``.xls`` files or specifying ``engine="xlrd"`` to ``pd.read_excel`` will not
-   raise a warning. However users should be aware that ``xlrd`` is already
-   broken with certain package configurations, for example with Python 3.9
-   when `defusedxml <https://github.com/tiran/defusedxml/>`_ is installed, and
-   is anticipated to be unusable in the future.
-
-   Attempting to use the the ``xlwt`` engine will raise a ``FutureWarning``
+   See the :func:`read_excel` documentation for more details.
+
+   Attempting to use the ``xlwt`` engine will raise a ``FutureWarning``
    unless the option :attr:`io.excel.xls.writer` is set to ``"xlwt"``.
    While this option is now deprecated and will also raise a ``FutureWarning``,
    it can be globally set and the warning suppressed. Users are recommended to
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index bf1011176693f..07c4a002fc61a 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -4,8 +4,9 @@
 from io import BufferedIOBase, BytesIO, RawIOBase
 import os
 from textwrap import fill
-from typing import Any, Dict, Mapping, Union, cast
+from typing import IO, Any, Dict, Mapping, Optional, Union, cast
 import warnings
+import zipfile
 
 from pandas._config import config
 
@@ -13,11 +14,12 @@
 from pandas._typing import Buffer, FilePathOrBuffer, StorageOptions
 from pandas.compat._optional import import_optional_dependency
 from pandas.errors import EmptyDataError
-from pandas.util._decorators import Appender, deprecate_nonkeyword_arguments
+from pandas.util._decorators import Appender, deprecate_nonkeyword_arguments, doc
 
 from pandas.core.dtypes.common import is_bool, is_float, is_integer, is_list_like
 
 from pandas.core.frame import DataFrame
+from pandas.core.shared_docs import _shared_docs
 
 from pandas.io.common import IOHandles, get_handle, stringify_path, validate_header_arg
 from pandas.io.excel._util import (
@@ -111,22 +113,19 @@
     - "pyxlsb" supports Binary Excel files.
 
     .. versionchanged:: 1.2.0
-        The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_
-        is no longer maintained, and is not supported with
-        python >= 3.9. When ``engine=None``, the following logic will be
-        used to determine the engine.
-
-        - 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 a bytes stream, the file has the
-          extension ``.xls``, or is an ``xlrd`` Book instance, then ``xlrd`` will
-          be used.
-        - Otherwise if `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
-          then ``openpyxl`` will be used.
-        - Otherwise ``xlrd`` will be used and a ``FutureWarning`` will be raised.
-
-        Specifying ``engine="xlrd"`` will continue to be allowed for the
-        indefinite future.
+
+       The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_ only reads
+       xls files in version 2.0 and above. When ``engine=None``,
+       the following logic will be used to determine the engine.
+
+       - 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 `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
+         then ``openpyxl`` will be used.
+       - Otherwise if ``xlrd >= 2.0`` is installed, a ``ValueError`` will be raised.
+       - Otherwise ``xlrd`` will be used and a ``FutureWarning`` will be raised.
 
 converters : dict, default None
     Dict of functions for converting values in certain columns. Keys can
@@ -888,39 +887,90 @@ def close(self):
         return content
 
 
-def _is_ods_stream(stream: Union[BufferedIOBase, RawIOBase]) -> bool:
+XLS_SIGNATURE = b"\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1"
+ZIP_SIGNATURE = b"PK\x03\x04"
+PEEK_SIZE = max(len(XLS_SIGNATURE), len(ZIP_SIGNATURE))
+
+
+@doc(storage_options=_shared_docs["storage_options"])
+def inspect_excel_format(
+    path: Optional[str] = None,
+    content: Union[None, BufferedIOBase, RawIOBase, bytes] = None,
+    storage_options: StorageOptions = None,
+) -> str:
     """
-    Check if the stream is an OpenDocument Spreadsheet (.ods) file
+    Inspect the path or content of an excel file and get its format.
+
+    At least one of path or content must be not None. If both are not None,
+    content will take precedence.
 
-    It uses magic values inside the stream
+    Adopted from xlrd: https://github.com/python-excel/xlrd.
 
     Parameters
     ----------
-    stream : Union[BufferedIOBase, RawIOBase]
-        IO stream with data which might be an ODS file
+    path : str, optional
+        Path to file to inspect. May be a URL.
+    content : file-like object, optional
+        Content of file to inspect.
+    {storage_options}
 
     Returns
     -------
-    is_ods : bool
-        Boolean indication that this is indeed an ODS file or not
+    str
+        Format of file.
+
+    Raises
+    ------
+    ValueError
+        If resulting stream is empty.
+    BadZipFile
+        If resulting stream does not have an XLS signature and is not a valid zipfile.
     """
-    stream.seek(0)
-    is_ods = False
-    if stream.read(4) == b"PK\003\004":
-        stream.seek(30)
-        is_ods = (
-            stream.read(54) == b"mimetype"
-            b"application/vnd.oasis.opendocument.spreadsheet"
-        )
-    stream.seek(0)
-    return is_ods
+    content_or_path: Union[None, str, BufferedIOBase, RawIOBase, IO[bytes]]
+    if isinstance(content, bytes):
+        content_or_path = BytesIO(content)
+    else:
+        content_or_path = content or path
+    assert content_or_path is not None
+
+    with get_handle(
+        content_or_path, "rb", storage_options=storage_options, is_text=False
+    ) as handle:
+        stream = handle.handle
+        stream.seek(0)
+        buf = stream.read(PEEK_SIZE)
+        if buf is None:
+            raise ValueError("stream is empty")
+        else:
+            assert isinstance(buf, bytes)
+            peek = buf
+        stream.seek(0)
+
+        if peek.startswith(XLS_SIGNATURE):
+            return "xls"
+
+        # ZipFile typing is overly-strict
+        # https://github.com/python/typeshed/issues/4212
+        zf = zipfile.ZipFile(stream)  # type: ignore[arg-type]
+
+        # Workaround for some third party files that use forward slashes and
+        # lower case names.
+        component_names = [name.replace("\\", "/").lower() for name in zf.namelist()]
+
+        if "xl/workbook.xml" in component_names:
+            return "xlsx"
+        if "xl/workbook.bin" in component_names:
+            return "xlsb"
+        if "content.xml" in component_names:
+            return "ods"
+        return "zip"
 
 
 class ExcelFile:
     """
     Class for parsing tabular excel sheets into DataFrame objects.
 
-    Uses xlrd engine by default. See read_excel for more documentation
+    See read_excel for more documentation.
 
     Parameters
     ----------
@@ -940,22 +990,18 @@ class ExcelFile:
 
         .. versionchanged:: 1.2.0
 
-           The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_
-           is no longer maintained, and is not supported with
-           python >= 3.9. When ``engine=None``, the following logic will be
-           used to determine the engine.
+           The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_ only reads
+           xls files in version 2.0 and above. When ``engine=None``,
+           the following logic will be used to determine the engine.
 
            - 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 a bytes stream, the file has the
-             extension ``.xls``, or is an ``xlrd`` Book instance, then ``xlrd``
-             will be used.
+           - Otherwise if ``path_or_buffer`` is an xls format,
+             ``xlrd`` will be used.
            - Otherwise if `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
              then ``openpyxl`` will be used.
+           - Otherwise if ``xlrd >= 2.0`` is installed, a ``ValueError`` will be raised.
            - Otherwise ``xlrd`` will be used and a ``FutureWarning`` will be raised.
-
-           Specifying ``engine="xlrd"`` will continue to be allowed for the
-           indefinite future.
     """
 
     from pandas.io.excel._odfreader import ODFReader
@@ -973,40 +1019,55 @@ class ExcelFile:
     def __init__(
         self, path_or_buffer, engine=None, storage_options: StorageOptions = None
     ):
-        if engine is None:
-            # Determine ext and use odf for ods stream/file
-            if isinstance(path_or_buffer, (BufferedIOBase, RawIOBase)):
-                ext = None
-                if _is_ods_stream(path_or_buffer):
-                    engine = "odf"
-            else:
-                ext = os.path.splitext(str(path_or_buffer))[-1]
-                if ext == ".ods":
-                    engine = "odf"
+        if engine is not None and engine not in self._engines:
+            raise ValueError(f"Unknown engine: {engine}")
 
-            if (
-                import_optional_dependency(
-                    "xlrd", raise_on_missing=False, on_version="ignore"
-                )
-                is not None
-            ):
-                from xlrd import Book
+        # Could be a str, ExcelFile, Book, etc.
+        self.io = path_or_buffer
+        # Always a string
+        self._io = stringify_path(path_or_buffer)
 
-                if isinstance(path_or_buffer, Book):
-                    engine = "xlrd"
+        # Determine xlrd version if installed
+        if (
+            import_optional_dependency(
+                "xlrd", raise_on_missing=False, on_version="ignore"
+            )
+            is None
+        ):
+            xlrd_version = None
+        else:
+            import xlrd
 
-            # GH 35029 - Prefer openpyxl except for xls files
-            if engine is None:
-                if ext is None or isinstance(path_or_buffer, bytes) or ext == ".xls":
-                    engine = "xlrd"
-                elif (
+            xlrd_version = xlrd.__version__
+
+        if isinstance(path_or_buffer, (BufferedIOBase, RawIOBase, bytes)):
+            ext = inspect_excel_format(
+                content=path_or_buffer, storage_options=storage_options
+            )
+        elif xlrd_version is not None and isinstance(path_or_buffer, xlrd.Book):
+            ext = "xls"
+        else:
+            # path_or_buffer is path-like, use stringified path
+            ext = inspect_excel_format(
+                path=str(self._io), storage_options=storage_options
+            )
+
+        if engine is None:
+            if ext == "ods":
+                engine = "odf"
+            elif ext == "xls":
+                engine = "xlrd"
+            else:
+                # GH 35029 - Prefer openpyxl except for xls files
+                if (
                     import_optional_dependency(
                         "openpyxl", raise_on_missing=False, on_version="ignore"
                     )
                     is not None
                 ):
                     engine = "openpyxl"
-                else:
+                elif xlrd_version is not None and xlrd_version < "2":
+                    # If xlrd_version >= "2", we error below
                     caller = inspect.stack()[1]
                     if (
                         caller.filename.endswith("pandas/io/excel/_base.py")
@@ -1016,28 +1077,30 @@ def __init__(
                     else:
                         stacklevel = 2
                     warnings.warn(
-                        "The xlrd engine is no longer maintained and is not "
-                        "supported when using pandas with python >= 3.9. However, "
-                        "the engine xlrd will continue to be allowed for the "
-                        "indefinite future. Beginning with pandas 1.2.0, the "
-                        "openpyxl engine will be used if it is installed and the "
-                        "engine argument is not specified. Either install openpyxl "
-                        "or specify engine='xlrd' to silence this warning.",
+                        f"Your version of xlrd is {xlrd_version}. In xlrd >= 2.0, "
+                        f"only the xls format is supported. As a result, the "
+                        f"openpyxl engine will be used if it is installed "
+                        f"and the engine argument is not specified. Either install "
+                        f"openpyxl or specify engine='xlrd' to silence this warning.",
                         FutureWarning,
                         stacklevel=stacklevel,
                     )
                     engine = "xlrd"
-        if engine not in self._engines:
-            raise ValueError(f"Unknown engine: {engine}")
+
+        if (
+            engine == "xlrd"
+            and xlrd_version is not None
+            and xlrd_version >= "2"
+            and not ext == "xls"
+        ):
+            raise ValueError(
+                f"Your version of xlrd is {xlrd_version}. In xlrd >= 2.0, "
+                f"only the xls format is supported."
+            )
 
         self.engine = engine
         self.storage_options = storage_options
 
-        # Could be a str, ExcelFile, Book, etc.
-        self.io = path_or_buffer
-        # Always a string
-        self._io = stringify_path(path_or_buffer)
-
         self._reader = self._engines[engine](self._io, storage_options=storage_options)
 
     def __fspath__(self):
diff --git a/pandas/tests/io/__init__.py b/pandas/tests/io/__init__.py
index c5e867f45b92d..39474dedba78c 100644
--- a/pandas/tests/io/__init__.py
+++ b/pandas/tests/io/__init__.py
@@ -14,4 +14,8 @@
         r"Use 'tree.iter\(\)' or 'list\(tree.iter\(\)\)' instead."
         ":PendingDeprecationWarning"
     ),
+    # GH 26552
+    pytest.mark.filterwarnings(
+        "ignore:As the xlwt package is no longer maintained:FutureWarning"
+    ),
 ]
diff --git a/pandas/tests/io/excel/__init__.py b/pandas/tests/io/excel/__init__.py
index 384f1006c44df..84a4b47adceaf 100644
--- a/pandas/tests/io/excel/__init__.py
+++ b/pandas/tests/io/excel/__init__.py
@@ -1,5 +1,7 @@
 import pytest
 
+from pandas.compat._optional import import_optional_dependency
+
 pytestmark = [
     pytest.mark.filterwarnings(
         # Looks like tree.getiterator is deprecated in favor of tree.iter
@@ -14,3 +16,14 @@
         "ignore:As the xlwt package is no longer maintained:FutureWarning"
     ),
 ]
+
+
+if (
+    import_optional_dependency("xlrd", raise_on_missing=False, on_version="ignore")
+    is None
+):
+    xlrd_version = None
+else:
+    import xlrd
+
+    xlrd_version = xlrd.__version__
diff --git a/pandas/tests/io/excel/test_readers.py b/pandas/tests/io/excel/test_readers.py
index 98a55ae39bd77..d7c2db18988d2 100644
--- a/pandas/tests/io/excel/test_readers.py
+++ b/pandas/tests/io/excel/test_readers.py
@@ -2,6 +2,7 @@
 from functools import partial
 import os
 from urllib.error import URLError
+from zipfile import BadZipFile
 
 import numpy as np
 import pytest
@@ -11,6 +12,7 @@
 import pandas as pd
 from pandas import DataFrame, Index, MultiIndex, Series
 import pandas._testing as tm
+from pandas.tests.io.excel import xlrd_version
 
 read_ext_params = [".xls", ".xlsx", ".xlsm", ".xlsb", ".ods"]
 engine_params = [
@@ -57,6 +59,13 @@ def _is_valid_engine_ext_pair(engine, read_ext: str) -> bool:
         return False
     if read_ext == ".xlsb" and engine != "pyxlsb":
         return False
+    if (
+        engine == "xlrd"
+        and xlrd_version is not None
+        and xlrd_version >= "2"
+        and read_ext != ".xls"
+    ):
+        return False
     return True
 
 
@@ -614,6 +623,19 @@ def test_bad_engine_raises(self, read_ext):
         with pytest.raises(ValueError, match="Unknown engine: foo"):
             pd.read_excel("", engine=bad_engine)
 
+    def test_missing_file_raises(self, read_ext):
+        bad_file = f"foo{read_ext}"
+        # CI tests with zh_CN.utf8, translates to "No such file or directory"
+        with pytest.raises(
+            FileNotFoundError, match=r"(No such file or directory|没有那个文件或目录)"
+        ):
+            pd.read_excel(bad_file)
+
+    def test_corrupt_bytes_raises(self, read_ext, engine):
+        bad_stream = b"foo"
+        with pytest.raises(BadZipFile, match="File is not a zip file"):
+            pd.read_excel(bad_stream)
+
     @tm.network
     def test_read_from_http_url(self, read_ext):
         url = (
@@ -1158,6 +1180,10 @@ def test_excel_read_binary(self, engine, read_ext):
         actual = pd.read_excel(data, engine=engine)
         tm.assert_frame_equal(expected, actual)
 
+    @pytest.mark.skipif(
+        xlrd_version is not None and xlrd_version >= "2",
+        reason="xlrd no longer supports xlsx",
+    )
     def test_excel_high_surrogate(self, engine):
         # GH 23809
         expected = DataFrame(["\udc88"], columns=["Column1"])
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index 80ebeb4c03d89..741dfedd2824e 100644
--- a/pandas/tests/io/excel/test_writers.py
+++ b/pandas/tests/io/excel/test_writers.py
@@ -1196,8 +1196,9 @@ def test_datetimes(self, path):
         write_frame = DataFrame({"A": datetimes})
         write_frame.to_excel(path, "Sheet1")
         # GH 35029 - Default changed to openpyxl, but test is for odf/xlrd
-        engine = "odf" if path.endswith("ods") else "xlrd"
-        read_frame = pd.read_excel(path, sheet_name="Sheet1", header=0, engine=engine)
+        if path.endswith("xlsx") or path.endswith("xlsm"):
+            pytest.skip("Defaults to openpyxl and fails - Should this pass?")
+        read_frame = pd.read_excel(path, sheet_name="Sheet1", header=0)
 
         tm.assert_series_equal(write_frame["A"], read_frame["A"])
 
diff --git a/pandas/tests/io/excel/test_xlrd.py b/pandas/tests/io/excel/test_xlrd.py
index f2fbcbc2e2f04..2a1114a9570f0 100644
--- a/pandas/tests/io/excel/test_xlrd.py
+++ b/pandas/tests/io/excel/test_xlrd.py
@@ -4,6 +4,7 @@
 
 import pandas as pd
 import pandas._testing as tm
+from pandas.tests.io.excel import xlrd_version
 
 from pandas.io.excel import ExcelFile
 
@@ -17,6 +18,8 @@ def skip_ods_and_xlsb_files(read_ext):
         pytest.skip("Not valid for xlrd")
     if read_ext == ".xlsb":
         pytest.skip("Not valid for xlrd")
+    if read_ext in (".xlsx", ".xlsm") and xlrd_version >= "2":
+        pytest.skip("Not valid for xlrd >= 2.0")
 
 
 def test_read_xlrd_book(read_ext, frame):
@@ -66,7 +69,7 @@ def test_excel_file_warning_with_xlsx_file(datapath):
             pd.read_excel(path, "Sheet1", engine=None)
 
 
-def test_read_excel_warning_with_xlsx_file(tmpdir, datapath):
+def test_read_excel_warning_with_xlsx_file(datapath):
     # GH 29375
     path = datapath("io", "data", "excel", "test1.xlsx")
     has_openpyxl = (
@@ -76,12 +79,19 @@ def test_read_excel_warning_with_xlsx_file(tmpdir, datapath):
         is not None
     )
     if not has_openpyxl:
-        with tm.assert_produces_warning(
-            FutureWarning,
-            raise_on_extra_warnings=False,
-            match="The xlrd engine is no longer maintained",
-        ):
-            pd.read_excel(path, "Sheet1", engine=None)
+        if xlrd_version >= "2":
+            with pytest.raises(
+                ValueError,
+                match="Your version of xlrd is ",
+            ):
+                pd.read_excel(path, "Sheet1", engine=None)
+        else:
+            with tm.assert_produces_warning(
+                FutureWarning,
+                raise_on_extra_warnings=False,
+                match="The xlrd engine is no longer maintained",
+            ):
+                pd.read_excel(path, "Sheet1", engine=None)
     else:
         with tm.assert_produces_warning(None):
             pd.read_excel(path, "Sheet1", engine=None)


Code review comments:
diff_hunk: @@ -1016,28 +1077,30 @@ def __init__(
                     else:
                         stacklevel = 2
                     warnings.warn(
-                        "The xlrd engine is no longer maintained and is not "
-                        "supported when using pandas with python >= 3.9. However, "
-                        "the engine xlrd will continue to be allowed for the "
-                        "indefinite future. Beginning with pandas 1.2.0, the "
-                        "openpyxl engine will be used if it is installed and the "
-                        "engine argument is not specified. Either install openpyxl "
-                        "or specify engine='xlrd' to silence this warning.",
+                        f"Your version of xlrd is {xlrd_version}. In xlrd >= 2.0, "
+                        f"only the xls format is supported. As a result, the "
+                        f"openpyxl engine will be used if it is installed "
+                        f"and the engine argument is not specified. Either install "
+                        f"openpyxl or specify engine='xlrd' to silence this warning.",
                         FutureWarning,
                         stacklevel=stacklevel,
                     )
                     engine = "xlrd"
-        if engine not in self._engines:
-            raise ValueError(f"Unknown engine: {engine}")
+
+        if (
+            engine == "xlrd"
+            and xlrd_version is not None
+            and xlrd_version >= "2"
path: pandas/io/excel/_base.py
text: @user1:
maybe using LooseVersion here is needed? (though if it works then ok)

@author:
Ah - yes, thanks. Updating now, should be quick.

@user1:
does this need updating?

@author:
xlrd_version is a LooseVersion here; LooseVersion comparison will handle `other` as being a string; but can replace with `LooseVersion("2")` if that's preferred.