import xlrd import pandas as pd from pandas import DataFrame from openpyxl import load_workbook excel_name = '文件路径/文件名.xlsx' #表格地址+表格名 wb = xlrd.open_workbook(excel_name) # 获取workbook中所有的表格 sheets = wb.sheet_names() # print(sheets) # 循环遍历所有sheet alldata = DataFrame() for i in range(len(sheets)): df = pd.read_excel(excel_name, sheet_name=i, index=False, encoding='utf8', dtype='str') alldata = alldata.append(df) #查看dataframe #print (alldata) #保存为新的sheet,首先新建sheet,合并后的数据保存到新sheet中 #可保存在一个文件下，需要另存的，要在本地新建文件 writer = pd.ExcelWriter('文件路径/合并数据.xlsx',engine='openpyxl') book = load_workbook(writer.path) writer.book = book #利用dataframe.to_excel保存合并后的数据到新的sheet alldata.to_excel(excel_writer=writer,sheet_name="NEWDATA")#生成新的sheet命名为NEWDATA writer.save() writer.close()
