利用python对excel工作簿合并与拆分

一、对含有多个excel工作簿的多个文件夹进行合并#

有A、B、C三个文件夹，每个文件夹都有多个 excel 工作簿（每个工作簿有且仅有一个工作表），当然A、B、C三个文件夹中的文件数量可以不一样，文件名称有交集即可，目的是将三个文件夹中的 excel 工作簿汇总到一个文件夹，并且将三个文件夹中名称相同的工作簿中的数据合并，有点类似于数学中的并集运算，求A、B、C三个集合的并集。

思路

• 获取数据文件夹名称，存储为列表 file_list;
• 遍历 file_list ，获取所有 excel 工作簿名称，存储为列表 excel_file_list;
• 遍历 excel_file_list，将重复在多个数据文件夹中的工作簿合并.

缺点：只适用于含有唯一工作表的 excel 工作簿！

代码

import pandas as pd
import os
def main():
    file_path = './data'
    file_list = os.listdir(file_path)
    excel_file_list = []
    for file in file_list:
        excel_file_list += os.listdir(file_path + '/' + file)
    excel_file_list = list(set(excel_file_list))

    for excel in excel_file_list:
        df_list = []
        for file_name in file_list:
            if excel in os.listdir(file_path + '/' + file_name):
                df_list.append(pd.read_excel(file_path + '/' + file_name + '/' + excel))
        res = pd.DataFrame()
        for df in df_list:
            res = pd.concat([res, df])
        res = res.drop_duplicates()
        res.to_excel('./result/' + excel, index=False)
if __name__ == "__main__":
    main()

文件目录

│  ExcelSortUtils.py
│  
├─data
│  ├─A组
│  │      MJ_20210131-am.xlsx
│  │      SM-省-0000-市-明细-0000.xlsx
│  │      ZDQY_20210118.xlsx
│  │      名单_20210127.xlsx
│  │      
│  ├─B组
│  │      20210125-620000-明细.xlsx
│  │      MJ_20210131-am.xlsx
│  │      SM-省-0000-市-明细-0000.xlsx
│  │      名单_20210127.xlsx
│  │      
│  └─C组
│          20210125-620000-明细.xlsx
│          MJ_20210131-am.xlsx
│          SM-省-0000-市-明细-0000.xlsx
│          ZDQY_20210118.xlsx
│          名单_20210127.xlsx
│          
└─result
        20210125-620000-明细.xlsx
        MJ_20210131-am.xlsx
        SM-省-0000-市-明细-0000.xlsx
        ZDQY_20210118.xlsx
        名单_20210127.xlsx

二、对含有多个sheet的 excel 工作簿进行合并#

有多个excel工作簿，每个工作簿都有多个工作表，工作薄中的工作表名称有没有交集都可以，目的是将所有工作簿中的数据汇总到一个工作簿，名称相同的工作表中的数据进行汇总。

思路

• 获取所有 excel 工作簿中的工作表，以字典形式保存到列表; df_list（df_list=[dict1, dict2, dict3, dict4, dict5])，即每个工作簿对应一个字典，工作簿中的表就是字典的元素，同时将所有工作表的名称保存为列表 sheet_list;
• 遍历 sheet_list，将在列表 df_list 中重复出现的工作表合并.

代码

import pandas as pd
from openpyxl import load_workbook
import os

def main():
    fileList=os.listdir("./data")
    df_list=[] 
    sheet_list=[]
    for workbook in fileList:
        wb = load_workbook(filename=file_path+"\\"+workbook)
        sheetnames = wb.sheetnames
        sheet_list += sheetnames
        df_dict=dict()
        for sheet in sheetnames:
            df = pd.read_excel(file_path+"\\"+workbook, sheet_name=sheet, sep='\t',  encoding='GBK')
            df_dict[sheet] = df
        df_list.append(df_dict)
        
    sheet_list = list(set(sheet_list)) # 所有工作表名称
    data_list = []
    
    for sheet in sheet_list:
        df = pd.DataFrame()
        for sheet_dic in df_list:
            try:
                df = df.append(sheet_dic[sheet])
            except:
                continue
        df = df.drop_duplicates()
        '''
            此处根据情况添加数据处理过程······
        '''
        data_list.append(df)

    # 将数据汇总到excel
    res_path = os.path.abspath('')+"\\result\汇总.xlsx"
    with pd.ExcelWriter(res_path) as writer:
        i = 0
        for df in data_list:
            df.to_excel(writer, sheet_name = sheet_list[i], index=False)
            i += 1
if __name__ == "__main__":
    main()

文件目录

│  excelconcat.py
│  
├─data
│      2月工单-1.xlsx
│      2月工单-2.xlsx
│      2月工单-3.xlsx
│      2月工单-4.xlsx
│      2月工单-5.xlsx
│      
└─result
        汇总.xlsx

三、对excel工作表中的数据进行拆分#

现有一张汇总好的工作表，需要将这张表按单位分成不同的excel工作薄下发到各单位，并且保证每张工作表的格式和公式与原来的表一致。

代码

import pandas as pd
from openpyxl import load_workbook
import os


def paste(sheet, begin, end, df):
    for i, tup in enumerate(sheet[begin:end]):
        for j, obj in enumerate(tup):
            try:
                obj.value = df.iloc[i, j]
            except:
                obj.value = ''


def main():
    file_name = os.path.abspath('.') + '\\data\\' + os.listdir('./data')[0]
    df_dic = pd.read_excel(file_name, sheet_name=["Sheet1"],
                           skiprows=1, sep='\t', header=None, encoding='GBK')
    # 各单位代码
    company_name = ['53731', '48624', '52964', '45234', '46763', '50911', '52983', 
                     '54236', '47326', '47852', '47327', '53442', '54208', '50669']
    company_dic = dict()
    for name in company_name:
        data_list = list()
        for i, key in enumerate(df_dic):
            value = pd.DataFrame(df_dic[key].values).iloc[:, 0:37]
            value[0] = value[0].apply(lambda x: str(x).rstrip())
            value = value[value[0] == name]
            data_list.append(value)
        company_dic[name] = data_list

    wb = load_workbook(filename=file_name)
    '''
    删除不需要的sheet
    sheetnames = wb.sheetnames
 	sheetlist = []
    for sheet in sheetnames:
        sheetlist.append(wb[sheet])
       
    for sheet in sheetlist:
        if sheet == wb['Sheet1']:
            continue
        else:
            wb.remove(wb[sheet])

    '''
    
    for name in company_name:
        paste(wb["Sheet1"], 'A2', 'AK1000', company_dic[name][0])
        wb.save(os.path.abspath('.') + '\\result\\' + '/%s.xlsx' % (name))

if __name__=='__main__':
    main()
    

文件目录

│  excelsplit.py
│  
├─data
│      测试数据.xlsx
│      
└─result
        45234.xlsx
        46763.xlsx
        47326.xlsx
        47327.xlsx
        47852.xlsx
        48624.xlsx
        50669.xlsx
        50911.xlsx
        52964.xlsx
        52983.xlsx
        53442.xlsx
        53731.xlsx
        54208.xlsx
        54236.xlsx

结果