You can use with
:
with pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a') as writer:
d1.to_excel(writer,sheet_name='d1')
d2.to_excel(writer,sheet_name='d2')
writer.save()
writer.close()
update
This should work just note that the a blank file needs to be created before hand. You can just create a blank file using python if you want. I created a simple loop to, in some ways, mimic the essence of what you are trying to accomplish:
import pandas as pd
from openpyxl import load_workbook
d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'],
"B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'],
"B":['1', '2', '3']})
dfs = [d1,d2]
for i in range(len(dfs)):
sheet = 'd'+str(i+1)
data = dfs[i]
writer = pd.ExcelWriter('atest.xlsx',engine='openpyxl', mode='a')
writer.book = load_workbook('atest.xlsx') # here is the difference
data.to_excel(writer,sheet_name=sheet)
writer.save()
writer.close()
or here is the modified first example:
d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'],
"B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'],
"B":['1', '2', '3']})
writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='w')
d1.to_excel(writer,sheet_name='d1')
writer.save()
writer.close()
writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='a')
writer.book = load_workbook('atest.xlsx')
d2.to_excel(writer,sheet_name='d2')
writer.save()
writer.close()
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…