生成邮件附件Excel时如何合并BDName与BDEmail的重复列值

我来帮你搞定这个需求——生成带有重复BDName/BDEmail合并显示效果的Excel，再把它作为邮件附件发出去。下面用Python给你两种实用的实现方案，都是日常项目里常用的思路：

方法一：Pandas预处理+Openpyxl合并单元格（适合大数据量） #

先通过Pandas标记重复行，再用Openpyxl实现单元格合并，视觉效果更规整：

步骤1：预处理数据，标记重复项 #

先把原始数据里重复的BDName和BDEmail标记出来，重复行的对应字段设为空：

import pandas as pd

# 假设你的原始数据是这种列表格式
raw_data = [
    {"BDName": "siva", "BDEmail": "a@a.com", "Process Id": "a1", "Customer Name": "hari", "Email": "h@h.com"},
    {"BDName": "siva", "BDEmail": "a@a.com", "Process Id": "a2", "Customer Name": "hari", "Email": "h@h.com"},
    {"BDName": "john", "BDEmail": "j@j.com", "Process Id": "b1", "Customer Name": "lisa", "Email": "l@l.com"},
    {"BDName": "john", "BDEmail": "j@j.com", "Process Id": "b2", "Customer Name": "mike", "Email": "m@m.com"},
]

df = pd.DataFrame(raw_data)
# 标记当前行的BD信息是否和上一行完全重复
df['is_duplicate'] = (df['BDName'] == df['BDName'].shift(1)) & (df['BDEmail'] == df['BDEmail'].shift(1))
# 重复行的BD字段设为空，只保留第一行的值
df.loc[df['is_duplicate'], ['BDName', 'BDEmail']] = ''

步骤2：生成Excel并合并重复单元格 #

用Openpyxl读取Pandas导出的临时文件，合并相同值的单元格：

from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter

# 导出临时Excel
temp_file = "temp_bd_data.xlsx"
df.to_excel(temp_file, index=False)

# 加载文件并处理合并
wb = load_workbook(temp_file)
ws = wb.active

# 设置单元格垂直居中，提升美观度
for row in ws.iter_rows():
    for cell in row:
        cell.alignment = Alignment(vertical='center', horizontal='center')

# 合并BDName和BDEmail列的重复单元格
start_row = 2  # 表头在第1行，从第2行开始处理
prev_bd_key = (ws['A2'].value, ws['B2'].value)

for row_num in range(3, ws.max_row + 1):
    current_bd_key = (ws[f'A{row_num}'].value, ws[f'B{row_num}'].value)
    if current_bd_key == ('', ''):
        continue
    else:
        if start_row != row_num - 1:
            ws.merge_cells(f'A{start_row}:A{row_num - 1}')
            ws.merge_cells(f'B{start_row}:B{row_num - 1}')
        prev_bd_key = current_bd_key
        start_row = row_num
# 处理最后一组重复项
if start_row != ws.max_row:
    ws.merge_cells(f'A{start_row}:A{ws.max_row}')
    ws.merge_cells(f'B{start_row}:B{ws.max_row}')

# 保存最终文件
final_excel = "bd_business_report.xlsx"
wb.save(final_excel)

步骤3：作为邮件附件发送 #

用Python内置的邮件库实现发送：

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

# 邮件配置信息，替换成你自己的
sender = "your_email@example.com"
receiver = "target_email@example.com"
password = "your_email_password_or_app_code"
smtp_server = "smtp.example.com"
smtp_port = 587

# 构建邮件
msg = MIMEMultipart()
msg['From'] = sender
msg['To'] = receiver
msg['Subject'] = "最新BD业务数据报表"

# 添加正文
body = "您好，附件是最新的BD业务数据，请查收。"
msg.attach(MIMEText(body, 'plain'))

# 添加附件
with open(final_excel, "rb") as f:
    part = MIMEBase('application', 'octet-stream')
    part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f'attachment; filename="{final_excel}"')
msg.attach(part)

# 发送邮件
with smtplib.SMTP(smtp_server, smtp_port) as server:
    server.starttls()
    server.login(sender, password)
    server.send_message(msg)
print("邮件发送成功！")

方法二：直接用Openpyxl逐行写入（适合小数据量） #

如果数据量不大，也可以直接逐行写入时判断重复，重复的BD字段留空：

from openpyxl import Workbook
from openpyxl.styles import Alignment

# 创建工作簿
wb = Workbook()
ws = wb.active

# 写入表头
headers = ["BDName", "BDEmail", "Process Id", "Customer Name", "Email"]
ws.append(headers)
# 设置表头样式
for cell in ws[1]:
    cell.alignment = Alignment(vertical='center', horizontal='center')
    cell.font = cell.font.copy(bold=True)

# 逐行写入并处理重复
prev_bd_name = None
prev_bd_email = None
for item in raw_data:
    row_content = []
    # 判断当前BD信息是否和上一行重复
    if item["BDName"] == prev_bd_name and item["BDEmail"] == prev_bd_email:
        row_content.extend(["", ""])
    else:
        row_content.extend([item["BDName"], item["BDEmail"]])
        prev_bd_name = item["BDName"]
        prev_bd_email = item["BDEmail"]
    # 添加其他字段
    row_content.extend([item["Process Id"], item["Customer Name"], item["Email"]])
    ws.append(row_content)

# 调整列宽适配内容
for col in ws.columns:
    max_len = 0
    col_letter = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_len:
                max_len = len(str(cell.value))
        except:
            pass
    ws.column_dimensions[col_letter].width = (max_len + 2) * 1.2

# 保存文件
wb.save("bd_report_simple.xlsx")

两种方法都能实现你要的效果：重复的BDName和BDEmail只在首次出现时显示，后续重复行的对应位置要么留空，要么合并成一个单元格。

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