# -*- 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()