见代码
import pandas as pd
df1 = pd.DataFrame(columns=['name', 'number'])
df1.loc[0] = ['cat', '3']
df2 = pd.DataFrame(columns=['name', 'number'])
df2.loc[0] = ['dog', '4']
excel_writer = pd.ExcelWriter('文件.xlsx') # 定义writer,选择文件(文件可以不存在)
df1.to_excel(excel_writer, sheet_name='AAA', index=False) # 写入指定表单
df2.to_excel(excel_writer, sheet_name='BBB', index=False)
excel_writer.save() # 储存文件
excel_writer.close()