Python自动化办公案例如何自动化处理办公

wen python案例 2026-06-29 241

本文目录导读：

Excel自动化处理
Word文档自动化
PDF批量处理
邮件自动化
文件管理自动化
综合应用实例
实用技巧

我来分享几个实用的Python自动化办公案例，涵盖Excel、Word、PDF等常见办公场景。

Excel自动化处理

批量合并Excel文件

import pandas as pd
import os
from pathlib import Path
def merge_excel_files(folder_path, output_file):
    """合并文件夹中所有Excel文件"""
    # 获取所有Excel文件
    excel_files = list(Path(folder_path).glob("*.xlsx"))
    # 读取并合并所有文件
    all_data = []
    for file in excel_files:
        df = pd.read_excel(file)
        df['来源文件'] = file.name  # 添加来源标识
        all_data.append(df)
    # 合并并保存
    merged_df = pd.concat(all_data, ignore_index=True)
    merged_df.to_excel(output_file, index=False)
    print(f"已合并 {len(excel_files)} 个文件到 {output_file}")
# 使用示例
merge_excel_files("./excel_files", "./merged_result.xlsx")

自动数据清洗

import pandas as pd
def clean_sales_data(file_path):
    """清洗销售数据"""
    df = pd.read_excel(file_path)
    # 1. 处理缺失值
    df = df.dropna(subset=['客户名称', '金额'])  # 删除关键字段为空的行
    # 2. 数据格式标准化
    df['日期'] = pd.to_datetime(df['日期'])  # 统一日期格式
    df['金额'] = df['金额'].str.replace('¥', '').astype(float)  # 清理金额格式
    # 3. 去除重复数据
    df = df.drop_duplicates(subset=['订单号'])
    # 4. 数据过滤
    df = df[df['金额'] > 0]  # 移除无效数据
    # 5. 添加计算列
    df['税额'] = df['金额'] * 0.13
    df['净额'] = df['金额'] - df['税额']
    return df

Word文档自动化

批量生成合同/报告

from docx import Document
from docx.shared import Pt, Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
import datetime
def generate_contract(contract_data, template_path, output_path):
    """根据模板生成合同"""
    doc = Document(template_path)
    # 替换占位符
    placeholders = {
        '{合同编号}': contract_data['id'],
        '{甲方}': contract_data['party_a'],
        '{乙方}': contract_data['party_b'],
        '{金额}': f"¥{contract_data['amount']:,.2f}",
        '{日期}': datetime.datetime.now().strftime('%Y年%m月%d日')
    }
    for paragraph in doc.paragraphs:
        for key, value in placeholders.items():
            if key in paragraph.text:
                paragraph.text = paragraph.text.replace(key, value)
                # 保留原有格式
                run = paragraph.runs[0]
                run.text = paragraph.text
    doc.save(output_path)
    print(f"合同已生成：{output_path}")
# 批量生成
contracts = [
    {'id': '2024001', 'party_a': '公司A', 'party_b': '公司B', 'amount': 150000},
    {'id': '2024002', 'party_a': '公司C', 'party_b': '公司D', 'amount': 200000},
]
for contract in contracts:
    generate_contract(contract, 'template.docx', f"合同_{contract['id']}.docx")

PDF批量处理

批量提取PDF表格数据

import tabula
import pandas as pd
import os
def extract_pdf_tables(pdf_folder, output_excel):
    """批量提取PDF中的表格到Excel"""
    all_tables = []
    for pdf_file in os.listdir(pdf_folder):
        if pdf_file.endswith('.pdf'):
            pdf_path = os.path.join(pdf_folder, pdf_file)
            try:
                # 提取PDF中的表格
                tables = tabula.read_pdf(pdf_path, pages='all', multiple_tables=True)
                for i, table in enumerate(tables):
                    table['来源文件'] = pdf_file
                    table['表格编号'] = i + 1
                    all_tables.append(table)
            except Exception as e:
                print(f"处理 {pdf_file} 时出错: {e}")
    # 合并所有表格
    if all_tables:
        result = pd.concat(all_tables, ignore_index=True)
        result.to_excel(output_excel, index=False)
        print(f"已提取 {len(all_tables)} 个表格到 {output_excel}")
    else:
        print("未提取到任何表格数据")

邮件自动化

自动发送报表邮件

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 schedule
import time
def send_report_email(report_path, recipients):
    """发送报表邮件"""
    # 邮件配置
    sender = 'your_email@company.com'
    password = 'your_password'
    smtp_server = 'smtp.company.com'
    smtp_port = 587
    # 创建邮件对象
    msg = MIMEMultipart()
    msg['Subject'] = f"销售日报 - {datetime.now().strftime('%Y-%m-%d')}"
    msg['From'] = sender
    msg['To'] = ', '.join(recipients)
    # 邮件正文
    body = f"""
    您好，
    请查收今日销售日报（{datetime.now().strftime('%Y-%m-%d')}）。
    数据统计时间：截至今日 18:00
    生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}
    如有问题，请及时联系。
    """
    msg.attach(MIMEText(body, 'plain', 'utf-8'))
    # 添加附件
    with open(report_path, 'rb') as f:
        attachment = MIMEBase('application', 'octet-stream')
        attachment.set_payload(f.read())
        encoders.encode_base64(attachment)
        attachment.add_header('Content-Disposition', 
                             'attachment', 
                             filename=report_path.split('/')[-1])
        msg.attach(attachment)
    # 发送邮件
    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls()
        server.login(sender, password)
        server.send_message(msg)
    print(f"邮件已发送至 {len(recipients)} 个收件人")
