Python跨文件合并多个Sheet页|Excel宏跨Excel合并Sheet页Excel整合多个Excel文件当中的Sheet页

69 阅读 0 评论 46 点赞

我是靠谱客的博主轻松板凳，最近开发中收集的这篇文章主要介绍Python跨文件合并多个Sheet页|Excel宏跨Excel合并Sheet页Excel整合多个Excel文件当中的Sheet页，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Excel整合多个Excel文件当中的Sheet页

踩坑描述：

需求说明：使用方法将多个Excel文件当中的Sheet合并到一个Excel当中。

坑：

1.使用Python中Openpyxl的库，用于复制Sheet页的方法，但是只能在同文件下复制，无法跨文件。

2.使用Pandas导入后再导出，只能汇总一个文件的Sheet页，批量复制会出现覆盖的问题。虽然能够解决这个问题，但是无法保留原始格式。

import pandas as pd
import openpyxl
import os


def mergeExcel(dataExcel):
    if not os.path.exists('./Res.xlsx'):
        temp = openpyxl.Workbook()
        temp.save('./Res.xlsx')
    workBook = openpyxl.load_workbook(dataExcel)
    sheetList = workBook.sheetnames
    workBook.close()
    resExcel = os.path.abspath('./Res.xlsx')
    f = open(dataExcel, 'rb')
    resultList = [pd.read_excel(f, sheet_name=sheetName) for sheetName in sheetList]
    f.close()
    book = openpyxl.load_workbook(resExcel)
    writer = pd.ExcelWriter(resExcel, engine='openpyxl')
    writer.book = book
    for i in range(len(resultList)):
        resultList[i].to_excel(writer, sheet_name=sheetList[i], index=False, header=False)
    writer.save()
    writer.close()
    book.close()


def totalExcel(path):
    excelList = [os.path.abspath(path + '/' + file) for file in os.listdir(path)]
    for excel in excelList:
        print(excel)
        mergeExcel(excel)
    workBook = openpyxl.load_workbook('./Res.xslx')
    workBook.remove_sheet('Sheet')
    workBook.close()


totalExcel('./File')

使用：

将需要合并的Excel全部放在同一个文件夹下，在其他地方创建一个存放结果的Excel。

3.通过Excel的原生VBA宏能够就解决问题，但是在其他地方调用执行宏的话会有问题，python和UiBot当中执行宏的方法是win32，使用宏比较没法。

解决方案

在Excel当中编写执行宏，保存为 xlsm 文件，当中含有可执行的宏。

调用使用Python的xlwings库。

可执行宏：

Sub CopySheet(dataExcelPatn As String, resExcelPath As String)
'
' 复制Sheet 宏
'

'
Dim WB As Workbook
Dim WB2 As Workbook
    Application.ScreenUpdating = True '冻结屏幕，
    Set WB2 = Workbooks.Open(resExcelPath)
    File = Dir(dataExcelPatn & "*.xlsx")   'afterPath 是文件夹路径，(afterPath & "*.xls")表示afterPath 路径下扩展名为.xls的文件
    Do While File <> ""
        Set WB = Workbooks.Open(dataExcelPatn & "" & File) '打开一个excel文件，WB表示打开的这个工作簿
        Debug.Print File
        Call setPro(WB, WB2)    '调用setPro方法
        WB.Close SaveChanges:=False
        File = Dir '找寻下一个excel文件
    Loop
    WB2.Close SaveChanges:=True
    Application.ScreenUpdating = True '解冻屏幕，让屏幕恢复正常刷新。和上面的那一句成对使用
End Sub


Sub setPro(WB As Workbook, WB2 As Workbook)
    '循环工作簿WB中的sheet（工作表）
    WB.Activate
    For i = 1 To WB.Worksheets.Count
        WB.Worksheets(i).Copy Before:=WB2.Sheets(1)
    Next i
End Sub

Python调用代码：

import xlwings as xw


def copySheet(vbaPath, filePath, resPath):
    vbaBook = xw.Book(vbaPath)
    mainFunction = vbaBook.macro("模块1.CopySheet")
    mainFunction(filePath, resPath)
    vbaBook.close()


vbaPath = r'D:桌面test.xlsm'
filePath = r'E:ProjetcupdateReportFile'
resPath = r'E:ProjetcupdateReporttest.xlsx'
copySheet(vbaPath, filePath, resPath)