1. import xlrd
    2. import pandas as pd
    3. from pandas import DataFrame
    4. from openpyxl import load_workbook
    6. #找到文件存储位置，本程序文件为data1.xlsx
    8. excel_name = r"D:\aaaa\data1.xlsx"
    9. wb = xlrd.open_workbook(excel_name)
    10. sheets = wb.sheet_names()
    11. alldata = DataFrame()
    12. for i in range(len(sheets)):
    13.     df = pd.read_excel(excel_name, sheet_name=i, index=False, encoding='utf8')
    14.     alldata = alldata.append(df)
    15. writer = pd.ExcelWriter(r"d:\aaaa\data1.xlsx",engine='openpyxl')
    16. book = load_workbook(writer.path)
    17. writer.book = book
    18. alldata.to_excel(excel_writer=writer,sheet_name="ALLDATA")
    19. writer.save()
    20. writer.close()

    xlrd需要搞成1.2.0版本的才可以