Python操作excel表格合并

Question

多个excel表格，每个表格包含多个sheet表，把相同sheet表内的内容合并到同一个sheet表内比如excel表1有a.b.c三个sheet表，表2有a.b.d.三个sheet表格，然后合成出来的excel表格为a.b.c.d四个sheet表格，其中新表的a包含表1的a和表2的a

Answer 1

import os
import xlrd
import openpyxl


def get_excle():
    dir_list = os.listdir()
    if 'excel' not in dir_list:
        os.mkdir('excel')
    os.chdir('excel')
    Excels = os.listdir()
    return Excels


# 获取单张表的数据
def get_data(filename):
    d_dict = {}
    file = xlrd.open_workbook(filename)
    sheet_names = file.sheet_names()
    for i in range(len(sheet_names)):
        sheet_list = []
        sheet = file.sheet_by_name(sheet_names[i])
        d_dict[sheet_names[i]] = d_dict.get(sheet_names[i], [])
        nrows = sheet.nrows
        ncols = sheet.ncols
        for row in range(nrows):
            new_list = []
            for col in range(ncols):
                new_list.append(sheet.cell(row, col).value)
            sheet_list.append(new_list)
        d_dict[sheet_names[i]] += sheet_list
    return d_dict


# 获取文件夹下所有Excel的数据
def sum_data():
    sum_dict = {}
    os.chdir('excel')
    excels = get_excle()
    for i in range(len(excels)):
        if '.xls' not in excels[i] or '.xlsx' not in excels[i]:
            continue
        data_dict = get_data(excels[i])
        sum_dict = create_dict_sheet(sum_dict, data_dict)
    os.chdir('..')
    return sum_dict


# 整合数据
def create_dict_sheet(dict0, dict1):
    new_dict = {}
    name0 = list(dict0.keys())
    name1 = list(dict1.keys())
    names = []
    for i in range(len(name0)):
        if name0[i] not in names:
            names.append(name0[i])
    for i in range(len(name1)):
        if name1[i] not in names:
            names.append(name1[i])
    for i in range(len(names)):
        new_dict[names[i]] = new_dict.get(names[i], [])
    for i in range(len(dict0.keys())):
        new_dict[list(dict0.keys())[i]] += dict0[list(dict0.keys())[i]]
    for i in range(len(dict1.keys())):
        new_dict[list(dict1.keys())[i]] += dict1[list(dict1.keys())[i]]
    return new_dict


# 将数据写入新表格
def create_excel(data):
    dir_list = os.listdir()
    if "result" not in dir_list:
        os.mkdir('result')
    os.chdir('result')
    sheets = list(data.keys())
    file = openpyxl.Workbook()
    if 'Sheet' not in sheets:
        wb = file['Sheet']
        file.remove(wb)
        for i in range(len(sheets)):
            file.create_sheet(f'{sheets[i]}')
        sheet_name = file.sheetnames
        for i in range(len(sheet_name)):
            sheet = file[sheet_name[i]]
            for j in range(len(data[sheet_name[i]])):
                for k in range(len(data[sheet_name[i]][j])):
                    sheet.cell(row=j + 1, column=k + 1, value=data[sheet_name[i]][j][k])
    else:
        for i in range(len(sheets)):
            file.create_sheet(f'{sheets[i]}')
        sheet_name = file.sheetnames
        for i in range(len(sheet_name)):
            sheet = file[sheet_name[i]]
            for j in range(len(data[sheet_name[i]])):
                for k in range(len(data[sheet_name[i]][j])):
                    sheet.cell(row=j + 1, column=k + 1, value=data[sheet_name[i]][j][k])
    file.save('new.xlsx')
    os.chdir('..')


if __name__ == '__main__':
    os.chdir('..')
    data_list = sum_data()
    create_excel(data_list)

有用的话点一下采纳