Python 合并多Excel案例

本文主要是介绍Python 合并多Excel案例，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

其实python有很多库都可以操作excel,比如xlrd,xlwt，但是xlwt只能写不超过65535行数据，有很大局限，因此我采用了openpyxl这个库读写excel。

这个案例分成两部分：

第一部分是创建多个excel文档，每个文档中有AB两个个sheet,每个sheet中有多列，多行。比如：

第二部分是合并多个excel文档，把多个文件的多个sheet根据名称合并到对应的sheet，每个sheet中的列也需要对应起来

第一部分：创建源文件

思路是专业的
1.创建单个文件
2.创建多个sheet
3.创建列标题，A B C D E
4.写入数据，具体的数据是100到999之间的随机数，在E列写入公式：SUM(A1:Ex)

使用到的库：os, random, gc, openpyxl

import os
import random
import gc
from openpyxl import Workbook, load_workbook

def clearFile(file):
    if os.path.exists(file):
        os.remove(file)

# 使用Openpyxl创建一个xlsx, 行数可以指定，2个sheet
def createExcelWithOpenpyxl(file,rows):
    wb = Workbook()
    # 创建2个sheet
    for name in ['A','B']:
        ws = wb.create_sheet(name)
    
    # 定义列标题    
    columns = ['A','B','C','D']
    
    # 删除默认的Sheet
    ws = wb['Sheet']
    wb.remove(ws)    
    
    sheetnames = wb.get_sheet_names()
    for sheet in sheetnames:
        ws = wb[sheet]
        sheetName = '数据 {n}'.format(n=sheet)
        print('开始写入 [{s}]...'.format(s=sheetName))
        
        # 设置列标题
        for i,c in enumerate(columns): 
            p = '{col}1'.format(col=c)
            
            ws[p] = '字段_{n}'.format(n=i+1)            
        ws['E1']='合并'
            
            
        # 写入数据    
        for i in range(1,rows+1):
            for c in columns:
                p = '{x}{y}'.format(x=c, y=i+1 )                
                
                # 单元格写入随机值
                ws[p] = random.randint(100,999)                
                
                # 合计列增加公式
                # 给合计列增加公式
                formula = "=SUM(A{a1}:D{a2})".format(a1=i+1,a2=i+1)
                ws['E{n}'.format(n=i+1)] = formula
             
                
        del ws
        gc.collect()    
    wb.save(file)    


# 创建一个excel文件，里面创建50个sheet    
def createExcelFile():
    path = r'data\\datafile_{n}.xlsx'
    
    # 定义文件数量
    fileCount = 200
    for i in range(fileCount):        
        finallyFileName = path.format(n=i+1)        
        print('\n开始创建{n}'.format(n=finallyFileName))
        # 删除文件
        clearFile(finallyFileName)
        
        # 每个文件创建100行数据
        createExcelWithOpenpyxl(finallyFileName,1000)
    
    print('createExcelWithOpenpyxl() done.')   
    
    
if __name__ == '__main__':
    createExcelFile()
    print('done.')    

使用createExcelWithOpenpyxl()创建具体的文件，用createExcelFile()来调用前面的函数，循环N次就可以创建N个文件。调用createExcelWithOpenpyxl()时指定好路径和每个文件中的数据行数即可。

第二部分：合并多个文件

思路：
1.遍历指定路径下的.xlsx文件
2.初始化好目标文件summery1.xlsx，根据源文件创建好目标文件中的sheet还有列标题
3.遍历源文件列表读取每个文件的每个Sheet数据
4.写入目标文件对应的sheet、对应的列当中

使用到的库：os, random, gc, openpyxl

import os
import time
from openpyxl import Workbook,load_workbook
import gc
from win32com.client import Dispatch


