Python Excel 自动化实战:从单元格更新到定时交付

每个月总有几件雷打不动的 Excel 例行工作:把各区域的销售表汇总进总表,更新几个数字,扫一眼有没有填错的行,导出一份 PDF 发给不开工作簿的同事,再把上个月的文件加密归档。这些活儿单步都不难,麻烦在重复:文件越多,步骤越容易走样,人也越耗在其中。所谓 Python Excel 自动化,就是把这些固定步骤交给脚本,让程序按计划去读、改、合并、检查和交付工作簿。

本文用 Spire.XLS for Python 16.8.2 把这条流程完整写出来:从几行代码的单元格更新,到合并多个工作簿、按客户拆分报表、标记异常数据,再到公式重算、PDF 导出和定时运行。每个环节都给出可运行的代码和对应的运行结果。开篇先交代常用库的分工,方便你判断哪些任务该交给谁。

快速导航


1. 如何选择 Python Excel 自动化库

先划一条边界:自动化适合那些按固定规则重复、以文件为载体、不需要人工判断的任务。月度报表拼装、周度数据检查、模板里的数字更新、面向下游系统的格式转换,都是典型场景。反过来,每次形态都不同、中间需要人来拿主意的任务,写脚本的代价会大于收益。另外本文只讨论 Excel 文件本身的处理,Excel 到 Word、PowerPoint 的文档生成是另一个话题。

库的选择取决于任务。下表汇总了五个最常见的库,每个都有一项看家的本事:

库 最适合的场景 需要权衡的地方
pandas 表格数据的分析加工 面向 DataFrame,工作簿级的对象需要搭配其他工具
openpyxl 编辑已有的 XLSX/XLSM 模板和单元格格式 保存公式本身,计算结果由 Excel 打开时得出
XlsxWriter 生成带格式和图表的新 XLSX 报表 面向新建文件的写入场景
xlwings 驱动本机的 Excel 应用,打通 Python 与 Excel 的双向交互 经典版基于本机安装的 Excel 运行;PRO File Reader 可在无 Excel 的 Linux 环境读取文件
Spire.XLS 用同一套 API 完成多格式读写、公式计算和文档转换 商业授权,提供厂商支持与版本更新

分工相当清晰:分析交给 pandas,改模板交给 openpyxl,写新报表交给 XlsxWriter,驱动 Excel 本身交给 xlwings。各库聚焦流程的一段,当任务从单一操作变成多步骤流程时,往往意味着引入多个依赖并处理它们之间不同的对象模型。Spire.XLS 把读取、编辑、计算、转换和保护收进同一套 API,本文的示例因此全程用它实现。先看下面这张图,对整条流程里各工具的位置有个印象:

Python Excel 自动化能处理的六类任务:更新、合并、校验、报表、定时、交付,以及各环节对应的库


2. 用 Python 读取和更新 Excel 工作簿

最常见的任务也最简单:加载工作簿,改几个单元格,保存。先安装依赖:

1
pip install Spire.XLS

然后整个任务几行代码就能完成:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
from spire.xls import Workbook, FileFormat

wb = Workbook()
wb.LoadFromFile("sales.xlsx") # 加载模板
ws = wb.Worksheets[0]

ws.Range["B2"].NumberValue = 150 # 更新已有单元格
ws.Range["A6"].Text = "产品E" # 追加一行
ws.Range["B6"].NumberValue = 45
ws.Range["C6"].NumberValue = 11.75
ws.Range["D6"].Formula = "=B6*C6" # 写入公式
ws.Range["D6"].NumberFormat = "#,##0.00"

wb.SaveToFile("sales_updated.xlsx", FileFormat.Version2016)
wb.Dispose() # 释放工作簿资源

在这段代码之上,有几个读值的约定值得先弄清楚。在 Spire.XLS 的 API 里,Range[addr].NumberValue 返回单元格的数值,单元格是文本或空值时返回 NaN;Range[addr].Text 返回显示字符串;对公式单元格,.Formula 返回公式串,FormulaNumberValue 返回计算结果。通过 .Formula 写入的是公式本身,要把计算结果一并写进文件,参考后文的公式重算部分。

