python 自动化 excel数据筛选后发送outlook邮件

import pandas as pd
from datetime import datetime, timedelta
import win32com.client as win32
import os  # 导入 os 模块用于文件删除
import glob
import openpyxl  # 导入 openpyxl 模块用于单元格合并和格式化

# 定义文件路径和工作表名称及起始行
file_path = '要筛选的excel.xlsx'
sheets = {
    '2022': 2,  # 从第2行开始
    '2023': 3,  # 从第3行开始
    '2024': 3  # 从第3行开始
}

# 定义邮件参数
to_emails = ['test1@example.com', 'test2@example.com', 'test3@example.com', 'test4@example.com', 'test5@example.com']  # 邮件接人人
cc_emails = ['manager1@example.com', 'manager2@example.com']  # 抄送  填写实际抄送人
subject = 'Upcoming Stability Tests'    #  邮件主题  根据实际情况修改
body = 'Dear Team,\nPlease find attached the list of upcoming stability tests.\nBest regards,\nYour Name'   # 邮件内容 根据实际情况修改

# 定义频率列名
frequency_columns = ['列1', '列2', '列3']


def filter_data(df, today):
    """筛选出过去 7 天内的稳定性测试数据"""
    # 计算过去 7 天的日期范围
    past_seven_days = today - timedelta(days=7)

    # 创建掩码，检查每个频率列是否在过去 7 天内
    mask = (df[frequency_columns] >= past_seven_days) & (df[frequency_columns] <= today)

    # 返回符合条件的行
    return df[mask.any(axis=1)]


# 读取数据并筛选
data_frames = {}
today = datetime.today()

file_pattern = "*.xlsx"
files = [f for f in glob.glob(file_pattern) if f != 'upcoming_stability_tests.xlsx']

data_found = False  # 标记是否找到数据

today = datetime.today()
seven_days_ago = today - timedelta(days=7)

for file_path in files:
    for sheet, header_row in sheets.items():
        try:
            # 筛选符合条件的行
            # 读取标题行和数据行
            title_row = pd.read_excel(file_path, sheet_name=sheet, header=None, nrows=1)
            df = pd.read_excel(file_path, sheet_name=sheet, skiprows=header_row - 1)
            filtered_df = filter_data(df, today)
            if not filtered_df.empty:
                # 获取标题行字符串
                title_str = title_row.values.flatten()[0]  # 获取标题字符串

                # 创建一个 DataFrame 来包含标题、列名和数据
                combined_df = pd.DataFrame(columns=df.columns.tolist())
                combined_df.loc[0] = [title_str] + [''] * (len(df.columns) - 1)  # 设置标题行
                combined_df.loc[1] = df.columns.tolist()  # 设置列名行
                # 添加数据行
                combined_df = pd.concat([combined_df, filtered_df.reset_index(drop=True)], ignore_index=True)
                data_frames[sheet] = combined_df  # 使用工作表名称作为字典的键

        except Exception as e:
            print(f"Error reading sheet {sheet}: {e}")

# 如果有数据，则导出为 Excel 文件并发送邮件
if data_frames:
    output_file = '新的excel.xlsx'

    # 创建 ExcelWriter 使用 openpyxl
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        for sheet_name, frame in data_frames.items():
            # 将 DataFrame 写入 Excel，禁止索引和标题
            frame.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=0)
            # frame.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=2)

            # 使用 openpyxl 进行额外操作
            workbook = writer.book
            worksheet = writer.sheets[sheet_name]

            # 获取标题行字符串（假设标题在 DataFrame 的第一行）
            title_value = frame.at[0, frame.columns[0]]

            # 合并第一行的单元格（标题行）
            worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(frame.columns))
            cell = worksheet.cell(row=1, column=1)
            cell.value = title_value  # 设置标题值
            cell.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')  # 居中对齐

    try:
        # 发送邮件
        outlook = win32.Dispatch('outlook.application')
        mail = outlook.CreateItem(0)
        mail.Subject = subject
        mail.Body = body
        mail.To = ';'.join(to_emails)
        mail.CC = ';'.join(cc_emails)
        mail.Attachments.Add(Source=output_file)
        mail.Send()

        # 邮件发送成功后删除文件
        os.remove(output_file)
        print(f"Email sent successfully and file {output_file} deleted.")

    except Exception as e:
        print(f"Error sending email: {e}")
else:
    print("No upcoming stability tests within the next 7 days.")