Python 自动化办公实战：每天节省2小时的脚本合集-冉冉博客

在日常工作中，大量重复性操作浪费了宝贵时间。作为冉冉博客的实用工具系列，今天分享几个 Python 自动化脚本，让你的工作效率翻倍。这些脚本都经过实战验证，网站编程和数据处理都能用上。

一、批量文件处理

文件夹整理、批量重命名、格式转换，Python 几行代码就能搞定。

批量重命名文件

import os
import re

def batch_rename(folder, pattern, replacement):
    """批量重命名文件"""
    count = 0
    for filename in os.listdir(folder):
        new_name = re.sub(pattern, replacement, filename)
        if new_name != filename:
            old_path = os.path.join(folder, filename)
            new_path = os.path.join(folder, new_name)
            os.rename(old_path, new_path)
            count += 1
            print(f'重命名: {filename} -> {new_name}')
    print(f'共处理 {count} 个文件')

# 使用示例：把所有空格替换为下划线
batch_rename('./downloads', r's+', '_')

自动整理下载文件夹

import shutil
from pathlib import Path

def organize_downloads():
    """按文件类型整理下载文件夹"""
    download_dir = Path.home() / 'Downloads'
    
    # 定义文件类型和对应文件夹
    categories = {
        '图片': ['.jpg', '.jpeg', '.png', '.gif', '.webp'],
        '文档': ['.pdf', '.doc', '.docx', '.txt', '.xlsx'],
        '压缩包': ['.zip', '.rar', '.7z', '.tar'],
        '视频': ['.mp4', '.avi', '.mkv', '.mov'],
        '音频': ['.mp3', '.wav', '.flac']
    }
    
    for file in download_dir.iterdir():
        if file.is_file():
            ext = file.suffix.lower()
            for category, extensions in categories.items():
                if ext in extensions:
                    target_dir = download_dir / category
                    target_dir.mkdir(exist_ok=True)
                    shutil.move(str(file), target_dir / file.name)
                    print(f'移动 {file.name} 到 {category}/')
                    break

organize_downloads()

二、Excel 数据处理神器

pandas + openpyxl 是处理 Excel 的最佳组合，比手动操作快 100 倍。

合并多个 Excel 文件

import pandas as pd
from pathlib import Path

def merge_excel_files(folder, output_file):
    """合并文件夹内所有 Excel 文件"""
    all_data = []
    
    for file in Path(folder).glob('*.xlsx'):
        df = pd.read_excel(file)
        df['来源文件'] = file.name  # 添加来源标记
        all_data.append(df)
        print(f'读取: {file.name}')
    
    merged = pd.concat(all_data, ignore_index=True)
    merged.to_excel(output_file, index=False)
    print(f'合并完成: {output_file}')
    print(f'共 {len(merged)} 行数据')

merge_excel_files('./reports', '合并报表.xlsx')

自动发送邮件报表

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

def send_report(to_email, subject, body, attachment_path):
    """自动发送邮件报表"""
    # 配置邮箱（以 QQ 邮箱为例）
    smtp_server = 'smtp.qq.com'
    smtp_port = 465
    sender = 'your_email@qq.com'
    password = '授权码'  # 不是密码，是 SMTP 授权码
    
    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = to_email
    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_SSL(smtp_server, smtp_port) as server:
        server.login(sender, password)
        server.send_message(msg)
    
    print(f'报表已发送至 {to_email}')

send_report('boss@company.com', '每日报表', '请查收附件', 'daily_report.xlsx')

三、网页数据采集

requests + BeautifulSoup 抓取网页数据，是数据分析的基础技能。

采集网站标题和链接

import requests
from bs4 import BeautifulSoup
import pandas as pd

def scrape_articles(url):
    """采集网站文章列表"""
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0.0.0'}
    
    response = requests.get(url, headers=headers)
    soup = BeautifulSoup(response.text, 'html.parser')
    
    articles = []
    for item in soup.select('article'):  # 根据实际网站调整选择器
        title = item.select_one('h2').text.strip()
        link = item.select_one('a')['href']
        articles.append({'标题': title, '链接': link})
    
    # 保存到 Excel
    df = pd.DataFrame(articles)
    df.to_excel('采集结果.xlsx', index=False)
    print(f'采集完成，共 {len(articles)} 篇文章')

scrape_articles('https://example.com/blog')

四、定时任务自动化

使用 schedule 库实现定时执行：

import schedule
import time

def daily_task():
    """每日任务"""
    print('开始执行每日任务...')
    merge_excel_files('./reports', 'daily_report.xlsx')
    send_report('boss@company.com', '每日报表', '自动发送', 'daily_report.xlsx')

# 每天上午 9 点执行
schedule.every().day.at('09:00').do(daily_task)

while True:
    schedule.run_pending()
    time.sleep(60)