如何将Django模型数据导出为Excel通过SMTP作为邮件附件发送

Django项目中存储了FinalBill业务账单数据，已通过django-import-export库实现Excel导出能力，需要补充逻辑：将导出的Excel文件作为附件，通过配置好的SMTP邮件服务发送给目标用户。

模型定义（models.py） #

class FinalBill(models.Model):
    shipDate = models.DateField(blank = True, null = True, auto_now=False, auto_now_add=False)
    customerReference = models.CharField(max_length = 200, blank = True, null = True)
    customerConfirmation = models.CharField(max_length = 200, blank = True, null = True)
    deliveryConfirmation = models.CharField(max_length = 200, blank = True, null = True)
    address = models.CharField(max_length = 200, blank = True, null = True)
    service = models.CharField(max_length = 200, blank = True, null = True)
    weight = models.FloatField(blank = True, null = True)
    pricingZone = models.CharField(max_length = 200, blank = True, null = True)
    uspsCompRate = models.FloatField(blank = True, null = True)
    charges = models.FloatField(blank = True, null = True)
    surcharges = models.FloatField(blank = True, null = True)
    totalSavings = models.FloatField(blank = True, null = True)
    totalCharges = models.FloatField(blank = True, null = True)
    customerID = models.CharField(max_length = 200)
    is_exported = models.BooleanField(default=False)
    exported_date = models.DateField(blank = True, null = True, auto_now=False, auto_now_add=False)
    def __str__(self):
        return str(self.deliveryConfirmation)

原有工具函数（utils.py） #

原有逻辑中file仅为字符串变量，没有承接导出的Excel内容，无法正常生成附件：

def send_bill_on_mail(mailerID):
   customerBill = FinalBill.objects.filter(customerID=mailerID, is_exported=False)
   dataset = Bills().export(customerBill)
   mail_subject = "Subject Name"
   message = "Test Email Message"
   to_email = "xyz@gmail.com"
   file = "file"        
   mail = EmailMessage(mail_subject, message,  settings.EMAIL_HOST_USER, [to_email])
   mail.attach(file.name, file.read(), file.content_type)
   mail.send()

django-import-export导出的Dataset对象支持直接输出Excel格式二进制内容，无需生成本地实体文件，直接在内存中构造附件即可，修正后逻辑如下：

from django.core.mail import EmailMessage
from django.conf import settings
from datetime import date
# 替换为项目中Bills资源类的实际导入路径
from .resources import Bills
from .models import FinalBill

def send_bill_on_mail(mailerID):
    # 查询指定客户下未导出的账单
    customer_bills = FinalBill.objects.filter(customerID=mailerID, is_exported=False)
    # 无待发送账单直接终止逻辑
    if not customer_bills.exists():
        return False
    
    # 导出数据集
    dataset = Bills().export(customer_bills)
    # 直接获取xlsx格式二进制内容
    excel_content = dataset.xlsx
    # 构造带客户ID、日期的文件名，避免重名
    file_name = f"账单_{mailerID}_{date.today().strftime('%Y%m%d')}.xlsx"
    # xlsx格式对应的MIME类型
    file_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

    # 构造邮件
    mail = EmailMessage(
        subject="您的本期账单明细",
        body="您好，附件为您本期的账单明细，请查收。",
        from_email=settings.EMAIL_HOST_USER,
        to=["xyz@gmail.com"] # 替换为实际客户邮箱
    )
    # 添加附件
    mail.attach(file_name, excel_content, file_content_type)
    # 发送邮件
    mail.send()

    # 更新账单导出状态，避免重复发送
    customer_bills.update(
        is_exported=True,
        exported_date=date.today()
    )
    return True

补充说明 #

• 若需要导出为旧版xls格式，将dataset.xlsx替换为dataset.xls，对应MIME类型改为application/vnd.ms-excel，文件名后缀改为.xls即可
• 若需要发送HTML格式正文，初始化EmailMessage时增加参数content_subtype="html"
• 生产环境建议给邮件发送逻辑加异常捕获，处理SMTP服务不可用、发送失败的场景
• 若客户邮箱存储在关联表中，直接替换硬编码的收件人地址即可

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