Python批量将带格式模板工作表添加至客户Excel文件

我是Python新手，已经完成以下操作：

• 通过API获取数据并保存到Excel文件
• 按CustomerID字段拆分生成独立的客户Excel文件

现在需要批量将带有格式（颜色、图表等）的Excel模板工作表添加到每个客户文件，让每个客户文件既有自身的数据工作表，又有模板的表单工作表。

之前用xlwings写循环代码尝试实现，但添加的模板工作表丢失了原有格式，求能保留模板格式的解决方案。

已完成的拆分代码 #

# 读取Excel数据
temp = pd.read_excel('Customer_data.xlsx')

# 获取CustomerID的唯一值
split_values = temp['CustomerID'].unique()

# 遍历每个唯一值生成对应文件
for value in split_values:
    df1 = temp[temp['CustomerID'] == value]
    output_file_name = "Customer_" + str(value) + "_file.xlsx"
    df1.to_excel(output_file_name, index=False)

当前尝试的批量添加代码（存在格式丢失问题） #

import glob
import xlwings as xw
import pandas as pd

# 获取所有客户Excel文件
excel_names = glob.glob('*.xlsx')

# 遍历每个客户文件
for excel in excel_names:
    df = pd.read_excel('Template.xlsx')
    # 要添加的模板工作表映射
    sheet_df_mapping = {'Form1': df,
                        'Form2': df}
            
    with xw.App(visible=False) as app:
        # 打开客户Excel文件
        wb = app.books.open(excel)

        # 获取当前工作表名称列表
        current_sheets = [sheet.name for sheet in wb.sheets]
        
        # 遍历工作表映射：存在则覆盖内容，不存在则新增工作表
        for sheet_name in sheet_df_mapping.keys():
            if sheet_name in current_sheets:
                wb.sheets(sheet_name).range('A1').options(index=False).value = sheet_df_mapping.get(sheet_name)
            else:
                new_sheet = wb.sheets.add(after=wb.sheets.count)
                new_sheet.range('A1').options(index=False).value = sheet_df_mapping.get(sheet_name)
                new_sheet.name = sheet_name
        wb.save()

解决方案：保留模板格式的批量添加代码 #

你之前的问题在于用pd.read_excel读取模板后写入，这只会保留单元格数据，完全丢失格式、图表、条件格式等元素。正确的做法是直接复制模板文件中的工作表到客户文件中，完整保留所有格式信息。

修改后的代码如下：

import glob
import xlwings as xw

# 获取所有客户Excel文件（排除模板文件，避免处理Template.xlsx）
excel_names = [f for f in glob.glob('*.xlsx') if not f.startswith('Template')]

# 后台打开Excel应用，批量处理文件
with xw.App(visible=False) as app:
    # 先打开模板工作簿，保持后台状态
    template_wb = app.books.open('Template.xlsx')
    
    # 遍历每个客户文件
    for excel_file in excel_names:
        customer_wb = app.books.open(excel_file)
        current_sheet_names = [sheet.name for sheet in customer_wb.sheets]
        
        # 遍历模板中的每个工作表，复制到客户文件
        for template_sheet in template_wb.sheets:
            sheet_name = template_sheet.name
            # 如果客户文件中已存在同名表，先删除再复制
            if sheet_name in current_sheet_names:
                customer_wb.sheets[sheet_name].delete()
            # 复制模板工作表到客户文件的最后位置
            template_sheet.copy(after=customer_wb.sheets[-1])
        
        # 保存并关闭当前客户文件
        customer_wb.save()
        customer_wb.close()
    
    # 关闭模板文件
    template_wb.close()

关键说明 #

• 直接复制模板工作表：通过template_sheet.copy()方法完整复制工作表的所有内容和格式，包括颜色、图表、单元格样式、公式等
• 排除模板文件：避免把模板文件当成客户文件处理
• 处理同名工作表：如果客户文件已有同名工作表，先删除再复制，确保使用最新模板格式
• 后台批量操作：保持Excel应用在后台运行，减少重复启动关闭的开销，提升处理效率

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