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

import pandas as pdfrom datetime import datetime, timedeltaimport win32com.client as win32import os  # 导入 os 模块用于文件删除import globimport 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.")