Python批量处理Excel工作簿和工作表的完整代码

前言

事情得从“批量”这两个字说起。当读者开始关注“一次性处理几十个Excel文件”这类需求时，意味着正式从单点操作进入自动化场景了。这种需求在实际工作中非常普遍——一个文件夹里堆着几十个甚至几百个Excel表格，需要统一改名、统一整理工作表、批量合并数据，或者批量生成结果文件。

这张图展示的是本章导读的核心内容：用Python批量操作Excel工作簿和工作表。

批量处理不是简单“写几行代码打开文件”那么直白。它至少涉及三个要素：数据源文件夹、Python自动化处理逻辑、输出结果文件夹。这三者如果分不清，后面写的脚本很容易变成一次性代码——能跑通一次，但换个场景就废了。

一上来就追求复杂代码并不是高效率的做法。比较稳妥的学习方式，是先想清楚“文件从哪来、要处理什么、结果放哪去”这条逻辑链。Python办公自动化的价值，从来不在于炫技，而在于把重复动作变成稳定可靠的流程。

2. 适用场景：这一章解决的是“重复劳动”问题

日常工作中，Excel最折磨人的地方不是做一次，而是反反复复做很多次。比如财务、资产、行政、人事、项目管理和桌面支持统计，经常要面对多个部门分别提交表格，最后需要统一合并、整理、筛选、归档。

典型场景包括：批量新建工作簿、批量打开工作簿、批量重命名工作表、批量复制工作表、批量合并多个Excel、批量拆分一个汇总表、批量导出结果文件。这些任务要是纯手工完成，最大的问题倒不是慢，而是容易出错。

一个比较推荐的做法是先把任务拆成三个层级：“文件层”、“Excel层”、“数据层”。文件层关心路径和文件名，Excel层关心工作簿和工作表，数据层关心表格内容和字段逻辑。拆清楚了，后续代码就不容易乱。

这里有个判断标准值得留意：如果只是读写Excel文件数据，很多时候用 pandas 加 openpyxl 就够了；但如果需要像人工一样操作Excel软件——比如调用宏、打印、处理公式刷新、操作已打开的工作簿——那么 xlwings 更适合。不要把所有Excel自动化都无脑交给同一个库处理。

3. 学习路线：先跑通，再理解，再迁移

这一章更适合用“案例驱动”的方式来学习。原因很简单：Excel自动化不是纯理论知识，很多问题只有真正跑脚本时才会暴露出来，比如路径写错、文件被占用、工作表名称重复、字段名不一致、保存路径不存在。

这张图展示的就是这种案例驱动的学习路径：先实现代码，再做代码解析，补充知识延伸，最后举一反三。

从图中能看出，真正有效的学习不是“复制一段代码就完事”，而是要经历四个动作：跑通、拆解、补充、迁移。如果只停留在第一步，换到自己真实的Excel文件时，很快就会卡住。

每学一个案例，建议按这样复盘：代码能不能运行；运行结果对不对；关键参数能不能解释清楚；换一个文件夹、换一个字段、换一个输出路径，还能不能改出来。能走完这四步，才算真正掌握。

最容易犯的错是只收藏代码不验证代码。收藏代码不会提升能力，只有把代码改成自己的业务场景，并且能解释每一步为什么这么写，才会形成可复用的能力。

4. 核心原理：os、xlwings、pandas 各自负责什么

第4章最关键的地方，不是记住某一个函数，而是理解不同工具的分工。Python处理Excel时，新手常把所有问题混在一起：文件找不到、工作表打不开、数据合并失败、保存结果异常。实际上这些问题属于不同层级，应当分别处理。

这张图展示的是批量处理Excel时常用的三大工具组合：os 管文件，xlwings 动Excel，pandas 算数据。

从图中可以看出，这三个工具并不是互相替代的关系，而是分工协作。os 偏底层文件管理，xlwings 偏Excel应用操控，pandas 偏数据清洗和分析。把这件事想明白，后面写代码就不会乱选工具。

4.1 os：负责文件系统层面的批处理

os 主要解决“文件在哪里、文件叫什么、要不要处理这个文件”的问题。比如遍历目录、拼接路径、判断扩展名、重命名文件、创建输出目录，这些都归文件系统层面管。

import os

folder = r"C:Tempexcel_batch"

for file_name in os.listdir(folder):
    if file_name.endswith(".xlsx"):
        full_path = os.path.join(folder, file_name)
        print(full_path)

这段代码作用很直接：遍历指定文件夹，只找出扩展名为 .xlsx 的文件。实际工作中一定要加过滤条件，因为Excel文件夹里可能混有临时文件，比如 ~$xxx.xlsx。如果不排除临时文件，脚本可能会报错，甚至误处理不该处理的文件。

4.2 xlwings：负责像人工一样操作Excel

xlwings 的特点是可以调用本机Excel程序，适合处理那些需要Excel应用参与的动作。比如打开工作簿、操作工作表、调用VBA、刷新公式、打印文件等。

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.open(r"C:Tempexcel_batchdemo.xlsx")

