如何用Python将DataFrame及AWS S3文件作为Excel附件发送邮件

Hey there! Let's work through your two requirements and fix up that problematic code of yours. I see you've already got plain-text emails working, so we just need to iron out the attachment handling bits. First, let's go over the key issues in your original code:

• df = modeloutput.to_excel("Outpout.xls"): The to_excel() method returns None, not a DataFrame—so you were trying to use None as a filename, which caused errors.
• Using MIMEText for Excel attachments: Excel files are binary, not plain text, so we need to use MIMEBase instead to handle them correctly.
• Missing encoding: Attachments need to be base64-encoded so they don't get corrupted in transit.

1. Send DataFrame as Excel Attachment #

Here's a fixed, complete version of code to convert your DataFrame to Excel and send it as an attachment:

import pandas as pd
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import smtplib
import os

# --- Step 1: Prepare your DataFrame (replace with your actual data loading logic) ---
# For example, reading from S3 like you did:
# import boto3
# from sagemaker import get_execution_role
# role = get_execution_role()
# bucket='cotydata'
# data_key = 'modeloutput.csv'
# data_location = f's3://{bucket}/{data_key}'
# modeloutput = pd.read_csv(data_location)

modeloutput = pd.DataFrame({"col1": [1,2,3], "col2": ["a","b","c"]}) # Example DataFrame

# Save DataFrame to Excel (note: to_excel returns None, no need to assign to a variable)
excel_filename = "Model_Output.xls"
modeloutput.to_excel(excel_filename, index=False) # index=False removes the extra index column

# --- Step 2: Build the email ---
msg = MIMEMultipart()
password = "your_google_app_password" # Important: For Gmail, use an App Password if 2FA is enabled
msg['From'] = "riskradar@gmail.com"
msg['To'] = "abc@gmail.com"
msg['Subject'] = "Model Output - Excel Attachment"

# Add optional plain-text body
email_body = """Hi there,

Please find the attached model output in Excel format.

Best regards,
Your Team"""
msg.attach(MIMEText(email_body, 'plain'))

# --- Step 3: Add the Excel attachment ---
with open(excel_filename, "rb") as attachment_file:
    # Create a MIMEBase object for the binary Excel file
    part = MIMEBase('application', 'vnd.ms-excel')
    part.set_payload(attachment_file.read())

# Encode the attachment to base64 (required for email transmission)
encoders.encode_base64(part)

# Set headers to mark this as an attachment
part.add_header(
    "Content-Disposition",
    f"attachment; filename= {excel_filename}",
)

# Attach the part to the email
msg.attach(part)

# --- Step 4: Send the email ---
server = smtplib.SMTP('smtp.gmail.com:587')
server.starttls() # Enable TLS encryption
server.login(msg['From'], password)
server.sendmail(msg['From'], msg['To'], msg.as_string())
server.quit()

# Optional: Clean up the local Excel file if you don't need to keep it
os.remove(excel_filename)

Key Notes: #

• For Gmail: If you have 2FA enabled, don't use your regular password—generate an App Password instead. If 2FA is off, you'll need to allow "less secure apps" (not recommended, better to enable 2FA).
• index=False ensures you don't include the DataFrame's index column in the Excel file (adjust if you need it).

2. Send Excel File Stored in AWS S3 as Attachment #

We have two scenarios here: either send an existing Excel file directly from S3, or convert a S3 CSV to Excel first (like your original code intended).

Scenario A: Send an Existing Excel File from S3 #

No need to save the file locally—we can read the binary content directly from S3 and attach it:

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

# --- Step 1: Fetch the Excel file from S3 ---
s3 = boto3.client('s3')
bucket_name = "model-output-ui"
s3_file_key = "UI_your_date.xls" # Replace with your actual S3 file key

# Read the file's binary content directly from S3
s3_response = s3.get_object(Bucket=bucket_name, Key=s3_file_key)
excel_content = s3_response['Body'].read()

# --- Step 2: Build the email ---
msg = MIMEMultipart()
password = "your_google_app_password"
msg['From'] = "riskradar@gmail.com"
msg['To'] = "abc@gmail.com"
msg['Subject'] = "S3 Stored Excel Attachment"

email_body = """Hi,

Please find the attached Excel file from our S3 bucket.

Best regards,
Your Team"""
msg.attach(MIMEText(email_body, 'plain'))

# --- Step 3: Attach the S3 file content ---
part = MIMEBase('application', 'vnd.ms-excel')
part.set_payload(excel_content)
encoders.encode_base64(part)

# Use the filename from the S3 key (split to get just the file name, not full path)
attachment_filename = s3_file_key.split("/")[-1]
part.add_header(
    "Content-Disposition",
    f"attachment; filename= {attachment_filename}",
)

msg.attach(part)

# --- Step 4: Send the email ---
server = smtplib.SMTP('smtp.gmail.com:587')
server.starttls()
server.login(msg['From'], password)
server.sendmail(msg['From'], msg['To'], msg.as_string())
server.quit()

Scenario B: Convert S3 CSV to Excel, Then Send #

This fixes your original code's issues, combining S3 CSV reading with Excel attachment sending:

import os
import boto3
import pandas as pd
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import smtplib
from sagemaker import get_execution_role

# --- Step 1: Read CSV from S3 ---
role = get_execution_role()
bucket='cotydata'
data_key = 'modeloutput.csv'
data_location = f's3://{bucket}/{data_key}'
modeloutput = pd.read_csv(data_location)

# --- Step 2: Convert to Excel ---
excel_filename = "Model_Output_From_S3.csv"
modeloutput.to_excel(excel_filename, index=False)

# --- Step 3: Build email and attach file ---
msg = MIMEMultipart()
password = "your_google_app_password"
msg['From'] = "riskradar@gmail.com"
msg['To'] = "abc@gmail.com"
msg['Subject'] = "Model Output (Converted from S3 CSV)"

email_body = """Hi,

Attached is the model output converted from our S3 CSV file to Excel.

Best regards,
Your Team"""
msg.attach(MIMEText(email_body, 'plain'))

with open(excel_filename, "rb") as f:
    part = MIMEBase('application', 'vnd.ms-excel')
    part.set_payload(f.read())

encoders.encode_base64(part)
part.add_header(
    "Content-Disposition",
    f"attachment; filename= {excel_filename}",
)

msg.attach(part)

# --- Step 4: Send email ---
server = smtplib.SMTP('smtp.gmail.com:587')
server.starttls()
server.login(msg['From'], password)
server.sendmail(msg['From'], msg['To'], msg.as_string())
server.quit()

# Clean up local file
os.remove(excel_filename)

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