合并多张excel的多张sheet数据

import os

import xlrd

import xlsxwriter

import pandas as pd

from pandas import DataFrame

import openpyxl

 

outputfile = r'……'#修改项

writer=pd.ExcelWriter(outputfile, engine='openpyxl')

root = r'……'#修改项

 

targetnames = [x for x in os.listdir(root) if x.endswith(".xlsx")]

sheetnamelist = []

for targetname in targetnames:

    wb = xlrd.open_workbook(os.path.join(root,targetname))

    sheets = wb.sheet_names()

    sheetnamelist.append(sheets)

data = DataFrame()

for i in range(len(targetnames)):

    for n in sheetnamelist[i]:

        print(n)

        df = pd.read_excel(os.path.join(root,targetnames[i]), sheet_name=n)

        data = data.append(df)

data.to_excel(excel_writer=writer,sheet_name='merged',encoding='utf-8',index=False)

writer.save()

writer.close()

print('合并多excel多sheet完成!')