跳转至

Excel/WPS 表格操作题接入 —— 修改要点记录

目标：把已完成的 Excel 表格判分能力接入「实时答题」与「整卷答题」，题型标识 excel_wps。 架构：前端判分、后端只存（后端不解析 Excel、不内置 JSON 解析器、不重算公式），入库只存文件地址；判分 JS 以 URL 入库，由浏览器加载执行。 本文档随开发同步更新，每个任务记录「改了什么 / 为什么 / 影响面 / 回归注意」。

任务 1：数据库幂等迁移（已完成）

改了什么

  1. sitedb.py（init_db() 内，attempt_items 建表之后）
  2. ensure_col(c, "attempt_items", "excel_sub_id", "excel_sub_id INTEGER")

  3. 含义：整卷答题时，该小题的作答条目指向 excel_submission.id（Excel 操作题专用；普通题为 NULL）。

  4. sitedb.py（init_db() 末尾，status 配置之后） 新增两张留痕表（评论式注释已写入代码）：

  5. excel_submission：整卷 / 练习作答留痕 id, session_id, user_id, qn_id, qb_id, file_path, file_name, file_size, score, score_max, passed, failed, unknown, total_rules, detail(MEDIUMTEXT), created_at, updated_at, UNIQUE(session_id, user_id, qn_id)
    • 索引 idx_exsub_session / idx_exsub_user / idx_exsub_qn

  6. excel_live_sub：实时答题作答留痕（按房间） id, room_code, player_id, qid, qb_src, file_path, file_name, file_size, score, score_max, passed, failed, unknown, total_rules, detail, score_added, created_at, UNIQUE(room_code, player_id, qid)

    • 索引 idx_exlive_room / idx_exlive_player

  7. qbank.py（init_db() 内，多租户加列之后）

  8. 循环给 qb_questions 加 template_file_url / json_standard_url / judge_js_url（三列均为 TEXT，无 DEFAULT）。

  9. main.py（init_db() 内，parent_id 加列之后）

  10. 沿用该文件既有的 qcols 快照 + ALTER TABLE 写法，给实时题表 questions 加同样三列。

为什么这么做

  • 严格复用仓库既有幂等迁移套路：sitedb.ensure_col / qbank._ensure_col / main.init_db 的 qcols 快照，SQLite（打包 exe）与 MySQL（开发环境）双源都走得通。
  • TEXT 列一律不带 DEFAULT ''：MySQL 8 明确禁止 TEXT 带字面默认值（既有 qbank.py 的 code TEXT 也是无默认值写法）。
  • 两张提交表分开（整卷 / 实时）：避免实时场景的 player_id 与整卷的 user_id 混用导致 UNIQUE 语义不清；也避免依赖 SQLite 部分索引语法。
  • 存储字段用 file_path（相对路径）而非 URL：便于打包 exe 后按 BASE_DIR 重定位，URL 由后续 API 层生成。

影响面 / 回归注意

  • attempt_items 的既有 INSERT/UPDATE 语句全部显式列名，新增列不影响旧路径；但 UPDATE 覆盖分支目前还没写 excel_sub_id，留到「整卷提交」任务补。
  • dbcore._LONG_TEXT_COLS 已含 detail → MySQL 下自动成 MEDIUMTEXT，长明细不截断；file_path/file_name 落成 VARCHAR(255)，够用。
  • 新 DDL 不含分号字面量（dbcore.exec_script 按 ; 切分），可安全放进 exec_script；本次是 c.execute() 单条执行，更稳。

已做的验证

  • 在 quiz.db 副本上（临时目录，DB_PATH 指向副本）跑通全部 init_db()，并重复执行验证幂等；五张表的目标列全部存在（结果 ALL OK）。
  • 把新增 DDL 过一遍 dbcore.ddl() 的 MySQL 翻译分支（只翻译不连库）：AUTOINCREMENT → AUTO_INCREMENT PRIMARY KEY、REAL → DOUBLE、detail → MEDIUMTEXT、file_path → VARCHAR(255)、索引 IF NOT EXISTS 与部分 WHERE 被正确剥离，均可被 MySQL 接受。
  • 校验脚本已删除，未留在仓库中；未修改 原始 quiz.db。

任务 2：后端文件仓储与 API（已完成）

新增文件

文件 作用
excelgrade/store.py 统一文件仓储：落盘 / 读取 / 删除 / 迁移 / zip 打包，含全部路径安全校验
excelgrade/api.py APIRouter(prefix="/api/excel")：14 个接口，只做「文件进出 + 分数入库」，不解析 Excel、不跑判分

接口清单

