用Python自动化Excel办公：5个你每天都在重复的操作

#python #tutorial #productivity

如果你每天都在手动处理Excel文件——复制粘贴、合并表格、做数据透视、调格式、发报表——那你正在浪费大量本可以自动化的时间。本文介绍5个最常见的Excel自动化场景，每个场景都有完整的Python代码。

环境准备

pip install openpyxl pandas xlwings

场景1：批量合并多个Excel文件

每个月各部门提交报表，你需要把几十个文件合并成一个。

import pandas as pd
import glob

def merge_excel_files(folder_path, output_file):
    all_files = glob.glob(f'{folder_path}/*.xlsx')
    dfs = []
    for file in all_files:
        df = pd.read_excel(file, sheet_name=0)
        df['来源文件'] = file.split('/')[-1]
        dfs.append(df)

    merged = pd.concat(dfs, ignore_index=True)
    merged.to_excel(output_file, index=False, sheet_name='合并结果')
    return merged

merge_excel_files('部门报表/', '月度汇总.xlsx')

场景2：自动生成数据透视表

def create_pivot_table(input_file, output_file):
    df = pd.read_excel(input_file)

    # 按产品和区域汇总销售额
    pivot_sales = df.pivot_table(
        values='销售额', index='产品类别', columns='区域',
        aggfunc='sum', fill_value=0, margins=True, margins_name='合计'
    )

    # 多维度分组统计
    stats = df.groupby('区域').agg(
        总销售额=('销售额', 'sum'),
        平均客单价=('销售额', 'mean'),
        订单数量=('订单号', 'count')
    ).round(2).reset_index()

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        pivot_sales.to_excel(writer, sheet_name='销售透视')
        stats.to_excel(writer, sheet_name='区域统计', index=False)

场景3：自动设置条件格式

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule
from openpyxl.styles import PatternFill, Font

def apply_conditional_formatting(file_path):
    wb = load_workbook(file_path)
    ws = wb.active

    # 数值大于10000标红
    red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')
    ws.conditional_formatting.add(
        'D2:D1000',
        CellIsRule(operator='greaterThan', formula=['10000'], fill=red_fill)
    )

    # 数据条（进度条效果）
    ws.conditional_formatting.add(
        'F2:F1000',
        DataBarRule(start_type='min', end_type='max', color='4472C4')
    )

    # 颜色渐变（热力图效果）
    ws.conditional_formatting.add(
        'G2:G1000',
        ColorScaleRule(
            start_type='min', start_color='F8696B',
            mid_type='percentile', mid_value=50, mid_color='FFEB84',
            end_type='max', end_color='63BE7B'
        )
    )

    wb.save(file_path)

场景4：自动生成周报

from datetime import datetime

def generate_weekly_report(data_file, output_file):
    df = pd.read_excel(data_file)
    df['日期'] = pd.to_datetime(df['日期'])

    today = datetime.now()
    this_week = df[df['日期'].dt.isocalendar().week == today.isocalendar().week]

    metrics = {
        '本周销售额': this_week['销售额'].sum(),
        '本周订单数': len(this_week),
        '平均客单价': this_week['销售额'].mean(),
    }

    # 写入Excel（含标题、指标、明细表格）
    # ... 完整代码见博客完整版

场景5：自动发送报表邮件

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders

def send_report_email(smtp_server, smtp_port,
                      sender_email, sender_password,
                      recipients, subject, body, attachment_path):
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = ', '.join(recipients)
    msg['Subject'] = subject
    msg.attach(MIMEText(body, 'plain', 'utf-8'))

    with open(attachment_path, 'rb') as f:
        part = MIMEBase('application', 'octet-stream')
        part.set_payload(f.read())
        encoders.encode_base64(part)
        part.add_header('Content-Disposition',
                       f'attachment; filename="{attachment_path}"')
        msg.attach(part)

    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls()
        server.login(sender_email, sender_password)
        server.send_message(msg)

完整自动化流水线

把5个场景串联起来，配合定时任务，每周五下午5点自动执行：

import schedule

def weekly_workflow():
    merge_excel_files('部门报表/', '原始数据_合并.xlsx')
    create_pivot_table('原始数据_合并.xlsx', '销售分析.xlsx')
    apply_conditional_formatting('销售分析.xlsx')
    generate_weekly_report('原始数据_合并.xlsx', '本周周报.xlsx')
    send_report_email(..., attachment_path='本周周报.xlsx')

schedule.every().friday.at("17:00").do(weekly_workflow)

总结

这5个场景覆盖了Excel办公中最常见的重复性操作：批量合并、数据透视、条件格式、自动报表、邮件发送。把这些脚本组合起来，你可以把每周几小时的Excel工作压缩到几分钟。

DEV Community