Updating existing Excel file with Pandas and Openpyxl throws an AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter

huangapple
go评论154阅读模式
英文:

Updating existing Excel file with Pandas and Openpyxl throws an AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter

问题

我一直在尝试更新现有Excel中的数据，过程如下：

  1. 从Excel读取数据

  2. 使用pandas将其与新数据合并

  3. 将合并后的数据帧保存到原始文件中

它一直返回以下错误，我认为这是由于在读取文件时写入（更新）同一文件导致的：

  1. AttributeError                            Traceback (most recent call last)
  2. Cell In[14], line 19
  3.      18 with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
  4. ---> 19     writer.book = book
  5.      20     writer.sheets = {ws.title: ws for ws in book.worksheets}
  7. AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter

我的代码在这里 - 运行它还会擦除原始文件的数据并使文件无法使用：

  1. # Load the Excel file
  2. file_path = 'original.xlsx'
  3. update = 'update.xlsx'
  5. # Open the file in read-only mode to prevent any locks
  6. with open(file_path, "rb") as file:
  7.     book = load_workbook(file)
  9. # Combine original with update file
  10. for sheet_name in ['sheet1', ...]:
  11.     df1 = pd.read_excel(file_path, sheet_name=sheet_name)
  12.     df2 = pd.read_excel(update, sheet_name=sheet_name)
  13.     df2 = df2.iloc[::-1]
  14.     df1 = pd.concat([df1, df2], ignore_index=True)
  15.     df1 = df1.drop_duplicates(subset='column1', keep='last')
  17.     # Write combined data to the sheet
  18.     with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
  19.         writer.book = book
  20.         writer.sheets = {ws.title: ws for ws in book.worksheets}
  21.         
  22.         # Set the sheet as the active sheet
  23.         book.active = book.sheetnames.index(sheet_name)
  24.         
  25.         df1.to_excel(writer, sheet_name=sheet_name, index=False, startrow=1)
  27.     print(f"Successfully updated '{sheet_name}' sheet in '{file_path}'.")

请注意，这是你的原始代码的中文翻译部分。

英文:

I have been trying to update data in an existing Excel--the process as follows:

1)read the data from Excel

2)combine it with a new data using pandas

3)save the combined dataframe into the original file

It keeps return the following error, which I think it is coming from writing(updating) the same file while reading it:

  1. AttributeError                            Traceback (most recent call last)
  2. Cell In[14], line 19
  3.      18 with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
  4. ---> 19     writer.book = book
  5.      20     writer.sheets = {ws.title: ws for ws in book.worksheets}
  7. AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter

My code is here--running it also erases the data of the original file and make the file unusable:

  1. # Load the Excel file
  2. file_path = 'original.xlsx'
  3. update = 'update.xlsx'
  5. # Open the file in read-only mode to prevent any locks
  6. with open(file_path, "rb") as file:
  7.     book = load_workbook(file)
  9. # Combine original with update file
  10. for sheet_name in ['sheet1', ...]:
  11.     df1 = pd.read_excel(file_path, sheet_name = sheet_name)
  12.     df2 = pd.read_excel(update, sheet_name = sheet_name)
  13.     df2 = df2.iloc[::-1]
  14.     df1 = pd.concat([df1, df2], ignore_index = True)
  15.     df1 = df1.drop_duplicates(subset = 'column1', keep = 'last')
  17.     # Write combined data to the sheet
  18.     with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
  19.         writer.book = book
  20.         writer.sheets = {ws.title: ws for ws in book.worksheets}
  21.         
  22.         # Set the sheet as the active sheet
  23.         book.active = book.sheetnames.index(sheet_name)
  24.         
  25.         df1.to_excel(writer, sheet_name = sheet_name, index = False, startrow = 1)
  27.     print(f"Successfully updated '{sheet_name}' sheet in '{file_path}'.")

答案1

得分: 0

这是因为 book 是只读属性。

由于您只是在更新文件，可以尝试使用 Pandas 提供的附加模式和 if_sheet_exists 标志，示例文档请查看：docs

结果将类似于以下内容：

  1. # 将原始数据与更新文件合并
  2. for sheet_name in ['sheet1', 'sheet2']:
  3.     df1 = pd.read_excel(file_path, sheet_name=sheet_name)
  4.     df2 = pd.read_excel(update, sheet_name=sheet_name)
  5.     df2 = df2.iloc[::-1]
  6.     df1 = pd.concat([df1, df2], ignore_index=True)
  7.     df1 = df1.drop_duplicates(subset='column1', keep='last')
  9.     # 将合并后的数据写入工作表
  10.     with pd.ExcelWriter(file_path, mode='a', if_sheet_exists='replace', engine='openpyxl') as writer:
  11.         df1.to_excel(writer, sheet_name=sheet_name, index=False, startrow=0)
  13.     print(f"成功更新了 '{sheet_name}' 工作表在 '{file_path}' 中。")
英文:

This is because book is read-only property.

Since you're only updating a file, you may try append mode with flags a and if_sheet_exists, provided by Pandas: docs

Result will look similar to that:

  1. # Combine original with update file
  2. for sheet_name in ['sheet1', 'sheet2']:
  3.     df1 = pd.read_excel(file_path, sheet_name=sheet_name)
  4.     df2 = pd.read_excel(update, sheet_name=sheet_name)
  5.     df2 = df2.iloc[::-1]
  6.     df1 = pd.concat([df1, df2], ignore_index=True)
  7.     df1 = df1.drop_duplicates(subset='column1', keep='last')
  9.     # Write combined data to the sheet
  10.     with pd.ExcelWriter(file_path, mode='a', if_sheet_exists='replace', engine='openpyxl') as writer:
  11.         df1.to_excel(writer, sheet_name=sheet_name, index=False, startrow=0)
  13.     print(f"Successfully updated '{sheet_name}' sheet in '{file_path}'.")

huangapple
  • 本文由 发表于 2023年5月29日 23:09:54
  • 转载请务必保留本文链接：https://go.coder-hub.com/76358445.html
  • excel
  • openpyxl
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定