我发现关键是在这里的代码中使用 writer.workbook 而不是 writer.book:
writer.workbook = openpyxl.load_workbook('test.xlsx')
并且在 pd.ExcelWriter 中添加选项:
mode='a',if_sheet_exists='overlay'
import pandas as pd
import openpyxl
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)
df_empty = pd.DataFrame()
df_empty.to_excel('test.xlsx')
workbook = openpyxl.load_workbook('test.xlsx')
ex_sheet = workbook['Sheet1']
ex_sheet.title = 'Tmp'
workbook.save('test.xlsx')
df = pd.DataFrame({'Column1': ['aa', 'bb', 'cc', 'dd'],
'Column2': [100, 170, 140, 160]})
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')
writer.workbook = openpyxl.load_workbook('test.xlsx')
df.to_excel(writer, sheet_name='Tmp', index=False, header=True, startrow=3, startcol=3)
writer.save()
我使用Python 3.8;openpyxl 版本为 3.0.10;pandas 版本为 1.5.0。