Python3使用win32com从Excel读取数据生成Word格式报告批量发邮件的示例详解

pip install pywin32

import win32com.client

import win32com.client

excel = win32com.client.dispatch("excel.application")
excel.visible = true  # 可见 excel 窗口（方便调试／查看效果）

workbook = excel.workbooks.add()
sheet = workbook.worksheets(1)

sheet.cells(1, 1).value = "hello"
sheet.cells(1, 2).value = "world"
sheet.cells(2, 1).value = "python"
sheet.cells(2, 2).value = "win32com"

workbook.saveas(r"c:\path\to\your\file.xlsx")
workbook.close(false)
excel.quit()

import win32com.client

word = win32com.client.dispatch("word.application")
word.visible = true

doc = word.documents.add()
rng = doc.range(0, 0)
rng.text = "这是由 python + win32com 自动生成的 word 文档内容。\n"

doc.saveas(r"c:\path\to\your\file.docx")
doc.close(false)
word.quit()

import win32com.client
import pythoncom
import os
import sys

def main():
    try:
        excel = win32com.client.dispatchex("excel.application")
        excel.visible = false
        excel.displayalerts = false

        wb = excel.workbooks.add()
        sheet = wb.worksheets(1)
        sheet.name = "data"

        # 写一些示例数据
        for i in range(1, 6):
            sheet.cells(i, 1).value = f"row {i}"
            sheet.cells(i, 2).value = i * 10

        # 假设你有一个 vba 宏模块，想插入并运行
        # 例如，导出数据、生成图表、格式化等
        # wb.vbproject.vbcomponents.add(...)  # 插入模块
        # wb.application.run("yourmacroname")

        out_path = os.path.join(os.getcwd(), "output.xlsx")
        wb.saveas(out_path)
        print("saved to", out_path)
    except exception as e:
        print("error:", e)
    finally:
        try:
            wb.close(false)
        except exception:
            pass
        try:
            excel.quit()
        except exception:
            pass

if __name__ == "__main__":
    # 在一些环境下，多线程或脚本打包需要初始化 com
    pythoncom.coinitialize()
    main()
    pythoncom.couninitialize()

