Python生成Excel受保护模板：锁定范围与允许编辑区

脚本专家

集团财务中心每季度要向十几个部门下发同一张《部门费用预算申报表》：科目、计量单位、单价都是统一口径，部门只能填申报数量和备注两列，金额列由“数量 × 单价”自动算出。裸表直接发出去，收回来往往已经变形——有人改单价凑总数，有人把公式覆盖成手填数字，有人插了一行把合计区间截断，还有人重排科目顺序导致无法与统一口径对齐。

用 Excel 自带的工作表保护能解决这类问题。难点不在“会不会用”，而在于把这件事做成可重复下发的脚本模板时，有几处默认行为与直觉相反：单元格天生就是锁定态；公式隐藏挂在 Range 上而不是 Style 上；多传一个枚举参数会把锁开的权限全部放回去。手工勾选界面时不太会踩到这些坑，写成脚本后一个参数就足以让整份模板形同虚设。

本文用 Free Spire.XLS for Python 把模板生成固定成一段脚本，并按“要下几个决定”的顺序逐条说明实测结果。安装方式：

2. pip install spire.xls.free

复制代码

示例裸表共 12 个科目：第 4 行是表头，第 5 至 16 行是明细，第 17 行是合计；列为 A(No.)、B(科目)、C(单位)、D(申报数量)、E(单价)、F(金额)、G(备注)。

决定一：交给填报人的格子划在哪里

可填区是这份模板的全部对外接口，先把它定死，后面所有设置都围着它转。开放的范围只有两段：

2. OPEN_RANGES = [("申报数量", "D5:D16"), ("备注说明", "G5:G16")]
3. LOCK_RANGES = ["A1:G4", "A5:C16", "E5:F16", "A17:G17"]

复制代码

这两段范围要用两套机制同时标注。一套是给填报人看的：把可填区刷成浅黄底，打开文件一眼就知道哪里能写。另一套是给 Excel 看的：把范围注册成“允许编辑区域”，并给它们起名字。

2. from spire.xls import Workbook, ExcelVersion, SheetProtectionType
3. from spire.xls.common import Color
5. PWD = "CW-2026Q4"
6. OPEN_RANGES = [("申报数量", "D5:D16"), ("备注说明", "G5:G16")]
7. LOCK_RANGES = ["A1:G4", "A5:C16", "E5:F16", "A17:G17"]
9. wb = Workbook()
10. wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
11. sheet = wb.Worksheets[0]
13. for _, addr in OPEN_RANGES:
14.     sheet.Range[addr].Style.Color = Color.get_LightYellow()
16. for name, addr in OPEN_RANGES:
17.     sheet.AddAllowEditRange(name, sheet.Range[addr])

复制代码

两套机制叠加不是冗余，因为它们的生效层级不同。单元格锁定的判定逻辑是“单元格被锁定 + 工作表已保护 → 不可编辑”；而允许编辑区域是在工作表保护这一层开的口子——填报人即使撤销保护、重新加保护（这种情况下允许编辑区域会丢失），可填区依然能写，因为那些单元格本身处在未锁定状态。反过来，注册了允许编辑区域，即使锁定标记没来得及清，Excel 也会放行。两者一起用，任何一套被误操作都不会立刻把模板变成死表。

决定二：锁定标记从哪里来

给锁定范围设 Style.Locked = True 看起来是“把要保护的地方锁上”，实际执行时会发现整张表都动不了。原因是 Excel 对单元格的默认值就是锁定：新工作簿里每个单元格的 Locked 都是 True，工作表一旦保护，未被显式改为 False 的区域全部进入锁定态。示例裸表进场时读回来就是这样：工作表受保护为 False，但 E5 的锁定标记已经是 True。

2. src_wb = Workbook()
3. src_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
4. src_sheet = src_wb.Worksheets[0]
5. print("工作表受保护=%s，E5 锁定标记=%s"
6.       % (src_sheet.IsPasswordProtected, src_sheet.Range["E5"].Style.Locked))
7. src_wb.Dispose()

复制代码

所以正确的动作顺序是先清零、再分档设置：

2. sheet.Range["A1:G60"].Style.Locked = False   # 先把整片区域的锁定标记清零
3. for addr in LOCK_RANGES:
4.     sheet.Range[addr].Style.Locked = True     # 再只把该锁的段落锁回去

