python实现Excel邮件合并_execl 实现邮件合并功能-CSDN博客

本文链接：https://blog.csdn.net/hhhhh_51/article/details/122313736

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