合法.xlsx文件上的openpyxl load_workbook()导致zipfile.BadZipFile错误 [英] openpyxl load_workbook() on a legit .xlsx file leads to a zipfile.BadZipFile error

问题描述

我试图做的是将数据框数据添加到现有的合法excel文件中.我使用了openpyxl的load_workbook()函数，但它系统地返回错误.这是一些在我的计算机上崩溃的代码:

What I was trying to do is appening dataframe data to an existing legit excel file. I used load_workbook() function from openpyxl, but it systematically returns an error. Here is some code that crashes on my machine:

from openpyxl import load_workbook

report_path = root_folder + '\\log_report.xlsx'
writer = pd.ExcelWriter(report_path, engine='openpyxl')
writer.book = load_workbook(report_path)
writer.close()

在这里，log_report.xlsx已经存在，并已通过熊猫.to_excel()创建.在使用openpyxl load_workbook()打开之前，可以将其打开，编辑并执行MS Excel允许的任何操作.我返回了以下错误:

Here, log_report.xlsx already exists and have been created through pandas .to_excel(). Before opening with openpyxl load_workbook(), it is possible to open it, to edit it and do anything MS Excel allows. I got the following error returned:

Traceback (most recent call last):
  File "D:/failsafe_counter/main.py", line 419, in <module>
    writer.book = load_workbook(report_path)
  File "D:\failsafe_counter\venv\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
  File "D:\failsafe_counter\venv\lib\site-packages\openpyxl\reader\excel.py", line 124, in __init__
    self.archive = _validate_archive(fn)
  File "D:\failsafe_counter\venv\lib\site-packages\openpyxl\reader\excel.py", line 96, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "C:\Users\XXXX\AppData\Local\Programs\Python\Python38-32\lib\zipfile.py", line 1269, in __init__
    self._RealGetContents()
  File "C:\Users\XXXX\AppData\Local\Programs\Python\Python38-32\lib\zipfile.py", line 1336, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

重要的方面是此操作使初始excel文件损坏，无法再次打开.

Important aspect of that is this operation left the initial excel file corrupted, and impossible to open again.

初始文件是合法的zip文件(通过将xlsx重命名为.zip进行验证).上面的小代码返回错误后，它将把Excel文件变成一个空的存档(通过相同的过程进行了验证).

Initial file is a legit zipfile (verified through renaming the xlsx into a .zip). Once the little code above returns the error, it turns the Excel file into an empty archive (verified through the same process).

我在以前的笔记本电脑上(在Windows 7下)成功使用了这些功能，但是由于我是在Windows 10下迁移的，所以我不再能够使用它们.他们俩都在运行python 3.8.

I employed such functions with success on my previous laptop (under windows 7) but since I migrated under windows 10 I'm not able to use them anymore. Both of them were running python 3.8.

在某些配置上是否存在有关openpyxl load_workbook()的已知问题?您是否知道如何解决此问题，或有任何解决方法?

Is there any known issue about openpyxl load_workbook() on some configs? Do you have any idea how to fix this, or any workaround?

推荐答案

我能够复制该问题.它与 pandas 有关.一切正常，直到熊猫1.1.5在熊猫1.2.0中，他们做了一些更改

I was able to replicate the problem. It is pandas related. Everything works just fine up to pandas 1.1.5 In pandas 1.2.0 they did some changes

使用

实例化 pd.ExcelWriter 时

writer = pd.ExcelWriter(report_path, engine='openpyxl')`

它将创建一个大小为0字节的空文件，并覆盖现有文件，然后在尝试加载该文件时出现错误.它与 openpyxl 不相关，因为在最新版本的 openpyxl 中，它与熊猫1.1.5兼容.

it creates empty file with size 0 bytes and overwrites the existing file and then you get error when try to load it. It is not openpyxl related, because with latest version of openpyxl it works fine with pandas 1.1.5.

解决方案-指定 mode ='a'，将上面的行更改为

The solution - specify mode='a', change the above line to

writer = pd.ExcelWriter(report_path, engine='openpyxl', mode='a')

或者-查看@CharlieClark解决方案或此解决方案，在实例化 pd之前，它们会在其中加载文件.ExcelWriter ..

Alternatively - look at @CharlieClark solution or this solution where they loads the file before instantiating the pd.ExcelWriter..

这篇关于合法.xlsx文件上的openpyxl load_workbook()导致zipfile.BadZipFile错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！