如何用Python批量复制带复杂格式的Excel模板并保留格式与公式？

针对你遇到的问题，核心矛盾是Python第三方库对Excel复杂格式、条件格式及公式引用的支持不如原生Excel完整，以下是几个可行的解决思路：

1. 用win32com调用Excel原生API（最可靠） #

直接借助Excel自身的引擎处理，能100%保留所有格式、条件格式和公式逻辑，完全避免错乱问题，适合Windows环境：

import win32com.client as win32
import os

# 初始化Excel应用
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False

# 打开原始文件
wb = excel.Workbooks.Open(os.path.abspath('原始文件.xlsx'))
data_sheet = wb.Sheets('数据')

# 批量处理每个目标位置
target_locations = ["位置1", "位置2", "位置3"]  # 替换为你的1000个位置列表
for location in target_locations:
    # 恢复原始数据（每次处理前重新加载，避免数据污染）
    wb.Close(SaveChanges=False)
    wb = excel.Workbooks.Open(os.path.abspath('原始文件.xlsx'))
    data_sheet = wb.Sheets('数据')
    
    # 筛选目标位置数据，删除无关行（从下往上删避免行号错乱）
    data_sheet.Range("A1").AutoFilter(Field=1, Criteria1=location)
    for row in range(data_sheet.UsedRange.Rows.Count, 1, -1):
        if data_sheet.Rows(row).Hidden:
            data_sheet.Rows(row).Delete()
    
    # 取消筛选，保存新文件
    data_sheet.AutoFilterMode = False
    wb.SaveAs(os.path.abspath(f'_{location}.xlsx'))

# 清理资源
wb.Close()
excel.Quit()

2. 优化openpyxl的使用方式（纯Python方案） #

如果必须用Python，需确保加载参数正确，同时处理数据时保留单元格格式和公式引用：

import openpyxl

# 加载文件时必须保留公式和格式
wb = openpyxl.load_workbook('原始文件.xlsx', data_only=False, keep_links=True)
template_sheet = wb['模板']
data_sheet = wb['数据']

target_location = "位置1"
# 提取目标位置的所有数据行（含单元格格式）
rows_to_keep = []
for row in data_sheet.iter_rows(min_row=2, values_only=False):
    if row[0].value == target_location:
        rows_to_keep.append(row)

# 清空数据工作表（保留表头）
data_sheet.delete_rows(2, data_sheet.max_row - 1)

# 写入筛选后的数据并复制格式
for idx, row in enumerate(rows_to_keep, start=2):
    for col_idx, cell in enumerate(row, start=1):
        new_cell = data_sheet.cell(row=idx, column=col_idx, value=cell.value)
        new_cell._style = cell._style  # 复制单元格格式

# 保存文件
wb.save(f'_{target_location}.xlsx')

注意：若模板中公式使用固定行范围（如数据!A2:A10000），需改为整列引用（如数据!A:A），确保数据删减后公式仍能正确遍历有效数据。

3. 用Excel VBA批量生成（无Python依赖） #

直接在Excel中编写宏，完全依托原生功能处理，适合熟悉VBA的场景：

Sub GenerateLocationWorkbooks()
    Dim wsData As Worksheet, wsTemplate As Worksheet
    Dim wbNew As Workbook
    Dim lastRow As Long, i As Long
    Dim currentLoc As String, prevLoc As String
    
    Set wsData = ThisWorkbook.Sheets("数据")
    Set wsTemplate = ThisWorkbook.Sheets("模板")
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' 按位置排序数据，确保分组连续
    wsData.Range("A1:Z" & lastRow).Sort Key1:=wsData.Range("A1"), Order1:=xlAscending, Header:=xlYes
    
    prevLoc = ""
    For i = 2 To lastRow
        currentLoc = wsData.Cells(i, "A").Value
        If currentLoc <> prevLoc Then
            ' 创建新工作簿并复制模板
            Set wbNew = Workbooks.Add
            wsTemplate.Copy Before:=wbNew.Sheets(1)
            
            ' 复制对应位置的数据
            wsData.Range("A1:Z" & lastRow).AutoFilter Field:=1, Criteria1:=currentLoc
            wsData.Range("A1:Z" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                wbNew.Sheets.Add(After:=wbNew.Sheets(1)).Range("A1")
            wbNew.Sheets(2).Name = "数据"
            
            ' 删除默认空白表并保存
            Application.DisplayAlerts = False
            wbNew.Sheets("Sheet1").Delete
            Application.DisplayAlerts = True
            wbNew.SaveAs ThisWorkbook.Path & "\_" & currentLoc & ".xlsx"
            wbNew.Close
            
            prevLoc = currentLoc
        End If
    Next i
    
    ' 取消筛选
    wsData.AutoFilterMode = False
End Sub

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