# 定义一个类使用openpyxl合并多个excel文件
class Merge:

    # 源文件路径
    srcFilePath=''
    
    # 目标文件路径
    destFilePath=''
    
    
    sheetRowDict = dict()
    
    def logback(self):
        pass

    def __init__(self, srcFilePath, destFilePath):
        self.srcFilePath = srcFilePath
        self.destFilePath = destFilePath
        
        #初始化汇总文件
        self.initExcel()
        
        
    # 初始化目标excel文件，创建好文件，根据原始src文件列表中第一个文件的内容，在目标文件中创建表头和sheet
    def initExcel(self):
        
        self.logbak('开始初始化汇总文件'+self.destFilePath)
        try:
            # 创建一个工作簿
            book = Workbook()# xlwt.Workbook(encoding="utf-8")
            
            ws =  book['Sheet']
            book.remove(ws)    
            
            self.logbak('开始收集源文件名集合')
            # 返回第一个src文件
            self.srcFileList = self.getExcelNameList()
            
            
            self.logbak('准备读取第一个源文件，创建汇总文件的表头和sheet集合')
            # 一个待合并execl的路径
            excel_path = self.srcFilePath + "\\" + self.srcFileList[0]
            
            self.logbak('准备读取第一个源文件的sheet 名字列表')
            # 获取excel的所有sheet
            sheetNameList = self.getSheets(excel_path)
            
            
            self.logbak('准备遍历第一个源文件的sheet对象，创建汇总文件的所有sheet')
            # 遍历sheet列表，给汇总文件增加好所有的sheet
            for sheetName in sheetNameList:
            
                # 初始化字典，每个sheet的开始行数初始化成2,表示从第2行开始写入数据
                Merge.sheetRowDict[sheetName] = 2;
                ws = book.create_sheet(sheetName)# book.add_sheet(sheetName)
                
                self.logbak('根据{f}的sheet:{s} 创建汇总文件表头'.format(f=self.srcFileList[0],s=sheetName))
                filedNames = self.getFields(excel_path,sheetName)
                
                self.logbak('开始创建表头，字段列表: '+ ', '.join(filedNames))

                # 给当前sheet写入表头
                for i in range(len(filedNames)):
                    
                    # cell(行号,列号) 行号，列号至少是1
                    ws.cell(1,i+1).value = filedNames[i]
                    
            book.save(self.destFilePath)
            self.logbak('初始化汇总文件完成')
        except Exception as e:
            print('初始化文件失败,',e)
        #return False
        
       
    
       
    # 读取指定目录下的所有文件,文件后缀默认是.xlsx
    def getExcelNameList(self,exten='.xlsx'):
        #excelName_list = os.listdir(self.srcFilePath)
        #return excelName_list       
        fileList=[]
        for root, dirs, files in os.walk(self.srcFilePath):
            for file in files:
                (filename, extension) = os.path.splitext(file)
                if extension == exten:
                    fileList.append(file)
        
        print('fileList',fileList)
        return fileList
        
        
    # 返回指定文件的所有sheet 名称
    def getSheets(self,file):
        workbook = load_workbook(file)
        sheets = workbook.get_sheet_names()
        #print('sheets=',sheets)
        return sheets
        
    # 返回指定文件指定sheet中第一行的数据，通常是字段名    
    def getFields(self,file, sheetName):
        workbook = load_workbook(file)
        
        # 得到指定的sheet
        ws = workbook[sheetName]
        
        # 得到第一行数据作为表头返回,ws.max_column 是当前sheet的列总数
        field_data = ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=1)
        fieldName = []
        field_tuple = tuple(field_data)        
        
        # 遍历第一行的数据，组装成list
        for row in field_tuple:
            for col in row:
                fieldName.append(col.value)
        return fieldName
        
        
    # 返回指定文件的指定的sheet
    def getSheetByName(self,file,sheetName):        
        workbook = load_workbook(file)        
        # 得到指定的sheet
        ws = workbook[sheetName]
        
        # 返回迭代器
        data_all = ws.rows
        data_tuple = tuple(data_all)
        return data_tuple
        
        
    # 自定义输出日志的格式    
    def logbak(self,info):
        datetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        print('[{m}] {s}'.format(m=datetime, s=info))
    
    # 解决openpyxl打开excel文件读取公式列是空的问题,把该文件打开后重新保存即可
    def resaveExcel(self,filename):
        xlApp = Dispatch("Excel.Application")
        xlApp.Visible = False
        xlBook = xlApp.Workbooks.Open(filename)
        xlBook.Save()
        xlBook.Close()
    
    # 核心方法合并文件
    def mergeExcel(self):
        # 用来记录总表中的行数
        total_excel_row = 1
        
        # 获取总表的book,sheet

        self.logbak('开始打开汇总文件')
        
        workbook = load_workbook(self.destFilePath)#, formatting_info=True)
        
        self.logbak('开始收集源文件名集合')
        # 得到原始的excel文件列表
        self.srcFileList=self.getExcelNameList()
        self.logbak('开始遍历源文件名集合'+ ','.join(self.srcFileList))

        
        # 遍历文件列表，打开每个文件
        for srcFile in self.srcFileList:     
            srcFielPath = self.srcFilePath+'\\'+srcFile
            print('\n')
            self.logbak('读取[{s}]获得所有的sheet'.format(s=srcFile))
            #self.resaveExcel(srcFielPath)
            srcSheetList = self.getSheets(srcFielPath)
            self.logbak('sheet个数:[{n}]'.format(n=len(srcSheetList)) )
            
            #total_excel_row = 1
            # 遍历源文件的所有sheet
            for index,sheetName in enumerate(srcSheetList):
                
                self.logbak('读取汇总文件 sheet index=[{i}]'.format(i=index))
                
                # 得到目标汇总表的对应的sheet
                destSheet = workbook[sheetName]#wtbook.get_sheet(index)
                
                self.logbak('读取源文件   sheet name=[{s}]'.format(s=sheetName))               

                
                # 打开需要合并的源文件, 仅仅装载数据，忽略公式
                srcWookbook = load_workbook(srcFielPath, data_only=True)
                
                # 激活指定的sheet
                srcSheet = srcWookbook[sheetName]
                
                srcDataTuple = tuple(srcSheet.rows)
                
                # n_rows-1表示减去标题行
                self.logbak('源数据[{rows}]行，写入汇总文件起始行:{startRow}'.format(rows=(srcSheet.max_row-1), startRow=Merge.sheetRowDict[sheetName]) )

                # 开始遍历读取数据，并写入数据，从1开始而非0开始是要跳过标题行
                for row_index in range(1,srcSheet.max_row): 
                
                    # 读取一行的数据，列表形式
                    row_data_list = srcDataTuple[row_index] 
                    
                    
                    # 遍历刚读取的一行数据
                    for index,data in enumerate(row_data_list):
                        #print('--> {x} {y}={v}'.format(x=Merge.sheetRowDict[sheetName], y=index+1, v=data.value) )
                        destSheet.cell(Merge.sheetRowDict[sheetName] ,index+1).value = data.value
                    
                    # 每写一行，相关的sheet的总行数加1
                    Merge.sheetRowDict[sheetName] += 1
                
                del srcWookbook,srcSheet
                gc.collect()
                
                self.logbak('源文件中[{sheet}]处理完成'.format(sheet=sheetName) )                
                workbook.save(self.destFilePath)

            
        self.logbak("数据合并已完成")
       
        
        
def testMerge():
    srcPath = r'data'
    destPath = r'dest\summery1.xlsx'
    m = Merge(srcPath,destPath)
    m.mergeExcel()    
    
if __name__ == '__main__':
    testMerge()
    print('done.')   

这篇关于Python 合并多Excel案例的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！