### Python代码实现根据Excel某一列的值进行拆分，并将结果发送到指定邮箱的功能 以下是一个完整的Python脚本，用于实现基于Excel某列的不同值拆分文件，并通过SMTP将结果发送到指定邮箱的功能[^1]。 #### 代码示例 ```python import pandas as pd import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders import os # 加载Excel文件并读取数据 def load_excel(file_path): df = pd.read_excel(file_path) # 使用pandas读取Excel文件[^2] return df # 根据某一列的值拆分数据 def split_dataframe_by_column(df, column_name): grouped = df.groupby(column_name) # 按照指定列进行分组[^3] return {name: group for name, group in grouped} # 保存每个分组为单独的Excel文件 def save_group_to_excel(grouped_data, output_folder): if not os.path.exists(output_folder): os.makedirs(output_folder) file_paths = {} for key, group in grouped_data.items(): file_name = f"{key}.xlsx" file_path = os.path.join(output_folder, file_name) group.to_excel(file_path, index=False) # 将每个分组保存为单独的Excel文件[^4] file_paths[key] = file_path return file_paths # 发送邮件 def send_email_via_smtp(subject, body, to_email, attachment_path=None): smtp_server = "smtp.example.com" # SMTP服务器地址 smtp_port = 587 # SMTP端口 from_email = "sender@example.com" # 发件人邮箱 password = "your_password" # 发件人邮箱密码 msg = MIMEMultipart() msg['From'] = from_email msg['To'] = to_email msg['Subject'] = subject msg.attach(MIMEText(body, 'plain')) # 添加邮件正文 if attachment_path: # 如果有附件，则添加附件 with open(attachment_path, "rb") as attachment: part = MIMEBase('application', 'octet-stream') part.set_payload(attachment.read()) encoders.encode_base64(part) part.add_header('Content-Disposition', f'attachment; filename={os.path.basename(attachment_path)}') msg.attach(part) server = smtplib.SMTP(smtp_server, smtp_port) server.starttls() # 启用TLS加密 server.login(from_email, password) # 登录SMTP服务器 text = msg.as_string() server.sendmail(from_email, to_email, text) # 发送邮件 server.quit() # 主函数 def main(): input_file = "data.xlsx" # 输入Excel文件路径 column_to_split = "Category" # 需要拆分的列名 output_folder = "output" # 输出文件夹路径 df = load_excel(input_file) # 加载Excel文件 grouped_data = split_dataframe_by_column(df, column_to_split) # 拆分数据 file_paths = save_group_to_excel(grouped_data, output_folder) # 保存每个分组为Excel文件 # 假设每个分组对应一个收件人邮箱 recipient_emails = { "Group1": "recipient1@example.com", "Group2": "recipient2@example.com", "Group3": "recipient3@example.com" } for key, file_path in file_paths.items(): if key in recipient_emails: to_email = recipient_emails[key] subject = f"Data for {key}" body = f"Please find attached the data for {key}." send_email_via_smtp(subject, body, to_email, file_path) # 发送邮件 if __name__ == "__main__": main() ``` #### 代码说明 1. **加载Excel文件**：使用`pandas`库加载Excel文件并读取数据。 2. **拆分数据**：通过`groupby`方法按指定列的值对数据进行分组。 3. **保存分组数据**：将每个分组保存为单独的Excel文件。 4. **发送邮件**：通过SMTP协议发送带有附件的邮件给指定收件人。