合并两个Excel文件时出现zipfile.BadZipFile错误的技术求助

问题描述 #

我用pandas合并两个Excel文件，指定了openpyxl引擎，路径用了原始字符串前缀r，还尝试过压缩文件，但运行时还是触发zipfile.BadZipFile: File is not a zip file错误。代码如下：

import pandas as pd

JL_MP_PE_07A = pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\JL-MP-PE-07A.xlsx', skiprows=22, engine='openpyxl')
JL_MP_PE_07B = pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\JL-MP-PE-07B.xlsx', skiprows=23, engine= 'openpyxl')

merged_df = pd.concat([JL_MP_PE_07A, JL_MP_PE_07B])

merged_df.to_excel('D:\Maintenance\Bloc 6\PROJECT KPI\Performance\merged_OT_file.xlsx', index=False)

错误栈信息：

File "d:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Performance_Excel merger.py", line 5, in <module>
    JL_MP_PE_07B = pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\JL-MP-PE-07B.xlsx', skiprows=23, engine= 'openpyxl')
   
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 478, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 1513, in __init__
    self._reader = self._engines[engine](self._io, storage_options=storage_options)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 549, in __init__
    super().__init__(filepath_or_buffer, storage_options=storage_options)
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 540, in __init__
    self.book = self.load_workbook(self.handles.handle)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 560, in load_workbook
    return load_workbook(
           ^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 344, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 123, in __init__
    self.archive = _validate_archive(fn)
                   ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 95, in _validate_archive
    archive = ZipFile(filename, 'r')
              ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\zipfile.py", line 1301, in __init__
    self._RealGetContents()
  File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\zipfile.py", line 1368, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

解决方案 #

错误核心是JL-MP-PE-07B.xlsx不是有效的xlsx格式（xlsx本质是zip压缩包，文件损坏或格式不匹配会触发这个错误），按以下步骤排查：

• 验证文件是否损坏：手动用Excel打开JL-MP-PE-07B.xlsx，如果Excel提示文件损坏，直接重新获取完好的文件；如果能打开，执行另存为，选择「Excel工作簿（.xlsx）」格式覆盖原文件，再重新运行代码。
• 检查文件实际格式：确认该文件不是把旧版.xls文件改名为.xlsx。如果是xls格式，要么转存为xlsx，要么修改代码用xlrd引擎（先执行pip install xlrd安装依赖），代码示例：

  JL_MP_PE_07B = pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\JL-MP-PE-07B.xlsx', skiprows=23, engine='xlrd')
  

• 修正输出路径的转义问题：你的输出路径没加原始字符串前缀r，可能因转义字符导致输出失败，修改为：

  merged_df.to_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\merged_OT_file.xlsx', index=False)
  

• 分步调试定位：先单独测试读取JL-MP-PE-07B.xlsx，确认文件没问题后再执行合并逻辑，避免其他代码干扰。

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