复制代码

清零用 A1:G60 而不是 AllocatedRange。AllocatedRange 只覆盖当前有内容的区域（示例表到第 23 行说明文字为止），而填报人使用过程中可能往下写、往右拉，留出余量可以避免出现“边缘格子锁着但看不出来”的情况。反过来说，清零范围也不能开得过大（例如整列），否则生成的 xlsx 会写入大量冗余样式，文件体积反而膨胀。

决定三：公式要不要让填报人看见

金额列和合计行属于“能看不能改”，但只设锁定还不够——单击金额单元格，公式会直接显示在编辑栏里。隐藏公式要单独设置：

2. sheet.Range["F5:F17"].IsFormulaHidden = True

复制代码

IsFormulaHidden 是 Range 上的成员，不在 Style 上；写成 sheet.Range["F5:F17"].Style.IsFormulaHidden 会找不到属性。隐藏只在工作表保护生效时才有意义——它改变的是编辑栏的显示，不改变单元格的求值。

把保存后的 xlsx 拆开看 xl/styles.xml，带 hidden="1" 的样式记录是 2 条，而不是 13 条。原因在于样式是共享的：F5:F16 这 12 个金额单元格共用同一个样式，F17 合计行单独一个，所以最终只需要两条记录。这也说明公式隐藏是挂在单元格样式上的，对整片区域设置只会落到那些实际用到的样式上，不会逐格复制。

决定四：保护挂在工作表上还是工作簿上

Spire.XLS 提供两个层级的保护：工作表级 sheet.Protect() 和工作簿级 wb.ProtectWorkbook()。工作簿级的代价需要实测才能看出来——把示例表加上工作簿保护后另存，再看文件开头的四个字节：

2. import os
3. import tempfile
5. probe_wb = Workbook()
6. probe_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
7. probe_wb.ProtectWorkbook(True, True, PWD)
8. probe_path = os.path.join(tempfile.gettempdir(), "_wb_probe.xlsx")
9. probe_wb.SaveToFile(probe_path, ExcelVersion.Version2013)
10. probe_wb.Dispose()
12. with open(probe_path, "rb") as f:
13.     print("文件头：%r" % f.read(4))

复制代码

输出为 b'\xd0\xcf\x11\xe0'，这是 OLE2 复合文档的魔数，也就是 .xls 的 BIFF 二进制格式。文件名仍然叫 .xlsx，但内容已经不是 OOXML 包，Excel 打开这类文件会提示格式与扩展名不符。工作簿级保护因此不适合“下发一份 .xlsx 模板”的场景，本文只使用工作表级。

工作表保护还有一个参数坑。Protect() 可以只传密码，也可以多传一个 SheetProtectionType：

2. sheet.Protect(PWD)                              # 本文采用
3. # sheet.Protect(PWD, SheetProtectionType.All)  # 不要这样写
4. wb.SaveToFile("部门费用预算申报表_2026Q4_下发模板.xlsx", ExcelVersion.Version2013)
5. wb.Dispose()

复制代码

同一个模板、同一套锁定范围，两种写法写进包里的 sheetProtection 节点差别很大。下面的函数把同一条建表流程跑两遍，只换最后一步写法，再把两次的节点原文取出来：

2. import os
3. import re
4. import tempfile
5. import zipfile
7. def sheet_protection_tag(mode):
8.     m_wb = Workbook()
9.     m_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
10.     m_sheet = m_wb.Worksheets[0]
11.     m_sheet.Range["A1:G60"].Style.Locked = False
12.     for addr in LOCK_RANGES:
13.         m_sheet.Range[addr].Style.Locked = True
14.     for name, addr in OPEN_RANGES:
15.         m_sheet.AddAllowEditRange(name, m_sheet.Range[addr])
16.     if mode == "all":
17.         m_sheet.Protect(PWD, SheetProtectionType.All)
18.     else:
19.         m_sheet.Protect(PWD)
20.     # 文件名带上密码，两种写法的产物不会互相覆盖，也不会盖掉别的申报表跑出来的探针
21.     m_path = os.path.join(tempfile.gettempdir(), "_prot_%s_%s.xlsx" % (mode, PWD))
22.     m_wb.SaveToFile(m_path, ExcelVersion.Version2013)
23.     m_wb.Dispose()
24.     with zipfile.ZipFile(m_path) as z:
25.         xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")
26.     return re.search(r"<sheetProtection[^>]*/>", xml).group(0)
28. print("Protect(PWD) → %s" % sheet_protection_tag("plain"))
29. print("Protect(PWD, All) → %s" % sheet_protection_tag("all"))