pip install pywin32
```:contentreference[oaicite:5]{index=5}  

import os
import win32com.client
import pythoncom  # 如果在脚本 / 多线程 /定时任务中运行，推荐初始化 com

def make_word_report(data_rows, output_docx_path):
    """
    data_rows: list of dict，每个 dict 对应一行，key 是列名
    output_docx_path: word 文档保存路径
    """
    word = win32com.client.dispatch("word.application")
    word.visible = false
    doc = word.documents.add()

    # 写入标题
    doc.content.insertafter("自动生成报告\n")
    doc.content.insertafter("====================\n\n")

    # 写入表格／数据（简单示例）
    for row in data_rows:
        # 假设 row 是 dict，按顺序写
        line = ", ".join(f"{k}: {v}" for k, v in row.items())
        doc.content.insertafter(line + "\n")

    doc.saveas(output_docx_path)
    doc.close(false)
    word.quit()


def read_excel_data(excel_path, sheet_name=none):
    """
    简单读取 excel，通过 com 控件，也可以用 pandas/openpyxl，再包装成 dict list
    这里假设你能用其他方式读取 excel，示例用 pandas 更方便
    """
    import pandas as pd
    df = pd.read_excel(excel_path, sheet_name=sheet_name)
    df = df.fillna("")  # 可选：替换空值
    records = df.to_dict(orient="records")
    return records


def send_email_with_attachment(to_email, subject, body, attachment_paths=none):
    outlook = win32com.client.dispatch("outlook.application")
    mail = outlook.createitem(0)  # 0 表示 olmailitem
    mail.to = to_email
    mail.subject = subject
    mail.body = body
    if attachment_paths:
        for path in attachment_paths:
            if os.path.exists(path):
                mail.attachments.add(path)
            else:
                print(f"[warning] 附件文件不存在: {path}")
    mail.send()


def main():
    pythoncom.coinitialize()

    # --- 配置区 ---
    excel_path = r"c:\path\to\your\data.xlsx"
    word_report_path = r"c:\path\to\your\report.docx"
    recipient = "recipient@example.com"
    email_subject = "自动生成报告 - " + os.path.basename(word_report_path)
    email_body = "你好，\n\n请查收自动生成的报告。如有问题，请回复。\n\nregards."

    # 1. 读取 excel
    data = read_excel_data(excel_path)

    # 2. 生成 word 报告
    make_word_report(data, word_report_path)

    # 3. 发送邮件（带上 word 报告作为附件）
    send_email_with_attachment(
        to_email=recipient,
        subject=email_subject,
        body=email_body,
        attachment_paths=[word_report_path]
    )

    pythoncom.couninitialize()


if __name__ == "__main__":
    main()

import os
import traceback
import pandas as pd
import win32com.client
import pythoncom
from pathlib import path

# --- 配置区 ---
excel_data_file = r"c:\path\to\your\recipients_and_data.xlsx"
output_dir = r"c:\path\to\output"   # 存放生成的附件、报告等
log_file = os.path.join(output_dir, "send_mail.log")

email_column = "email"      # excel 表格中收件人邮箱地址所在列名
cc_column = "cc"            # 可选：抄送人列名（若无，可设为 none）
subject_column = "subject"  # 邮件主题列名
body_column = "body_html"   # 邮件 html 正文列名（可设 body_text 替代纯文本）
attachment_column = "attachment_path"  
# 如果每行有自己附件路径，可放在这一列；也可以留空，然后你自己动态生成

# --- 辅助函数 ---
def log(msg):
    with open(log_file, 'a', encoding='utf-8') as f:
        f.write(msg + "\n")
    print(msg)

def ensure_folder(path: str):
    if not os.path.isdir(path):
        os.makedirs(path, exist_ok=true)

def read_data():
    df = pd.read_excel(excel_data_file, dtype=str)
    df = df.fillna("")  # 将 nan 转为空字符串
    return df.to_dict(orient="records")

def create_custom_attachment(row: dict) -> str:
    """
    如果你希望根据每行数据生成自定义附件（excel、word、pdf...等），
    在这里实现生成逻辑，并返回文件路径；若已有附件路径，可直接返回 row[attachment_column]
    """
    # 示例：假设已有附件路径
    path = row.get(attachment_column, "").strip()
    if path and os.path.isfile(path):
        return path
    # 否则 —— 举例，生成一个简单 txt 报告
    fn = os.path.join(output_dir, f"report_for_{row.get('id', '')}.txt")
    with open(fn, 'w', encoding='utf-8') as f:
        f.write("report for: " + row.get(email_column, "") + "\n")
        # 根据实际字段写内容
        for k, v in row.items():
            f.write(f"{k} = {v}\n")
    return fn

def send_mail_via_outlook(to_addr: str, subject: str, body_html: str,
                          cc_addr: str = none, attachments: list = none,
                          send_on_behalf: str = none):
    outlook = win32com.client.dispatch("outlook.application")
    mail = outlook.createitem(0)  # 0 = olmailitem
    mail.to = to_addr
    if cc_addr:
        mail.cc = cc_addr
    if send_on_behalf:
        mail.sentonbehalfofname = send_on_behalf  # 使用指定发件邮箱（如有权限）
    mail.subject = subject
    # 设置 html 正文
    mail.bodyformat = 2  # 2 = html
    mail.htmlbody = body_html
    # 添加附件
    if attachments:
        for fp in attachments:
            if os.path.isfile(fp):
                mail.attachments.add(fp)
            else:
                log(f"[warn] 附件不存在: {fp}")
    mail.send()

def main():
    pythoncom.coinitialize()
    ensure_folder(output_dir)
    data_rows = read_data()
    log("开始处理，共 {} 行".format(len(data_rows)))

    success = 0
    fail = 0

    for idx, row in enumerate(data_rows, start=1):
        try:
            to_addr = row.get(email_column, "").strip()
            if not to_addr:
                log(f"[skip #{idx}] 收件人地址为空，跳过")
                continue

            subject = row.get(subject_column, "（无主题）")
            body_html = row.get(body_column, "")
            cc_addr = row.get(cc_column, "").strip() or none

            # 准备附件
            att = create_custom_attachment(row)
            attachments = [att] if att else none

            send_mail_via_outlook(to_addr=to_addr,
                                  subject=subject,
                                  body_html=body_html,
                                  cc_addr=cc_addr,
                                  attachments=attachments)

            log(f"[ok #{idx}] 发送邮件 到: {to_addr}, 附件: {attachments}")
            success += 1
        except exception as e:
            log(f"[error #{idx}] 收件人: {row.get(email_column)} 异常: {e}\n" + traceback.format_exc())
            fail += 1

    log(f"完成。成功: {success}, 失败: {fail}")
    pythoncom.couninitialize()

if __name__ == "__main__":
    main()