运行这段代码后,更新的数值、追加的行、数字格式和原有表头样式都完整保留,公式按原样存入文件;重新加载后调用 CalculateAllValue(),追加行的 =B6*C6 读出 528.75,与手算一致。

用 Spire.XLS 更新后的 Excel 工作簿:单元格更新、追加行与公式均完整保留


3. 批量合并工作簿并生成客户报表

把多个区域文件合并成总表

自动化真正省时间的地方从合并开始:逐个加载区域文件,按已用区域读取数据行,写进一张带来源列的新总表,让每一行都能追溯出处:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import glob, os
from spire.xls import Workbook, FileFormat

master = Workbook()
ms = master.Worksheets[0]
ms.Range["A1"].Text = "月份"
ms.Range["B1"].Text = "门店"
ms.Range["C1"].Text = "营收"
ms.Range["D1"].Text = "来源文件"
out_row = 2

for path in glob.glob("region_*.xlsx"):
wb = Workbook()
wb.LoadFromFile(path)
ws = wb.Worksheets[0]
for r in range(2, ws.AllocatedRange.RowCount + 1): # 跳过各文件的表头
for c in range(1, 4):
src = ws.Range[r, c]
nv = src.NumberValue
if not (isinstance(nv, float) and nv != nv): # NaN 表示非数值
ms.Range[out_row, c].NumberValue = nv
else:
ms.Range[out_row, c].Text = src.Value
ms.Range[out_row, 4].Text = os.path.basename(path)
out_row += 1
wb.Dispose()

master.SaveToFile("master_report.xlsx", FileFormat.Version2016)
master.Dispose()

运行结果:三个区域文件合并出 9 行数据,营收列全部是数值,合计 94,298.00,与源文件分毫不差;来源列恰好是三个文件名,没有表头混进数据。合并结果用 openpyxl 也能正常读取,可以直接接进下一段流程。有一点需要留意:这个循环复制的是单元格的值,不含源文件的格式。如果源列是真日期、百分比或货币数值,需要给目标列设置对应的数字格式;本文示例中的月份列是文本,因此原样一致。

三个区域工作簿合并成一个 Excel 总表,并附带来源文件列

按客户生成格式化报表

合并得到的是一份完整数据集,报表生成把它拆成可交付的文件:读取账单主表,按客户分组,为每位客户生成一张带合并标题、样式表头、金额格式和 SUM 合计的发票工作簿:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
from spire.xls import Workbook, FileFormat, Color

wb = Workbook()
wb.LoadFromFile("invoice_master.xlsx")
ws = wb.Worksheets[0]

clients = {}
for r in range(2, ws.AllocatedRange.RowCount + 1):
client = ws.Range[r, 1].Value
clients.setdefault(client, []).append(
(ws.Range[r, 2].Value, ws.Range[r, 3].NumberValue))

for client, items in clients.items():
inv = Workbook()
sheet = inv.Worksheets[0]
sheet.Range["A1"].Text = f"发票 - {client}"
sheet.Range["A1"].Style.Font.IsBold = True
sheet.Range["A1"].Style.Font.Size = 13
sheet.Range["A1:B1"].Merge()
sheet.Range["A3"].Text = "服务项目"
sheet.Range["B3"].Text = "金额"
header = sheet.Range["A3:B3"]
header.Style.Color = Color.FromArgb(255, 68, 114, 196)
header.Style.Font.Color = Color.FromArgb(255, 255, 255, 255)
header.Style.Font.IsBold = True
row = 4
for service, amount in items:
sheet.Range[row, 1].Text = service
sheet.Range[row, 2].NumberValue = amount
sheet.Range[row, 2].NumberFormat = "#,##0.00"
row += 1
sheet.Range[row, 1].Text = "合计"
sheet.Range[row, 1].Style.Font.IsBold = True
sheet.Range[row, 2].Formula = f"=SUM(B4:B{row - 1})"
sheet.Range[row, 2].NumberFormat = "#,##0.00"
sheet.SetColumnWidth(1, 22)
sheet.SetColumnWidth(2, 14)
inv.SaveToFile(f"{client}_发票.xlsx", FileFormat.Version2016)
inv.Dispose()

