将多个sheet合并成一个sheet导出，将多个dataframe导出至多个sheet

# -*- coding: utf-8 -*-
"""
Created on Mon Jan 20 13:02:27 2020

@author: twx253
"""

import xlrd
import pandas as pd
from  pandas import DataFrame
from openpyxl import load_workbook

excel_name = 'C:/Users/twx253/Desktop/many_sheets.xlsx'  
wb = xlrd.open_workbook(excel_name)
sheets = wb.sheet_names() # 获取workbook中所有的表格
# 循环遍历所有sheet
alldata = DataFrame()
for i in range(len(sheets)):
    df = pd.read_excel(excel_name, sheet_name=i, index=False, encoding='utf8')
    print(df.shape)
    alldata = alldata.append(df)
    
#指定合并后的数据放在哪个excel里
writer = pd.ExcelWriter('C:/Users/twx253/Desktop/many_sheets.xlsx',engine='openpyxl') 
book = load_workbook(writer.path)
writer.book = book

#指定合并后的数据放在哪个sheet里
alldata.to_excel(excel_writer=writer,sheet_name="ALLDATA",index=False)
writer.save()
writer.close()
print('处理完成！')

# =============================================================================
# 现在需要将dataframe1,dataframe2和dataframe3中的数据存放到result.xlsx中的三个不同的sheet中,
# sheet的名字分别为dataframe1,dataframe2,datafame3.
# =============================================================================
writer = pd.ExcelWriter('result.xlsx')
for i in ['dataframe1','dataframe2','dataframe3']:
       eval(i).to_excel(excel_writer=writer,sheet_name=i,index=False)
writer.save()
writer.close()