Python合并或拆分Excel文件——基于openpyxl实现

应用场景：某个文件夹中有多个Excel文件，现在需要将其合成一个Excel文件，一个工作簿中包含多个表单页

openpyxl是经典的处理Excel文件的工具，面临上述需求时，首先想到的就是openpyxl库进行处理，但是，好像openpyxl只支持单个单元格级别的处理，不支持正页表单级别的拷贝，而且不支持较老的.xls文件格式，所以对于.xls类型的文件，需要利用pandas进行处理，当数据量较大时合并速度较慢，比单纯用pandas的方法慢一倍CSDN，在我的笔记本（CPU双核2.70GHz）上，对12个25列1000行的Excel文本文件进行合并时，本篇方法耗时约为12s，而pandas实现的方法CSDN耗时约为6s，话不多说，直接上代码：

#coding: utf-8
import os
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

#将Excel中的sheet中的内容逐单元格拷贝到另一个sheet
#@wso:output sheet, @wst:target sheet
def copy_sheet(wso,wst):
    for i in range(1, wst.max_row+1):
        for j in range(1, wst.max_column+1):
            v=wst.cell(i,j).value#逐单元格赋值
            if v:
                wso.cell(i,j,v)

#将Excel中的sheet中的内容添加到另一个Excel的workbook
#@wb:output workbook, @wst:target sheet
def add_sheet(wb,wst):
    wso=wb.create_sheet(wst.title)#新建sheet页
    copy_sheet(wso,wst)
    
#将Excel中各个sheet中的内容添加到另一个Excel的workbook
#wbo:output workbook, wbt:target workbook
def merge_workbook(wbo,wbt):
    for sh in wbt.sheetnames:#逐sheet页添加
        add_sheet(wbo,wbt[sh])
        
#清除工作表中的空白sheet页
def clear_workbook(wb):
    for sh in wb.sheetnames:#逐sheet页比对：只有1格单元格且单元格内容为空
        if wb[sh].dimensions==r'A1:A1' and wb[sh]['A1'].value==None:
            del wb[sh]
        
#使用openpyxl库和pandas库将指定路径文件夹中的所有Excel(*.xlsx和*.xls)文件合并为一个文件
#由于在openpyxl库中是逐单元格进行的数据赋值，所以速度较慢，数据量较大时尤为明显
def merge_ExcelFiles(dirPath, outputFileName='合并结果.xlsx'):
    dirPath+='\\'
    wbo=Workbook()
    clear_workbook(wbo)#新建立的Excel文件一般都包含若干空的sheet，首先清除默认生成的空sheet
    os.chdir(dirPath)
    file_list=os.listdir(dirPath)
    for file in file_list:
        #如果是*.xlsx文件，则比较简单，直接利用openpyxl来处理
        if file.endswith('.xlsx'):
            wbt=load_workbook(dirPath+file)
            merge_workbook(wbo, wbt)
            print(file)
        #如果是*.xls文件，由于openpyxl不支持，则需要用pandas来对*.xls文件进行读取
        if file.endswith('.xls'):#参数sheet_name默认为0，此时函数返回的直接就是DataFrame类型，但是那样的话只能读取第一个sheet，设置为None可以读取所有的sheet，返回数据则是字典类型
            df=pd.read_excel(dirPath+file, sheet_name=None, dtype='object')#参数dtype默认是None，但为了保护数据（例如身份证号被科学计数法丢失后面几位数字），需要使用object类型
            for key, value in df.items():#但是依然使用openpyxl来保存，因为openpyxl对Excel的支持较好，例如如果sheet的名字相同，则会自动进行“sheet+1”避免重名
                ws=wbo.create_sheet(key)
                for r in dataframe_to_rows(pd.DataFrame(value), index=False, header=True):
                    ws.append(r)
            print(file)
    clear_workbook(wbo)
    print('OK!')
    wbo.save(dirPath+outputFileName)

    
if __name__=="__main__":
    dirPath=r'D:\课程\学生基础信息'
    import time
    time0=time.time()
    merge_ExcelFiles(dirPath,'合并结果.xlsx')
    time1=time.time()
    print("Spending Time：{:.3f}s".format(time1-time0))