“OpenpyxlWriter”对象的属性“sheets”没有使用熊猫和openpyxl的设置器
“OpenpyxlWriter”对象的属性“sheets”没有使用熊猫和openpyxl的设置器
提问于 2022-11-10 02:05:37
这段代码用于获取xlsx文件并对其进行写入，但是在从熊猫1.1.5升级到1.5.1之后，我得到了zipfile.badzipfile file is not a zip file
然后我读到了here，在熊猫1.2.0之后，pd.ExcelWriter(report_path, engine='openpyxl')创建了一个新文件，但是由于这是一个完全空的文件，openpyxl无法加载它。
知道了这一点，我把代码改成了这个，但现在我得到了AttributeError: property 'sheets' of 'OpenpyxlWriter' object has no setter。我该怎么处理呢？
book = load_workbook('Resultados.xlsx')
writer = pd.ExcelWriter('Resultados.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
reader = pd.read_excel(r'Resultados.xlsx')
df = pd.DataFrame.from_dict(dict_)
df.to_excel(writer, index=False, header=False, startrow=len(reader) + 1)
writer.close()回答 2
Stack Overflow用户
回答已采纳
发布于 2022-11-10 16:31:25
TLDR
writer.sheets
- Rearrange
- 使用
.update修改脚本的顺序，使其运行
# run before initializing the ExcelWriter
reader = pd.read_excel("Resultados.xlsx", engine="openpyxl")
book = load_workbook("Resultados.xlsx")
# use `with` to avoid other exceptions
with pd.ExcelWriter("Resultados.xlsx", engine="openpyxl") as writer:
writer.book = book
writer.sheets.update(dict((ws.title, ws) for ws in book.worksheets))
df.to_excel(writer, index=False, header=False, startrow=len(reader)+1)详细信息
用一些假数据重新创建问题
import numpy as np
from openpyxl import load_workbook
import pandas as pd
if __name__ == "__main__":
# make some random data
np.random.seed(0)
df = pd.DataFrame(np.random.random(size=(5, 5)))
# this makes an existing file
with pd.ExcelWriter("Resultados.xlsx", engine="openpyxl") as writer:
df.to_excel(excel_writer=writer)
# make new random data
np.random.seed(1)
df = pd.DataFrame(np.random.random(size=(5, 5)))
# what you tried...
book = load_workbook("Resultados.xlsx")
writer = pd.ExcelWriter("Resultados.xlsx", engine="openpyxl")
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
reader = pd.read_excel("Resultados.xlsx")
# skipping this step as we defined `df` differently
# df = pd.DataFrame.from_dict(dict_)
df.to_excel(writer, index=False, header=False, startrow=len(reader)+1)
writer.close()我们得到相同的错误加上一个FutureWarning
...\StackOverflow\answer.py:23: FutureWarning: Setting the `book` attribute is not part of the public API, usage can give unexpected or corrupted results and will be removed in a future version
writer.book = book
Traceback (most recent call last):
File "...\StackOverflow\answer.py", line 24, in <module>
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
AttributeError: can't set attribute 'sheets'AttributeError是因为sheets是writer实例的property。如果你不熟悉它，here is a resource。
在较短的范围内，会引发异常，因为sheets不能以您正在尝试的方式进行修改。但是，您可以这样做：
# use the `.update` method
writer.sheets.update(dict((ws.title, ws) for ws in book.worksheets))这将使我们越过AttributeError，但我们将按下几行ValueError：
reader = pd.read_excel("Resultados.xlsx")Traceback (most recent call last):
File "...\StackOverflow\answer.py", line 26, in <module>
reader = pd.read_excel("Resultados.xlsx")
...
File "...\lib\site-packages\pandas\io\excel\_base.py", line 1656, in __init__
raise ValueError(
ValueError: Excel file format cannot be determined, you must specify an engine manually.执行错误消息所述的操作，并向engine参数提供参数
reader = pd.read_excel("Resultados.xlsx", engine="openpyxl")现在我们回到原来的zipfile.BadZipFile异常
Traceback (most recent call last):
File "...\StackOverflow\answer.py", line 26, in <module>
reader = pd.read_excel("Resultados.xlsx", engine="openpyxl")
...
File "...\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玩了一会儿之后，我注意到在运行这一行之后无法手动打开Resultados.xlsx文件：
writer = pd.ExcelWriter("Resultados.xlsx", engine="openpyxl")
因此，我重新排序了代码中的一些步骤：
# run before initializing the ExcelWriter
reader = pd.read_excel("Resultados.xlsx", engine="openpyxl")
book = load_workbook("Resultados.xlsx")
# the old way
# writer = pd.ExcelWriter("Resultados.xlsx", engine="openpyxl")
with pd.ExcelWriter("Resultados.xlsx", engine="openpyxl") as writer:
writer.book = book
writer.sheets.update(dict((ws.title, ws) for ws in book.worksheets))
df.to_excel(writer, index=False, header=False, startrow=len(reader)+1)Stack Overflow用户
发布于 2022-11-10 06:24:48
试试这个：
filepath = r'Resultados.xlsx'
with pd.ExcelWriter(
filepath,
engine='openpyxl',
mode='a',
if_sheet_exists='overlay') as writer:
reader = pd.read_excel(filepath)
df.to_excel(
writer,
startrow=reader.shape[0] + 1,
index=False,
header=False)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74383395
复制相关文章
相似问题
腾讯云开发者
