python 多个excel合并成多个sheet以及多个excel合并成一个sheet

最近有两个需求，第一是要求把指定目录下多个结构相同的excel合并为一个excel，每个excel都对应合并后的一个sheet；第二是要求把指定目录下多个结构相同的excel合并为一个sheet，并在合并后的sheet添加一列，标识数据来源与哪一个sheet，来自同一数据源的多行数据第一列要求合并单元格。

• 多个excel合并的效果：

import os
import pandas as pd
import xlwt

import xlrd
from xlutils.copy import copy
import sys

# 要写入的excel全路径及名称
file_to = sys.argv[1]
# 要合并的excel的目录
rootdir = sys.argv[2]
# 要合并的excel文件名的公共部分
file_name = sys.argv[3]


# 新建列表，存放文件名
filename_excel = []


# 列出文件夹下所有的目录与文件
def list_all_files(rootdir):
    _files = []
    list = os.listdir(rootdir)
    for i in range(0, len(list)):
        path = os.path.join(rootdir, list[i])
        if os.path.isdir(path):
            _files.extend(list_all_files(path))
        if os.path.isfile(path):
            _files.append(path)
    return _files


# 获取指定的文件名和要写入的sheet名
def get_file(files, file_name):
    # 存放该excel的list
    file_list = []
    # 存放文件去除后缀名的list
    file_name_list = []
    for file in files:
        # 判断此文件名称是否包含此关键名称
        if file_name in file:
            file_list.append(file)
            print(file)
            # 截取要写入的sheet名（此处截取的规则是：截取最后一个'/'之后，'.'之前，'）'之后）
            # /Users/ywq/Downloads/工作/温检/数据/市/2018年12月-2019年11月/050101_[1]合计_地区分布(行)_[27]结案、提出抗诉（件）（温州2018年12月-2019年11月）.xlsx
            # 结案、提出抗诉（件）（温州2018年12月-2019年11月）
            file_name_list.append(file.split('/')[-1].split('.')[0].split(')')[1])
    return file_list, file_name_list


def merge_files(file_to, files, file_names):
    # 创建sheet
    workbook = xlwt.Workbook()
    for file_name in file_names:
        workbook.add_sheet(file_name, cell_overwrite_ok=True)
        # print(file_name)
        # print(files[file_names.index(file_name)])
        workbook.save(file_to)

    # 向不同sheet写入数据
    writer = pd.ExcelWriter(file_to)
    for file_name in file_names:
        df = pd.read_excel(files[file_names.index(file_name)])
        # print(df.columns)
        # df.rename(columns={'Unnamed: 0': ''})
        df.to_excel(writer, sheet_name=file_name, startcol=0, index=False)
    writer.save()


    rb = xlrd.open_workbook(file_to, formatting_info=True)
    wb = copy(rb)
    # 修改第一个cell的值('Unnamed: 0' -> '')   这个步骤可根据实际情况选择
    for file_name in file_names:
        ws = wb.get_sheet(file_name)
        ws.write(0, 0, '')
    wb.save(file_to)

files, file_names = get_file(list_all_files(rootdir), file_name)
merge_files(file_to, files, file_names)

• 多个sheet合并成一个sheet的效果：

import os
from openpyxl import load_workbook
import pandas as pd
import sys

# # 要写入的excel全路径及名称
# file_to = sys.argv[1]
# # 要合并的excel的目录
# rootdir = sys.argv[2]
# # 要合并的excel文件名的标识
# file_name = sys.argv[3]

# 新建列表，存放文件名
filename_excel = []


# 列出文件夹下所有的目录与文件
def list_all_files(rootdir):
    _files = []
    list = os.listdir(rootdir)
    for i in range(0, len(list)):
        path = os.path.join(rootdir, list[i])
        if os.path.isdir(path):
            _files.extend(list_all_files(path))
        if os.path.isfile(path):
            _files.append(path)
    return _files


# 获取指定的文件
def get_file(files, file_name_list):
    file_list = []

    for file_name in files:
        flag = 1
        # 判断此文件名称是否包含此关键名称
        for i in file_name_list:
            if i not in file_name:
                flag = 0
        if flag == 1:
            file_list.append(file_name)
    return file_list


# for i in list:
#     print(i)

#  合并第一列单元格
def merge_cell(result_file, sheet_name):
    wb = load_workbook(result_file)
    ws = wb[sheet_name]

    # 获取第一列数据
    type_list = []
    i = 2
    while True:
        r = ws.cell(i, 1).value
        if r:
            type_list.append(r)
        else:
            break
        i += 1

    # 判断合并单元格的始末位置
    s = 0
    e = 0
    flag = type_list[0]
    for i in range(len(type_list)):
        if type_list[i] != flag:
            flag = type_list[i]
            e = i - 1
            if e >= s:
                ws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))
                s = e + 1
        if i == len(type_list) - 1:
            e = i
            ws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))
    wb.save(result_file)


def merge_files(file_list, file_to):
    frames = []
    for i in file_list:
        index_list = []
        df = pd.read_excel(i,dtype=str)
        for num in range(0, df.shape[0]):
            # 可根据实际需求获取第一列的字段名称
            index_list.append(i.split('/')[-1].split('.')[0])

        # 在第一列插入数据来源文件名
        df.insert(0, '文件', index_list)
        filename_excel.append(i)
        frames.append(df)

    # 合并所有数据
    result = pd.concat(frames, axis=0)
    result.to_excel(file_to, sheet_name='merge')

    # 去掉第一列无用数据
    wb = load_workbook(file_to)
    ws = wb.active
    ws.delete_cols(1)
    wb.save(file_to)

    # 按照第一列合并相同单元格
    merge_cell(file_to, 'merge')

    
    # 修改此时第一行第二列cell的值('Unnamed: 0' -> '')
    rb = xlrd.open_workbook(file_to)
    wb = copy(rb)
    ws = wb.get_sheet('merge')
    ws.write(0, 1, '')
    wb.save(file_to)


list = get_file(list_all_files(rootdir), file_name)
merge_files(list, file_to)
os.rename(file_to, file_to[:-1])