复制代码

两个探针文件写在系统临时目录里，不进交付目录：_prot_plain_CW-2026Q4.xlsx 与 _prot_all_CW-2026Q4.xlsx。实测输出为：

2. Protect(PWD) → <sheetProtection password="E550" sheet="1" objects="1" scenarios="1" />
3. Protect(PWD, All) → <sheetProtection password="E550" sheet="1" formatCells="0" formatColumns="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0" deleteRows="0" sort="0" autoFilter="0" pivotTables="0" />

复制代码

OOXML 里这些属性表示的是“该操作被禁止”，值为 0 即禁止被取消、操作被放行。多传 SheetProtectionType.All 的结果是额外放开了 11 项：设置单元格格式、设置列宽行高、插入行列、删除行列、插入超链接、排序、筛选、数据透视表。用本机 Excel 分别打开两个产物确认：

2. 检查项              Protect(PWD)   Protect(PWD, SheetProtectionType.All)
3. 单元格受保护        是             是
4. D5 申报数量可写     通过           通过
5. G9 备注可写         通过           通过
6. E5 单价写入         被拦           被拦
7. B5 科目写入         被拦           被拦
8. F5 金额写入         被拦           被拦
9. 插入整行            被拦           通过

复制代码

两者的差别落在“插入整行”这一行上。插入行会平移合计公式的引用区间，正是这份模板最不希望发生的事情之一；排序与筛选对一张固定科目顺序的申报表同样没有放开的需要。所以模板脚本里只传密码，让所有未显式放开的能力保持关闭。

决定五：密码怎么收回来

模板下发给部门之后，保护必须能撤销，否则回收的表格无法用统一脚本汇总。撤销用 Unprotect()，注意大小写是 Unprotect 而不是 UnProtect：

2. wb = Workbook()
3. wb.LoadFromFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
4. s = wb.Worksheets[0]
5. before = s.IsPasswordProtected   # True
6. s.Unprotect(PWD)
7. after = s.IsPasswordProtected    # False
8. s.Range["G17"].Text = "已于 2026-10-09 由财务中心复核"
9. wb.SaveToFile("部门费用预算申报表_2026Q4_回收解锁版.xlsx", ExcelVersion.Version2013)
10. wb.Dispose()
12. chk = Workbook()
13. chk.LoadFromFile("部门费用预算申报表_2026Q4_回收解锁版.xlsx")
14. still = chk.Worksheets[0].IsPasswordProtected
15. chk.Dispose()
16. print("解锁前：%s → 解锁后：%s → 另存读回：%s" % (before, after, still))

复制代码

输出为“解锁前：True → 解锁后：False → 另存读回：False”。解锁不会自动清除页面上的说明文字（“本表已开启保护……”），也不会清掉浅黄底色；回收环节要做的是撤销保护、写入复核标记、另存为独立文件，全程不动原模板。另存后重新加载读到的 IsPasswordProtected 仍是 False，说明解锁状态确实写进了文件，而不是只在内存里改了一下对象属性。

交付前的三层验证

模板生成完，需要回答“到底锁住了什么”。只看脚本里的赋值不够，因为 Style.Locked 是样式层的标记，最终是否生效取决于保存时有没有落到正确的样式记录上。验证做三层，每层换一个独立的观察者。

第一层，用 Spire.XLS 读回，逐个检查关键单元格：

2. chk = Workbook()
3. chk.LoadFromFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
4. s = chk.Worksheets[0]
5. checks = [
6.     ("工作表受保护", s.IsPasswordProtected),
7.     ("A4 表头锁定", s.Range["A4"].Style.Locked),
8.     ("B5 科目锁定", s.Range["B5"].Style.Locked),
9.     ("E5 单价锁定", s.Range["E5"].Style.Locked),
10.     ("F5 金额锁定", s.Range["F5"].Style.Locked),
11.     ("A17 合计锁定", s.Range["A17"].Style.Locked),
12.     ("D5 申报数量可改", not s.Range["D5"].Style.Locked),
13.     ("D16 申报数量可改", not s.Range["D16"].Style.Locked),
14.     ("G9 备注可改", not s.Range["G9"].Style.Locked),
15.     ("F5:F17 公式隐藏", s.Range["F5:F17"].IsFormulaHidden),
16. ]
17. chk.Dispose()
18. for label, value in checks:
19.     print("  %-18s %s" % (label, value))

