Python生成Excel受保护模板：锁定范围与允许编辑区
集团财务中心每季度要向十几个部门下发同一张《部门费用预算申报表》：科目、计量单位、单价都是统一口径，部门只能填申报数量和备注两列，金额列由“数量 × 单价”自动算出。裸表直接发出去，收回来往往已经变形——有人改单价凑总数，有人把公式覆盖成手填数字，有人插了一行把合计区间截断，还有人重排科目顺序导致无法与统一口径对齐。用 Excel 自带的工作表保护能解决这类问题。难点不在“会不会用”，而在于把这件事做成可重复下发的脚本模板时，有几处默认行为与直觉相反：单元格天生就是锁定态；公式隐藏挂在 Range 上而不是 Style 上；多传一个枚举参数会把锁开的权限全部放回去。手工勾选界面时不太会踩到这些坑，写成脚本后一个参数就足以让整份模板形同虚设。
本文用 Free Spire.XLS for Python 把模板生成固定成一段脚本，并按“要下几个决定”的顺序逐条说明实测结果。安装方式：
pip install spire.xls.free
示例裸表共 12 个科目：第 4 行是表头，第 5 至 16 行是明细，第 17 行是合计；列为 A(No.)、B(科目)、C(单位)、D(申报数量)、E(单价)、F(金额)、G(备注)。
决定一：交给填报人的格子划在哪里
可填区是这份模板的全部对外接口，先把它定死，后面所有设置都围着它转。开放的范围只有两段：
OPEN_RANGES = [("申报数量", "D5:D16"), ("备注说明", "G5:G16")]
LOCK_RANGES = ["A1:G4", "A5:C16", "E5:F16", "A17:G17"]
这两段范围要用两套机制同时标注。一套是给填报人看的：把可填区刷成浅黄底，打开文件一眼就知道哪里能写。另一套是给 Excel 看的：把范围注册成“允许编辑区域”，并给它们起名字。
from spire.xls import Workbook, ExcelVersion, SheetProtectionType
from spire.xls.common import Color
PWD = "CW-2026Q4"
OPEN_RANGES = [("申报数量", "D5:D16"), ("备注说明", "G5:G16")]
LOCK_RANGES = ["A1:G4", "A5:C16", "E5:F16", "A17:G17"]
wb = Workbook()
wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
sheet = wb.Worksheets
for _, addr in OPEN_RANGES:
sheet.Range.Style.Color = Color.get_LightYellow()
for name, addr in OPEN_RANGES:
sheet.AddAllowEditRange(name, sheet.Range)
两套机制叠加不是冗余，因为它们的生效层级不同。单元格锁定的判定逻辑是“单元格被锁定 + 工作表已保护 → 不可编辑”；而允许编辑区域是在工作表保护这一层开的口子——填报人即使撤销保护、重新加保护（这种情况下允许编辑区域会丢失），可填区依然能写，因为那些单元格本身处在未锁定状态。反过来，注册了允许编辑区域，即使锁定标记没来得及清，Excel 也会放行。两者一起用，任何一套被误操作都不会立刻把模板变成死表。
决定二：锁定标记从哪里来
给锁定范围设 Style.Locked = True 看起来是“把要保护的地方锁上”，实际执行时会发现整张表都动不了。原因是 Excel 对单元格的默认值就是锁定：新工作簿里每个单元格的 Locked 都是 True，工作表一旦保护，未被显式改为 False 的区域全部进入锁定态。示例裸表进场时读回来就是这样：工作表受保护为 False，但 E5 的锁定标记已经是 True。
src_wb = Workbook()
src_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
src_sheet = src_wb.Worksheets
print("工作表受保护=%s，E5 锁定标记=%s"
% (src_sheet.IsPasswordProtected, src_sheet.Range["E5"].Style.Locked))
src_wb.Dispose()
所以正确的动作顺序是先清零、再分档设置：
sheet.Range["A1:G60"].Style.Locked = False # 先把整片区域的锁定标记清零
for addr in LOCK_RANGES:
sheet.Range.Style.Locked = True # 再只把该锁的段落锁回去
清零用 A1:G60 而不是 AllocatedRange。AllocatedRange 只覆盖当前有内容的区域（示例表到第 23 行说明文字为止），而填报人使用过程中可能往下写、往右拉，留出余量可以避免出现“边缘格子锁着但看不出来”的情况。反过来说，清零范围也不能开得过大（例如整列），否则生成的 xlsx 会写入大量冗余样式，文件体积反而膨胀。
决定三：公式要不要让填报人看见
金额列和合计行属于“能看不能改”，但只设锁定还不够——单击金额单元格，公式会直接显示在编辑栏里。隐藏公式要单独设置：
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()。工作簿级的代价需要实测才能看出来——把示例表加上工作簿保护后另存，再看文件开头的四个字节：
import os
import tempfile
probe_wb = Workbook()
probe_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
probe_wb.ProtectWorkbook(True, True, PWD)
probe_path = os.path.join(tempfile.gettempdir(), "_wb_probe.xlsx")
probe_wb.SaveToFile(probe_path, ExcelVersion.Version2013)
probe_wb.Dispose()
with open(probe_path, "rb") as f:
print("文件头：%r" % f.read(4))
输出为 b'\xd0\xcf\x11\xe0'，这是 OLE2 复合文档的魔数，也就是 .xls 的 BIFF 二进制格式。文件名仍然叫 .xlsx，但内容已经不是 OOXML 包，Excel 打开这类文件会提示格式与扩展名不符。工作簿级保护因此不适合“下发一份 .xlsx 模板”的场景，本文只使用工作表级。
工作表保护还有一个参数坑。Protect() 可以只传密码，也可以多传一个 SheetProtectionType：
sheet.Protect(PWD) # 本文采用
# sheet.Protect(PWD, SheetProtectionType.All)# 不要这样写
wb.SaveToFile("部门费用预算申报表_2026Q4_下发模板.xlsx", ExcelVersion.Version2013)
wb.Dispose()
同一个模板、同一套锁定范围，两种写法写进包里的 sheetProtection 节点差别很大。下面的函数把同一条建表流程跑两遍，只换最后一步写法，再把两次的节点原文取出来：
import os
import re
import tempfile
import zipfile
def sheet_protection_tag(mode):
m_wb = Workbook()
m_wb.LoadFromFile("部门费用预算申报表_2026Q3_裸表.xlsx")
m_sheet = m_wb.Worksheets
m_sheet.Range["A1:G60"].Style.Locked = False
for addr in LOCK_RANGES:
m_sheet.Range.Style.Locked = True
for name, addr in OPEN_RANGES:
m_sheet.AddAllowEditRange(name, m_sheet.Range)
if mode == "all":
m_sheet.Protect(PWD, SheetProtectionType.All)
else:
m_sheet.Protect(PWD)
# 文件名带上密码，两种写法的产物不会互相覆盖，也不会盖掉别的申报表跑出来的探针
m_path = os.path.join(tempfile.gettempdir(), "_prot_%s_%s.xlsx" % (mode, PWD))
m_wb.SaveToFile(m_path, ExcelVersion.Version2013)
m_wb.Dispose()
with zipfile.ZipFile(m_path) as z:
xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")
return re.search(r"<sheetProtection[^>]*/>", xml).group(0)
print("Protect(PWD) → %s" % sheet_protection_tag("plain"))
print("Protect(PWD, All) → %s" % sheet_protection_tag("all"))
两个探针文件写在系统临时目录里，不进交付目录：_prot_plain_CW-2026Q4.xlsx 与 _prot_all_CW-2026Q4.xlsx。实测输出为：
Protect(PWD) → <sheetProtection password="E550" sheet="1" objects="1" scenarios="1" />
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 分别打开两个产物确认：
检查项 Protect(PWD) Protect(PWD, SheetProtectionType.All)
单元格受保护 是 是
D5 申报数量可写 通过 通过
G9 备注可写 通过 通过
E5 单价写入 被拦 被拦
B5 科目写入 被拦 被拦
F5 金额写入 被拦 被拦
插入整行 被拦 通过
两者的差别落在“插入整行”这一行上。插入行会平移合计公式的引用区间，正是这份模板最不希望发生的事情之一；排序与筛选对一张固定科目顺序的申报表同样没有放开的需要。所以模板脚本里只传密码，让所有未显式放开的能力保持关闭。
决定五：密码怎么收回来
模板下发给部门之后，保护必须能撤销，否则回收的表格无法用统一脚本汇总。撤销用 Unprotect()，注意大小写是 Unprotect 而不是 UnProtect：
wb = Workbook()
wb.LoadFromFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
s = wb.Worksheets
before = s.IsPasswordProtected # True
s.Unprotect(PWD)
after = s.IsPasswordProtected # False
s.Range["G17"].Text = "已于 2026-10-09 由财务中心复核"
wb.SaveToFile("部门费用预算申报表_2026Q4_回收解锁版.xlsx", ExcelVersion.Version2013)
wb.Dispose()
chk = Workbook()
chk.LoadFromFile("部门费用预算申报表_2026Q4_回收解锁版.xlsx")
still = chk.Worksheets.IsPasswordProtected
chk.Dispose()
print("解锁前：%s → 解锁后：%s → 另存读回：%s" % (before, after, still))
输出为“解锁前：True → 解锁后：False → 另存读回：False”。解锁不会自动清除页面上的说明文字（“本表已开启保护……”），也不会清掉浅黄底色；回收环节要做的是撤销保护、写入复核标记、另存为独立文件，全程不动原模板。另存后重新加载读到的 IsPasswordProtected 仍是 False，说明解锁状态确实写进了文件，而不是只在内存里改了一下对象属性。
交付前的三层验证
模板生成完，需要回答“到底锁住了什么”。只看脚本里的赋值不够，因为 Style.Locked 是样式层的标记，最终是否生效取决于保存时有没有落到正确的样式记录上。验证做三层，每层换一个独立的观察者。
第一层，用 Spire.XLS 读回，逐个检查关键单元格：
chk = Workbook()
chk.LoadFromFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
s = chk.Worksheets
checks = [
("工作表受保护", s.IsPasswordProtected),
("A4 表头锁定", s.Range["A4"].Style.Locked),
("B5 科目锁定", s.Range["B5"].Style.Locked),
("E5 单价锁定", s.Range["E5"].Style.Locked),
("F5 金额锁定", s.Range["F5"].Style.Locked),
("A17 合计锁定", s.Range["A17"].Style.Locked),
("D5 申报数量可改", not s.Range["D5"].Style.Locked),
("D16 申报数量可改", not s.Range["D16"].Style.Locked),
("G9 备注可改", not s.Range["G9"].Style.Locked),
("F5:F17 公式隐藏", s.Range["F5:F17"].IsFormulaHidden),
]
chk.Dispose()
for label, value in checks:
print("%-18s %s" % (label, value))
这一层能发现“设置没生效”，但发现不了“设置生效了、含义却和预期相反”——它读回的正是脚本刚写进去的标记。
第二层，用本机 Excel 实际打开。用 Excel 的 COM 接口加载模板，读它自己解析出来的权限标志，并真实写一次单元格、插一次行。这一层是唯一能证明“填报人看到的界面行为”与预期一致的手段，也是发现 SheetProtectionType.All 多开权限的途径。
第三层，把 xlsx 当 zip 拆开读 XML，绕开所有 Office 库，直接看包里的原文：
import re
import zipfile
z = zipfile.ZipFile("部门费用预算申报表_2026Q4_下发模板.xlsx")
sheet_xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")
styles_xml = z.read("xl/styles.xml").decode("utf-8")
z.close()
sp = re.search(r"<sheetProtection[^>]*/>", sheet_xml)
pr = re.search(r"<protectedRanges>.*?</protectedRanges>", sheet_xml, re.S)
hidden = len(re.findall(r'<protection[^>]*hidden="(?:1|true)"', styles_xml))
unlocked = len(re.findall(r'<protection[^>]*locked="(?:0|false)"', styles_xml))
print(sp.group(0))
print(pr.group(0))
print("隐藏公式的样式 %d 条、未锁定样式 %d 条" % (hidden, unlocked))
得到的关键结果：
<sheetProtection password="E550" sheet="1" objects="1" scenarios="1" />
<protectedRanges>
<protectedRange sqref="D5:D16" name="申报数量" />
<protectedRange sqref="G5:G16" name="备注说明" />
</protectedRanges>
三个数字是：带隐藏标记的样式 2 条、未锁定样式 9 条、允许编辑区域 2 个。这一层的价值在于它不受任何库的读取实现影响——即使某个库把保护信息读错了，XML 里的原文仍然可查。
用到的类与成员
Workbook.LoadFromFile / SaveToFile：加载与保存，保存时用 ExcelVersion.Version2013 指定 OOXML 格式。
Workbook.ProtectWorkbook：工作簿级保护，实测会把文件写成 BIFF 二进制，本方案不使用。
Worksheet.Range：按 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 两个常量上。
Re: Python生成Excel受保护模板：锁定范围与允许编辑区
这个分享很实在，尤其是把“允许编辑区域”和“单元格未锁定”两层机制分开讲清楚。之前很容易以为注册了允许编辑区域就万事大吉，没想到撤销保护再重新加保护后允许编辑区域会丢，而未锁定的单元格还能兜底。先 A1:G60 清零 Locked、再按 LOCK_RANGES 锁回去，这个顺序确实是关键，不然默认锁定会让整张表都动不了。用浅黄底标出可填区也很贴合下发模板的场景，填报人一眼就知道哪里能写。公式隐藏那块提醒得很及时，IsFormulaHidden 挂在 Range 上而不是 Style 上，这个坑很容易写错；拆 styles.xml 看到 hidden=1 只有两条，也说明样式共享，这个细节很有说服力。决定四标题停在“保护挂”，感觉后面应该要讲工作表保护密码和选项设置，期待补全。整体上这套思路先定对外接口，再处理锁定和公式隐藏，逻辑很顺，对多部门收表确实实用。Re: Python生成Excel受保护模板：锁定范围与允许编辑区
这个场景很真实，集团模板发下去最怕的就是格式和公式被改乱。你把“可填区既要刷色给人看，又要注册成允许编辑区域给 Excel 放行”这层说得很清楚，尤其是“单元格默认就是锁定态”和“先清零再分档锁回去”这两个坑，确实很多人写成脚本后才会踩到。公式隐藏挂在 Range 上而不是 Style 上，还有拆 xl/styles.xml 看到共享样式只生成两条记录，这个验证很有说服力。 另外代码里 AddAllowEditRange 那段似乎写成了 sheet.Range，循环里的 addr 好像没用上，正式版应该是 sheet.Range 吧？整体思路对季度下发模板很实用，也期待你把“决定四：保护挂”之后的内容继续补全，比如保护密码、允许筛选排序、插入行这些边界怎么处理。下发前用不同角色试填，专门试改单价、覆盖公式、撤销再保护，基本就能看出模板稳不稳。Re: Python生成Excel受保护模板：锁定范围与允许编辑区
这个场景太真实了，财务模板下发最头疼的就是收回来五花八门。楼主把"单元格天生锁定"这个反直觉的点专门拎出来讲很有必要，我当初也是想当然地写 Style.Locked = True，结果整张表全锁死，排查半天才发现默认值就是 True。 公式隐藏挂在 Range 而不是 Style 上、样式共享导致 styles.xml 只写两条记录，这两点分析得很细，之前只知道设置隐藏，没拆过文件看过底层行为，受教了。A1:G60 预留余量而不开整列这个取舍也说得在理，整列刷样式确实会让文件虚胖。 想请教两个问题：一是 AddAllowEditRange 的 name 参数，如果后续要按部门分发、每个部门单独改名字，会不会出现重名冲突或者被覆盖；二是帖子开头提到"多传一个枚举参数会把锁开的权限全部放回去"，具体是保护类型枚举那里有什么坑，能不能展开讲讲。 另外帖子最后"决定四：保护挂"似乎没写完，是不是被截断了？期待后面的内容，尤其是密码这块，硬编码在脚本里感觉也不太稳妥，不知道楼主有没有更好的下发方式。
页:
[1]