Openpyxl批量优化：从源Excel分城市写入子文件夹最新工作簿

现有一段基于Openpyxl的Python代码，功能是根据源.xlsx文件（含2个工作表，数据在"Sheet 1"，共5万+行、A-Q列）的A列城市值，将对应行复制粘贴到子文件夹中最新修改的.xlsm文件的指定工作表"Sheet 5"（从第2行开始粘贴，目标文件R-T列为公式列）。目前代码仅支持单个子文件夹，需重复执行50次，效率极低。

源文件A列包含DENVER、COLUMBUS、PORTLAND等城市，对应子文件夹命名规则：

• DENVER对应路径c/windows/users/me/documents/mainfolder/DEN
• COLUMBUS对应c/windows/users/me/documents/mainfolder/COL
• 其他城市依此类推

子文件夹内文件命名如"DEN 2022 随机字符串 r1.xlsm"，需找到其中最新修改的文件进行写入。

原代码如下：

from copy import copy
import os
import openpyxl
from openpyxl import Workbook, load_workbook

#my source file
wb_sf= load_workbook(r'C:\Users\me\Documents\Consol\Sourcefile.xlsx')
ws = wb_sf["Sheet 1"]

# my destination file
#find most recently modified file
dest_path = r'c\windows\users\me\documents\mainfolder\DEN'   #DENVER subfolder
latest_file = max(glob.glob(f"{dest_path}/*.xlsm"), key=os.path.getmtime)
#load destination file
wb_df= load_workbook(latest_file, keep_vba = True)
ws2 = wb_df["Sheet 5"]

copyfrom_max_columns = ws.max_column

paste_start_min_row = 1
city_list =  ['DENVER']  #search for DENVER
for city_number, city in enumerate(city_list, 1):  
    search_min_row = paste_start_min_row   
    for row in ws.iter_rows(max_col=1, min_row=search_min_row):  
        for cell in row:
            if cell.value == city:  
                paste_start_min_row += 1  
                for i in range(copyfrom_max_columns):  
                        # Set the copy and paste Cells
                        copy_cell = cell.offset(column=i)
                        paste_cell = ws2.cell(row=paste_start_min_row, column=i + 1)
                        paste_cell.value = copy_cell.value
                        paste_cell.number_format = copy_cell.number_format

wb_pst.save(latest_file)

import os
import glob
import openpyxl
from openpyxl import load_workbook

# 定义城市与子文件夹的映射关系，补充剩余城市即可
CITY_FOLDER_MAP = {
    "DENVER": r"c/windows/users/me/documents/mainfolder/DEN",
    "COLUMBUS": r"c/windows/users/me/documents/mainfolder/COL",
    "PORTLAND": r"c/windows/users/me/documents/mainfolder/POR",
    # 此处添加其他47个城市的映射
}

# 只读模式加载源文件，降低内存占用，提升大文件读取效率
wb_sf = load_workbook(r'C:\Users\me\Documents\Consol\Sourcefile.xlsx', read_only=True)
ws_source = wb_sf["Sheet 1"]

# 预分类存储各城市对应的行数据，避免多次遍历源文件
city_rows = {city: [] for city in CITY_FOLDER_MAP.keys()}

# 遍历源文件一次，按城市分类收集行数据
for row in ws_source.iter_rows(min_row=2, max_col=17, values_only=False):
    city_name = row[0].value
    if city_name in city_rows:
        city_rows[city_name].append(row)

# 关闭源文件释放资源
wb_sf.close()

# 批量处理所有城市的目标文件
for city, folder_path in CITY_FOLDER_MAP.items():
    # 获取文件夹内所有xlsm文件
    xlsm_files = glob.glob(f"{folder_path}/*.xlsm")
    if not xlsm_files:
        print(f"警告：{folder_path} 未找到xlsm文件，跳过")
        continue
    
    # 定位最新修改的文件
    latest_file = max(xlsm_files, key=os.path.getmtime)
    
    # 加载目标文件，保留VBA宏
    wb_dest = load_workbook(latest_file, keep_vba=True)
    ws_dest = wb_dest["Sheet 5"]
    
    # 计算起始粘贴行：从现有数据下一行开始，至少从第2行（跳过表头）
    paste_start_row = ws_dest.max_row + 1 if ws_dest.max_row >= 1 else 2
    
    # 写入当前城市的所有行数据
    for row_data in city_rows[city]:
        for col_idx, cell in enumerate(row_data, start=1):
            dest_cell = ws_dest.cell(row=paste_start_row, column=col_idx)
            dest_cell.value = cell.value
            dest_cell.number_format = cell.number_format
        
        paste_start_row += 1
    
    # 保存并关闭目标文件
    wb_dest.save(latest_file)
    wb_dest.close()
    print(f"完成 {city} 数据写入：{latest_file}")

• 批量映射管理：通过CITY_FOLDER_MAP统一维护城市与子文件夹的对应关系，一次性处理所有目标，无需重复执行代码
• 单次遍历源文件：仅读取源文件一次，将各城市数据分类存储，避免50次重复读取大文件，大幅提升效率
• 只读模式加载：针对5万+行的大文件，使用read_only=True减少内存占用，加快读取速度
• 自动定位起始行：通过ws_dest.max_row +1自动计算粘贴起始位置，无需手动维护行号
• 异常防护：增加空文件夹判断，避免因无文件导致的程序中断
• 资源释放：及时关闭工作簿，防止资源泄漏

内容的提问来源于stack exchange，提问作者eladel