三个客户生成三份发票,合并标题和样式表头都在,=SUM 公式原样保存。调用 CalculateAllValue() 后,宏达贸易发票的合计读出 8,680.50,与三个明细项一致,同时单元格里仍是公式串,Excel 打开时会继续重算。文件名取自客户名,如果真实客户名里可能出现 /、: 这类字符,拼文件名前先做一次清理。单元格级的读写还有不少可组合的用法,完整清单见 Python 创建、读取或更新 Excel 文档。


4. 校验数据并高亮异常行

自动化不只生产文件,还要检查文件。一个实用的模式是加一道扫描步骤:遍历工作簿,套用你的数据质量规则,把不合格的行标记出来,让人只看被标记的行。这是带可视标记的数据检查,与 Excel 的数据验证功能(控制用户能往单元格里输什么)是两回事。下面的扫描通过 NumberValue 读值,它对文本和空单元格都返回 NaN,所以第一条规则如实报告”缺失或非数字”,并不区分这两种情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import math
from spire.xls import Workbook, FileFormat, Color

LIGHT_RED = Color.FromArgb(255, 254, 226, 226)
NOTE_RED = Color.FromArgb(255, 185, 28, 28)

wb = Workbook()
wb.LoadFromFile("payroll.xlsx")
ws = wb.Worksheets[0]

for row in range(2, ws.AllocatedRange.RowCount + 1):
salary = ws.Range[row, 2].NumberValue
bonus = ws.Range[row, 3].NumberValue
reasons = []
if math.isnan(salary) or math.isnan(bonus):
reasons.append("缺失或非数字")
if not math.isnan(salary) and salary > 100000:
reasons.append("数值异常")
if not math.isnan(bonus) and bonus < 0:
reasons.append("奖金为负")
if reasons:
ws.Range[row, 1, row, 3].Style.Color = LIGHT_RED
ws.Range[row, 4].Text = ";".join(reasons)
ws.Range[row, 4].Style.Font.Color = NOTE_RED

wb.SaveToFile("qa_flagged.xlsx", FileFormat.Version2016)
wb.Dispose()

在测试数据上,四行问题数据全部被标记:两行缺失或非数字,一行工资超过阈值,一行奖金为负;干净的行保持原样,每行的问题原因写进 D 列,文件重开后标注完整。规则本身刻意保持简单,真实工资数据的重点在阈值和规则集,它们都在你的代码里,可以进版本管理、可以评审。

Python 脚本用红色填充和原因标注高亮 Excel 中的异常数据行

这个模式和前面的章节可以串起来:先合并区域文件,再对合并结果做扫描,根据扫描结果决定进入人工复核还是直接交付。


5. 让脚本定时运行

只能手动触发的脚本只算完成了一半,另一半是让它按计划自己跑。前面的步骤都是普通 Python 代码,把整条流程(加载、合并、扫描、生成、交付)装进一个函数,交给操作系统调度即可:

方式 运行机制 适合场景
Windows 任务计划程序 由操作系统启动 Python 脚本 Windows 上的周期报表
cron 由操作系统在 Linux/macOS 上定时启动脚本 服务器端的定时任务

两种方式都是启动脚本、执行、退出,不需要常驻进程。错过计划的补跑行为两者不同:Windows 任务计划程序可以配置为错过后尽快运行,标准的 cron 任务对离线期间错过的执行不做补跑,需要补跑时通常搭配 anacron 或 systemd timer。部署方面有个省事的点:Spire.XLS 直接处理工作簿文件,运行环境不必安装桌面 Excel,上面的脚本可以直接跑在 Linux 服务器或容器里。当然,不装 Excel 不等于能复现 Excel 的全部行为,下一节讲的正是那些不容易复现的部分。


6. 处理更复杂的自动化任务

对多数流程,前面几节已经是完整答案。有些需求会继续叠加:输入格式更多、公式必须重算、要出 PDF、归档要加密。本节逐个处理这些点,代码基于 Spire.XLS for Python 16.8.2(PyPI 上的独立 Python 包)。它是应对这些复杂需求的实现路径之一,不是本文所有任务的默认答案。

处理多种文件格式

