将文件夹下所有excel写入同一个excel多个sheet

import xlrd,xlwt
import os,shutil
from xutils.copy import copy

def get_all_excel(path):
    # 获取当前路径下所有excel
    # 返回al1_excel 是excel路径，all_name 是所有excel名字
    folder = os.walk(path)
    folder = list(folder)
    al1_name = [i.split('.')[0] for i in folder[0][2:][0]]
    all_excel = [path + '1' + i for i in folder[0][2:][0]]
    return all_excel, al1_name

def xlrd_read_allsheet(filename):
    # 读取所有excel中所有sheet名字
    # :return: excel名_sheet名:excel中数据

    # 读取所有的sheet内容 iparam filename:

     workbook = xlrd.open_workbook(filename)
     names = workbook.sheet_names()# 返回所有Sheet的名字 name_data=0# 创建字典
     name_data={}
     for name in names: # 遍历当前excel下所有的sheet
        table = workbook.sheet_by_name(name)
        rows = table.nrows
        cols = table.ncols
        al1_data = []
        for row in range(rows):
            inner_data = []
            for col in range(cols):
                inner_data.append(table.cell_value(row, col))
            al1_data.append(inner_data) # 单个sheet数据 name_data[filename,split()[-1].split0,)[0]	 name] = all data # excel
        name_data[filename.split('\\')[-1].split('.')[0]+'_'+name]=al1_data
     return name_data

def xlwt_write(data, path,	 sheet_name, title=None):
    try:
        workbook= copy(xlrd.open_workbook(path))# 若写入的excel存在 
    except:
        workbook= xlwt.Workbook()# 若写入的excel不存在，则创建excel 
    worksheet = workbook.add_sheet(sheet_name)# 使用原excel名称+sheet 
    if title: # 如果表头存在，写入表头，不存在则pass
        for row in range(len(data)):
            for col in range(len(data[row])):
                worksheet.write(row+ 1,col, data[row][col])
    else:# 写Aexcel
        for row in range(len(data)):
            for col in range(len(data[row])):
                worksheet.write(row, col,data[row][col])
    workbook.save(path)
    return path + '写入成功'

# 调用
def run():
    path = r'E:\下载表'
    all_excel,all_name = get_all_excel(path)
    excel_data =[]
    for filename in all_excel:
        name_data = xlrd_read_allsheet(filename) 
        excel_data.append(name_data)
    for excel in range(len(excel_data)):
        for k, v in excel_data[excel].items():
            xlwt_write(v, r'result.xLsx', sheet_name = k.split('_')[0] +"_"+str(excel))

    return 

run()