使用Pandas ExcelWriter写入已有Excel工作表报BadZipFile错误如何修复

架构老林

2026-9-1

问题描述

尝试删除并覆盖名为Data的整张Excel工作表时，运行代码返回BadZipFile: File is not a zip file报错。
原始代码如下：

import pandas as pd
from openpyxl import load_workbook

file_loc = 'G:\\my_existing_excel_data.xlsx'

with pd.ExcelWriter(file_loc, datetime_format='m/d/yyyy') as writer:
    
    book = load_workbook(file_loc)
    writer = pd.ExcelWriter(file_loc, engine='openpyxl') 
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    
    date_format = workbook.add_format({'num_format': 'm/d/yyyy'})
    pct_format = workbook.add_format({'num_format': '0.00%'})
    wrap_fmt = workbook.add_format({'text_wrap': True})
    
    df.to_excel(writer, 'Data')

完整报错回溯：

Traceback (most recent call last):

  File "C:\Users\AppData\Local\Temp\2/ipykernel_20416/3303582457.py", line 3, in <module>
    book = load_workbook(file_loc)

  File "C:\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,

  File "C:\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 124, in __init__
    self.archive = _validate_archive(fn)

  File "C:\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 96, in _validate_archive
    archive = ZipFile(filename, 'r')

  File "C:\Anaconda3\lib\zipfile.py", line 1257, in __init__
    self._RealGetContents()

  File "C:\Anaconda3\lib\zipfile.py", line 1324, in _RealGetContents
    raise BadZipFile("File is not a zip file")

BadZipFile: File is not a zip file

报错原因

代码执行顺序错误：进入with pd.ExcelWriter(...)上下文时，已经以写入模式打开目标xlsx文件，文件会被直接截断清空，后续调用load_workbook(file_loc)读取的是被清空的无效文件。xlsx本质是zip格式压缩包，空文件不符合zip结构规范，因此触发BadZipFile报错。
重复创建ExcelWriter实例：第一行with语句创建的writer没有被任何逻辑使用，后续又重新实例化了一个openpyxl引擎的writer，逻辑冗余。
引擎方法不匹配：代码中调用的workbook.add_format()是xlsxwriter引擎的专属API，和使用的openpyxl引擎不兼容，即使解决了zip报错也会触发属性错误。
缺少删除旧工作表逻辑：代码没有提前删除已存在的Data工作表，直接写入会生成重名工作表，达不到覆盖原有工作表的目的。

修复代码

注意：运行前先确认原Excel文件没有被之前的错误运行损坏（如果文件已经变成0字节空文件，需要先恢复备份的原文件再执行代码）。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import numbers, Alignment

file_loc = 'G:\\my_existing_excel_data.xlsx'

# 第一步：提前加载完整原工作簿，不要提前以写入模式占用文件
book = load_workbook(file_loc)
# 已存在同名Data工作表则先删除
if 'Data' in book.sheetnames:
    del book['Data']

# 第二步：创建写入器绑定已加载的工作簿
with pd.ExcelWriter(
    file_loc,
    engine='openpyxl',
    datetime_format='m/d/yyyy',
    mode='a',
    if_sheet_exists='replace' # pandas 1.4+版本支持该参数，可自动替换同名工作表
) as writer:
    writer.book = book
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    
    # 写入DataFrame，index参数根据自身需求决定是否保留行索引
    df.to_excel(writer, sheet_name='Data', index=False)
    
    # 以下为openpyxl设置单元格格式的逻辑，替换原xlsxwriter的add_format写法
    ws = writer.sheets['Data']
    # 设置日期格式（对应m/d/yyyy），假设日期在A列，根据实际列位置调整列号
    for cell in ws['A'][1:]:
        cell.number_format = 'm/d/yyyy'
    # 设置百分比格式（对应0.00%），假设百分比数据在C列，根据实际列位置调整
    for cell in ws['C'][1:]:
        cell.number_format = numbers.FORMAT_PERCENTAGE_00
    # 设置自动换行
    for row in ws.iter_rows():
        for cell in row:
            cell.alignment = Alignment(wrap_text=True)

补充说明

如果不需要保留原文件内其他工作表的内容，可以直接用默认写入模式创建ExcelWriter，不需要提前加载原工作簿，写入时会自动覆盖整个文件，逻辑更简单。
操作重要Excel文件前务必备份，避免代码运行异常导致文件损坏数据丢失。

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