上游系统导出 .xls、.xlsb 或 .ods 而你的流程期望 .xlsx,是常见的衔接问题,不同格式通常意味着不同的解析库和引擎。Spire.XLS 用同一套工作簿 API 处理它们:在 16.8.2 的运行中,同一个工作簿对象加载并保存了 .xls、.ods、.xlsx 和 .xlsb(FileFormat.Xlsb2007 / Xlsb2010),.xls、.ods 和 .xlsb 都能原格式往返且数值不变,写出的 .xlsb 在 Excel 中直接打开。下游若使用其他解析库读取生成的文件,建议先用实际文件跑一遍验证:

1
2
3
4
5
6
from spire.xls import Workbook, FileFormat

wb = Workbook()
wb.LoadFromFile("legacy_input.xls") # 同样支持 .xlsb 与 .ods
wb.SaveToFile("converted.xlsx", FileFormat.Version2016)
wb.Dispose()

区分两个目标:”编辑后按原格式保存”和”转换后交付”是不同的事,前者保留原地的工作文件,后者面向下游消费者。下面的端到端示例会同时出现这两种。

公式重算

写公式的库未必写结果。openpyxl 保留公式串但不存储计算结果,不重算的下游读取时拿到的就是空值,这对定时管线是个实际问题:文件交付给按原样读取的系统,数字直接缺失。计算引擎补上这一环:

1
2
3
4
5
6
7
from spire.xls import Workbook, FileFormat

wb = Workbook()
wb.LoadFromFile("model.xlsx") # 其他工具写入的公式,文件中没有缓存结果
wb.CalculateAllValue() # 计算所有公式
wb.SaveToFile("model_calculated.xlsx", FileFormat.Version2016)
wb.Dispose()

两侧都验证过:加载时 Spire.XLS 默认已在内存中计算(RecalculateOnOpen 开启),FormulaNumberValue 立即可读,但把结果持久化进文件需要在保存前显式调用 CalculateAllValue()。调用后保存的文件里带着计算结果,openpyxl 以 data_only=True 读取确认;不调用时同样的单元格读出 None。保存之后,任何库都能读到结果。计算覆盖的是引擎支持的函数和依赖,工作簿里包含外部链接或加载项函数时,建议先用实际文件验证计算结果。单元格公式的读写细节,参考 Python 在 Excel 中添加或读取公式。

公式重算对比:data_only 读取为 None,CalculateAllValue 并保存后可读到合计结果

把报表导出为 PDF

定时报告的终点通常是一份 PDF,给不开工作簿的人看。表格的忠实渲染(列宽、字体、图表)恰恰是通用转换工具容易出错的地方,Spire.XLS 由工作表自己渲染,先定义页面设置:

1
2
3
4
5
6
7
8
9
10
from spire.xls import Workbook, PageOrientationType

wb = Workbook()
wb.LoadFromFile("model_calculated.xlsx")
sheet = wb.Worksheets[0]
sheet.PageSetup.Orientation = PageOrientationType.Portrait
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 1
sheet.SaveToPdf("report.pdf") # 工作表级导出
wb.Dispose()

FitToPagesWide 和 FitToPagesTall 把工作表缩放到一页宽、一页高,列不会跨页拆分;列特别多的工作表缩放后字体会变小,可结合列宽和字号调整。工作表级的 SaveToPdf 只导出你选中的那张表。运行结果:导出的 PDF 为一页,标题、完整表格(含重算后的合计)和图表都在。页面设置的更多选项见 Python 将 Excel 转为 PDF。

Spire.XLS for Python 将 Excel 报表导出为 PDF:表格、合计与图表完整保留

处理图表和透视表

报表工作簿通常带着对象而不只是单元格。Spire.XLS 的工作簿 API 覆盖透视表和透视图的创建、刷新与字段操作。在测试工作簿上,图表经过 16.8.2 的一次读写往返后,XML 层面完整,Excel 重新打开渲染正常。这是在本文夹具上的结果,不构成对每种 DrawingML 对象的承诺,复杂形状请用你自己的文件验证,这条建议对任何库都适用。透视表建议同样处理:用自己的工作簿跑一遍往返再上线。

保护工作簿

归档或外发的报表经常需要打开密码,一次调用完成:

1
2
3
wb.Protect("Report2026")
wb.SaveToFile("protected_report.xlsx", FileFormat.Version2016)
wb.Dispose()

