Python 自动化办公实战：用代码解放重复劳动
每天花大量时间处理 Excel 表格、整理文件、发送邮件？
这些重复工作其实都可以交给 Python 自动完成。今天分享几个我日常使用频率最高的自动化脚本，拿来就能用。
环境准备
安装依赖：
bash
pip install openpyxl pandas python-docx pillow pyautogui schedule每个库的用途：
| 库 | 用途 |
|------|------|
| openpyxl | 读写 Excel 文件 |
| pandas | 数据处理与分析 |
| python-docx | 操作 Word 文档 |
| pyautogui | GUI 自动化操作 |
| schedule | 定时任务调度 |
1. Excel 批量处理
合并多个 Excel 文件
python
import pandas as pd
from pathlib import Path
def merge_excel_files(input_dir, output_file):
"""合并目录下所有 Excel 文件"""
all_data = []
for file in Path(input_dir).glob('*.xlsx'):
df = pd.read_excel(file)
df['来源文件'] = file.name
all_data.append(df)
print(f'已读取: {file.name} ({len(df)} 行)')
if all_data:
result = pd.concat(all_data, ignore_index=True)
result.to_excel(output_file, index=False)
print(f'合并完成! 共 {len(result)} 行数据 -> {output_file}')
else:
print('未找到 Excel 文件')
merge_excel_files('./销售报表', './汇总报表.xlsx')拆分大 Excel 文件
python
import pandas as pd
def split_excel(input_file, chunk_size=1000):
"""按行数拆分大 Excel 文件"""
df = pd.read_excel(input_file)
total_rows = len(df)
file_count = (total_rows + chunk_size - 1) // chunk_size
for i in range(file_count):
start = i * chunk_size
end = min(start + chunk_size, total_rows)
chunk = df.iloc[start:end]
output_name = f'{Path(input_file).stem}_part{i+1}.xlsx'
chunk.to_excel(output_name, index=False)
print(f'已生成: {output_name} ({len(chunk)} 行)')
print(f'拆分完成，共 {file_count} 个文件')
split_excel('年度数据.xlsx', 2000)2. 文件整理与重命名
按类型自动归类
python
from pathlib import Path
import shutil
def organize_downloads(directory):
"""按文件类型自动归类"""
rules = {
'图片': ['.jpg', '.jpeg', '.png', '.gif', '.webp'],
'文档': ['.pdf', '.doc', '.docx', '.txt', '.md'],
'表格': ['.xlsx', '.xls', '.csv'],
'压缩包': ['.zip', '.rar', '.7z', '.tar.gz'],
'程序': ['.exe', '.msi', '.dmg', '.app'],
'视频': ['.mp4', '.avi', '.mkv', '.mov'],
}
for file in Path(directory).iterdir():
if not file.is_file():
continue
moved = False
for folder, exts in rules.items():
if file.suffix.lower() in exts:
target = Path(directory) / folder
target.mkdir(exist_ok=True)
shutil.move(str(file), str(target / file.name))
print(f'{file.name} -> {folder}/')
moved = True
break
if not moved:
other = Path(directory) / '其他'
other.mkdir(exist_ok=True)
shutil.move(str(file), str(other / file.name))
print(f'{file.name} -> 其他/')
organize_downloads('C:/Users/Admin/Downloads')批量重命名
python
import re
from pathlib import Path
def batch_rename(directory, pattern, replacement):
"""正则批量重命名文件"""
count = 0
for file in Path(directory).iterdir():
if not file.is_file():
continue
new_name = re.sub(pattern, replacement, file.name)
if new_name != file.name:
file.rename(file.parent / new_name)
print(f'{file.name} -> {new_name}')
count += 1
print(f'共重命名 {count} 个文件')
# 示例：将 "报告20240101.xlsx" 改为 "20240101-报告.xlsx"
batch_rename('./docs', r'^(.+?)(\d{8})\.', r'\2-\1.')3. 自动生成 Word 报告
python
from docx import Document
from docx.shared import Inches, Pt, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
import datetime
def generate_report(title, data, output_file):
"""生成格式化的 Word 报告"""
doc = Document()
# 标题
heading = doc.add_heading(title, 0)
heading.alignment = WD_ALIGN_PARAGRAPH.CENTER
# 日期
date_para = doc.add_paragraph()
date_para.alignment = WD_ALIGN_PARAGRAPH.RIGHT
date_para.add_run(f'生成日期：{datetime.date.today()}').font.size = Pt(10)
doc.add_paragraph() # 空行
# 数据表格
if data:
table = doc.add_table(rows=1, cols=len(data[0]))
table.style = 'Light Grid Accent 1'
# 表头
hdr_cells = table.rows[0].cells
for i, key in enumerate(data[0].keys()):
hdr_cells[i].text = key
# 数据行
for item in data:
row_cells = table.add_row().cells
for i, value in enumerate(item.values()):
row_cells[i].text = str(value)
doc.save(output_file)
print(f'报告已生成: {output_file}')
# 示例
report_data = [
{'月份': '一月', '收入': 45000, '支出': 32000, '利润': 13000},
{'月份': '二月', '收入': 52000, '支出': 35000, '利润': 17000},
{'月份': '三月', '收入': 48000, '支出': 31000, '利润': 17000},
]
generate_report('季度财务报告', report_data, '季度报告.docx')4. 定时任务 + 邮件通知
python
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_email(sender, password, receiver, subject, body, attachments=None):
"""发送邮件（支持附件）"""
msg = MIMEMultipart()
msg['From'] = sender
msg['To'] = receiver
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain', 'utf-8'))
if attachments:
for filepath in attachments:
with open(filepath, '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="{Path(filepath).name}"'
)
msg.attach(part)
with smtplib.SMTP_SSL('smtp.qq.com', 465) as server:
server.login(sender, password)
server.send_message(msg)
print(f'邮件已发送给 {receiver}')
# 每周一早上9点自动发送周报
def weekly_report_job():
generate_report('周报', get_weekly_data(), '周报.docx')
send_email(
sender='your@qq.com',
password='your_password',
receiver='boss@company.com',
subject='本周工作汇报',
body='请查收附件中的周报。',
attachments=['周报.docx']
)
schedule.every().monday.at('09:00').do(weekly_report_job)
while True:
schedule.run_pending()
time.sleep(60)5. 批量图片处理
python
from PIL import Image, ImageFilter
from pathlib import Path
def batch_process_images(input_dir, output_dir, size=(800, 800)):
"""批量调整图片尺寸并添加水印"""
output_path = Path(output_dir)
output_path.mkdir(exist_ok=True)
for img_file in Path(input_dir).glob('*.{jpg,png,jpeg,webp}'):
with Image.open(img_file) as img:
# 调整尺寸（保持比例）
img.thumbnail(size, Image.LANCZOS)
# 添加简单水印
from PIL import ImageDraw, ImageFont
draw = ImageDraw.Draw(img)
text = '© Your Name'
# 在右下角添加水印
bbox = draw.textbbox((0, 0), text)
tw, th = bbox[2] - bbox[0], bbox[3] - bbox[1]
x = img.width - tw - 10
y = img.height - th - 10
draw.text((x, y), text, fill=(255, 255, 255, 128))
# 保存
output_file = output_path / img_file.name
img.save(output_file, quality=85)
print(f'已处理: {img_file.name}')
batch_process_images('./原图', './处理后', (1200, 1200))6. 综合实战：日报自动生成
把上面的功能组合起来，做一个每天自动运行的日报系统：
python
import pandas as pd
from pathlib import Path
from docx import Document
import smtplib
import schedule
import json
class DailyReportSystem:
def __init__(self, config_file):
with open(config_file, 'r', encoding='utf-8') as f:
self.config = json.load(f)
def collect_data(self):
"""从多个 Excel 收集数据"""
all_data = []
for file_pattern in self.config['data_sources']:
for file in Path().glob(file_pattern):
df = pd.read_excel(file)
all_data.append(df)
return pd.concat(all_data, ignore_index=True) if all_data else pd.DataFrame()
def generate_report(self, data):
"""生成 Word 日报"""
doc = Document()
doc.add_heading(f'日报 - {datetime.date.today()}', 0)
# 汇总数据
table = doc.add_table(rows=1, cols=len(data.columns))
table.style = 'Light Grid Accent 1'
for i, col in enumerate(data.columns):
table.rows[0].cells[i].text = col
for _, row in data.iterrows():
cells = table.add_row().cells
for i, val in enumerate(row):
cells[i].text = str(val)
output = f'日报_{datetime.date.today()}.docx'
doc.save(output)
return output
def run(self):
data = self.collect_data()
report_file = self.generate_report(data)
print(f'日报已生成: {report_file}')
return report_file
# 每天下午5点自动生成
report_system = DailyReportSystem('config.json')
schedule.every().day.at('17:00').do(report_system.run)
print('日报系统已启动，每天 17:00 自动运行...')注意事项
路径处理
使用 pathlib 处理路径，跨平台兼容：
python
# 推荐
from pathlib import Path
data_dir = Path('data') / 'reports'
# 而不是
import os
data_dir = os.path.join('data', 'reports') # 可读性差异常处理
文件操作一定要加异常处理：
python
def safe_read_excel(filepath):
try:
return pd.read_excel(filepath)
except FileNotFoundError:
print(f'文件不存在: {filepath}')
return pd.DataFrame()
except Exception as e:
print(f'读取失败 {filepath}: {e}')
return pd.DataFrame()编码问题
处理中文文件时注意编码：
python
# 读取 CSV 时指定编码
df = pd.read_csv('数据.csv', encoding='utf-8-sig')
# 处理乱码
df = pd.read_csv('数据.csv', encoding='gbk')总结
Python 自动化办公的核心思路是：识别重复 → 写脚本 → 定时执行。
别想着一次搞定所有自动化，从一个最小的脚本开始，慢慢完善。每周省下 2 小时，一年就是 100 小时。
推荐指数：⭐⭐⭐⭐⭐
项目代码：https://github.com/your-repo/python-office-automation
💬 评论区 (0)
暂无评论，快来抢沙发吧！