pandas读取Excel因工作表状态非法触发ValueError的Python处理方案

通过自动化脚本从网站下载得到CallHistory.xlsx文件，使用pandas读取该文件时触发异常，无法正常加载表格数据。

复现代码 #

import pandas as pd
df = pd.read_excel('CallHistory.xlsx')

报错信息 #

运行上述代码后抛出如下异常：

ValueError                                Traceback (most recent call last)
c:\Users\minhviet\Box\Telio\vietpm\python\crawler\test_crawl_3.ipynb Cell 7' in <module>
      1 import pandas as pd
----> 2 df = pd.read_excel('CallHistory.xlsx')
      3 df

File c:\Users\minhviet\Anaconda3\lib\site-packages\pandas\util\_decorators.py:311, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    305 if len(args) > num_allow_args:
    306     warnings.warn(
    307         msg.format(arguments=arguments),
    308         FutureWarning,
    309         stacklevel=stacklevel,
    310     )
---> 311 return func(*args, **kwargs)

File c:\Users\minhviet\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:364, in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
    362 if not isinstance(io, ExcelFile):
    363     should_close = True
---> 364     io = ExcelFile(io, storage_options=storage_options, engine=engine)
    365 elif engine and engine != io.engine:
    366     raise ValueError(
    367         "Engine should not be specified when passing "
    368         "an ExcelFile - ExcelFile already has the engine set"
...
    127     if value not in self.values:
---> 128         raise ValueError(self.__doc__)
    129     super(Set, self).__set__(instance, value)

ValueError: Value must be one of {'visible', 'hidden', 'veryHidden'}

已排查结论 #

• 错误根因：文件内工作表的state属性取值不符合OpenXML规范，属于上游Excel生成工具的已知缺陷
• 手动修复验证：使用本地Excel桌面客户端打开文件，执行任意编辑操作后保存，修复后的文件可被pandas正常读取，但该方案依赖人工操作，无法嵌入全自动化脚本流程，不满足使用需求

需求 #

寻找纯Python实现的无人工介入自动化处理方案，可批量修复该类异常Excel文件，适配自动化爬取-数据处理的全流程运行要求。

内容的提问来源于stack exchange，提问作者Viet Pm