sheet = wb.sheets[0]
sheet.range("A1").value = "Python 批量处理测试"

wb.sa ve()
wb.close()
app.quit()

需要注意的是，xlwings 通常依赖本机Excel环境。它更像是“自动帮你操作Excel软件”。如果目标电脑没有安装Excel，或者Excel被弹窗卡住，脚本就可能异常。

4.3 pandas：负责数据清洗、汇总和导出

pandas 更适合处理表格数据本身，比如读取Excel、筛选行、选择列、分组汇总、拼接多个表格、导出结果文件。它不是在模拟人工点Excel，而是在直接处理数据。

import pandas as pd

df = pd.read_excel(r"C:Tempexcel_batchsales.xlsx")

result = df.groupby("产品", as_index=False)["销售额"].sum()

result.to_excel(r"C:Tempexcel_batch销售额汇总.xlsx", index=False)

如果需求是“把多个表的数据汇总成一个结果”，优先考虑用 pandas，通常更快、更稳定，也更容易批量化。但如果需求涉及保留复杂格式、宏、打印、图表刷新，就得结合 xlwings 或其他库做判断。

5. 批处理通用模板：换的是逻辑，不换的是流程

批量处理Excel的底层流程其实很固定。无论是批量新建、批量打开、批量合并，还是批量重命名，本质上都是先确定路径，再遍历文件，过滤目标文件，逐个处理，最后保存输出并关闭资源。

这张图展示的是批量处理Excel的通用模板，从确定路径到关闭资源，基本覆盖了批处理脚本的完整生命周期。

从图中能看出，批处理不是“想到哪写到哪”，而是一套固定流程。真正变化的是中间的数据处理逻辑，不变的是外层流程框架。一旦掌握这个模板，后续遇到类似需求，只需要替换处理逻辑，不需要每次从零开始。

下面是一段比较贴近实际工作的批处理代码模板，用了 pathlib 和 pandas。相比传统 os.path，pathlib 写路径会更清晰一些。

from pathlib import Path
import pandas as pd

input_dir = Path(r"C:Tempexcel_batchinput")
output_dir = Path(r"C:Tempexcel_batchoutput")
output_dir.mkdir(exist_ok=True)

for file_path in input_dir.glob("*.xlsx"):
    # 跳过 Excel 临时文件
    if file_path.name.startswith("~$"):
        continue

    print(f"正在处理：{file_path.name}")

    df = pd.read_excel(file_path)

    # 示例处理逻辑：删除完全空白行
    df = df.dropna(how="all")

    output_path = output_dir / f"{file_path.stem}_处理后.xlsx"
    df.to_excel(output_path, index=False)

print("批量处理完成")

这段代码不复杂，但结构比较稳。它先定义输入和输出目录，再遍历所有 .xlsx 文件，跳过Excel临时文件，读取数据，执行处理逻辑，最后输出到新目录。实际工作中，尽量不要直接覆盖原文件，先输出到新目录更安全。

6. 运行前准备：批量操作前必须先排雷

批量处理最怕什么？不是代码报错，而是代码没报错，却把一批文件处理错了。尤其是涉及资产、财务、人事、项目数据时，误覆盖、误删除、误合并都会带来很高的返工成本。

这张图展示的是运行批处理脚本前的准备动作：统一目录、先做备份、关闭占用、验证结果。

从图中可以看出，运行前准备不是可有可无的形式动作，而是批量处理的安全边界。批量脚本一旦写错，错误会被快速放大。所以越是批量任务，越要先小范围测试，再扩大处理范围。

6.1 统一目录，避免路径混乱

建议把测试文件、正式输入文件、输出文件分开放。比如使用 input、output、backup 三个目录。这样即使脚本出问题，也比较容易回退。

C:Tempexcel_batch

├─ input 原始待处理文件

├─ output 脚本输出结果

└─ backup 原始文件备份

推荐先复制3到5个样本文件进行测试。不要一开始就对几百个文件直接跑脚本，这种做法看似效率高，实际风险很大。

6.2 先做备份，避免不可逆损失

只要脚本涉及批量改名、删除、移动、覆盖保存，都应该先备份。尤其是初学阶段，不要过度相信自己的代码。代码没有主观判断，它只会严格执行你写下的逻辑。

不要在原始文件上直接做破坏性操作。如果必须覆盖，也应该等测试通过后，再对正式文件执行。

6.3 关闭占用，避免保存失败

Excel文件被打开时，脚本可能无法写入，或者写入结果异常。特别是多人共享目录、企业网盘、同步盘环境下，文件占用问题更常见。

如果使用 xlwings，还要注意脚本结束后是否正确执行 wb.close() 和 app.quit()。否则后台可能残留Excel进程，下一次运行时就会出现莫名其妙的问题。

6.4 验证结果，不能只看“运行完成”

脚本打印“处理完成”不代表结果正确。真正的验证至少包括：输出文件数量是否正确、字段是否完整、行数是否符合预期、关键金额或数量是否一致、是否存在空文件或异常文件。

from pathlib import Path