教师端：GET /meta/{qid}（三件套状态回填）、POST /q/{qid}/files（上传/替换）、DELETE /q/{qid}/files/{kind}（移除）、POST /tmp/{key}/files（新建题目无 id 时的草稿暂存）、POST /q/{qid}/attach（草稿迁移到正式题目并改写库内地址）、GET /stats/{sid}（所有 Excel 小题的学生提交与得分）、GET /detail/{sub_id}（要点级明细）、GET /sub/{sub_id}/file（单份下载）、POST /zip/{sid}（勾选学生批量打包）。

学生端：GET /res/{qid}/{kind} 与 /res/tmp/{key}/{kind}（鉴权后取模板/JSON/JS）、POST /exam/{sid}/answer（整卷提交）、GET /exam/{sid}/{qn_id}/mine（查本人结果）、POST /live/submit（实时答题提交并累加实时分）。

改了什么 / 为什么

  1. main.py
  2. app.mount("/excelgrade", StaticFiles(...))：把 excelgrade/web 暴露出去，/excelgrade/js/*、/excelgrade/lib/* 供学生浏览器懒加载 EG 引擎（同源挂载，JSZip+DOMParser 读图表/条件格式才可用），/excelgrade/index.html 保留为教师备课工具页。
  3. ADMIN_PAGE_PREFIX 增加 "/excelgrade"，同时新增 EXCEL_PUBLIC_ASSET 例外：管理面守卫中间件放行 /excelgrade/js|lib|css/，否则学生拉不到判分引擎。
  4. try: from excelgrade import api as excel_api; app.include_router(...)：判分模块异常不能拖垮整站。
  5. excelgrade/__init__.py：Python 判分引擎改为软加载（缺 openpyxl 时降级 HAS_PY_ENGINE=False，调用才报错）。因为主应用 import excelgrade.api 会触发包体 __init__，而 openpyxl 在 requirements 里是可选依赖——硬失败会让整站起不来，Web 判分根本不需要它。
  6. 评分折算：前端回传 (score, score_max) → _ratio() → points_got = 小题分值 × 得分率；重传走 UNIQUE 冲突更新分支，连 excel_sub_id 一起更新（明细永远指向最新一次提交）。
  7. 实时端重复提交：score_added 记录上次加分，重传时按 增量 更新 players.score（只 diff），避免重复刷分。
  8. 回显策略：POST /exam/{sid}/answer 仅在 norm_policy(reveal_policy)=="inline"（教师选了「答完给解析」）时回传 inline.detail；实时端同理看房间 reveal_ana。这是用户明确要求的「跟随老师端策略」。
  9. 存储约定：落 BASE_DIR/uploads/excel/，刻意避开 static/——避免匿名直连拉走学生答卷；文件名要么是固定名（template.xlsx/standard.json/judge.js），要么是 时间戳_随机hex.后缀，绝不用用户传入文件名拼路径。
  10. .gitignore：新增 uploads/（含学生答卷，禁止入库）。

影响面 / 回归注意

  • /api/excel/* 不在 ADMIN_API_PREFIX 里（学生也要用），因此每个 handler 内部都自行鉴权：教职工走 qbank._scope+_can_write/_read_cond；学生按「本人参与的 session 含此题 / 所在房间的实时题表含此题」判定资源可读。
  • 磁盘清理：answers/、live/ 目录当前只增不删，教师删除场次不会自动清盘（后续如需清理再补定时任务）。
  • SCORE_PER_QUESTION=10 与 main.py 保持一致；若改实时每题基础分，两处需同步。
  • plan_from_paper() 目前要求每题必须有解析才会进卷，Excel 操作题没有传统解析——留到「整卷端接入」任务处理。

已做的验证

  • import main 后经 OpenAPI 确认 14 条 /api/excel/* 路由全部注册成功。
  • 在 quiz.db 副本上跑冒烟：三件套落盘/同类覆盖（旧扩展名文件被清）/扩展名与体积拦截/路径穿越 ../ 拦截/草稿迁移/作答 zip 打包（含中文名与重复名去重）/_ratio、_counts 边界值；并等价跑通「整卷提交」的 INSERT+UPDATE SQL、「实时提交」的 INSERT，验证 UNIQUE(session_id,user_id,qn_id) 确实能触发更新分支、excel_sub_id 联查正常。
  • 修复：_counts() 原本把明细状态 pass/fail 漏计（实际状态是 pass/fail/unknown），已加映射。
  • 冒烟脚本与测试产生的 uploads/ 已删除。

任务 3：判分引擎懒加载器（已完成）

新增 static/excel-wps.js

  • ensureEngine()：按 lib(4) → js(14) 顺序注入 /excelgrade/**，Promise 单例（同页多道题/多次调用只注入一次，失败清空可重试）；带 ?v=25 版本号破缓存。
  • gradeFile(file, cfg)：判分主入口，EG.reader.readWorkbook → EG.recalc.recalc → EG.grader.grade；若教师上传了判分脚本，则由 window.EGJudge.grade(facts, rules, ctx) 接管。
  • mountStudentUI(mount, opts)：学生作答面板（三步引导 / 拖放或点选 / 进度条 / 得分卡 / 要点明细 / 重传），onGraded 回调把「文件 + 分数 + 明细」交给页面上传。
  • uploadResource()：教师端三类文件上传小工具。
  • 弹窗：一律走 appAlert；未加载 dialog.js 时退化为页面内轻提示，不使用原生 alert（项目硬性约定）。

本地判分工具去原生弹窗

  • 新增 excelgrade/web/js/dlg.js（EG.dlg.alert/confirm：有 dialog.js 时复用，离线 file:// 时用自带轻量弹层）。
  • app.js(2 处)、examIO.js(1 处) 的原生 alert 全部替换；index.html 引入 dlg.js 并把资源版本统一到 ?v=25。

任务 4：教师编辑界面（已完成）

文件 改动
qbank.py TYPES/TYPE_CN 增加 excel_wps；QIn 增加三个 url 字段；_validate 对操作题直接放行（无传统答案）；新增 _apply_excel_files() 在保存后写回三列（None=本次未提交不覆盖）
records.py plan_from_paper() 对操作题豁免“解析必填”（否则操作题进不了整卷）
templates/qbank.html 编辑弹窗与两处筛选下拉增加「Excel/WPS 操作题」；新增 #qm-excel-wrap 判分文件区块 + 本地判分工具跳转按钮 + 配套样式
static/qbank.js TYPE_CN/TYPE_CLS 增加题型；setTypeUi() 按题型显隐该区块；renderExRows() 渲染三行「上传/替换/移除/下载」；新建题（无 id）用草稿桶 tmp/<key>，保存后 attachExFiles() 迁移到正式题目；doSave() 校验模板+考点必填并提交三个地址
static/qbank.css 新增 .t-excel 列表配色

任务 5/6：学生端（已完成）

整卷（records.py + templates/exam.html + static/exam.js） - exam_paper 下发 excel{template_file_url,json_standard_url,judge_js_url}；exam_answer 拒绝操作题走文本提交（提示改用文件接口）。 - renderQuestion() 遇到 excel_wps 走 renderExcelQuestion()：挂载作答面板，判分后 POST /api/excel/exam/{sid}/answer；教师设为“统一公布”时调用 ui.reRender(false) 隐藏要点明细，答后立现时即时展示。

实时（main.py + templates/index.html） - q_type_of() 保留 excel_wps（否则被“答案长度推断”误判成单选）；build_state 增加三列查询并下发 question.excel；qb_draw 新增操作题分支（抽入时同步三件套地址）；/api/submit 两条分支（随机/同步）都拒绝操作题。 - render() 与 renderRandom() 增加 excel_wps 分支 → renderExcelLive()；收题后锁定面板；切回普通题时恢复选项区/提交条。 - 页面补引 /static/dialog.js（原本没有）。

任务 7：教师端统计与场控（已完成）

  • templates/exam_control.html + static/exam_control.js：新增「Excel / WPS 操作题」面板（按小题看提交数/考点均分/折算均分，点学生看要点级明细并单份下载）；考生明细表首列复选框 + 表头全选 + 「已选 N 人 · 打包下载所选作答」+ 取消选择。
  • templates/admin_stats.html + static/admin_stats.js：报告页新增 Excel 操作题面板，逐小题统计 + 要点明细展开 + 单份下载。
  • 打包文件名：学号_姓名_Q题号_得分_月日时分.xlsx。
  • 权限修复：/stats、/zip 原先只靠 room_scoped()，而无房场次会直接放行 → 已显式加 _staff() 校验（学生调用返回 403）。

任务 8：打包与文档（已完成）

  • exepack/prepare.py：build() 把 excelgrade/web/{js,lib,index.html} 拷进资源树 excelgrade/（不加密——EG 模块靠 window.EG 互相依赖，按单文件加密下发会丢依赖）。
  • exepack/build.py：spec 的 datas 与 onefile 命令行都追加 excelgrade。
  • exepack/entry.py：打包版按 RES_DIR/excelgrade 重新挂载 /excelgrade（BASE_DIR 在 frozen 下是 exe 目录，源码挂载会失效），并把 excelgrade.store.ROOT 指到可写的 DATA_DIR/uploads/excel。
  • .gitignore：新增 uploads/（含学生答卷，禁止入库）。

任务 9：判分工具页空白 · 判分报“没有判分脚本” · 加载慢（已完成）

现象与根因

  • 用户报告三处：① http://…/excelgrade/index.html 打开空白；② 学生提交后判分报“没有判分脚本”；③ 点击下载/判分很慢。
  • ① 空白根因：main.py 把 /excelgrade 列入 ADMIN_PAGE_PREFIX，未登录访问被守卫重定向到登录页 → 表现为空白。但该目录全是纯前端静态资源（判分引擎 + 本地判分工具 index.html），本地工具只在用户自己浏览器读 Excel、不调用任何服务器接口、不碰题目数据，本就无需权限，列入守卫是误伤。
  • ② “没有判分脚本”根因（关键）：前端运行时根本没有“没有判分脚本”这个报错；excel-wps.js 的 gradeFile 只在 rulesUrl 为空时抛“该题目未配置评分标准”。学生端 rulesUrl = exam_paper 下发的 excel.json_standard_url。经核对 exam_paper(records.py:519) 与 attach(api.py:292) 都正确下发/回写、无代码 bug。故 json_standard_url 为空 = 老师侧没传“考点 / 评分标准 JSON”。而老师之前正因为 ① 工具页打不开 → 导不出考点 JSON → 没传 → 学生判分失败。两处互为因果。
  • ③ 慢根因：判分引擎约 2.6MB（ExcelJS/HyperFormula/JSZip/SheetJS），首次判分/下载时浏览器一次性拉取，局域网首次较慢。

改动

文件 改动
main.py 从 ADMIN_PAGE_PREFIX 移除 /excelgrade（纯前端、无服务器数据，无需权限）；新增 excel_cache 中间件给 /excelgrade 下 lib/js/css 加 Cache-Control: public, max-age=86400, immutable（index.html 仍不缓存）
static/excel-wps.js gradeFile 两处报错文案明确为“缺少『考点 / 评分标准 JSON』”，指明需用本地判分工具导出；judge 脚本可选逻辑不变
static/exam.js 学生端面板缺文件提示区分“缺模板 / 缺考点 JSON”
static/qbank.js “判分脚本 JS”改名“判分脚本 JS（高级·可留空）”，hint 说明一般用不到、留空走内置引擎；“考点 JSON”改名“考点 / 评分标准 JSON（必填·判分必需）”，hint 指明用本地工具导出

验证

  • TestClient：/excelgrade/index.html、/excelgrade/lib/*.js、/excelgrade/js/*.js 均 200；未登录访问 /api/excel/res/1/template 仍 403（权限边界未被误伤）。
  • 链路确认：paper 下发 json_standard_url、attach 回写库地址、qb_question SELECT * 带出新列 —— 三处均无 bug，判分失败纯为配置（老师未传考点 JSON）。

给用户的实操指引

  1. 老师：题库→编辑 Excel 操作题→点“打开本地判分工具”按钮（现已能开）→用标准答案导出考点 JSON 并上传（必填）。
  2. “判分脚本 JS”一般不用填，留空走内置判分引擎（仅极端特例才需老师自写）。
  3. 学生端：下载模板→作答→上传；首次判分约需数秒拉取引擎，之后缓存秒开。

联调与测试记录（全部通过）

  1. 判分引擎（Node 里跑同一份浏览器代码）：openpyxl 造标准答案/好卷/坏卷 → EG 提取 22 个考点（重算 3 个公式）→ 好卷 27.5/27.5 全通过，坏卷 15/27.5（准确指出 D2 公式应为 =B2+C2、结果应为 180 却得 -4）。
  2. 整卷链路（TestClient + quiz.db 副本）：建题→上传三件套（非法扩展名被拒）→元信息回填→资源可读→场次取卷（题型与地址正确）→提交 15/27.5 → 折算小题分 5.45 → 重传满分覆盖为 10 → 学生自查看得见（inline）→ 教师统计/明细/单份下载 → 打包 zip 文件名 e2e_stu_联调学生_Q893_27.5_09150056.xlsx → 学生调教师接口 403、无关学生读考点 403。
  3. 实时链路：建房间→抽题（三件套地址同步到实时表）→GET /api/current 题面带 excel→提交 15/27.5 加 5 分→重传满分只补差额到 10（不重复加分）→普通 /api/submit 拒绝操作题→收题后再提交拒绝→留痕表与 answers 一致。
  4. 静态检查：改动的 Python（py_compile）与 JS（node --check / 内联脚本 new Function）全部通过；node --check 还揪出 dlg.js 一处括号错误并已修。
  5. 临时脚本与测试产生的 uploads/、_tmp_excel/ 已全部删除，未污染开发库。