我一直在try 更新现有Excel中的数据--过程如下:
1)从Excel中读取数据
2)使用Pandas 将其与新数据相结合
3)将合并后的数据帧保存到原始文件中
它一直返回以下错误,我认为这是因为在读取相同的文件时写入(更新)了该文件:
AttributeError Traceback (most recent call last)
Cell In[14], line 19
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}
AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter
我的代码如下--运行它还会擦除原始文件的数据,并使该文件不可用:
# Load the Excel file
file_path = 'original.xlsx'
update = 'update.xlsx'
# Open the file in read-only mode to prevent any locks
with open(file_path, "rb") as file:
book = load_workbook(file)
# Combine original with update file
for sheet_name in ['sheet1', ...]:
df1 = pd.read_excel(file_path, sheet_name = sheet_name)
df2 = pd.read_excel(update, sheet_name = sheet_name)
df2 = df2.iloc[::-1]
df1 = pd.concat([df1, df2], ignore_index = True)
df1 = df1.drop_duplicates(subset = 'column1', keep = 'last')
# Write combined data to the sheet
with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
# Set the sheet as the active sheet
book.active = book.sheetnames.index(sheet_name)
df1.to_excel(writer, sheet_name = sheet_name, index = False, startrow = 1)
print(f"Successfully updated '{sheet_name}' sheet in '{file_path}'.")