output_dir = Path(r"C:Tempexcel_batchoutput")
files = list(output_dir.glob("*.xlsx"))

print(f"输出文件数量：{len(files)}")
for file in files[:5]:
    print(file.name)

这一小段代码可以快速检查输出目录中生成了多少个Excel文件。对于批量任务来说，验证动作本身也应该脚本化，不要完全依赖人工肉眼检查。

7. 常见问题与踩坑提醒

Excel批处理看起来门槛不高，但真实工作里的坑不少。下面这些问题，建议在写脚本时就提前考虑，而不是等报错后再补救。

7.1 路径问题：中文路径和反斜杠

Windows路径里经常有反斜杠，如果直接写字符串，可能会触发转义问题。建议使用原始字符串 r"路径"，或者使用 pathlib.Path。

from pathlib import Path

folder = Path(r"C:Tempexcel_batchinput")

推荐用 pathlib 管理路径。路径拼接更清晰，也能减少手写反斜杠导致的错误。

7.2 临时文件问题：跳过 ~$ 开头文件

Excel打开文件时，目录里可能会出现以 ~$ 开头的临时文件。如果脚本没有跳过这些文件，就可能读取失败。

if file_path.name.startswith("~$"):
    continue

这个判断很小，但非常实用。很多批量脚本现场报错，就是因为把Excel临时文件也当成正式文件处理了。

7.3 字段名问题：不要假设每张表都一样

多个部门提交的Excel，看起来模板一样，但字段名可能有细微差异，比如“资产编号”和“资产编码”、“部门名称”和“所属部门”。如果脚本直接按固定字段读取，就会报错。

required_columns = {"资产编号", "资产名称", "使用部门"}

missing = required_columns - set(df.columns)
if missing:
    print(f"字段缺失：{missing}")

这里的关键不是让代码更复杂，而是让脚本提前发现问题。批量处理脚本要有基本的输入校验，否则错误会扩散到最终结果。

7.4 资源释放问题：打开了就要关闭

如果使用 xlwings 批量打开Excel，一定要处理关闭逻辑。现场经常出现脚本运行几次后越来越卡，本质上可能是后台Excel进程没有释放。

import xlwings as xw

app = xw.App(visible=False)
try:
    wb = app.books.open(r"C:Tempexcel_batchdemo.xlsx")
    # 这里写处理逻辑
    wb.sa ve()
    wb.close()
finally:
    app.quit()

推荐使用 try...finally 保证资源释放。不要只写正常流程，因为真实环境里异常比想象的要多。

8. 效果验证：批处理结果要能复盘

一段合格的办公自动化脚本，不只是能生成文件，还应该能留下最基本的处理痕迹。比如处理了多少个文件，跳过了多少个文件，哪些文件失败，失败原因是什么。

下面这个模板加入了简单日志，适合后续改造成更完整的批处理工具。

from pathlib import Path
import pandas as pd

input_dir = Path(r"C:Tempexcel_batchinput")
output_dir = Path(r"C:Tempexcel_batchoutput")
output_dir.mkdir(exist_ok=True)

success_count = 0
fail_count = 0

for file_path in input_dir.glob("*.xlsx"):
    if file_path.name.startswith("~$"):
        continue

    try:
        df = pd.read_excel(file_path)
        df = df.dropna(how="all")

        output_path = output_dir / f"{file_path.stem}_处理后.xlsx"
        df.to_excel(output_path, index=False)

        print(f"[成功] {file_path.name} -> {output_path.name}")
        success_count += 1

    except Exception as e:
        print(f"[失败] {file_path.name}，原因：{e}")
        fail_count += 1

print(f"处理完成：成功 {success_count} 个，失败 {fail_count} 个")

这段代码虽然还不是完整工具，但已经比单纯打印“完成”要可靠。它能告诉我哪些文件成功，哪些文件失败，失败原因是什么。办公自动化脚本要可复盘，否则出了问题很难定位。

如果后续要提升成正式工具，可以继续增加日志文件、图形界面、输出目录选择、失败文件清单、处理前后数量校验等功能。对企业桌面支持或办公自动化场景来说，这些能力比单纯写一段“能跑的代码”更有价值。

9. 总结提升：这一章真正要练的是自动化思维

这一篇是第4章的导读，不想把它写成简单的目录介绍。真正值得带走的是一套思维：先识别重复动作，再抽象成流程，最后用Python固化成脚本。

如果只看代码，这一章可能不难；但如果放到真实办公场景里，难点会变成路径管理、文件筛选、异常处理、结果验证和风险控制。也就是说，Python技术只是工具，真正决定脚本质量的是对业务流程的拆解能力。

后续每学一个案例，都建议保留一份“可复用模板”。比如批量遍历模板、批量读取模板、批量输出模板、异常日志模板。积累到一定程度后，很多临时需求就不用重新写，只需要拼装和改造。

最后再提醒一句：批量脚本不要直接对正式文件下手。先备份、先小样本测试、先验证输出，再扩大处理范围。自动化不是为了冒险，而是为了让重复工作更稳定、更可控。