从零到接单 12：办公自动化——Excel、Word、PDF 批量处理
pythontutorial自动化接单ExcelWordPDF办公自动化
系列目录：本文是「从零到接单：Python 自动化与爬虫实战」系列的第 12 篇。如果说爬虫是"数据的搬运工"，办公自动化就是"数据的加工厂"。用 Python 自动处理 Excel、Word、PDF，帮客户把几天的活缩到几分钟——这就是自动化接单最赚钱的原因。
在接单平台（猪八戒、Fiverr、淘宝）上，"Excel 批量处理"、"Word 报告自动生成"、"PDF 数据提取"是最常见的需求。客户愿意为"省时间"付钱。
一、Excel 自动化（openpyxl 进阶）
第 11 篇已讲了 openpyxl 基础读写，这篇聚焦批量处理场景。
场景 1：合并多个 Excel 文件
from openpyxl import load_workbook, Workbook
from pathlib import Path
def merge_excel_files(folder, output="merged.xlsx"):
"""合并文件夹中所有 Excel 文件到一张表"""
wb_new = Workbook()
ws_new = wb_new.active
ws_new.title = "汇总"
header_written = False
total_rows = 0
for excel_file in Path(folder).glob("*.xlsx"):
print(f"处理：{excel_file.name}")
wb = load_workbook(excel_file)
ws = wb.active
for row in ws.iter_rows(values_only=True):
if not header_written:
# 写表头（只写一次）
ws_new.append(list(row))
header_written = True
elif row != tuple(ws[1]): # 跳过重复的表头
# 跳过空行和表头行
if any(cell is not None for cell in row):
ws_new.append(list(row))
total_rows += 1
wb.close()
wb_new.save(output)
print(f"✅ 合并完成！共 {total_rows} 条数据，保存到 {output}")
# merge_excel_files("./reports/")
场景 2：按条件拆分 Excel
from openpyxl import Workbook
import csv
def split_excel_by_column(filepath, column_name, output_dir="split_output"):
"""按某列的值将 Excel 拆分为多个文件"""
from openpyxl import load_workbook
Path(output_dir).mkdir(exist_ok=True)
wb = load_workbook(filepath)
ws = wb.active
# 找到表头
headers = [cell.value for cell in ws[1]]
col_idx = headers.index(column_name) + 1
# 分组数据
groups = {}
for row in ws.iter_rows(min_row=2, values_only=True):
key = row[col_idx - 1]
if key is None:
continue
if key not in groups:
groups[key] = []
groups[key].append(list(row))
# 为每组创建文件
for key, rows in groups.items():
wb_out = Workbook()
ws_out = wb_out.active
ws_out.append(headers)
for row in rows:
ws_out.append(row)
filename = Path(output_dir) / f"{key}.xlsx"
wb_out.save(filename)
print(f"✅ {key}: {len(rows)} 行 → {filename}")
wb.close()
print(f"\n完成！共拆分为 {len(groups)} 个文件")
# split_excel_by_column("employees.xlsx", "城市")
场景 3：自动化报表生成
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter
def generate_sales_report(data, output="销售报表.xlsx"):
"""生成带有样式和图表的销售报表"""
wb = Workbook()
ws = wb.active
ws.title = "销售数据"
# 样式定义
header_font = Font(bold=True, color="FFFFFF", size=11)
header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
header_align = Alignment(horizontal="center", vertical="center")
thin_border = Border(
left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin"),
)
# 写标题
ws.merge_cells("A1:F1")
title_cell = ws.cell(row=1, column=1, value="2026年7月销售报表")
title_cell.font = Font(bold=True, size=14, color="2F5496")
title_cell.alignment = Alignment(horizontal="center")
# 写表头
headers = ["日期", "产品", "数量", "单价", "金额", "销售员"]
for col, header in enumerate(headers, 1):
cell = ws.cell(row=3, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_align
cell.border = thin_border
# 写数据
for row_idx, item in enumerate(data, 4):
ws.cell(row=row_idx, column=1, value=item["日期"]).border = thin_border
ws.cell(row=row_idx, column=2, value=item["产品"]).border = thin_border
ws.cell(row=row_idx, column=3, value=item["数量"]).border = thin_border
price_cell = ws.cell(row=row_idx, column=4, value=item["单价"])
price_cell.number_format = '¥#,##0.00'
price_cell.border = thin_border
amount_cell = ws.cell(row=row_idx, column=5)
amount_cell.value = f"=C{row_idx}*D{row_idx}" # 公式！
amount_cell.number_format = '¥#,##0.00'
amount_cell.border = thin_border
ws.cell(row=row_idx, column=6, value=item["销售员"]).border = thin_border
last_data_row = len(data) + 3
# 汇总行
summary_row = last_data_row + 2
ws.cell(row=summary_row, column=1, value="合计").font = Font(bold=True)
ws.cell(row=summary_row, column=3, value=f"=SUM(C4:C{last_data_row})").font = Font(bold=True)
ws.cell(row=summary_row, column=5, value=f"=SUM(E4:E{last_data_row})").font = Font(bold=True)
ws.cell(row=summary_row, column=5).number_format = '¥#,##0.00'
# 图表
chart = BarChart()
chart.title = "各产品销售数量"
chart.y_axis.title = "数量"
chart.x_axis.title = "产品"
chart.style = 10
data_ref = Reference(ws, min_col=3, min_row=3, max_row=last_data_row)
cats_ref = Reference(ws, min_col=2, min_row=4, max_row=last_data_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
ws.add_chart(chart, f"A{summary_row + 2}")
# 调整列宽
col_widths = [12, 20, 8, 10, 12, 10]
for col, width in enumerate(col_widths, 1):
ws.column_dimensions[get_column_letter(col)].width = width
wb.save(output)
print(f"✅ 报表已生成：{output}")
# 测试数据
test_data = [
{"日期": "2026-07-01", "产品": "Python 爬虫服务", "数量": 3, "单价": 500, "销售员": "张三"},
{"日期": "2026-07-05", "产品": "Excel 自动化脚本", "数量": 5, "单价": 300, "销售员": "李四"},
{"日期": "2026-07-10", "产品": "数据清洗服务", "数量": 2, "单价": 800, "销售员": "张三"},
{"日期": "2026-07-15", "产品": "Python 爬虫服务", "数量": 4, "单价": 500, "销售员": "王五"},
{"日期": "2026-07-20", "产品": "RPA 脚本开发", "数量": 1, "单价": 2000, "销售员": "李四"},
]
# generate_sales_report(test_data)
二、Word 自动化（python-docx）
pip install python-docx
生成 Word 文档
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
def generate_report(data, output="报告.docx"):
"""生成格式化的 Word 报告"""
doc = Document()
# ── 标题 ──
title = doc.add_heading("XX项目数据采集报告", level=0)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER
# ── 副标题 ──
subtitle = doc.add_paragraph()
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = subtitle.add_run("编制单位：XX科技 | 日期：2026年7月")
run.font.size = Pt(10)
run.font.color.rgb = RGBColor(128, 128, 128)
doc.add_paragraph() # 空行
# ── 正文 ──
doc.add_heading("一、采集概况", level=1)
doc.add_paragraph(
f"本次共计采集数据 {data['total']} 条，"
f"成功 {data['success']} 条，失败 {data['fail']} 条，"
f"成功率 {data['success'] / data['total'] * 100:.1f}%。"
)
# ── 表格 ──
doc.add_heading("二、数据明细", level=1)
table = doc.add_table(rows=1, cols=4, style="Light Grid Accent 1")
table.alignment = WD_TABLE_ALIGNMENT.CENTER
# 表头
header_cells = table.rows[0].cells
for i, text in enumerate(["序号", "标题", "来源", "采集时间"]):
header_cells[i].text = text
for paragraph in header_cells[i].paragraphs:
for run in paragraph.runs:
run.font.bold = True
run.font.size = Pt(9)
# 数据行
for idx, item in enumerate(data["items"][:20], 1): # 只展示前 20 条
row = table.add_row()
row.cells[0].text = str(idx)
row.cells[1].text = item.get("title", "")[:50]
row.cells[2].text = item.get("source", "")
row.cells[3].text = item.get("time", "")
# ── 图片 ──
doc.add_heading("三、数据可视化", level=1)
doc.add_paragraph("以下为采集数据的分布统计：")
# doc.add_picture("chart.png", width=Inches(5)) # 如果有图表图片
# ── 结论 ──
doc.add_heading("四、结论与建议", level=1)
doc.add_paragraph(
"根据本次采集的数据分析，建议从以下三个方面优化数据采集策略：\n"
"1. 增加代理 IP 池以提高采集成功率\n"
"2. 优化请求频率避免触发反爬机制\n"
"3. 引入自动化监控实现异常实时告警"
)
# ── 页脚 ──
doc.add_paragraph()
footer_text = doc.add_paragraph()
footer_text.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = footer_text.add_run("— 报告结束 —")
run.font.size = Pt(9)
run.font.color.rgb = RGBColor(128, 128, 128)
doc.save(output)
print(f"✅ 报告已生成：{output}")
# 测试
sample_data = {
"total": 1000,
"success": 985,
"fail": 15,
"items": [
{"title": "Python 爬虫入门教程", "source": "example.com", "time": "2026-07-16 10:00"},
{"title": "办公自动化实战", "source": "blog.dev", "time": "2026-07-16 10:01"},
] * 10
}
# generate_report(sample_data)
读取 Word 文档
from docx import Document
def extract_text_from_docx(filepath):
"""提取 Word 文档中的纯文本"""
doc = Document(filepath)
text = []
for para in doc.paragraphs:
if para.text.strip():
text.append(para.text)
return "\n".join(text)
def extract_tables_from_docx(filepath):
"""提取 Word 文档中的所有表格"""
doc = Document(filepath)
tables_data = []
for table in doc.tables:
table_data = []
for row in table.rows:
row_data = [cell.text for cell in row.cells]
table_data.append(row_data)
tables_data.append(table_data)
return tables_data
三、PDF 处理
读取 PDF
pip install pdfplumber PyPDF2
import pdfplumber
# pdfplumber：提取文本和表格（推荐）
def read_pdf_with_pdfplumber(filepath):
with pdfplumber.open(filepath) as pdf:
all_text = []
all_tables = []
for i, page in enumerate(pdf.pages):
# 提取文本
text = page.extract_text()
if text:
all_text.append(f"--- 第 {i+1} 页 ---\n{text}")
# 提取表格
tables = page.extract_tables()
for table in tables:
if table:
all_tables.append(table)
return "\n".join(all_text), all_tables
# PyPDF2：基础操作（合并、拆分、旋转）
from PyPDF2 import PdfReader, PdfWriter
def merge_pdfs(file_list, output="merged.pdf"):
"""合并多个 PDF"""
writer = PdfWriter()
for file in file_list:
reader = PdfReader(file)
for page in reader.pages:
writer.add_page(page)
with open(output, "wb") as f:
writer.write(f)
print(f"✅ 已合并 {len(file_list)} 个文件 → {output}")
def split_pdf(filepath, output_prefix="page"):
"""拆分 PDF 为单页"""
reader = PdfReader(filepath)
for i, page in enumerate(reader.pages):
writer = PdfWriter()
writer.add_page(page)
with open(f"{output_prefix}_{i+1}.pdf", "wb") as f:
writer.write(f)
print(f"✅ 已拆分为 {len(reader.pages)} 页")
def extract_pdf_metadata(filepath):
"""提取 PDF 元数据"""
reader = PdfReader(filepath)
return {
"页数": len(reader.pages),
"作者": reader.metadata.author,
"标题": reader.metadata.title,
"创建者": reader.metadata.creator,
}
创建 PDF
pip install reportlab
from reportlab.lib.pagesizes import A4
from reportlab.pdfgen import canvas
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
def create_pdf_report(output="report.pdf"):
"""生成 PDF 报告"""
# 注册中文字体（需要下载对应的 .ttf 文件）
# pdfmetrics.registerFont(TTFont("SimSun", "SimSun.ttf"))
c = canvas.Canvas(output, pagesize=A4)
width, height = A4
# 标题
c.setFont("Helvetica-Bold", 20)
c.drawString(50, height - 50, "数据采集报告")
# 正文
c.setFont("Helvetica", 12)
y = height - 90
lines = [
"采集时间：2026年7月16日",
"采集数量：1,000条",
"成功率：98.5%",
"",
"本报告记录了本次数据采集的详细过程和结果。",
"所有数据已按照要求的格式存储于附件中。",
]
for line in lines:
c.drawString(50, y, line)
y -= 20
# 页脚
c.setFont("Helvetica", 8)
c.drawString(50, 30, f"第 1 页 | 生成时间：2026-07-16")
c.save()
print(f"✅ PDF 已生成：{output}")
# create_pdf_report()
四、实战：自动化接单工具模板
把常见的办公自动化功能打包成一个工具类：
"""
office_tools.py —— 办公自动化工具集
用法示例：
tools = OfficeTools()
tools.merge_excels("./data/", "merged.xlsx")
tools.excel_to_csv("data.xlsx", "data.csv")
"""
from pathlib import Path
import csv
class OfficeTools:
"""办公自动化工具箱"""
@staticmethod
def excel_to_csv(excel_path, csv_path=None):
"""Excel 转 CSV"""
from openpyxl import load_workbook
wb = load_workbook(excel_path)
ws = wb.active
if csv_path is None:
csv_path = Path(excel_path).with_suffix(".csv")
with open(csv_path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
for row in ws.iter_rows(values_only=True):
writer.writerow(row)
wb.close()
return csv_path
@staticmethod
def csv_to_excel(csv_path, excel_path=None):
"""CSV 转 Excel"""
from openpyxl import Workbook
if excel_path is None:
excel_path = Path(csv_path).with_suffix(".xlsx")
wb = Workbook()
ws = wb.active
with open(csv_path, "r", encoding="utf-8-sig") as f:
reader = csv.reader(f)
for row in reader:
ws.append(row)
wb.save(excel_path)
return excel_path
@staticmethod
def extract_text_from_pdf(pdf_path, output_txt=None):
"""PDF 提取文本"""
import pdfplumber
if output_txt is None:
output_txt = Path(pdf_path).with_suffix(".txt")
with pdfplumber.open(pdf_path) as pdf:
text = "\n".join(
page.extract_text() or "" for page in pdf.pages
)
with open(output_txt, "w", encoding="utf-8") as f:
f.write(text)
return output_txt, text
if __name__ == "__main__":
tools = OfficeTools()
print("OfficeTools 初始化完成 ✅")
print("可用方法：excel_to_csv, csv_to_excel, extract_text_from_pdf")
总结
| 场景 | 工具库 | 关键操作 |
|------|--------|----------|
| Excel 读写 | openpyxl | Workbook, ws.append(), 样式设置 |
| Word 生成 | python-docx | Document(), add_heading(), add_table() |
| PDF 读取 | pdfplumber | extract_text(), extract_tables() |
| PDF 生成 | reportlab | canvas.Canvas |
| PDF 合并/拆分 | PyPDF2 | PdfReader, PdfWriter |
办公自动化是接单的"现金牛"——需求大、单价高、技术门槛相对较低。下一篇，我们将把这些技能全部串联起来，做一个从抓取到报表的完整自动化流水线项目。
练习：写一个脚本，读取一个包含多 sheet 的 Excel 文件，将每个 sheet 的内容提取出来，分别保存为单独的 CSV 文件，并生成一个汇总 Word 文档（包含各 sheet 的行数统计）。
Comments
Sign in to leave a comment.