分享一个Python自动化案例

背景

• 渠道正在开展一系列共性的市场活动
• 我们需要总结上一阶段的活动
• 统计结果以及结算费用
• 事先通过销售总监安排了每场活动的小结，
• 计划写一个脚本自动汇总小结生成月度报告

import pandas as pd
import os
import fnmatch


def extract_summary_info(excel_path):
    try:
        # 读取文件
        excel_file = pd.ExcelFile(excel_path)
        # 获取所有表名
        sheet_names = excel_file.sheet_names
        if not sheet_names:
            raise ValueError(f"文件 {excel_path} 中没有工作表")

        # 获取指定工作表中的数据
        df = excel_file.parse(sheet_names[0])

        # 提取所需内容
        activity_topic = df.loc[1, '嘉智联渠道市场推广活动报告']
        jzl_person_in_charge = df.loc[1, 'Unnamed: 8']
        activity_location = df.loc[4, '嘉智联渠道市场推广活动报告']
        activity_time = df.loc[4, 'Unnamed: 5']
        organizer = df.loc[7, '嘉智联渠道市场推广活动报告']
        contact_person = df.loc[7, 'Unnamed: 7']
        contact_number = df.loc[8, 'Unnamed: 8']

        # 活动议程部分，从第 12 行(index=11)到第 15 行(index=14)获取时间和议程信息
        agenda_rows = df.loc[11:14, ['嘉智联渠道市场推广活动报告', 'Unnamed: 2', 'Unnamed: 7']]
        agenda_rows.columns = ['开始时间', '议程', '负责担当']
        activity_agenda = '；'.join([
            f"{row['开始时间']} - {row['议程']}（{row['负责担当']}）" if pd.notna(row['负责担当']) 
            else f"{row['开始时间']} - {row['议程']}" for _, row in agenda_rows.iterrows()
        ])
        activity_summary = df.loc[35, '嘉智联渠道市场推广活动报告']

        # 创建汇总数据的 DataFrame
        data = {
            '活动主题': [activity_topic],
            '嘉智联担当': [jzl_person_in_charge],
            '活动地点': [activity_location],
            '活动时间': [activity_time],
            '主办单位': [organizer],
            '联系人': [contact_person],
            '联系电话': [contact_number],
            '活动议程': [activity_agenda],
            '活动小结': [activity_summary]
        }

        result_df = pd.DataFrame(data)
        return result_df
    except Exception as e:
        print(f"处理文件 {excel_path} 时出现错误: {e}")
        return pd.DataFrame()


def find_excel_files(root_folder):
    excel_files = []
    for root, dirs, files in os.walk(root_folder):
        for file in files:
            if fnmatch.fnmatch(file, '*.xlsx') or fnmatch.fnmatch(file, '*.xls'):
                excel_files.append(os.path.join(root, file))
    return excel_files


def main(file_list, result_file_path):
    dfs = []
    excel_files = file_list
    for excel_file in excel_files:
        result_df = extract_summary_info(excel_file)
        dfs.append(result_df)

    # 循环结束后一次性合并 DataFrame
    if dfs:
        result = pd.concat(dfs, ignore_index=True)
        result.to_excel(result_file_path, index=False)
        print(result)
    else:
        print("没有找到有效的数据")


if __name__ == '__main__':
    path = r".\会议资料2507"
    main(find_excel_files(path), r".\会议汇总2507.xlsx")

完美，俺又可以愉快的喝茶了~