Excel 文件自动判分功能 · 技术设计文档¶
适用场景：信息技术上机练习 / 作业 / 可信局域网环境的 Excel 操作题自动判分（计算机二级风格）。 定位：在现有「局域网答题」平台（FastAPI + SQLite + 原生 JS）上新增一个独立的 Excel 判分模块，复用其账号 / 房间 / Web 框架，但判分逻辑自包含。 本文档同时面向开发者（架构、数据结构、接口、测试用例）与非技术人员（功能说明、用例），第 1 节与第 5 节偏业务，其余偏技术。
0. 关键澄清与默认设计（先对齐 5 个问题）¶
| # | 澄清点 | 本文采用的设计 |
|---|---|---|
| 1 | 判分对象 | ① 学生提交的 Excel 文件（作业/练习）；② 教师提交的标准答案 Excel，用于预先提取考点规则。两者都是普通 .xls/.xlsx。支持「单文件多 Sheet 判分」与「批量多文件判分（整班）」。 |
| 2 | 判分规则 | 四类全支持：固定单元格值比对 / 公式文本校验 / 公式结果校验 / 格式·图表·批注等。规则可由教师自由组合并编排进对应「小题」（一道小题 = 多条规则，多条小题 = 一份试卷），每条规则配分值，支持部分得分。 |
| 3 | 输入/输出 | 输入：多个 Excel 文件（批量）或单个文件内多个 Sheet。输出：总分汇总表（每生总分）+ 逐文件逐规则明细（每条规则 对/错、期望/实际）+ 错误项标注（定位到单元格/工作表）+ 导出 CSV。 |
| 4 | 技术环境 | 前端：原生 HTML/JS + ExcelJS/SheetJS（读文件）+ HyperFormula（算公式结果）。后端：Python（FastAPI + SQLite + pandas 导出）。必须 Web 界面；兼容 .xls 与 .xlsx。 |
| 5 | 文档读者 | 开发者看第 2–6 节（架构/数据结构/接口/测试）；非技术看第 1、5 节（功能与用例）。 |
判分执行位置（最重要技术决策）：本场景为练习 / 作业 / 可信局域网环境，采用 「前端判分」 架构——判分引擎跑在学生浏览器本机，交卷即出分，不依赖服务器算力，服务端也无需安装 MS Office / WPS / LibreOffice。后端（FastAPI+SQLite）只负责存规则、收文件、存成绩、出汇总，信任前端回传的分数（可信环境，不防蓄意造假）。 公式结果计算：浏览器端用 HyperFormula 直接按 Excel 语义计算公式结果（等效于把「LibreOffice 无头重算」搬到前端，且无进程管理负担）；读文件用 ExcelJS（样式/公式齐全）+ SheetJS（兼容
.xls读值）。这套 JS 引擎也可在 Node 后端复用，作为将来若需「权威重判」时的同一份真相源（当前可信场景不启用）。
1. 功能需求（非技术人员也能看懂）¶
1.1 背景¶
信息技术上机练习、计算机二级都有大量 Excel 操作题（求和、函数、格式、图表、透视表）。传统人工阅卷慢且易错，需要一个提交即判分的系统：学生交 Excel 文件，系统对照教师预设的考点规则，逐条核对，自动算出分数并指出错在哪。本方案面向练习/作业/可信局域网，学生本机算分、即时反馈，服务器只做存档与汇总。
1.2 角色与用例¶
| 角色 | 核心用例 |
|---|---|
| 教师（出题人） | 上传标准答案 Excel → 前端自动提取候选考点，或教师手工录入/勾选规则 → 把规则编排成若干「小题」并定分 → 发布判分任务 |
| 学生 | 在 Web 页上传自己的 Excel 作业（支持批量：整班一次交多份）→ 本机立即判分，看到总分与逐条明细、错在哪 |
| 教务/管理员 | 查看整班成绩汇总、导出 CSV、可下载学生原文件复核 |
1.3 功能清单（FR）¶
- FR1 标准答案规则提取：上传标准答案
.xlsx/.xls，前端解析后给出可勾选的考点候选（单元格值、公式、格式等），教师确认即生成规则库。 - FR2 规则定义与组合：支持单元格值、公式文本、公式结果、数字格式、字体、填充、边框、对齐、工作表名/隐藏、图表、透视表、批注等类型；教师可改期望值、运算符、分值、容差；多条规则组合进一个「小题」。
- FR3 文件提交：单文件多 Sheet、批量多文件（按文件名/学号匹配学生）两种入口；前端即时判分。
- FR4 前端自动判分引擎：按规则逐条读取学生文件并校验，支持数值容差、文本包含/正则、部分得分；公式结果由 HyperFormula 计算。
- FR5 结果输出：总分汇总、逐文件逐规则明细、错误项标注（单元格级）、导出 CSV/打印；教师可下载学生原文件复核。
- FR6 格式兼容：
.xls与.xlsx均支持（.xls经 SheetJS 读值/公式，样式题建议用.xlsx）。 - FR7 Web 界面：原生 HTML/JS，无需安装客户端。
1.4 非功能需求（NFR）¶
- 算力：判分在客户端完成，服务端几乎零计算压力，天然支持整班并发提交。
- 跨平台：后端 Python 可跑 Linux/Windows；前端依赖现代浏览器（File API、可选 Web Worker）。
- 精度：公式结果以 HyperFormula 计算为准；与微软官方存在细微差异时，在配置中标注。
- 安全：后端仍做文件类型/大小校验；可信环境不校验收到的分数，但存原始文件供教师抽查复核。
2. 总体架构¶
2.1 分层（判分在客户端，后端只存）¶
学生浏览器（本机判分，零服务器算力）
┌──────────────────────────────────────────┐
│ Web 前端（原生 HTML/JS） │
│ · 教师：定义/编排规则（规则存后端） │
│ · 学生：上传 Excel → 前端判分引擎 │
│ ExcelJS / SheetJS 读文件 │
│ HyperFormula 算公式结果 │
│ 逐规则校验 + 计分（即时反馈） │
│ POST { 原始文件, 总分, 逐规则明细 } │
└───────────────────┬──────────────────────┘
│ HTTP / JSON (FastAPI, Python)
┌───────────────────▼──────────────────────┐
│ 后端服务层（FastAPI + SQLite） │
│ · 规则 CRUD / 下发 │
│ · 接收提交：文件落盘 + 成绩入库（不重算） │
│ · 汇总 / 导出 CSV（pandas） │
│ ※ 信任前端回传分数（可信局域网场景） │
└───────────────┬──────────────────────────┘
┌───────────────▼──────────────────────────┐
│ 存储层（SQLite，复用现有） │
│ exam / question / rule / │
│ submission / grade / grade_detail │
│ 文件：学生原始 Excel（磁盘目录） │
└──────────────────────────────────────────┘
2.2 判分主流程¶
教师：上传标准答案
└─> [前端解析] 提取单元格/公式/格式候选
└─> [教师确认/编排] 规则存后端（excel_rule，挂到小题→试卷）
学生：打开提交页 → 拉取本题规则 → 选 Excel 上传
└─> [前端] ExcelJS/SheetJS 读文件 + HyperFormula 算结果
└─> [前端判分引擎] 逐规则校验、计分（即时看到对错与总分）
└─> POST 原始文件 + 总分 + 逐规则明细 到后端
后端：存文件 + 写 grade/grade_detail + 汇总
教师：结果报表看整班汇总、导出 CSV、可下载学生原文件复核
2.3 前端公式计算策略（HyperFormula）¶
| 环节 | 实现 | 说明 |
|---|---|---|
| 读文件 | ExcelJS（.xlsx 样式/公式齐全）/ SheetJS（.xls 读值+公式文本） |
浏览器内纯 JS，无需上传即可解析 |
| 算公式结果 | HyperFormula |
按 Excel 语义重算整表，拿到真实 .Value，规避 openpyxl「只读到缓存值」的坑 |
| 降级 | 无 HyperFormula 覆盖的函数 | 该类规则改判「公式文本比对」（检查函数名/$ 绝对引用）并提示 |
后端不参与公式重算；因此服务端完全不依赖 Office/LibreOffice，也不存在 COM 进程泄漏问题。
3. 数据结构¶
3.1 SQLite 表（复用现有连接与账号/房间隔离）¶
-- 判分任务（一份试卷/一次作业）
CREATE TABLE excel_exam (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
desc TEXT,
school_id INTEGER, -- 复用多学校隔离
room_code TEXT, -- 复用房间隔离（可选）
created_by INTEGER,
status TEXT DEFAULT 'draft', -- draft|published|closed
created_at TEXT
);
-- 小题（可挂父题做材料/大题）
CREATE TABLE excel_question (
id INTEGER PRIMARY KEY,
exam_id INTEGER,
parent_id INTEGER, -- 材料大题头行
no INTEGER, -- 小题序号
title TEXT,
score REAL -- 小题总分 = 其下规则分之和（冗余存储）
);
-- 考点规则（判分最小单元；后端存储、前端拉取执行）
CREATE TABLE excel_rule (
id INTEGER PRIMARY KEY,
exam_id INTEGER,
qid INTEGER, -- 所属小题
sheet TEXT, -- 工作表名；"*" 表示任意/全部表
addr TEXT, -- 单元格或区域，如 D3 / A2:A10
target TEXT, -- cell|range|sheet|workbook
check TEXT, -- 见 3.2 类型枚举
expected TEXT, -- 期望值（JSON 字符串，按 check 类型解释）
op TEXT DEFAULT 'eq', -- eq|ne|contains|regex|in|gte|lte
score REAL,
tolerance REAL DEFAULT 0, -- 数值容差
must_recalc INTEGER DEFAULT 0,-- 1=依赖公式结果（前端用 HyperFormula 算）
desc TEXT, -- 考点说明（展示给学生）
order_no INTEGER
);
-- 学生提交（一次提交 = 一份文件；成绩来自前端回传）
CREATE TABLE excel_submission (
id INTEGER PRIMARY KEY,
exam_id INTEGER,
user_id INTEGER, -- 复用账号体系
filename TEXT,
raw_path TEXT, -- 学生原始文件（落盘，供复核）
status TEXT DEFAULT 'graded',
submitted_at TEXT
);
-- 判分结果（一次提交一场；total/passed 来自前端回传，后端存证）
CREATE TABLE excel_grade (
id INTEGER PRIMARY KEY,
submission_id INTEGER,
total REAL, -- 前端回传总分
passed INTEGER, -- 命中规则数
total_rules INTEGER,
created_at TEXT
);
-- 逐规则明细（错误标注来源；前端回传，后端存证）
CREATE TABLE excel_grade_detail (
id INTEGER PRIMARY KEY,
grade_id INTEGER,
rule_id INTEGER,
qid INTEGER,
pass INTEGER,
actual TEXT, -- 学生文件实际值（JSON）
expected TEXT, -- 期望（冗余，便于展示）
score_got REAL,
note TEXT -- 错误标注，如 “D3 期望 SUMIFS，实际为空”
);
3.2 规则 JSON Schema（核心，前端引擎直接消费）¶
{
"rule_id": "r001",
"qid": 1,
"sheet": "销售汇总",
"addr": "D3",
"target": "cell",
"check": "formula_text",
"expected": "=SUMIFS($B$2:$B$100,C2:C100,\"已完成\")",
"op": "contains",
"score": 2,
"tolerance": 0,
"must_recalc": false,
"desc": "D3 使用 SUMIFS 按状态汇总已完成金额"
}
check 类型枚举与取值：
| check | 读取内容 | expected 解释 |
|---|---|---|
cell_value |
单元格值 | 文本/数值；op 支持 eq/contains/regex/gte/lte；数值带 tolerance |
formula_text |
公式源码 | 文本；op contains 可查函数名/$ 绝对引用 |
formula_result |
HyperFormula 计算结果 | 同 cell_value，但 must_recalc=1 |
number_format |
数字格式串 | 如 0.00%、yyyy/mm/dd |
font |
字体对象 | JSON：{name,size,bold,color} 逐字段比对 |
fill |
填充色 | JSON：{color} |
border |
边框 | JSON：{style,color}（四边） |
alignment |
对齐 | JSON：{horizontal,vertical,wrap} |
sheet_name |
工作表名 | 文本 eq/contains |
sheet_hidden |
隐藏状态 | bool |
chart |
图表 | JSON：{type,data_range} |
pivot |
透视表 | JSON：{fields,rows}（ExcelJS 解析能力有限，部分属性降级） |
comment |
批注 | 文本（批注内容） |
3.3 文件存储¶
- 学生原始文件：
uploads/excel/{exam_id}/{user_id}_{sub_id}_{filename}（落盘供教师复核；前端判分用同一份文件，无需服务端再读）。
4. 接口设计¶
4.1 页面路由¶
| 页面 | 路由 | 说明 |
|---|---|---|
| 判分工作台（教师） | /excel |
建考试、传标准答案、编排规则、看汇总 |
| 学生提交页 | /excel/submit?exam=ID |
上传作业（单/批量），前端即时判分 |
| 结果报表 | /excel/result?exam=ID |
汇总 + 逐生明细 + 导出 + 下载原文件复核 |
4.2 REST API¶
| 方法 | 路径 | 说明 |
|---|---|---|
| POST | /api/excel/exams |
新建判分任务（标题/描述） |
| POST | /api/excel/exams/{id}/answer |
上传标准答案文件（后端仅落盘存档；候选考点由前端解析供教师勾选） |
| PUT | /api/excel/exams/{id}/rules |
保存规则库（教师确认/编排后的完整 JSON，前端组装后回传） |
| GET | /api/excel/exams/{id}/rules |
学生端拉取本题规则用于本机判分 |
| POST | /api/excel/submit |
学生提交：multipart 文件 + JSON {total, details:[{rule_id,pass,actual,expected,score_got,note}]}；后端只存不重算 |
| GET | /api/excel/grade/{submission_id} |
单份明细 |
| GET | /api/excel/exams/{id}/summary |
整班汇总（每生总分、各小题均分） |
| GET | /api/excel/exams/{id}/export?fmt=csv |
导出成绩 CSV（pandas） |
信任模型：
/api/excel/submit回传的total/details直接入库作为正式成绩（可信局域网/练习场景）。后端仅做文件落盘与结构校验；如需将来升级为「考试防作弊」，可改为后端用同一 JS 引擎（Node）重判，接口不变。
4.3 前端判分引擎接口（浏览器内 JS 模块）¶
// 主入口：读文件 → 逐规则校验 → 返回总分与明细
async function gradeWorkbook(file, rules) {
const wb = await readWorkbook(file); // ExcelJS / SheetJS
const hf = buildHyperFormula(wb); // 建计算模型，拿公式结果
const details = rules.map(r => checkRule(wb, hf, r));
const total = details.reduce((s, d) => s + d.score_got, 0);
return { total, details };
}
// 单条规则校验
function checkRule(wb, hf, rule) {
// 定位 sheet/addr → 按 check 取 actual → 按 op/tolerance 比对 expected
// 返回 { pass, actual, score_got, note }
}
// 公式结果：HyperFormula 计算（替代 LibreOffice 重算）
function cellResult(hf, sheet, addr) { return hf.getCellValue(sheet, addr); }
同一套
gradeWorkbook/checkRule逻辑若日后需在服务端跑（权威重判），可原样迁移到 Node，实现前后端单一真相源。
5. 判分引擎详细设计（业务 + 技术）¶
5.1 逐规则校验逻辑（运行在浏览器）¶
- 定位工作表：按
sheet（支持*通配 / 按序号）；找不到表 → 该规则判 0 分并标注「工作表缺失」。 - 读取目标：按
target/addr用 ExcelJS 读值或公式或样式对象；formula_result走 HyperFormula 取计算值。 - 比较：按
op比较actual与expected： - 数值：
abs(actual-expected) <= tolerance视为相等； - 文本：
eq/contains/regex/in； - 公式文本：
op=contains常用于「是否用了 SUMIFS」「是否有$绝对引用」。 - 得分：
pass则该规则得score，否则 0；小题分 = Σ规则分；总分 = Σ小题分。 - 错误标注：写
note，如「D3 期望含 SUMIFS，实际为空」「字体期望微软雅黑，实际宋体」。
5.2 容错与降级¶
- 文件损坏/打不开：前端提示，不允许提交。
- HyperFormula 不支持的某函数：该
formula_result规则降级为formula_text比对并提示。 .xls样式不全：格式类规则在.xls下可能读不到，建议格式题要求.xlsx，或在规则设计时为.xls提供降级（仅校验值/公式文本）。
5.3 批量匹配¶
- 批量上传时按文件名解析学号/姓名（如
20260001_张三.xlsx），匹配账号；匹配不上则按文件名展示，教师可事后补认领。
6. 测试用例¶
6.1 单元测试（各规则类型，用样例文件，浏览器内跑）¶
| 用例 | 输入 | 期望 |
|---|---|---|
| 单元格值相等 | D3=1256.32，规则 cell_value eq 1256.32 |
pass |
| 数值容差 | D3=1256.3，tolerance=0.1 | pass |
| 公式文本 | D3==SUM($B$2:$B$9)，规则 contains SUM 且 contains $ |
pass |
| 公式结果 | HyperFormula 算得 D3=1256.32，规则 formula_result eq 1256.32 |
pass |
| 字体 | A1 微软雅黑14加粗，规则 font 匹配 | pass |
| 数字格式 | D3 0.00%，规则匹配 |
pass |
| 图表 | 存在簇状柱形图数据源 A2:C10 | pass |
| 工作表名 | 表名「销售汇总」 | pass |
| 否定 | 实际为空，期望非空 | fail，note 标注 |
6.2 集成测试（端到端）¶
- 教师上传标准答案 → 前端返回候选考点 → 教师勾选 5 条规则编排成 2 小题（小题1=3分，小题2=4分）→ 发布。
- 学生 A 上传全对文件 → 前端判分总分 7，明细全 pass，POST 后端。
- 学生 B 上传部分错文件（1 条值错、1 条格式错） → 前端总分 4.5，明细标注错项与单元格，POST 后端。
- 教师结果报表：汇总显示 A=7、B=4.5，可下载两人原文件复核。
- 导出 CSV → 含 学号/姓名/总分/各小题分。
.xls文件提交 → SheetJS 读值正常判分（样式题按降级处理）。
6.3 二级样题示例¶
- 样题：在「销售汇总」表 D3 用
SUMIFS按状态汇总「已完成」金额，要求绝对引用，A1 设为微软雅黑 14 加粗，插入簇状柱形图数据源 A2:C10。 - 规则：
formula_text contains SUMIFS、formula_text contains $、font {name:微软雅黑,size:14,bold:true}、chart {type:柱形图,range:A2:C10}。 - 判分：前端逐条核对，满分如 6 分，缺图表扣 2 分并标注「未检测到源 A2:C10 的柱形图」。
7. 风险与注意事项（前端判分版）¶
- 信任前端分数：可信局域网/练习可接受；若升级为正式考试，必须后端权威重判（预留 Node 复用同一引擎），否则分数可被伪造。
- HyperFormula 公式覆盖率：与微软 Excel 存在细微差异（尤其财务/数组函数），复杂题需实测；其优势是直接计算，规避 openpyxl「缓存值空」问题。
.xls样式：SheetJS 社区版读.xls样式不全，格式类考点建议要求.xlsx；值/公式文本可用。- 浏览器兼容与性能：需现代浏览器；大 Excel 解析可能卡顿，可用 Web Worker 放到后台线程避免界面冻结。
- 文件落盘：后端仍做类型/大小校验并保存原文件，供教师抽查复核（虽不自动重判，但保留证据）。
- 图表/透视表解析：ExcelJS 对图表、透视表的读取能力有限，相关考点设计时需实测，必要时降级为部分分或仅校验存在性。
8. 后续规划（可选）¶
- 标准答案「一键提取 + 教师微调」降低出题成本（前端解析候选）。
- 规则模板市场（二级常考考点预置模板）。
- 与现有「整卷测验」打通：Excel 判分作为整卷里的一种题型。
- 若需防作弊升级：后端起 Node 服务复用同一 JS 引擎做权威重判，接口不变。
本文档为设计稿，已按「练习/作业/可信局域网」场景定为前端判分、后端只存。编码前请就第 0 节的 5 项默认设计进行评审确认。