Appearance
【录播赠送】Python 办公自动化项目
学习目标
- 掌握 PDF 表格提取技术（PDFPlumber）
- 掌握 Excel 批量处理（openpyxl / pandas）
- 掌握 Word 文档生成（python-docx）
- 了解 AI 表格识别的基本用法
- 掌握 30+ 常用办公自动化脚本，覆盖数据处理、批量操作、重命名、跨格式转换
一、PDF 表格提取 —— PDFPlumber
1.1 基础概述
定义：PDFPlumber 是一个基于 pdfminer.six 的 PDF 解析库，支持提取文本、表格、图片等元素。相比 pdfminer 和 PyPDF2，PDFPlumber 对表格提取的精度更高。
bash
pip install pdfplumber1.2 提取文本内容
python
import pdfplumber
with pdfplumber.open("report.pdf") as pdf:
# 获取页面总数
total_pages = len(pdf.pages)
print(f"共 {total_pages} 页")
# 逐页提取文本
for i, page in enumerate(pdf.pages):
text = page.extract_text()
print(f"=== 第 {i + 1} 页 ===")
print(text[:200]) # 只打印前 200 字1.3 提取表格数据
python
import pdfplumber
import pandas as pd
with pdfplumber.open("财务报告.pdf") as pdf:
all_tables = []
for page_num, page in enumerate(pdf.pages):
tables = page.extract_tables()
for table in tables:
# table 是列表的列表，第一行通常是表头
df = pd.DataFrame(table[1:], columns=table[0])
df["来源页"] = page_num + 1
all_tables.append(df)
result = pd.concat(all_tables, ignore_index=True)
result.to_csv("表格数据.csv", index=False, encoding="utf-8-sig")
print(f"提取了 {len(result)} 行数据")1.4 表格提取的高级设置
python
import pdfplumber
with pdfplumber.open("复杂表格.pdf") as pdf:
for page in pdf.pages:
# 设置表格提取参数
table_settings = {
"vertical_strategy": "text", # 通过文本位置确定竖线
"horizontal_strategy": "text", # 通过文本位置确定横线
"snap_tolerance": 3, # 对齐容差
"intersection_tolerance": 3, # 交叉容差
"edge_min_length": 3, # 最小边长度
}
table = page.extract_table(table_settings)
if table:
for row in table:
print(" | ".join(cell or "" for cell in row))1.5 批量处理 PDF 并合并
python
import pdfplumber
import pandas as pd
from pathlib import Path
def extract_all_tables_from_folder(folder_path):
"""批量提取文件夹中所有 PDF 的表格并合并"""
all_data = []
for pdf_file in Path(folder_path).glob("*.pdf"):
print(f"处理: {pdf_file.name}")
with pdfplumber.open(pdf_file) as pdf:
for page in pdf.pages:
tables = page.extract_tables()
for table in tables:
if table and len(table) > 1: # 至少表头+数据
df = pd.DataFrame(table[1:], columns=table[0])
df["文件名"] = pdf_file.name
all_data.append(df)
if all_data:
result = pd.concat(all_data, ignore_index=True)
result.to_csv("合并表格.csv", index=False, encoding="utf-8-sig")
print(f"合并完成，共 {len(result)} 行")
else:
print("未找到表格数据")
extract_all_tables_from_folder("./pdfs")二、Excel 批量处理 —— openpyxl / pandas
2.1 openpyxl 基础
定义：openpyxl 是一个用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库，支持样式设置、公式、图表等高级功能。
bash
pip install openpyxlpython
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
# ===== 创建新工作簿 =====
wb = Workbook()
ws = wb.active
ws.title = "销售报表"
# 写入数据
ws["A1"] = "商品名称"
ws["B1"] = "销量"
ws["C1"] = "单价"
ws["D1"] = "总金额"
data = [
("iPhone 15", 120, 6999, 120 * 6999),
("MacBook Pro", 45, 14999, 45 * 14999),
("AirPods Pro", 300, 1999, 300 * 1999),
]
for row_idx, (name, qty, price, total) in enumerate(data, start=2):
ws.cell(row=row_idx, column=1, value=name)
ws.cell(row=row_idx, column=2, value=qty)
ws.cell(row=row_idx, column=3, value=price)
ws.cell(row=row_idx, column=4, value=total)
wb.save("销售报表.xlsx")2.2 openpyxl 样式设置
python
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
# 加载已有工作簿
wb = load_workbook("销售报表.xlsx")
ws = wb.active
# === 表头样式 ===
header_font = Font(name="微软雅黑", bold=True, size=12, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_align = Alignment(horizontal="center", vertical="center")
for col in range(1, 5):
cell = ws.cell(row=1, column=col)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_align
# === 数据区域样式 ===
thin_border = Border(
left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin"),
)
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, max_col=4):
for cell in row:
cell.border = thin_border
cell.alignment = Alignment(horizontal="center")
# === 列宽自适应 ===
for col_idx in range(1, 5):
max_length = 0
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=col_idx, max_col=col_idx):
for cell in row:
cell_length = len(str(cell.value or ""))
if cell_length > max_length:
max_length = cell_length
ws.column_dimensions[get_column_letter(col_idx)].width = max_length + 4
wb.save("销售报表_美化.xlsx")2.3 pandas 高效处理 Excel
python
import pandas as pd
from pathlib import Path
# ===== 读取 Excel =====
df = pd.read_excel("销售报表.xlsx", sheet_name="销售报表")
print(df.head())
# ===== 数据清洗 =====
# 读取所有 sheet
xls = pd.ExcelFile("原始数据.xlsx")
for sheet_name in xls.sheet_names:
df = pd.read_excel(xls, sheet_name=sheet_name)
# ===== 批量处理文件夹中的 Excel =====
def merge_excel_files(folder_path, output_file):
"""合并文件夹中所有 Excel 文件"""
all_dfs = []
for file in Path(folder_path).glob("*.xlsx"):
print(f"读取: {file.name}")
df = pd.read_excel(file)
df["来源文件"] = file.name
all_dfs.append(df)
result = pd.concat(all_dfs, ignore_index=True)
result.to_excel(output_file, index=False)
print(f"合并完成，共 {len(result)} 行")
merge_excel_files("./excel_files", "合并结果.xlsx")
# ===== 数据透视表 =====
df = pd.read_excel("订单数据.xlsx")
pivot = pd.pivot_table(
df,
values="金额",
index="商品分类",
columns="月份",
aggfunc="sum",
fill_value=0,
)
pivot.to_excel("透视表.xlsx")
# ===== 写入多 Sheet =====
with pd.ExcelWriter("多Sheet报表.xlsx") as writer:
df_销售.to_excel(writer, sheet_name="销售数据", index=False)
df_库存.to_excel(writer, sheet_name="库存数据", index=False)
df_客户.to_excel(writer, sheet_name="客户信息", index=False)2.4 openpyxl 高级技巧
python
# ===== 合并单元格 =====
ws.merge_cells("A1:D1")
ws["A1"] = "2024 年度销售汇总"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
# ===== 条件格式（模拟）=====
for row in range(2, ws.max_row + 1):
total = ws.cell(row=row, column=4).value
if total and total > 500000:
for col in range(1, 5):
ws.cell(row=row, column=col).font = Font(color="FF0000")
# ===== 插入图表 =====
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.title = "各商品销量"
chart.x_axis.title = "商品"
chart.y_axis.title = "销量"
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F2")
# ===== 公式 =====
ws["E2"] = "=B2*C2" # 公式直接写入
for row in range(3, ws.max_row + 1):
ws.cell(row=row, column=5).value = f"=B{row}*C{row}"三、Word 文档生成 —— python-docx
3.1 基础入门
定义：python-docx 是一个创建和修改 Microsoft Word .docx 文件的 Python 库。
bash
pip install python-docxpython
from docx import Document
from docx.shared import Inches, Pt, Cm, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
# ===== 创建文档 =====
doc = Document()
# 标题
doc.add_heading("工作报告", level=0)
# 段落
doc.add_paragraph("这是一份自动生成的 Python 工作报告。")
p = doc.add_paragraph()
p.add_run("粗体文本").bold = True
p.add_run(" 和 ")
p.add_run("红色文本").font.color.rgb = RGBColor(255, 0, 0)
# 二级标题
doc.add_heading("一、数据概况", level=1)
# 列表
doc.add_paragraph("2024 年销售额同比增长 25%", style="List Bullet")
doc.add_paragraph("新增客户 1,200 家", style="List Bullet")
doc.add_paragraph("用户满意度达到 4.8 分", style="List Bullet")
doc.save("工作报告.docx")3.2 插入表格
python
from docx import Document
from docx.shared import Cm
from docx.oxml.ns import qn
doc = Document()
doc.add_heading("销售数据表", level=1)
# 创建表格
headers = ["商品", "销量", "单价", "总金额"]
data = [
("iPhone 15", 120, 6999, 839880),
("MacBook Pro", 45, 14999, 674955),
("AirPods Pro", 300, 1999, 599700),
]
table = doc.add_table(rows=1 + len(data), cols=4, style="Light Grid Accent 1")
table.alignment = WD_TABLE_ALIGNMENT.CENTER
# 表头
for i, header in enumerate(headers):
cell = table.rows[0].cells[i]
cell.text = header
# 表头加粗
for paragraph in cell.paragraphs:
for run in paragraph.runs:
run.bold = True
# 数据行
for row_idx, row_data in enumerate(data, start=1):
for col_idx, value in enumerate(row_data):
table.rows[row_idx].cells[col_idx].text = str(value)
doc.save("带表格的报告.docx")3.3 从模板生成报告
python
from docx import Document
def generate_report(template_path, output_path, data):
"""
基于模板生成报告
模板中使用 {{placeholder}} 占位符
"""
doc = Document(template_path)
for paragraph in doc.paragraphs:
for key, value in data.items():
placeholder = "{{" + key + "}}"
if placeholder in paragraph.text:
paragraph.text = paragraph.text.replace(placeholder, str(value))
# 保留格式
for run in paragraph.runs:
if placeholder in run.text:
run.text = run.text.replace(placeholder, str(value))
for table in doc.tables:
for row in table.rows:
for cell in row.cells:
for key, value in data.items():
placeholder = "{{" + key + "}}"
if placeholder in cell.text:
cell.text = cell.text.replace(placeholder, str(value))
doc.save(output_path)
print(f"报告生成完成: {output_path}")
# 使用
data = {
"title": "2024 年度销售总结",
"total_sales": "21,345,678",
"growth_rate": "25.3%",
"customer_count": "1,200",
"top_product": "iPhone 15",
}
generate_report("模板.docx", "年度报告.docx", data)3.4 批量生成合同/通知书
python
from docx import Document
import pandas as pd
def batch_generate_contracts(excel_path, template_path, output_dir):
"""从 Excel 读取数据，批量生成合同"""
df = pd.read_excel(excel_path)
for idx, row in df.iterrows():
doc = Document(template_path)
# 替换占位符
placeholders = {
"contract_no": row["合同编号"],
"party_a": row["甲方"],
"party_b": row["乙方"],
"amount": str(row["金额"]),
"date": str(row["签订日期"]),
}
for paragraph in doc.paragraphs:
for key, value in placeholders.items():
placeholder = "{{" + key + "}}"
if placeholder in paragraph.text:
paragraph.text = paragraph.text.replace(placeholder, value)
output_path = f"{output_dir}/合同_{row['合同编号']}.docx"
doc.save(output_path)
print(f"生成: {output_path}")
batch_generate_contracts("合同数据.xlsx", "合同模板.docx", "./contracts")四、AI 表格识别
4.1 使用 PaddleOCR 识别图片表格
定义：AI 表格识别是指使用深度学习模型自动检测和识别图片中的表格结构及文字内容。
bash
pip install paddlepaddle paddleocrpython
from paddleocr import PaddleOCR
# 初始化 OCR（首次会自动下载模型）
ocr = PaddleOCR(use_angle_cls=True, lang="ch")
# 识别图片中的文字
img_path = "扫描件.png"
result = ocr.ocr(img_path, cls=True)
# 提取文字内容
for line in result[0]:
text = line[1][0]
confidence = line[1][1]
print(f"文本: {text}, 置信度: {confidence:.2f}")4.2 使用 Camelot 提取 PDF 表格（AI 辅助）
python
import camelot
# 提取 PDF 中的表格（基于 Lattice 算法，适合有线表格）
tables = camelot.read_pdf("财报.pdf", pages="1-5", flavor="lattice")
print(f"提取了 {tables.n} 个表格")
for i, table in enumerate(tables):
print(f"\n表格 {i + 1}:")
print(table.df.head())
table.to_csv(f"表格_{i + 1}.csv", encoding="utf-8-sig")
# Stream 模式（适合无线表格）
tables_stream = camelot.read_pdf("扫描件.pdf", pages="1", flavor="stream")4.3 图片转 Excel 完整流程
python
from paddleocr import PaddleOCR
import pandas as pd
import re
def image_table_to_excel(image_path, output_path):
"""图片表格识别并导出为 Excel"""
ocr = PaddleOCR(use_angle_cls=True, lang="ch")
result = ocr.ocr(image_path, cls=True)
# 提取带坐标的文本
cells = []
for line in result[0]:
bbox = line[0] # 四个角坐标
text = line[1][0]
y_center = (bbox[0][1] + bbox[2][1]) / 2 # 垂直中心
x_center = (bbox[0][0] + bbox[1][0]) / 2 # 水平中心
cells.append((y_center, x_center, text))
# 按行（y）分组，每行内按列（x）排序
rows = {}
for y, x, text in sorted(cells, key=lambda c: c[0]):
row_key = round(y / 30) * 30 # 30px 容差
if row_key not in rows:
rows[row_key] = []
rows[row_key].append((x, text))
# 构建 DataFrame
data = []
for y in sorted(rows.keys()):
row_cells = [text for _, text in sorted(rows[y], key=lambda c: c[0])]
data.append(row_cells)
df = pd.DataFrame(data)
df.to_excel(output_path, index=False, header=False)
print(f"识别完成，保存到: {output_path}")
image_table_to_excel("表格图片.jpg", "识别结果.xlsx")五、30+ 脚本案例库
5.1 数据处理基础类
python
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# === 1. CSV 合并器 ===
"""合并多个 CSV 文件为一个"""
import pandas as pd
from pathlib import Path
def merge_csv(folder, output="merged.csv"):
df_list = [pd.read_csv(f) for f in Path(folder).glob("*.csv")]
pd.concat(df_list, ignore_index=True).to_csv(output, index=False, encoding="utf-8-sig")
# === 2. Excel 拆分器 ===
"""按某列的值拆分 Excel 为多个文件"""
def split_excel_by_column(filepath, column, output_dir="./split"):
df = pd.read_excel(filepath)
Path(output_dir).mkdir(exist_ok=True)
for value, group in df.groupby(column):
group.to_excel(f"{output_dir}/{value}.xlsx", index=False)
# === 3. 数据去重器 ===
"""去除重复行并输出统计"""
def dedup_excel(filepath, key_columns, output="deduped.xlsx"):
df = pd.read_excel(filepath)
before = len(df)
df_clean = df.drop_duplicates(subset=key_columns)
print(f"去重前: {before} 行, 去重后: {len(df_clean)} 行, 移除: {before - len(df_clean)} 行")
df_clean.to_excel(output, index=False)
# === 4. 空值填充器 ===
"""用指定值填充 Excel 中的空值"""
def fillna_excel(filepath, fill_value=0, output="filled.xlsx"):
df = pd.read_excel(filepath)
df = df.fillna(fill_value)
df.to_excel(output, index=False)
# === 5. 列筛选器 ===
"""保留指定列并重新排序"""
def select_columns(filepath, columns, output="selected.xlsx"):
df = pd.read_excel(filepath)
df[columns].to_excel(output, index=False)
# === 6. 数据格式转换器 ===
"""将指定列的数据类型转换（如字符串转日期）"""
def convert_dtypes(filepath, col_types, output="converted.xlsx"):
df = pd.read_excel(filepath)
for col, dtype in col_types.items():
df[col] = df[col].astype(dtype)
df.to_excel(output, index=False)
# === 7. 统计汇总器 ===
"""生成描述性统计报表"""
def generate_stats(filepath, output="stats.xlsx"):
df = pd.read_excel(filepath)
stats = df.describe(include="all")
stats.to_excel(output)
# === 8. 编码检测与修复 ===
"""检测并转换文件编码"""
def fix_encoding(filepath, output="fixed.csv"):
import chardet
with open(filepath, "rb") as f:
result = chardet.detect(f.read(10000))
encoding = result["encoding"]
df = pd.read_csv(filepath, encoding=encoding)
df.to_csv(output, index=False, encoding="utf-8-sig")
print(f"原始编码: {encoding}")5.2 批量操作类
python
# === 9. 批量重命名 Excel Sheet ===
"""将工作簿中所有 Sheet 重命名为指定模式"""
from openpyxl import load_workbook
def rename_sheets(filepath, prefix="Sheet"):
wb = load_workbook(filepath)
for i, name in enumerate(wb.sheetnames, 1):
ws = wb[name]
ws.title = f"{prefix}{i}"
wb.save(filepath)
# === 10. 批量添加水印 ===
"""给 PDF 文件批量添加文字水印"""
def add_watermark_to_pdfs(folder, watermark_text="内部资料"):
from PyPDF2 import PdfReader, PdfWriter
from reportlab.pdfgen import canvas
import io
# 创建水印
packet = io.BytesIO()
c = canvas.Canvas(packet, pagesize=(595, 842))
c.setFont("Helvetica", 40)
c.setFillAlpha(0.1)
c.rotate(45)
c.drawString(200, 400, watermark_text)
c.save()
for pdf_file in Path(folder).glob("*.pdf"):
reader = PdfReader(pdf_file)
writer = PdfWriter()
for page in reader.pages:
page.merge_page(PdfReader(packet).pages[0])
writer.add_page(page)
with open(pdf_file, "wb") as f:
writer.write(f)
# === 11. 批量图片压缩 ===
"""压缩文件夹中的所有图片"""
def compress_images(folder, quality=85):
from PIL import Image
for img_file in Path(folder).glob("*.{jpg,jpeg,png}"):
img = Image.open(img_file)
img.save(img_file, optimize=True, quality=quality)
print(f"压缩: {img_file.name}")
# === 12. 批量 PDF 转图片 ===
def pdf_to_images(pdf_path, output_dir="./pdf_images"):
from pdf2image import convert_from_path
Path(output_dir).mkdir(exist_ok=True)
images = convert_from_path(pdf_path, dpi=200)
for i, img in enumerate(images):
img.save(f"{output_dir}/page_{i+1}.png", "PNG")5.3 重命名实战类
python
# === 13. 批量文件重命名（模式替换）===
"""将文件名中的指定文本替换为新的文本"""
import re
from pathlib import Path
def rename_replace(folder, old, new):
for f in Path(folder).iterdir():
if f.is_file():
new_name = f.name.replace(old, new)
f.rename(f.parent / new_name)
print(f"{f.name} → {new_name}")
# === 14. 按创建日期重命名 ===
"""用文件的创建日期作为前缀重命名"""
def rename_by_date(folder):
for f in Path(folder).iterdir():
if f.is_file():
import os
timestamp = os.path.getctime(f)
from datetime import datetime
date_str = datetime.fromtimestamp(timestamp).strftime("%Y%m%d")
new_name = f"{date_str}_{f.name}"
f.rename(f.parent / new_name)
# === 15. 序号重命名 ===
"""批量重命名为 001, 002... 格式"""
def rename_sequential(folder, prefix="file", ext_filter=None):
for i, f in enumerate(sorted(Path(folder).iterdir()), 1):
if f.is_file():
if ext_filter and f.suffix not in ext_filter:
continue
new_name = f"{prefix}_{i:03d}{f.suffix}"
f.rename(f.parent / new_name)
# === 16. 文件夹按文件名归类 ===
"""根据文件名模式将文件移动到对应的子文件夹"""
def organize_by_pattern(folder, pattern=r"(\d{4})"):
for f in Path(folder).iterdir():
if f.is_file():
match = re.search(pattern, f.name)
if match:
subfolder = Path(folder) / match.group(1)
subfolder.mkdir(exist_ok=True)
f.rename(subfolder / f.name)
# === 17. 去除文件名中的特殊字符 ===
def sanitize_filenames(folder):
for f in Path(folder).iterdir():
if f.is_file():
clean_name = re.sub(r'[<>:"/\\|?*]', "", f.stem)
clean_name = re.sub(r"\s+", "_", clean_name)
new_name = clean_name + f.suffix
f.rename(f.parent / new_name)5.4 跨格式应用类
python
# === 18. TXT 转 Excel ===
"""将文本文件按分隔符转为 Excel"""
def txt_to_excel(txt_path, sep="\t", output="output.xlsx"):
df = pd.read_csv(txt_path, sep=sep)
df.to_excel(output, index=False)
# === 19. JSON 转 Excel ===
def json_to_excel(json_path, output="output.xlsx"):
df = pd.read_json(json_path)
df.to_excel(output, index=False)
# === 20. HTML 表格转 Excel ===
def html_table_to_excel(html_path, output="output.xlsx"):
tables = pd.read_html(html_path)
for i, df in enumerate(tables):
df.to_excel(f"table_{i+1}.xlsx", index=False)
# === 21. Word 提取所有表格转 Excel ===
def word_tables_to_excel(docx_path, output="word_tables.xlsx"):
from docx import Document
doc = Document(docx_path)
with pd.ExcelWriter(output) as writer:
for i, table in enumerate(doc.tables):
data = [[cell.text for cell in row.cells] for row in table.rows]
df = pd.DataFrame(data[1:], columns=data[0])
df.to_excel(writer, sheet_name=f"表格{i+1}", index=False)
# === 22. Excel 转 Word 报告 ===
def excel_to_word_report(excel_path, template_path, output="report.docx"):
df = pd.read_excel(excel_path)
doc = Document(template_path)
doc.add_heading("数据明细", level=1)
# 插入表格
table = doc.add_table(rows=1 + len(df), cols=len(df.columns))
for j, col in enumerate(df.columns):
table.rows[0].cells[j].text = str(col)
for i in range(len(df)):
for j in range(len(df.columns)):
table.rows[i + 1].cells[j].text = str(df.iloc[i, j])
doc.save(output)
# === 23. PDF 批量转 Word ===
def pdf_to_word_batch(folder):
import pdfplumber
from docx import Document
for pdf_file in Path(folder).glob("*.pdf"):
doc = Document()
with pdfplumber.open(pdf_file) as pdf:
for page in pdf.pages:
text = page.extract_text()
if text:
doc.add_paragraph(text)
output_path = pdf_file.with_suffix(".docx")
doc.save(output_path)
print(f"转换: {pdf_file.name} → {output_path.name}")
# === 24. 图片 OCR 转 Word ===
def image_ocr_to_word(image_path, output="ocr_result.docx"):
from paddleocr import PaddleOCR
from docx import Document
ocr = PaddleOCR(use_angle_cls=True, lang="ch")
result = ocr.ocr(image_path, cls=True)
doc = Document()
doc.add_heading("OCR 识别结果", level=1)
for line in result[0]:
text = line[1][0]
doc.add_paragraph(text)
doc.save(output)
# === 25. 邮件正文转 Excel ===
"""从 Outlook/EML 文件中提取表格"""
def email_table_to_excel(eml_path, output="email_table.xlsx"):
import email
from email import policy
with open(eml_path, "rb") as f:
msg = email.message_from_binary_file(f, policy=policy.default)
for part in msg.walk():
if part.get_content_type() == "text/html":
html_content = part.get_content()
tables = pd.read_html(html_content)
for i, df in enumerate(tables):
df.to_excel(f"email_table_{i+1}.xlsx", index=False)5.5 实用工具类
python
# === 26. 文件夹大小统计 ===
def folder_size_report(path, output="folder_size.xlsx"):
data = []
for f in Path(path).rglob("*"):
if f.is_file():
data.append({"路径": str(f), "大小(MB)": round(f.stat().st_size / 1024 / 1024, 2)})
pd.DataFrame(data).to_excel(output, index=False)
# === 27. 文件差异对比器 ===
"""对比两个文件夹，输出差异文件"""
def compare_folders(folder1, folder2, output="diff.xlsx"):
files1 = {f.name: f for f in Path(folder1).iterdir() if f.is_file()}
files2 = {f.name: f for f in Path(folder2).iterdir() if f.is_file()}
records = []
for name in set(list(files1.keys()) + list(files2.keys())):
if name in files1 and name in files2:
if files1[name].stat().st_size != files2[name].stat().st_size:
records.append({"文件": name, "状态": "大小不同"})
elif name in files1:
records.append({"文件": name, "状态": "仅在文件夹1"})
else:
records.append({"文件": name, "状态": "仅在文件夹2"})
pd.DataFrame(records).to_excel(output, index=False)
# === 28. 定时备份脚本 ===
"""每小时备份指定文件夹到备份目录"""
def auto_backup(source, backup_root):
from datetime import datetime
import shutil
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
backup_dir = Path(backup_root) / f"backup_{timestamp}"
shutil.copytree(source, backup_dir)
print(f"备份完成: {backup_dir}")
# === 29. 文件搜索器 ===
"""搜索包含关键字的文件内容并输出到 Excel"""
def search_in_files(folder, keyword, ext=".txt"):
results = []
for f in Path(folder).rglob(f"*{ext}"):
try:
content = f.read_text(encoding="utf-8", errors="ignore")
if keyword in content:
results.append({"文件": str(f), "匹配内容": content[:100]})
except:
pass
pd.DataFrame(results).to_excel("search_results.xlsx", index=False)
# === 30. 日志分析器 ===
"""分析日志文件，统计各等级日志的数量"""
def analyze_log(log_path):
from collections import Counter
pattern = re.compile(r"(INFO|WARNING|ERROR|DEBUG|CRITICAL)")
levels = []
with open(log_path, encoding="utf-8", errors="ignore") as f:
for line in f:
match = pattern.search(line)
if match:
levels.append(match.group(1))
stats = Counter(levels)
df = pd.DataFrame(stats.items(), columns=["等级", "数量"])
df.to_excel("log_stats.xlsx", index=False)
print(df)
# === 31. 文件类型统计 ===
"""统计文件夹中各类型文件的数量"""
def file_type_stats(folder, output="file_stats.xlsx"):
stats = {}
for f in Path(folder).rglob("*"):
if f.is_file():
ext = f.suffix.lower() or "(无后缀)"
stats[ext] = stats.get(ext, 0) + 1
df = pd.DataFrame(stats.items(), columns=["类型", "数量"])
df = df.sort_values("数量", ascending=False)
df.to_excel(output, index=False)小结
- PDF 处理：PDFPlumber 提取文本和表格，支持批量处理和自定义提取参数
- Excel 处理：openpyxl 负责样式美化，pandas 负责数据处理和分析
- Word 生成：python-docx 从模板生成报告，支持表格、图文混排
- AI 表格识别：PaddleOCR 识别图片表格，Camelot 处理 PDF 表格
- 30+ 脚本库：覆盖数据处理、批量操作、文件重命名、跨格式转换四大类场景
练习
- 财务报表提取：给定一份包含表格的 PDF 财务报告，使用 PDFPlumber 提取所有表格并保存为 Excel。
- 批量报表生成：使用 openpyxl 生成 12 个月的销售报表（每月一个 Sheet），包含表头美化、列宽自适应和合计公式。
- 合同批量生成：使用 python-docx 从模板批量生成 10 份合同，数据从 Excel 读取。
- 图片表格识别：拍一张表格照片，使用 PaddleOCR 识别其中的文字并导出为 Excel。
- 自动化脚本组合：编写一个脚本，每天定时：
- 从指定邮箱下载附件中的 CSV 文件
- 合并到总数据表中
- 生成月度统计报表（Excel）
- 用 Word 生成分析报告并发送邮件