pandas.error

Question

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.

Answer 1

该回答引用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.