多个Excel文件合并为一个文件的多个sheet页

# !/usr/bin/env python3
# _*_ encoding: utf-8 _*_
# @Created time      : 2021-03-30 15:01:53
# @Last Modified time: 2021-03-30 15:08:31
import os
import pandas as pd
 

def merge_sheet(in_path):
    '''
    :arg listdir:源文件路径
    :arg file_path:文件输出路径
    合并多个excel为同一个文件的多个sheet
    '''
    excel_name = '_merge_' + datetime.datetime.now().strftime('%Y%m%d') + '.xlsx'
    # 初始化文件路径
    merge_path = r'E:/Ace/in_file/merge/'
    if not os.path.exists(merge_path):
        os.makedirs(merge_path)
    else:
        print('merge_path: {0} is exists!'.format(merge_path))
    # 初始化输出文件
    writer = pd.ExcelWriter(merge_path + excel_name)
    # 输出当前目录下所有文件
    file_list = os.listdir(in_path)
    for index, i in enumerate(file_list):
        # 文件名超长，截断处理
        file_name = i[-35: -5]
        # 拼接文件读取路径
        file_path = in_path + r'/%s' % i
        print('已读取表：', file_path)
        data = (i.replace('.xlsx', ''), file_name.replace('.xlsx', ''))
        with open(merge_path + 'map.txt', 'a', encoding='utf-8') as f:
            f.write((((str(data) + '\n').replace('(', '')).replace(')', '')).replace("'", ""))
        content = pd.read_excel(file_path, skiprows=[0], engine='openpyxl')
        sheet_name = file_name
        # print(i, sheet_name)
        content.to_excel(writer, sheet_name, index=True, encoding='utf-8')
    writer.save()


if __name__ == '__main__':
	in_path = r'/data/'
	merge_sheet(in_path)