众所周知word/WPS中的邮件合并功能可以根据word模板从excel表格中读取数据，进而生成不同的文件
但Excel却没有这个功能，如果需要根据excel表格模板生成不同文件的话，手动填写就显得很麻烦
举例：需对1、2图片模板表格的2个sheet中所有'{姓名}'、'{案卷号}'修改为第3个图片表格的数据，按姓名保存为不同文件
模板表格sheet1
模板表格sheet2
数据表格
使用程序读取数据excel表格，对固定模板excel表格指定内容进行替换，并生成文件，就是一个比较好的选择
实际使用中，模板excel表格中单个单元格内，可能有多个需要替换的内容，为便于使用，单独将字符串替换功能写为函数
def replace_all(string, **kwargs):
"""
将字符串string中所有与替换字典kwargs中key相同的字符串，替换为value
:param str string: 待替换的原始字符串
:param dict kwargs: 需替换的旧新字符串键值对，字典
"""
rep_string = string
for key,value in kwargs.items():
rep_string = rep_string.replace(key, value)
return rep_string
python实现方法1
xlrd模块，常用于读取xls和xlsx格式excel表格
xlwt模块，常用于写入xls格式excel表格
xlutils模块，可将xlrd.Book对象转为xlwt.Workbook对象，从而实现对已有excel表格的写入功能
from xlrd import open_workbook # 从0开始计数
import xlwt # 从0开始计数
from xlutils.copy import copy
from time import time
def excel_replace_1():
start_time = time()
# 数据文件读取
data_wb = open_workbook(r'E:\测试\数据文件.xlsx')
data_ws = data_wb.sheet_by_index(0)
name_list = data_ws.col_values(1)[1:]
num_list = data_ws.col_values(2)[1:]
# 模板文件，formatting_info=True参数不改变原样式
template_wb = open_workbook(r'E:\测试\模板文件.xls',formatting_info=True)
num_sheets = template_wb.nsheets # 模板文件sheet数量
for i in range(len(name_list)):
copy_wb = copy(template_wb) # 复制xlrd.workbook对象
rep_dict = {'{姓名}': name_list[i], '{案卷号}': str(int(num_list[i]))}
for k in range(0, num_sheets):
copy_ws = copy_wb.get_sheet(k)
template_ws = template_wb.sheet_by_index(k)
for r in range(1, template_ws.nrows):
for c in range(1, template_ws.ncols):
cell_value = str(template_ws.cell_value(r, c)) # 读取为字符串，否则会报错
if ('{房号}' in cell_value) or ('{案卷号}' in cell_value):
cell_value = replace_all(cell_value, **rep_dict) # 替换内容
copy_ws.write(r, c, cell_value)
# 保存文件
copy_wb.save(r'E:\测试\输出文件\政治保卫局《{}》资料.xls'.format(rep_dict['{姓名}']))
#break # 仅循环一次，测试用
end_time = time()
print('所有文件已生成，累计用时：%.4f秒' % (end_time - start_time))
优点：速度快；缺点：单元格赋值会改变单元格格式
方法1只能生成xls格式excel表格，如果需要生成xlsx格式，参考方式2
python实现方法2
openpyxl模块，常用于读取、写入xlsx格式excel表格
from openpyxl import load_workbook # 从1开始计数
def excel_replace_2():
start_time = time()
# 数据文件读取
data_wb = load_workbook(r'E:\测试\数据文件.xlsx')
data_ws = data_wb['Sheet1']
name_list = data_ws['B'][1:]
num_list = data_ws['C'][1:]
# 遍历，写入模板文件
for i in range(len(name_list)):
write_wb = load_workbook(r'E:\测试\模板文件.xlsx')
sheet_list = write_wb.sheetnames # 模板文件sheet名称列表
rep_dict = {'{姓名}': name_list[i].value, '{案卷号}': str(num_list[i].value)}
for sheet in sheet_list:
write_ws = write_wb[sheet]
for colum in write_ws.columns:
for cell in colum:
cell_value = str(cell.value)
if ('{姓名}' in cell_value) or ('{案卷号}' in cell_value):
cell_value = replace_all(cell_value, **rep_dict)
cell.value = cell_value
# 保存文件
write_wb.save(r'E:\测试\输出文件\政治保卫局《{}》资料.xlsx'.format(rep_dict['{姓名}']))
#break # 仅循环一次，测试用
end_time = time()
print('所有文件已生成，累计用时：%.4f秒' % (end_time - start_time))
优点：单元格赋值不改变格式，缺点：较方式1速度太慢
对2种方式的文件生成速度进行测试：生成1500个文件，方式1用时12秒，方式2用时98秒
python实现方法3
pywin32模块可以直接调用Excel程序，试验使用该模块实现以上功能
from win32com.client import Dispatch
excel = Dispatch('Excel.Application')
excel.Application.Visible = 0 # 后台运行，不显示界面
excel.Application.DisplayAlerts = 0 # 不显示警告信息
def excel_replace_3():
start_time = time()
# 数据文件
data_wb = excel.Workbooks.Open(r'E:\测试\数据文件.xlsx')
data_ws = data_wb.Worksheets(1)
max_row = data_ws.UsedRange.Rows.count # 最大行数
for i in range(2, max_row+1):
write_wb = excel.Workbooks.Open(r'E:\测试\模板文件.xlsx')
sheet_count = write_wb.Sheets.count
rep_dict = {'{姓名}': data_ws.Cells(i, 2).Value, '{案卷号}': str(int(data_ws.Cells(i, 3).Value))}
for s in range(1, sheet_count+1):
write_ws = write_wb.Worksheets(s)
max_r = write_ws.UsedRange.Rows.count
max_c = write_ws.UsedRange.Columns.count
for r in range(1, max_r+1):
for c in range(1, max_c+1):
cell_value = str(write_ws.Cells(r, c).Value)
if ('{姓名}' in cell_value) or ('{案卷号}' in cell_value):
cell_value = replace_all(cell_value, **rep_dict)
write_ws.Cells(r, c).Value = cell_value
save_file = r'E:\测试\输出文件\政治保卫局《{}》资料.xlsx'.format(rep_dict['{姓名}'])
write_wb.SaveCopyAs(save_file) # 保存文件
write_wb.Close() # 关闭wb
#break # 仅循环一次，测试用
data_wb.Close()
excel.Application.Quit()
end_time = time()
print('所有文件已生成，累计用时：%.4f秒' % (end_time - start_time))
测试结果：由于运行太慢，只测试生成150个文件，却用时494秒
既然试验了pywin32调用Excel程序实现邮件合并功能，为什么不直接在Excel中使用VBA呢？
VBA实现方法
Sub excel_replace()
tm = Now()
Application.ScreenUpdating = False '关闭屏幕更新
Application.Visible = False '后台运行，不显示界面
Application.DisplayAlerts = False '不显示警告信息
Dim data_wb, write_wb As Workbook '数据源文件，模板文件
Dim data_ws As Worksheet '文件中的worksheet
Dim arr '数组
Set data_wb = Application.Workbooks.Open("E:\测试\数据文件.xlsx") '打开数据源文件
Set data_ws = data_wb.Worksheets(1)
arr = data_ws.UsedRange '所有数据行读取为数组
For i = 2 To UBound(arr):
Set write_wb = Application.Workbooks.Open("E:\测试\模板文件.xlsx") '打开模板文件
For Each sht In Worksheets
With sht
.Cells.Replace What:="{姓名}", Replacement:=arr(i, 2) '替换内容
.Cells.Replace What:="{案卷号}", Replacement:=arr(i, 3)
End With
Next
save_file = "E:\测试\输出文件\政治保卫局《" & arr(i, 2) & "》资料.xlsx"
write_wb.SaveCopyAs filename:=save_file
write_wb.Close (False)
'Exit For '强制退出for循环，单次测试使用
Next
data_wb.Close (False)
Application.ScreenUpdating = True '打开
Application.Visible = True
Application.DisplayAlerts = True
Debug.Print ("所有文件已生成，累计用时:" & Format(Now() - tm, "hh:mm:ss")) '耗时
End Sub