# 定时任务
schedule.every().day.at("18:00").do(
    send_report_email, 
    report_path="./daily_report.xlsx",
    recipients=['manager@company.com', 'team@company.com']
)
while True:
    schedule.run_pending()
    time.sleep(60)

文件管理自动化

智能文件分类整理

import os
import shutil
from pathlib import Path
def organize_files(source_folder):
    """智能整理文件"""
    # 文件类型分类规则
    categories = {
        '文档': ['.doc', '.docx', '.txt', '.pdf'],
        '表格': ['.xls', '.xlsx', '.csv'],
        '图片': ['.jpg', '.jpeg', '.png', '.gif'],
        '视频': ['.mp4', '.avi', '.mkv'],
        '压缩包': ['.zip', '.rar', '.7z'],
        '代码': ['.py', '.java', '.html', '.css', '.js']
    }
    # 创建分类文件夹并移动文件
    for filename in os.listdir(source_folder):
        file_path = os.path.join(source_folder, filename)
        if os.path.isfile(file_path):
            file_ext = os.path.splitext(filename)[1].lower()
            # 查找对应的分类
            for category, extensions in categories.items():
                if file_ext in extensions:
                    category_folder = os.path.join(source_folder, category)
                    os.makedirs(category_folder, exist_ok=True)
                    shutil.move(file_path, os.path.join(category_folder, filename))
                    print(f"移动 {filename} -> {category}")
                    break
            else:
                # 未分类的文件放入"其他"
                other_folder = os.path.join(source_folder, '其他')
                os.makedirs(other_folder, exist_ok=True)
                shutil.move(file_path, os.path.join(other_folder, filename))
                print(f"移动 {filename} -> 其他")

综合应用实例

自动生成日报表

import pandas as pd
import matplotlib.pyplot as plt
from docx import Document
import datetime
def create_daily_report(data_file):
    """自动生成日报表"""
    # 1. 读取数据
    df = pd.read_excel(data_file)
    # 2. 数据分析
    today = datetime.now().strftime('%Y-%m-%d')
    today_data = df[df['日期'] == today]
    summary = {
        '今日订单数': len(today_data),
        '今日销售额': today_data['金额'].sum(),
        '今日客户数': today_data['客户ID'].nunique(),
        '平均客单价': today_data['金额'].mean()
    }
    # 3. 生成图表
    fig, axes = plt.subplots(2, 2, figsize=(12, 8))
    # 销售额趋势
    df.groupby('日期')['金额'].sum().plot(ax=axes[0,0], title='销售额趋势')
    # 产品分类占比
    df.groupby('产品类别')['金额'].sum().plot(kind='pie', ax=axes[0,1], title='产品占比')
    # 客户分布
    df.groupby('地区')['金额'].sum().plot(kind='bar', ax=axes[1,0], title='地区销售分布')
    # 今日概览
    axes[1,1].axis('off')
    axes[1,1].text(0.1, 0.9, f"今日概览（{today}）", fontsize=14, fontweight='bold')
    y = 0.7
    for key, value in summary.items():
        axes[1,1].text(0.1, y, f"{key}: {value:,}", fontsize=12)
        y -= 0.1
    plt.tight_layout()
    plt.savefig('daily_chart.png', dpi=300)
    # 4. 生成Word报告
    doc = Document()
    doc.add_heading(f'每日销售报告 - {today}', 0)
    doc.add_heading('核心数据', level=1)
    for key, value in summary.items():
        doc.add_paragraph(f"{key}：{value:,}")
    doc.add_heading('销售图表', level=1)
    doc.add_picture('daily_chart.png', width=Inches(6))
    doc.add_heading('详细数据', level=1)
    table = doc.add_table(rows=1, cols=len(today_data.columns))
    table.style = 'Light Grid Accent 1'
    # 写入表头
    for i, col in enumerate(today_data.columns):
        table.rows[0].cells[i].text = col
    # 写入数据
    for row_idx, row in today_data.iterrows():
        row_cells = table.add_row().cells
        for col_idx, value in enumerate(row):
            row_cells[col_idx].text = str(value)
    report_path = f'日报表_{today}.docx'
    doc.save(report_path)
    print(f"日报表已生成：{report_path}")
    return summary
# 运行
summary = create_daily_report('sales_data.xlsx')