使用openpyxl保存Excel工作簿及pandas读文件报错排查

从业务系统导出的Excel工作表默认附带多余空白行与空白列，数据加工前需要先清理这类冗余内容。编写Python代码实现「删除工作表前2列、前3行后保存修改」的逻辑时，先后触发两类运行错误：

• 第一类报错：最初直接调用类方法Workbook.save(path)触发回溯，经确认该写法不符合调用规范，即使仅传入文件名作为保存参数也会触发同类错误，将类形式调用修正为实例调用workbook.save(path)后，该问题解决。
• 第二类报错：修正保存方法的笔误后，运行代码触发pandas读取Excel相关的回溯错误，核心报错信息为pandas._config.config.OptionError: No such keys(s): 'io.excel.zip.reader'

问题复现代码 #

import openpyxl
import pandas as pd
from openpyxl import Workbook

# 定义文件路径
path = 'C:\\Users\\cbout\\Desktop\\2022 Data.xlsx'
filename = '2022 Data.xlsx'

DF = pd.read_excel(path, sheet_name=1)

# 加载工作簿
workbook = openpyxl.load_workbook(path)

# 获取活动工作表对象
worksheet = workbook.active

# 删除前2列
worksheet.delete_cols(0,2)
# 删除前3行
worksheet.delete_rows(0,4)

workbook.save(path)

报错根因 #

代码共存在3处可触发运行异常的问题：

• openpyxl行列索引规则误用：openpyxl的行、列索引起始值为1，并非Python常见的0起始，delete_cols、delete_rows传入起始索引0属于非法参数，会导致保存后的Excel文件结构损坏。xlsx本质是zip格式压缩包，文件结构损坏后pandas无法将其识别为正常Excel文件，会误判为普通zip文件，最终触发找不到io.excel.zip.reader配置项的报错。
• 删除范围参数错误：要删除前3行，删除长度参数传3即可，原代码传4会多删1行有效数据。
• 读写顺序冲突：代码先用pandas读取目标Excel文件，再用openpyxl加载同一路径的文件做修改保存，文件被占用时极易触发读写冲突。

修正后可运行代码 #

import openpyxl
import pandas as pd

# 定义文件路径
path = 'C:\\Users\\cbout\\Desktop\\2022 Data.xlsx'

# 优先执行openpyxl文件清理逻辑
workbook = openpyxl.load_workbook(path)
worksheet = workbook.active
# 从第1列开始删除，共删2列
worksheet.delete_cols(1, 2)
# 从第1行开始删除，共删3行
worksheet.delete_rows(1, 3)
# 清理结果先保存到本地
workbook.save(path)

# 文件保存完成后，再用pandas读取清理后的数据
DF = pd.read_excel(path, sheet_name=1)

补充提示：如果不需要保留原Excel的单元格格式、公式、校验规则等内容，也可以直接用pandas读取时跳过指定行、读取后丢弃指定列再重存，代码逻辑会更简洁。

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