How can i attach django models data as Excel file and send it as mail with SMTP

Question

i have some data in django models and i want to make excel file from that data and attach it in SMTP as file and send it to target user. i am also using django-import-export to export the excel files .but in this case i want to attach the file in email.

Model

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)

Utills.py

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 = "[email protected]"
   file = "file"        
   mail = EmailMessage(mail_subject, message,  settings.EMAIL_HOST_USER, [to_email])
   mail.attach(file.name, file.read(), file.content_type)
   mail.send()

Answer 1

def export_data_to_excel(mailerID) -> None: 
  excelfile = BytesIO()
  workbook = Workbook()
  workbook.remove(workbook.active)
  worksheet = workbook.create_sheet(title='Title Name', index=1)

  bill_queryset =  Bills.objects.filter(cus_id=mailerID, is_exported=False)
  columns = ['Date', 'Reference', 'Confirmation', 'Confirmation', 'Address', 'Service', 'Weight', 'Pricing Zone', 'UspsRate', 'Charges', 'Sur Charges', 'Total Savings', 'Total Charges', 'Customer ID']
  row_num = 1

# Assign the titles for each cell of the header
  for col_num, column_title in enumerate(columns, 1):
    cell = worksheet.cell(row=row_num, column=col_num)
    cell.value = column_title
    cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
    cell.font = Font(bold=True)
# Iterate through all coins
 for _, bill in enumerate(bill_queryset, 1):
    row_num += 1

    # Define the data for each cell in the row
    row = [
        bill.shipDate,
        bill.customerReference,
        bill.customerConfirmation,
        bill.deliveryConfirmation,
        bill.address,
        bill.service,
        bill.weight,
        bill.pricingZone,
        bill.uspsCompRate,
        bill.charges,
        bill.surcharges,
        bill.totalSavings,
        bill.totalCharges,
        bill.customerID,
    ]

    # Assign the data for each cell of the row
    for col_num, cell_value in enumerate(row, 1):
        cell = worksheet.cell(row=row_num, column=col_num)
        cell.value = cell_value
        cell.protection = Protection(locked=True)
workbook.save(excelfile)
mail_subject = f'Invoice {mailerID} on {date.today()}'
message = ""
to_email = "[email protected]"
message = EmailMessage(mail_subject, message, settings.EMAIL_HOST_USER, [to_email])
message.attach(f'Invoice {mailerID}.xlsx', excelfile.getvalue(), 'application/vnd.ms-excel')
message.send()