如何使用pandas从Excel文件中更新一个工作表

Question

我有一个1。Excel文件中包含sheet_1, shee_2 sheet_3

还有一个2。包含sheet_2的Excel文件

，我想更新替换2。将Excel sheet_2文件转换为1。Excel文件sheet_2

和

finaldest = '1.xlsx'
REPORT2WRITERFINAL = pd.ExcelWriter(finaldest)
path = '1.xlsx'
x0 = pd.ExcelFile(path, engine='openpyxl')
path2 = '2.xlsx'
x1 = pd.ExcelFile(path2, engine='openpyxl')
for sheet in x0.sheet_names:
if sheet in x1.sheet_names:
t0 = x1.parse(sheet, header=0)
t0.to_excel(REPORT2WRITERFINAL, startrow=0, startcol=1, sheet_name=sheet, index=False)
REPORT2WRITERFINAL.save()
REPORT2WRITERFINAL.close()

Answer 1

您必须在附加模式下使用ExcelWriter替换表单策略:

with pd.ExcelWriter('1.xlsx', mode='a', if_sheet_exists='replace') as x0, 
pd.ExcelFile('2.xlsx') as x1:
for sheet in set(x0.book.sheetnames).intersection(x1.book.sheetnames):
df = x1.parse(sheet, header=0)
df.to_excel(x0, sheet, startrow=0, startcol=1, index=False)