欢迎订阅公众号 数据分析那些事儿

使用openpyxl合并Excel

功能：

• 读取某一个目录中的所有Excel文件
• 遍历每一个Excel文件，读出要求的内容
• 将读出的内容合并并保存
• 将合并后的数据写入目的Excel

vscode of windows

import os
from openpyxl import Workbook
from openpyxl import load_workbook

'''常量'''
ROW_START = 2
ROW_END = 3
COL_START = 4
COL_END = 15
DEST_FILE_NAME = '汇总.xlsm'

'''获取所有待合并文件名称'''
filePath = r'C:\all'
nameList = os.listdir(filePath)
ss=[]
for i in range(len(nameList)):
    ss.append('all' + '\\' + nameList[i])

'''把每张表的数据放入数组mergeDat'''
mergeDat = [['']*(COL_END-COL_START+1) for _ in range(ROW_END-ROW_START+1)]
for i in range(len(nameList)):
    wbb = load_workbook(filename = ss[i])
    wss = wbb.get_sheet_by_name('明细')
    for row in range(ROW_START,ROW_END+1):
        for col in range(COL_START,COL_END+1):
           mergeDat[row-ROW_START][col-COL_START]+=wss.cell(row=row,column=col).value

'''读取目的表格'''
wb = load_workbook(DEST_FILE_NAME)
ws = wb["明细"]

'''写入目的表格'''
for row in range(ROW_START,ROW_END+1):
    for col in range(COL_START,COL_END+1):
        _ = ws.cell(column=col, row=row, value=mergeDat[row-ROW_START][col-COL_START])
        
wb.save(filename = DEST_FILE_NAME)