Python自动化办公：10个实战脚本让你的工作效率翻倍

写在前面：每天花2小时做重复性工作？这些Python脚本能帮你把2小时压缩到5分钟。每个脚本都是完整可运行的，复制粘贴即可使用。本文覆盖Excel处理、PDF操作、邮件发送、文件管理、图片处理等10个高频场景。建议收藏备用。—## 环境准备所有脚本基于 Python 3.8+，需要安装以下依赖：bashpip install openpyxl PyPDF2 python-docx Pillow yagmail schedule pandas> 提示：建议使用虚拟环境，避免与系统包冲突。—## 脚本1：Excel批量合并——把100个表格合成1个痛点场景：每个月收到各部门的Excel报表，需要手动复制粘贴到一个汇总表。100个文件，至少花1小时。pythonimport pandas as pdimport osfrom datetime import datetimedef merge_excel_files(folder_path, output_file="汇总表.xlsx"): all_data = [] file_count = 0 for filename in os.listdir(folder_path): if filename.endswith((".xlsx", ".xls")) and not filename.startswith("~$"): filepath = os.path.join(folder_path, filename) try: df = pd.read_excel(filepath) df["来源文件"] = filename all_data.append(df) file_count += 1 except Exception as e: print(f"读取失败: {filename} - {e}") if not all_data: return merged = pd.concat(all_data, ignore_index=True) merged.to_excel(output_file, index=False) print(f"合并完成！共 {file_count} 个文件，{len(merged)} 行数据")if __name__ == "__main__": merge_excel_files("./excel_files", "月度汇总.xlsx")效率提升：100个文件从1小时 -> 10秒。—## 脚本2：Excel数据清洗——自动处理异常值痛点场景：从系统导出的数据总有空值、重复行、格式不统一的问题。pythonimport pandas as pddef clean_excel(input_file, output_file="清洗后数据.xlsx"): df = pd.read_excel(input_file) original_rows = len(df) print(f"原始数据: {original_rows} 行") # 删除空白行列 df.dropna(how="all", inplace=True) df.dropna(axis=1, how="all", inplace=True) # 去重 dup = len(df) - len(df.drop_duplicates()) df.drop_duplicates(inplace=True) # 智能填充空值 for col in df.columns: if df[col].dtype in ["float64", "int64"]: df[col].fillna(df[col].median(), inplace=True) else: df[col].fillna("未知", inplace=True) # 去除文本前后空格 for col in df.select_dtypes(include=["object"]).columns: df[col] = df[col].str.strip() df.to_excel(output_file, index=False) print(f"清洗完成: {len(df)}行, 保留率{len(df)/original_rows*100:.1f}%")if __name__ == "__main__": clean_excel("原始数据.xlsx")—## 脚本3：PDF批量合并痛点场景：报销时要合并10多个PDF发票，合同归档时要合并多份扫描件。pythonfrom PyPDF2 import PdfMergerimport osdef merge_pdfs(folder_path, output_file="合并结果.pdf"): merger = PdfMerger() pdf_files = sorted([f for f in os.listdir(folder_path) if f.lower().endswith(".pdf")]) if not pdf_files: print("未找到PDF") return for filename in pdf_files: filepath = os.path.join(folder_path, filename) try: merger.append(filepath) print(f" + {filename}") except Exception as e: print(f" x {filename} - {e}") merger.write(output_file) merger.close() mb = os.path.getsize(output_file) / (1024 * 1024) print(f"合并完成！{output_file} ({mb:.1f} MB)")if __name__ == "__main__": merge_pdfs("./pdf_files")—## 脚本4：PDF按页拆分痛点场景：扫描了份100页的合同，需要按章节拆分成单独的PDF。pythonfrom PyPDF2 import PdfReader, PdfWriterimport osdef split_pdf(input_file, pages_per_split=5, output_folder="./split_output"): os.makedirs(output_folder, exist_ok=True) reader = PdfReader(input_file) total = len(reader.pages) for start in range(0, total, pages_per_split): end = min(start + pages_per_split, total) writer = PdfWriter() for i in range(start, end): writer.add_page(reader.pages[i]) name = f"第{start+1}-{end}页.pdf" with open(os.path.join(output_folder, name), "wb") as fp: writer.write(fp) print(f" + {name}") print("拆分完成！")if __name__ == "__main__": split_pdf("大文件.pdf", pages_per_split=10)—## 脚本5：邮件自动发送——批量发工资条痛点场景：每月发工资条，HR需要逐个员工发邮件，200人至少花2小时。pythonimport yagmailimport pandas as pdfrom datetime import datetimeimport osdef send_salary_slips(excel_file, sender_email, auth_code): df = pd.read_excel(excel_file) yag = yagmail.SMTP(user=sender_email, password=auth_code, host="smtp.qq.com") success = 0 for _, row in df.iterrows(): name = row["姓名"] email = row["邮箱"] subject = f"{datetime.now().strftime("%Y年%m月")}工资条 - {name}" contents = f"""<h2>{name}，您好！</h2> <p>以下是您本月的工资明细：</p> <table border="1" cellpadding="8"> <tr><td>基本工资</td><td>¥{row["基本工资"]:,.2f}</td></tr> <tr><td>绩效奖金</td><td>¥{row["绩效奖金"]:,.2f}</td></tr> <tr><td>社保扣除</td><td style="color:red">-¥{row["社保扣除"]:,.2f}</td></tr> <tr><td><b>实发工资</b></td> <td style="color:green"><b>¥{row["实发工资"]:,.2f}</b></td></tr> </table>""" try: yag.send(to=email, subject=subject, contents=contents) success += 1 print(f" + 已发送: {name}") except Exception as e: print(f" x 失败: {name} - {e}") yag.close() print(f"发送完成！成功 {success}/{len(df)} 封")if __name__ == "__main__": send_salary_slips("工资表.xlsx", "your@qq.com", os.environ.get("EMAIL_AUTH_CODE"))>安全提示：密码不要硬编码！使用环境变量保护敏感信息。—## 脚本6：文件批量重命名痛点场景：从相机导出的照片命名杂乱，需要改成统一格式。pythonimport osfrom datetime import datetimedef batch_rename(folder_path, prefix="项目", dry_run=True): files = [f for f in os.listdir(folder_path) if os.path.isfile(os.path.join(folder_path, f))] print(f"找到 {len(files)} 个文件 ({"预览" if dry_run else "执行"}模式)") plan = [] for i, old_name in enumerate(sorted(files), 1): _, ext = os.path.splitext(old_name) ts = datetime.now().strftime("%Y%m%d") new_name = f"{prefix}_{ts}_{i:03d}{ext}" plan.append((old_name, new_name)) print(f" {old_name} -> {new_name}") if dry_run: print("预览模式，确认后改 dry_run=False") return success = 0 for old_name, new_name in plan: try: os.rename(os.path.join(folder_path, old_name), os.path.join(folder_path, new_name)) success += 1 except Exception as e: print(f" x {old_name}: {e}") print(f"重命名完成！成功 {success}/{len(plan)} 个")if __name__ == "__main__": batch_rename("./photos", prefix="团建活动", dry_run=True)—## 脚本7：图片批量压缩痛点场景：产品图每张5MB，上传到网站和发给客户都太慢。pythonfrom PIL import Imageimport osdef compress_images(folder_path, output_folder="./compressed", quality=85): os.makedirs(output_folder, exist_ok=True) exts = (".jpg", ".jpeg", ".png", ".webp", ".bmp") files = [f for f in os.listdir(folder_path) if f.lower().endswith(exts)] total_before, total_after = 0, 0 for filename in files: filepath = os.path.join(folder_path, filename) original_size = os.path.getsize(filepath) total_before += original_size img = Image.open(filepath) if img.mode in ("RGBA", "P"): img = img.convert("RGB") if max(img.size) > 1920: ratio = 1920 / max(img.size) img = img.resize((int(img.size[0]*ratio), int(img.size[1]*ratio)), Image.Resampling.LANCZOS) out_name = os.path.splitext(filename)[0] + ".jpg" out_path = os.path.join(output_folder, out_name) img.save(out_path, "JPEG", quality=quality, optimize=True) comp_size = os.path.getsize(out_path) total_after += comp_size pct = (1 - comp_size / original_size) * 100 print(f" {filename}: {original_size//1024}KB -> {comp_size//1024}KB ({pct:.0f}%)") saved = total_before - total_after print(f"总计: {total_before/1024/1024:.1f}MB -> {total_after/1024/1024:.1f}MB (节省{saved/total_before*100:.0f}%)")if __name__ == "__main__": compress_images("./product_images", quality=80)—## 脚本8：Word文档批量转PDF痛点场景：合同、报告批量转PDF，手动另存为效率太低。pythonimport osimport comtypes.clientdef word_to_pdf_windows(folder_path, output_folder="./pdf_output"): """使用Word COM接口转换（仅Windows，保留完整排版）""" os.makedirs(output_folder, exist_ok=True) word = comtypes.client.CreateObject("Word.Application") word.Visible = False for filename in os.listdir(folder_path): if filename.endswith((".doc", ".docx")): inp = os.path.abspath(os.path.join(folder_path, filename)) out_name = os.path.splitext(filename)[0] + ".pdf" outp = os.path.abspath(os.path.join(output_folder, out_name)) doc = word.Documents.Open(inp) doc.SaveAs(outp, FileFormat=17) doc.Close() print(f" + {filename} -> {out_name}") word.Quit() print("转换完成！")if __name__ == "__main__": word_to_pdf_windows("./docx_files")—## 脚本9：定时任务调度器痛点场景：每天需要定时执行数据备份、报表生成、文件清理等任务。pythonimport scheduleimport timeimport shutilimport osfrom datetime import datetimeclass AutoWorker: def backup_data(self, src, dst): ts = datetime.now().strftime("%Y%m%d_%H%M%S") p = os.path.join(dst, f"backup_{ts}") shutil.make_archive(p, "zip", src) mb = os.path.getsize(p + ".zip") / (1024*1024) print(f"[{datetime.now():%H:%M:%S}] 备份完成: {mb:.1f}MB") def clean_temp(self, folder, days=7): now = time.time() n = 0 for f in os.listdir(folder): fp = os.path.join(folder, f) if os.path.isfile(fp) and now - os.path.getmtime(fp) > days*86400: os.remove(fp) n += 1 print(f"[{datetime.now():%H:%M:%S}] 清理 {n} 个过期文件") def setup(self): schedule.every().day.at("09:00").do( lambda: print(f"[{datetime.now():%H:%M:%S}] 日报生成")) schedule.every().day.at("18:00").do( self.backup_data, "./data", "./backups") schedule.every().monday.at("08:00").do( self.clean_temp, "./temp", 7) print("已配置: 09:00日报 / 18:00备份 / 周一清理") def start(self): self.setup() print("调度器启动，Ctrl+C停止") try: while True: schedule.run_pending() time.sleep(60) except KeyboardInterrupt: print("已停止")if __name__ == "__main__": AutoWorker().start()—## 脚本10：文件夹��控自动处理痛点场景：需要实时监控文件夹，有新文件就自动分类。pythonimport osimport timeimport shutilfrom datetime import datetimeclass FolderWatcher: RULES = { ".xlsx": "Excel", ".xls": "Excel", ".csv": "Excel", ".pdf": "PDF", ".doc": "Word", ".docx": "Word", ".jpg": "图片", ".jpeg": "图片", ".png": "图片", ".mp4": "视频", ".zip": "压缩", } def __init__(self, watch, output="./分类输出"): self.watch = watch self.output = output self.seen = set() os.makedirs(output, exist_ok=True) def process(self, filename): src = os.path.join(self.watch, filename) if not os.path.isfile(src): return ext = os.path.splitext(filename)[1].lower() cat = self.RULES.get(ext, "其他") dst_dir = os.path.join(self.output, cat) os.makedirs(dst_dir, exist_ok=True) name = os.path.splitext(filename)[0] _, e = os.path.splitext(filename) ts = datetime.now().strftime("%H%M%S") shutil.move(src, os.path.join(dst_dir, f"{name}_{ts}{e}")) print(f"[{datetime.now():%H:%M:%S}] {filename} -> {cat}/") def start(self, interval=5): self.seen = set(os.listdir(self.watch)) print("监控中... Ctrl+C停止") try: while True: current = set(os.listdir(self.watch)) for f in current - self.seen: if not f.startswith("."): self.process(f) self.seen = current time.sleep(interval) except KeyboardInterrupt: print("已停止")if __name__ == "__main__": FolderWatcher("./下载", "./自动分类").start(3)—## 效率收益总结| 脚本 | 手动耗时 | 自动化耗时 | 节省时间 ||—|—|—|—|| Excel批量合并 | 60分钟 | 10秒 | 59分50秒 || 数据清洗 | 30分钟 | 10秒 | 29分50秒 || PDF合并 | 15分钟 | 5秒 | 14分55秒 || PDF拆分 | 20分钟 | 8秒 | 19分52秒 || 邮件批量发送 | 120分钟 | 30秒 | 119分30秒 || 文件批量重命名 | 30分钟 | 3秒 | 29分57秒 || 图片批量压缩 | 45分钟 | 20秒 | 44分40秒 || Word转PDF | 25分钟 | 15秒 | 24分45秒 || 定时任务调度 | 每天重复 | 一次配置 | 持续受益 || 文件夹监控 | 实时盯盘 | 全自动 | 彻底解放 |总计：原来需要345分钟的工作，自动化后不到2分钟，每天节省近6小时！—## 写在最后以上10个脚本覆盖了办公场景中最常见的重复性工作。核心思路就三步：1.识别重复：找到每天都在手动做的操作2.拆解步骤：把操作拆成「读取 -> 处理 -> 输出」的标准流程3.编写脚本：用Python把流程自动化建议学习路径：- 第一周：先用好脚本1-3（文件合并类），入门pandas- 第二周：学习脚本5-6（邮件和文件管理），掌握os和shutil- 第三周：挑战脚本9-10（定时任务和监控），进阶自动化> 专栏持续更新中，下一期将分享「Python+AI自动化：让ChatGPT帮你处理Excel」，敬请关注！>> 觉得有用的话，点个赞关注一下，更多Python自动化干货持续推送~—本文收录于 [Python自动化办公实战] 专栏，专栏包含50+实战脚本和完整源码，持续更新中。