pandas.error

I created the test.xlsx with xlwt and wanted to add sheet using the

def write(pureList,resultList,outPath,sheetName):
    d = {'words':pureList,'translated':resultList}
    df = pd.DataFrame(data=d)
    with pd.ExcelWriter(outPath,mode='a',engine="openpyxl") as writer:
        df.to_excel(writer,sheet_name=sheetName)

And here is the Error:

Traceback (most recent call last):
  File "d:\ori-desktop\exe\python\pandas\21excelwrite.py", line 25, in <module>
    write(pureList,resultList,outPath,sheetName)
  File "d:\ori-desktop\exe\python\pandas\21excelwrite.py", line 14, in write
    with pd.ExcelWriter(outPath,mode='a',engine="openpyxl") as writer:
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\excel\_openpyxl.py", line 73, in __init__
    self._book = load_workbook(self._handles.handle, **engine_kwargs)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 124, in __init__
    self.archive = _validate_archive(fn)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 96, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\zipfile.py", line 1267, in __init__
    self._RealGetContents()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python310\lib\zipfile.py", line 1334, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

I want to know how to deal this.

该回答引用ChatGPT

The error message suggests that the file specified in outPath is not a valid zip file.

The pd.ExcelWriter() function requires an existing Excel file to which it can add new sheets. If the file does not exist, it will create a new one. It seems like in your case, the file specified in outPath might not be a valid Excel file.

You can try to add a check to see if the file exists and if not, create a new Excel file. Here is an example:


import os
import pandas as pd

def write(pureList, resultList, outPath, sheetName):
    d = {'words':pureList,'translated':resultList}
    df = pd.DataFrame(data=d)
    if os.path.exists(outPath):
        with pd.ExcelWriter(outPath, mode='a', engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name=sheetName)
    else:
        with pd.ExcelWriter(outPath, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name=sheetName)

This code checks if the file specified in outPath exists using the os.path.exists() function. If it exists, it will append the new sheet to the existing file. If it does not exist, it will create a new Excel file with the specified sheet.

如保存的文件不存在，直接用pd.ExcelWriter
如果保存的文件已存在，用openpyxl.load_workbook加载已有的文件, 再使用pd.ExcelWriter
如果pd.ExcelWriter和openpyxl.load_workbook的顺序反了，则报错：BadZipFile: File is not a zip file
参考代码：

https://blog.csdn.net/i4scareCrawl/article/details/124170080

有帮助的话，请点采纳～

不知道你这个问题是否已经解决, 如果还没有解决的话:

建议你看下这篇博客👉 ：关于pandas一些warning的解决办法

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

根据报错，你那个filename文件不是zip文件，程序要求的是zip文件所以报错，你看对应的格式改一下能不能解决问题。

有了点新进展：

def creatExcle(outPath):
    workBook = xlwt.Workbook()
    workBook.add_sheet('sheet')
    workBook.save(outPath)

def write(pureList, resultList, outPath, sheetName):
    d = {'words':pureList,'translated':resultList}
    df = pd.DataFrame(data=d)
    # if os.path.exists(outPath):
    #     with pd.ExcelWriter(outPath, mode='a', engine='openpyxl') as writer:
    #         df.to_excel(writer, sheet_name=sheetName)
    # else:
    with pd.ExcelWriter(outPath,mode='a', engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name=sheetName)

这两个单独运行都没有问题，如果放在同一个函数里面：

if __name__=='__main__':
    pureList = [1,2,3]
    resultList = [4,5,6]
    outPath = 'test.xlsx'
    sheetName = '1.1.1'
    creatExcle(outPath)
    write(pureList, resultList, outPath, sheetName)

就会有上述的报错