python(excel合并)

多个文件合并到同个文件同个sheet（追加）

# -*- coding: utf-8 -*-
from openpyxl import load_workbook, Workbook
import glob

path = "C:\\xiaoming"
new_workbook = Workbook()
new_sheet = new_workbook.active

# 用flag变量明确新表是否已经添加了表头，只要添加过一次就无须重复再添加
flag = 0

for file in glob.glob(path + '/*.xlsx'):
    workbook = load_workbook(file)
    sheet = workbook.active

    coloum_A = sheet['A']
    row_lst = []
    for cell in coloum_A:
        if cell:
            print(cell.row)
            row_lst.append(cell.row)

    if not flag:
        header = sheet[1]
        header_lst = []
        for cell in header:
            header_lst.append(cell.value)
        new_sheet.append(header_lst)
        flag = 1

    for row in row_lst:
        data_lst = []
        for cell in sheet[row]:
            data_lst.append(cell.value)
        new_sheet.append(data_lst)

new_workbook.save(path + '/' + 'result.xlsx')

多个文件合并到同个文件不同sheet

# -*- coding: utf-8 -*-
import os
import pandas as pd

dir = "C:\\xiaoming"
# 获取目录下所有的表
origin_file_list = os.listdir(dir)
print(origin_file_list)

with pd.ExcelWriter('C:\\xiaoming\\result.xlsx') as writer:
    # 循环遍历表格
    for i in origin_file_list:
        # 拼接每个文件的路径
        file_path = dir + '/' + i
        # 把表名赋予给对应的sheet
        sheet_name = i[:-4]
        df = pd.read_excel(file_path)

        # 变相解决表格中第一行第一列为空的缺陷
        string = "".join(list(str(i) for i in df.index))
        # 判断如果索引都为数字，则不保留索引（根据自己代码调整）
        if string.isdigit():
            df.to_excel(writer, sheet_name, index=False)
        else:
            df.to_excel(writer, sheet_name)