设置后文件没有密码打不开,openpyxl 读取加密容器同样报错;用同一库读回时通过 OpenPassword 属性提供密码。也就是说,如果一条流程既要保护又要回读受保护文件,工具链的读取端需要支持这一点。

一个端到端示例

把上面的能力串成一条转换交付流程:读取 .xlsb 输入,更新数值,重算,交付 PDF 和加密的 .xlsx:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from spire.xls import Workbook, FileFormat, PageOrientationType

wb = Workbook()
wb.LoadFromFile("monthly_input.xlsb") # 读取二进制格式

sheet = wb.Worksheets[0]
sheet.Range["B2"].NumberValue = 125000 # 更新输入单元格

wb.CalculateAllValue() # 持久化计算结果

sheet.PageSetup.Orientation = PageOrientationType.Portrait
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 1
sheet.SaveToPdf("monthly_report.pdf") # 交付物 1:PDF

wb.Protect("Report2026") # 交付物 2:加密 xlsx
wb.SaveToFile("monthly_report.xlsx", FileFormat.Version2016)
wb.Dispose() # 释放工作簿

端到端的 Python Excel 自动化输出:从一份 xlsb 输入交付 PDF 和加密工作簿

运行环境为 Spire.XLS for Python 16.8.2 与 Python 3.13。如果这条流程符合你的需求,可以了解 Spire.XLS for Python 的完整能力。

生产环境的选库考虑

API 之外,生产依赖还需要一份维护计划。Spire.XLS for Python 持续发布新版本,16.8.2(2026 年 8 月,即本文使用的版本)加入了 TEXTSPLIT 公式支持。授权为永久授权:授权覆盖条款范围内的持续使用,新版本的获取跟随更新权益,授权方案包含一年的产品更新与 hotfix;商业支持为格式、计算或转换类问题提供确定的厂商响应渠道。响应目标与各方案的具体条款以授权方案页为准。


7. 常见问题

Python 可以在没有安装 Excel 的机器上自动化 Excel 吗?

可以,文件处理类任务完全不需要桌面 Excel,本文所有示例都在无 Excel 参与的环境运行。例外是 xlwings 经典版,它驱动的是本机的 Excel 应用。”不需要 Excel”指的是运行代码,不等于复现 Excel 的全部行为。

Python Excel 自动化用哪个库好?

看任务:数据分析用 pandas,改 .xlsx 模板用 openpyxl,写新的格式化报表用 XlsxWriter,驱动 Excel 本身用 xlwings。要用一套 API 覆盖读取、编辑、计算、转换和保护,可以用 Spire.XLS for Python,本文的示例即基于它。

openpyxl 能重算 Excel 公式吗?

不能。它保留公式串但没有计算引擎,由它写入的公式不携带计算结果;data_only=True 读取的是文件里已有的缓存,因此对这类单元格读出 None。需要计算并持久化结果时,参考本文的公式重算部分。

怎么定时运行 Python Excel 脚本?

用 Windows 任务计划程序或 cron 让操作系统启动脚本;任务计划程序可配置补跑错过的任务,标准 cron 对离线期间的错过不做补跑。脚本直接处理工作簿文件,运行机器上不需要装桌面 Excel。

怎么把 Excel 转成 PDF?

设置页面参数(方向、缩放)后由库自行渲染导出工作表。Spire.XLS 的工作表级 SaveToPdf 一行完成;开源路线需要另配转换工具并自行处理版式。


8. 总结

Python Excel 自动化的跨度很大:五行代码的单元格更新,到合并文件、标记异常、重算公式并定时交付 PDF 和加密工作簿的完整管线。pandas、openpyxl 这些库在分析和单一操作上依然是好选择;当任务成为流程,多格式输入、计算结果输出、按计划交付,一套统一的工作簿 API 加上持续的版本维护和支持渠道,才是把它带进生产的组合。本文从单元格更新到端到端交付的每一段代码,都基于 Spire.XLS for Python 完整跑通。

准备评估时,可以直接安装 Free Spire.XLS for Python 做基础验证;商业项目建议申请一个月的临时许可证,用你自己的工作簿完整测试后再选择授权。