社区首页 >专栏 >『开发技巧』解决Python使用pandas读取xlsx文件报错“ImportError: Missing optional dependency ‘xlrd‘”的问题

『开发技巧』解决Python使用pandas读取xlsx文件报错“ImportError: Missing optional dependency ‘xlrd‘”的问题

小宋是呢

发布于 2021-09-10 02:32:31

5.9K00

代码可运行

文章被收录于专栏：深度应用深度应用

运行总次数：0

代码可运行

0x01:引子

笔者在使用Mac进行Python开发时使用pandas读取xlsx文件遇到这个错误:

ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

看似简单直接安装xlrd即可，实则在操作过程中并不顺利，又报出其他错误。笔者在这里分享一下自己遇到问题及解决步骤。

0x02:历程

按照提示“Use pip or conda to install xlrd.”，这里笔者使用pip安装，命令行指令如下：

pip install xlrd

输出为：可以看出，安装的为2.0.1版本xlrd，已满足xlrd >= 1.0.0的需求。

(ml) bash-3.2$ pip install xlrd
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting xlrd
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/a6/0c/c2a72d51fe56e08a08acc85d13013558a2d793028ae7385448a6ccdfae64/xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
     |████████████████████████████████| 96 kB 1.1 MB/s 
Installing collected packages: xlrd
Successfully installed xlrd-2.0.1

可是在执行代码时，还是报出以下错误：

(ml) bash-3.2$ python demo.py 
Traceback (most recent call last):
  File "demo.py", line 6, in <module>
    datas = pd.read_excel("data.xlsx")
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/util/_decorators.py", line 299, in wrapper
    return func(*args, **kwargs)
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 336, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 1080, in __init__
    raise ValueError(
ValueError: Your version of xlrd is 2.0.1. In xlrd >= 2.0, only the xls format is supported. Install openpyxl instead.

关键信息：Your version of xlrd is 2.0.1. In xlrd >= 2.0, only the xls format is supported. Install openpyxl instead.

xlrd版本问题，大于等于2.0时，仅支持xls格式，建议安装openpyxl。

其实有两种解决方法：

对xlrd进行降级用来支持xlsx
安装openpyxl替代对xlrd依赖

笔者这里选择的是对xlrd降级操作，安装1.0版本xlrd，指令：pip install xlrd==1.0

提示：1.”==“符号用于指定版本，注意是双等于号，这里与conda单等于号不同。2.在使用pip降级安装时，不用手动卸载高版本，系统会直接替换。

(ml) bash-3.2$ pip install xlrd==1
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting xlrd==1
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/0c/b0/8946fe3f9c2690c164aaa88dfd43b56347d3cdeac34124b988acd1aaa151/xlrd-1.0.0-py3-none-any.whl (143 kB)
     |████████████████████████████████| 143 kB 150 kB/s 
Installing collected packages: xlrd
  Attempting uninstall: xlrd
    Found existing installation: xlrd 2.0.0
    Uninstalling xlrd-2.0.0:
      Successfully uninstalled xlrd-2.0.0
Successfully installed xlrd-1.0.0

笔者这里安装了1.0版本，不太幸运的是在运行又出错了~~，如下：

(ml) bash-3.2$ python demo.py 
Traceback (most recent call last):
  File "demo.py", line 6, in <module>
    datas = pd.read_excel("data.xlsx")
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/util/_decorators.py", line 299, in wrapper
    return func(*args, **kwargs)
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 336, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
  File "/Users/song/miniforge3/envs/ml/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 1052, in __init__
    xlrd_version = LooseVersion(xlrd.__version__)
AttributeError: module 'xlrd' has no attribute '__version__'

继续设法解决，笔者考虑，可能是1.0版本过旧的缘故。笔者试着安装了1.x版本。尝试几次后，安装了1.2：

(ml) bash-3.2$ pip install xlrd==1.8
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
ERROR: Could not find a version that satisfies the requirement xlrd==1.8
ERROR: No matching distribution found for xlrd==1.8
(ml) bash-3.2$ pip install xlrd==1.6
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
ERROR: Could not find a version that satisfies the requirement xlrd==1.6
ERROR: No matching distribution found for xlrd==1.6
(ml) bash-3.2$ pip install xlrd==1.2
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting xlrd==1.2
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/b0/16/63576a1a001752e34bf8ea62e367997530dc553b689356b9879339cf45a4/xlrd-1.2.0-py2.py3-none-any.whl (103 kB)
     |████████████████████████████████| 103 kB 61 kB/s 
Installing collected packages: xlrd
  Attempting uninstall: xlrd
    Found existing installation: xlrd 1.0.0
    Uninstalling xlrd-1.0.0:
      Successfully uninstalled xlrd-1.0.0
Successfully installed xlrd-1.2.0