复制代码

这一层能发现“设置没生效”，但发现不了“设置生效了、含义却和预期相反”——它读回的正是脚本刚写进去的标记。

第二层，用本机 Excel 实际打开。用 Excel 的 COM 接口加载模板，读它自己解析出来的权限标志，并真实写一次单元格、插一次行。这一层是唯一能证明“填报人看到的界面行为”与预期一致的手段，也是发现 SheetProtectionType.All 多开权限的途径。

第三层，把 xlsx 当 zip 拆开读 XML，绕开所有 Office 库，直接看包里的原文：

2. import re
3. import zipfile
5. z = zipfile.ZipFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
6. sheet_xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")
7. styles_xml = z.read("xl/styles.xml").decode("utf-8")
8. z.close()
10. sp = re.search(r"<sheetProtection[^>]*/>", sheet_xml)
11. pr = re.search(r"<protectedRanges>.*?</protectedRanges>", sheet_xml, re.S)
12. hidden = len(re.findall(r'<protection[^>]*hidden="(?:1|true)"', styles_xml))
13. unlocked = len(re.findall(r'<protection[^>]*locked="(?:0|false)"', styles_xml))
14. print(sp.group(0))
15. print(pr.group(0))
16. print("隐藏公式的样式 %d 条、未锁定样式 %d 条" % (hidden, unlocked))

复制代码

得到的关键结果：

2. <sheetProtection password="E550" sheet="1" objects="1" scenarios="1" />
3. <protectedRanges>
4.   <protectedRange sqref="D5:D16" name="申报数量" />
5.   <protectedRange sqref="G5:G16" name="备注说明" />
6. </protectedRanges>

复制代码

三个数字是：带隐藏标记的样式 2 条、未锁定样式 9 条、允许编辑区域 2 个。这一层的价值在于它不受任何库的读取实现影响——即使某个库把保护信息读错了，XML 里的原文仍然可查。

用到的类与成员

Workbook.LoadFromFile / SaveToFile：加载与保存，保存时用 ExcelVersion.Version2013 指定 OOXML 格式。
Workbook.ProtectWorkbook：工作簿级保护，实测会把文件写成 BIFF 二进制，本方案不使用。
Worksheet.Range[addr]：按 A1 地址取区域。
CellRange.Style：区域样式，可设 Color 底色、Font 字号字重。
Style.Locked：单元格锁定标记，必须在保护之前设置，且需从默认的 True 清零。
CellRange.IsFormulaHidden：隐藏编辑栏公式，挂在 Range 而非 Style 上。
Worksheet.AddAllowEditRange：注册允许编辑区域，参数是区域名与区域对象。
Worksheet.Protect：工作表保护，只传密码即可；传 SheetProtectionType 会额外放开权限。
Worksheet.Unprotect：用原密码撤销保护。
Worksheet.IsPasswordProtected：读回保护状态，用于验证。
Color.get_LightYellow：可填区底色。

适用前提与可延伸方向

这份模板的适用前提是“科目与单价由上级统一确定，下级只报数量”，也就是填报自由度被刻意压缩到两列。如果实际业务流程里部门需要自行增删科目，那么当前的锁定范围（A5:C16 与 E5:F16）就会成为阻碍，应改为只锁合计行与公式列，或者把科目表的维护权收回到财务中心、按部门裁剪后再下发。

可选的延伸方向包括：把可填区改成数据验证下拉（限定计量单位）配合条件格式（数量为空时标红），让填报质量在录入阶段就被约束；在模板里预留一行“复核”栏并由汇总脚本回写，形成下发—填报—复核—归档的闭环；把密码与锁定范围抽成配置文件，让同一套脚本支持多张不同口径的申报表。这些扩展都用同一组 API，改动集中在 OPEN_RANGES 与 LOCK_RANGES 两个常量上。