560 lines
21 KiB
JavaScript
560 lines
21 KiB
JavaScript
|
'use strict';
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 成绩公示表导出引擎 —— 纯函数层（R176）
|
|||
|
|
*
|
|||
|
|
* 目标：以赛事官方参考模板（`lib/assets/publicity-template.xlsx`）为**基底**生成
|
|||
|
|
* xlsx，只填数据、不改版式，人工后续补「获奖等级」即可直接用。
|
|||
|
|
*
|
|||
|
|
* 🔴 为什么不用 `@e965/xlsx` 直接改模板再写回（实测结论，2026-10-19）：
|
|||
|
|
* 读入模板 → 写一个单元格 → 写回，`sheet1.xml` 里的 `s=` 样式引用数
|
|||
|
|
* 从 **214 → 0**、`styles.xml` 从 22773 → 1546 字节，合并单元格还在但
|
|||
|
|
* 边框/字号/列宽全部丢失。该库 CE 版**不写出样式**，`cellStyles: true`
|
|||
|
|
* 只影响读、不影响写。
|
|||
|
|
* ⇒ 唯一能保住版式的做法 = 只替换 `xl/worksheets/sheet1.xml` 这一个 zip 条目，
|
|||
|
|
* 其余条目**原始压缩字节照抄**（不解压不重压 ⇒ 连 CRC 都不用重算）。
|
|||
|
|
*
|
|||
|
|
* 🔴 零依赖约束：只用 Node 内置 `zlib`，CRC32 自己实现。这样 `--self-test`
|
|||
|
|
* 可以在零 DB / 零后端 / 零网络的条件下直接 require 本文件跑矩阵。
|
|||
|
|
*
|
|||
|
|
* 🔴 数据行数可以超过模板预留的 27 行：模板数据区是 row 3..29，两条签字行在 row 30
|
|||
|
|
* （裁判长）与 row 33（仲裁长）。新表按「新数据末行 + 1」与「新数据末行 + 4」
|
|||
|
|
* 的相对偏移重排两条签字行（N=27 时正好复现 row 30 / row 33）。
|
|||
|
|
* ⚠️ 备注块合并区 `H3:J8` 固定不动（H:J 列，与 A:G 的数据列不冲突），
|
|||
|
|
* 但必须保证至少生成 6 个数据行，否则备注框的下边框会落空。
|
|||
|
|
*/
|
|||
|
|
|
|||
|
|
const zlib = require('zlib');
|
|||
|
|
|
|||
|
|
// ==================== 常量（模板结构的单一描述源） ====================
|
|||
|
|
|
|||
|
|
/** 模板第 2 行的 7 个表头，仅用于自证断言与文档；实际写值不依赖它 */
|
|||
|
|
const TEMPLATE_HEADERS = ['序号', '参赛单位名称', '参赛选手', '指导教师', '成绩排名', '总分数', '获奖等级'];
|
|||
|
|
|
|||
|
|
/** 数据区起止行（1 基，含端点）。row 2 = 表头，row 3 起才是数据 */
|
|||
|
|
const DATA_FIRST_ROW = 3;
|
|||
|
|
/**
|
|||
|
|
* 🔴 数据区实际末行是 29，**不是 30**。
|
|||
|
|
* 模板的 row 30 是「裁判长签字：」行（A30 / E30 各一个），row 33 是「仲裁长签字：」行。
|
|||
|
|
* 把 30 当数据行会直接抹掉裁判长签字 —— 2026-10-19 实测踩过这个坑。
|
|||
|
|
*/
|
|||
|
|
const DATA_LAST_ROW = 29;
|
|||
|
|
/** 签字行原始行号（模板里两条：裁判长在 30，仲裁长在 33） */
|
|||
|
|
const SIGN_ROWS = [30, 33];
|
|||
|
|
/** 模板预留的数据行数（row 3..29 = 27 行；模板里 row 29 没有 row 元素，不影响计数口径） */
|
|||
|
|
const RESERVED_DATA_ROWS = DATA_LAST_ROW - DATA_FIRST_ROW + 1;
|
|||
|
|
/** 末条签字行行号 */
|
|||
|
|
const TEMPLATE_TAIL_ROW = SIGN_ROWS[SIGN_ROWS.length - 1];
|
|||
|
|
/** 签字行相对「数据区末行」的偏移（33 - 29 = 4） */
|
|||
|
|
const TAIL_OFFSET = TEMPLATE_TAIL_ROW - DATA_LAST_ROW;
|
|||
|
|
/** 备注块 H3:J8 要求的最小数据行数（8 - 3 + 1 = 6） */
|
|||
|
|
const MIN_DATA_ROWS_FOR_NOTE = 6;
|
|||
|
|
/** 数据列 A..G */
|
|||
|
|
const DATA_COLS = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];
|
|||
|
|
/** 备注块所在列（只补空样式单元格，撑住合并区边框） */
|
|||
|
|
const NOTE_COLS = ['H', 'I', 'J'];
|
|||
|
|
|
|||
|
|
/** 模板标题里等待替换的两段占位文本 */
|
|||
|
|
const TITLE_PLACEHOLDER_ITEM = '***赛项（选拔赛/国内决赛）比赛结果公示表';
|
|||
|
|
const TITLE_PLACEHOLDER_CODE = '赛项编号：';
|
|||
|
|
|
|||
|
|
// ==================== CRC32 / zip 读写 ====================
|
|||
|
|
|
|||
|
|
let CRC_TABLE = null;
|
|||
|
|
function crcTable() {
|
|||
|
|
if (CRC_TABLE) return CRC_TABLE;
|
|||
|
|
const t = new Int32Array(256);
|
|||
|
|
for (let n = 0; n < 256; n++) {
|
|||
|
|
let c = n;
|
|||
|
|
for (let k = 0; k < 8; k++) {
|
|||
|
|
c = (c & 1) ? (0xEDB88320 ^ (c >>> 1)) : (c >>> 1);
|
|||
|
|
}
|
|||
|
|
t[n] = c;
|
|||
|
|
}
|
|||
|
|
CRC_TABLE = t;
|
|||
|
|
return t;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 标准 CRC-32（IEEE 802.3），返回无符号 32 位 */
|
|||
|
|
function crc32(buf) {
|
|||
|
|
const t = crcTable();
|
|||
|
|
let c = -1;
|
|||
|
|
for (let i = 0; i < buf.length; i++) {
|
|||
|
|
c = t[(c ^ buf[i]) & 0xFF] ^ (c >>> 8);
|
|||
|
|
}
|
|||
|
|
return (c ^ -1) >>> 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 从尾部找 End Of Central Directory。找不到返回 -1 */
|
|||
|
|
function findEOCD(buf) {
|
|||
|
|
const min = Math.max(0, buf.length - 65557);
|
|||
|
|
for (let i = buf.length - 22; i >= min; i--) {
|
|||
|
|
if (buf[i] === 0x50 && buf[i + 1] === 0x4b && buf[i + 2] === 0x05 && buf[i + 3] === 0x06) return i;
|
|||
|
|
}
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 读 zip，返回每个条目的**原始压缩字节**（不解压）。
|
|||
|
|
* 中央目录里的 compSize / crc 总是准确的（即使 local header 用了 data descriptor），
|
|||
|
|
* 所以定位数据区一律以中央目录为准。
|
|||
|
|
*/
|
|||
|
|
function readZip(buf) {
|
|||
|
|
if (!Buffer.isBuffer(buf) || buf.length < 22) throw new Error('不是有效的 zip/xlsx（内容过短）');
|
|||
|
|
const eocd = findEOCD(buf);
|
|||
|
|
if (eocd < 0) throw new Error('不是有效的 zip/xlsx（找不到 EOCD）');
|
|||
|
|
|
|||
|
|
const count = buf.readUInt16LE(eocd + 10);
|
|||
|
|
let off = buf.readUInt32LE(eocd + 16);
|
|||
|
|
if (off >= buf.length) throw new Error('中央目录偏移越界');
|
|||
|
|
|
|||
|
|
const entries = [];
|
|||
|
|
for (let i = 0; i < count; i++) {
|
|||
|
|
if (off + 46 > buf.length || buf.readUInt32LE(off) !== 0x02014b50) {
|
|||
|
|
throw new Error(`中央目录第 ${i} 项签名不符 @${off}`);
|
|||
|
|
}
|
|||
|
|
const method = buf.readUInt16LE(off + 10);
|
|||
|
|
const crc = buf.readUInt32LE(off + 16);
|
|||
|
|
const compSize = buf.readUInt32LE(off + 20);
|
|||
|
|
const uncompSize = buf.readUInt32LE(off + 24);
|
|||
|
|
const nameLen = buf.readUInt16LE(off + 28);
|
|||
|
|
const extraLen = buf.readUInt16LE(off + 30);
|
|||
|
|
const commentLen = buf.readUInt16LE(off + 32);
|
|||
|
|
const localOff = buf.readUInt32LE(off + 42);
|
|||
|
|
const name = buf.slice(off + 46, off + 46 + nameLen).toString('utf8');
|
|||
|
|
|
|||
|
|
if (localOff + 30 > buf.length || buf.readUInt32LE(localOff) !== 0x04034b50) {
|
|||
|
|
throw new Error(`条目 ${name} 的本地头损坏`);
|
|||
|
|
}
|
|||
|
|
const lnLen = buf.readUInt16LE(localOff + 26);
|
|||
|
|
const leLen = buf.readUInt16LE(localOff + 28);
|
|||
|
|
const dataStart = localOff + 30 + lnLen + leLen;
|
|||
|
|
if (dataStart + compSize > buf.length) throw new Error(`条目 ${name} 的数据区越界`);
|
|||
|
|
|
|||
|
|
entries.push({
|
|||
|
|
name,
|
|||
|
|
method,
|
|||
|
|
crc,
|
|||
|
|
compSize,
|
|||
|
|
uncompSize,
|
|||
|
|
data: buf.slice(dataStart, dataStart + compSize),
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
off += 46 + nameLen + extraLen + commentLen;
|
|||
|
|
}
|
|||
|
|
return entries;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 把条目按原压缩字节重新打包成 zip（不重压缩 ⇒ CRC 无需重算） */
|
|||
|
|
function writeZip(entries) {
|
|||
|
|
const locals = [];
|
|||
|
|
const centrals = [];
|
|||
|
|
let offset = 0;
|
|||
|
|
|
|||
|
|
for (const e of entries) {
|
|||
|
|
const nameBuf = Buffer.from(e.name, 'utf8');
|
|||
|
|
const crc = e.crc >>> 0;
|
|||
|
|
const compSize = e.data.length;
|
|||
|
|
const uncompSize = e.uncompSize;
|
|||
|
|
|
|||
|
|
const lh = Buffer.alloc(30);
|
|||
|
|
lh.writeUInt32LE(0x04034b50, 0);
|
|||
|
|
lh.writeUInt16LE(20, 4); // version needed to extract
|
|||
|
|
lh.writeUInt16LE(0x0800, 6); // general purpose flag: UTF-8 名称
|
|||
|
|
lh.writeUInt16LE(e.method, 8);
|
|||
|
|
lh.writeUInt16LE(0, 10); // last mod time（不关心，固定 0）
|
|||
|
|
lh.writeUInt16LE(0, 12); // last mod date
|
|||
|
|
lh.writeUInt32LE(crc, 14);
|
|||
|
|
lh.writeUInt32LE(compSize, 18);
|
|||
|
|
lh.writeUInt32LE(uncompSize, 22);
|
|||
|
|
lh.writeUInt16LE(nameBuf.length, 26);
|
|||
|
|
lh.writeUInt16LE(0, 28); // extra len
|
|||
|
|
locals.push(lh, nameBuf, e.data);
|
|||
|
|
|
|||
|
|
const ch = Buffer.alloc(46);
|
|||
|
|
ch.writeUInt32LE(0x02014b50, 0);
|
|||
|
|
ch.writeUInt16LE(20, 4); // version made by
|
|||
|
|
ch.writeUInt16LE(20, 6); // version needed
|
|||
|
|
ch.writeUInt16LE(0x0800, 8);
|
|||
|
|
ch.writeUInt16LE(e.method, 10);
|
|||
|
|
ch.writeUInt16LE(0, 12);
|
|||
|
|
ch.writeUInt16LE(0, 14);
|
|||
|
|
ch.writeUInt32LE(crc, 16);
|
|||
|
|
ch.writeUInt32LE(compSize, 20);
|
|||
|
|
ch.writeUInt32LE(uncompSize, 24);
|
|||
|
|
ch.writeUInt16LE(nameBuf.length, 28);
|
|||
|
|
ch.writeUInt16LE(0, 30); // extra
|
|||
|
|
ch.writeUInt16LE(0, 32); // comment
|
|||
|
|
ch.writeUInt16LE(0, 34); // disk number
|
|||
|
|
ch.writeUInt16LE(0, 36); // internal attrs
|
|||
|
|
ch.writeUInt32LE(0, 38); // external attrs
|
|||
|
|
ch.writeUInt32LE(offset, 42);
|
|||
|
|
centrals.push(ch, nameBuf);
|
|||
|
|
|
|||
|
|
offset += lh.length + nameBuf.length + e.data.length;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const centralStart = offset;
|
|||
|
|
const centralBuf = Buffer.concat(centrals);
|
|||
|
|
const eocd = Buffer.alloc(22);
|
|||
|
|
eocd.writeUInt32LE(0x06054b50, 0);
|
|||
|
|
eocd.writeUInt16LE(0, 4);
|
|||
|
|
eocd.writeUInt16LE(0, 6);
|
|||
|
|
eocd.writeUInt16LE(entries.length, 8);
|
|||
|
|
eocd.writeUInt16LE(entries.length, 10);
|
|||
|
|
eocd.writeUInt32LE(centralBuf.length, 12);
|
|||
|
|
eocd.writeUInt32LE(centralStart, 16);
|
|||
|
|
eocd.writeUInt16LE(0, 20);
|
|||
|
|
return Buffer.concat([Buffer.concat(locals), centralBuf, eocd]);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 解压单个条目为 utf8 文本（method 0=store / 8=deflate） */
|
|||
|
|
function entryText(entry) {
|
|||
|
|
if (!entry) throw new Error('条目不存在');
|
|||
|
|
const raw = entry.method === 0 ? entry.data : zlib.inflateRawSync(entry.data);
|
|||
|
|
return raw.toString('utf8');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 用 utf8 文本替换某条目（重新 deflate + 重算 CRC） */
|
|||
|
|
function replaceEntryText(entries, name, text) {
|
|||
|
|
const raw = Buffer.from(text, 'utf8');
|
|||
|
|
const deflated = zlib.deflateRawSync(raw);
|
|||
|
|
let hit = 0;
|
|||
|
|
const out = entries.map(e => {
|
|||
|
|
if (e.name !== name) return e;
|
|||
|
|
hit++;
|
|||
|
|
return {
|
|||
|
|
name: e.name,
|
|||
|
|
method: 8,
|
|||
|
|
crc: crc32(raw),
|
|||
|
|
compSize: deflated.length,
|
|||
|
|
uncompSize: raw.length,
|
|||
|
|
data: deflated,
|
|||
|
|
};
|
|||
|
|
});
|
|||
|
|
if (hit !== 1) throw new Error(`条目 ${name} 命中 ${hit} 次（应恰好 1 次）`);
|
|||
|
|
return out;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ==================== XML 片段工具 ====================
|
|||
|
|
|
|||
|
|
const XML_ESCAPE = { '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' };
|
|||
|
|
function esc(s) {
|
|||
|
|
return String(s === null || s === undefined ? '' : s).replace(/[&<>"']/g, c => XML_ESCAPE[c]);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const ROW_RE = /<row\b[^>]*\/>|<row\b[^>]*>[\s\S]*?<\/row>/g;
|
|||
|
|
const CELL_RE = /<c\b[^>]*\/>|<c\b[^>]*>[\s\S]*?<\/c>/g;
|
|||
|
|
|
|||
|
|
function rowNumber(rowXml) {
|
|||
|
|
const m = /<row\b[^>]*\br="(\d+)"/.exec(rowXml);
|
|||
|
|
return m ? parseInt(m[1], 10) : -1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 从一行 XML 里取 { 列字母: s 值 } */
|
|||
|
|
function rowStyles(rowXml) {
|
|||
|
|
const out = {};
|
|||
|
|
const cells = rowXml.match(CELL_RE) || [];
|
|||
|
|
for (const c of cells) {
|
|||
|
|
const rm = /\br="([A-Z]+)\d+"/.exec(c);
|
|||
|
|
if (!rm) continue;
|
|||
|
|
const sm = /\bs="(\d+)"/.exec(c);
|
|||
|
|
if (sm) out[rm[1]] = sm[1];
|
|||
|
|
}
|
|||
|
|
return out;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 生成一个数据单元格（数值 / 文本 / 空） */
|
|||
|
|
function cellXml(col, row, style, value) {
|
|||
|
|
const ref = col + row;
|
|||
|
|
const sAttr = style === undefined ? '' : ` s="${style}"`;
|
|||
|
|
if (value === null || value === undefined || value === '') {
|
|||
|
|
return `<c r="${ref}"${sAttr}/>`;
|
|||
|
|
}
|
|||
|
|
if (typeof value === 'number' && isFinite(value)) {
|
|||
|
|
return `<c r="${ref}"${sAttr}><v>${value}</v></c>`;
|
|||
|
|
}
|
|||
|
|
// 用 inlineStr 而不是 sharedStrings：新引入的字符串不用再去维护共享表下标
|
|||
|
|
return `<c r="${ref}"${sAttr} t="inlineStr"><is><t xml:space="preserve">${esc(value)}</t></is></c>`;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 选「最常见的数据行样式」作为新行模板，避免拿模板里被手工改过的那一行当标准 */
|
|||
|
|
function pickDataRowStyles(dataRows) {
|
|||
|
|
const counted = new Map();
|
|||
|
|
for (const r of dataRows) {
|
|||
|
|
const st = rowStyles(r.xml);
|
|||
|
|
const sig = DATA_COLS.map(c => st[c] === undefined ? '-' : st[c]).join(',');
|
|||
|
|
const cur = counted.get(sig);
|
|||
|
|
if (cur) cur.n++;
|
|||
|
|
else counted.set(sig, { n: 1, styles: st });
|
|||
|
|
}
|
|||
|
|
let best = null;
|
|||
|
|
for (const [, v] of counted) {
|
|||
|
|
if (!best || v.n > best.n) best = v;
|
|||
|
|
}
|
|||
|
|
return best ? best.styles : { A: '6', B: '7', C: '7', D: '8', E: '6', F: '6', G: '6' };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 生成新的 sheetData。
|
|||
|
|
*
|
|||
|
|
* @param {string} sheetXml 原 sheet1.xml
|
|||
|
|
* @param {Array<Object>} rows buildPublicityRows 的产物
|
|||
|
|
* @returns {{ xml: string, dataRows: number, tailRow: number, styles: Object }}
|
|||
|
|
*/
|
|||
|
|
function patchSheetData(sheetXml, rows) {
|
|||
|
|
const sheetDataMatch = /<sheetData\b[^>]*>([\s\S]*?)<\/sheetData>/.exec(sheetXml);
|
|||
|
|
if (!sheetDataMatch) throw new Error('模板缺少 <sheetData>');
|
|||
|
|
|
|||
|
|
const allRows = sheetDataMatch[1].match(ROW_RE) || [];
|
|||
|
|
const head = [];
|
|||
|
|
const data = [];
|
|||
|
|
const tail = [];
|
|||
|
|
for (const r of allRows) {
|
|||
|
|
const n = rowNumber(r);
|
|||
|
|
if (n <= 2) head.push(r);
|
|||
|
|
else if (n >= SIGN_ROWS[0]) tail.push(r);
|
|||
|
|
else data.push({ n, xml: r });
|
|||
|
|
}
|
|||
|
|
if (head.length !== 2) throw new Error(`模板表头行应为 2 行，实际 ${head.length}`);
|
|||
|
|
if (data.length === 0) throw new Error('模板没有数据行可作样式参照');
|
|||
|
|
// 🔴 必须同时守住两条签字行：只保留一条等于把「裁判长签字」或「仲裁长签字」丢了
|
|||
|
|
if (tail.length !== SIGN_ROWS.length) {
|
|||
|
|
throw new Error(`模板签字行应为 ${SIGN_ROWS.length} 条（row ${SIGN_ROWS.join(' / ')}），实际 ${tail.length} 条`);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const styles = pickDataRowStyles(data);
|
|||
|
|
// 备注块 H3:J8 的空单元格样式：直接取模板数据行里 H/I/J 的 s（row 3 有备注文本，取 row 4 起）
|
|||
|
|
const noteStyleFrom = {};
|
|||
|
|
for (const d of data) {
|
|||
|
|
if (d.n <= 8) {
|
|||
|
|
const st = rowStyles(d.xml);
|
|||
|
|
for (const c of NOTE_COLS) {
|
|||
|
|
if (st[c] !== undefined && noteStyleFrom[c] === undefined) noteStyleFrom[c] = st[c];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const count = Math.max(rows.length, MIN_DATA_ROWS_FOR_NOTE);
|
|||
|
|
const out = [];
|
|||
|
|
for (const h of head) out.push(h);
|
|||
|
|
|
|||
|
|
for (let i = 0; i < count; i++) {
|
|||
|
|
const r = DATA_FIRST_ROW + i;
|
|||
|
|
const row = rows[i] || null;
|
|||
|
|
const cells = [
|
|||
|
|
cellXml('A', r, styles.A, row ? row.seq : null),
|
|||
|
|
cellXml('B', r, styles.B, row ? row.org : null),
|
|||
|
|
cellXml('C', r, styles.C, row ? row.players : null),
|
|||
|
|
cellXml('D', r, styles.D, row ? row.teachers : null),
|
|||
|
|
cellXml('E', r, styles.E, row ? row.rank : null),
|
|||
|
|
cellXml('F', r, styles.F, row ? row.score : null),
|
|||
|
|
// 获奖等级：用户裁定「空置人工填入」⇒ 永远不写值
|
|||
|
|
cellXml('G', r, styles.G, null),
|
|||
|
|
];
|
|||
|
|
// 备注合并区 H3:J8 需要这些单元格存在才能撑住边框
|
|||
|
|
if (r <= 8) {
|
|||
|
|
for (const c of NOTE_COLS) cells.push(cellXml(c, r, noteStyleFrom[c], null));
|
|||
|
|
}
|
|||
|
|
out.push(`<row r="${r}" customHeight="1" spans="1:7">${cells.join('')}</row>`);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const delta = (count + (DATA_FIRST_ROW - 1)) - DATA_LAST_ROW; // 新数据末行 - 旧数据末行
|
|||
|
|
const shiftRow = (xml, add) => {
|
|||
|
|
if (add === 0) return xml;
|
|||
|
|
return xml.replace(/(<row\b[^>]*\br=")(\d+)(")/, (m, p1, p2, p3) => p1 + (parseInt(p2, 10) + add) + p3)
|
|||
|
|
.replace(/(<c\b[^>]*\br="[A-Z]+)(\d+)(")/g, (m, p1, p2, p3) => p1 + (parseInt(p2, 10) + add) + p3);
|
|||
|
|
};
|
|||
|
|
for (const t of tail) out.push(shiftRow(t, delta));
|
|||
|
|
|
|||
|
|
const newSheetData = `<sheetData>${out.join('')}</sheetData>`;
|
|||
|
|
let xml = sheetXml.replace(sheetDataMatch[0], newSheetData);
|
|||
|
|
|
|||
|
|
// dimension 同步（否则 Excel 认为表格只到旧末行，滚动区/打印区会截断）
|
|||
|
|
const tailRow = TEMPLATE_TAIL_ROW + delta;
|
|||
|
|
xml = xml.replace(/<dimension\b[^>]*\bref="[^"]*"/, `<dimension ref="A1:J${tailRow}"`);
|
|||
|
|
|
|||
|
|
return { xml, dataRows: count, tailRow, styles };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 替换标题里的赛项名与赛项编号。
|
|||
|
|
*
|
|||
|
|
* 标题是 A1 一个单元格里的三行文本（存在 sharedStrings 第 0 项），
|
|||
|
|
* 所以改 sharedStrings 比改 sheet1 更准（A1 是 t="s" 引用，改 sheet 只能整体替换）。
|
|||
|
|
*
|
|||
|
|
* @param {string} xml sharedStrings.xml 原文
|
|||
|
|
* @param {{itemName?: string, itemCode?: string}} meta
|
|||
|
|
* @returns {{ xml: string, replacedItem: boolean, replacedCode: boolean }}
|
|||
|
|
*/
|
|||
|
|
function patchSharedStringsTitle(xml, meta) {
|
|||
|
|
const itemName = meta && meta.itemName ? String(meta.itemName).trim() : '';
|
|||
|
|
const itemCode = meta && meta.itemCode ? String(meta.itemCode).trim() : '';
|
|||
|
|
|
|||
|
|
let replacedItem = false;
|
|||
|
|
let replacedCode = false;
|
|||
|
|
|
|||
|
|
// 定位第 1 个 <si>（标题）
|
|||
|
|
const siMatch = /<si>([\s\S]*?)<\/si>/.exec(xml);
|
|||
|
|
if (!siMatch) throw new Error('sharedStrings 里没有 <si>');
|
|||
|
|
|
|||
|
|
let inner = siMatch[1];
|
|||
|
|
|
|||
|
|
if (itemName && inner.indexOf(TITLE_PLACEHOLDER_ITEM) >= 0) {
|
|||
|
|
// 赛项名替换掉「***」，保留「（选拔赛/国内决赛）比赛结果公示表」的公文后缀
|
|||
|
|
inner = inner.replace(TITLE_PLACEHOLDER_ITEM, `${esc(itemName)}（选拔赛/国内决赛）比赛结果公示表`);
|
|||
|
|
replacedItem = true;
|
|||
|
|
}
|
|||
|
|
if (itemCode && inner.indexOf(TITLE_PLACEHOLDER_CODE) >= 0) {
|
|||
|
|
// 编号为空时不追加，避免留下一个孤零零的「赛项编号：」
|
|||
|
|
inner = inner.replace(TITLE_PLACEHOLDER_CODE, `${TITLE_PLACEHOLDER_CODE}${esc(itemCode)}`);
|
|||
|
|
replacedCode = true;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!replacedItem && !replacedCode) return { xml, replacedItem, replacedCode };
|
|||
|
|
|
|||
|
|
const newSi = `<si>${inner}</si>`;
|
|||
|
|
return { xml: xml.replace(siMatch[0], newSi), replacedItem, replacedCode };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 完整流程：模板字节 + 数据行 + 标题信息 → 新 xlsx 字节。
|
|||
|
|
* @param {Buffer} templateBuf
|
|||
|
|
* @param {Array<Object>} rows
|
|||
|
|
* @param {{itemName?: string, itemCode?: string}} [meta]
|
|||
|
|
*/
|
|||
|
|
function buildXlsx(templateBuf, rows, meta) {
|
|||
|
|
const entries = readZip(templateBuf);
|
|||
|
|
|
|||
|
|
const sheetEntry = entries.find(e => e.name === 'xl/worksheets/sheet1.xml');
|
|||
|
|
if (!sheetEntry) throw new Error('模板缺少 xl/worksheets/sheet1.xml');
|
|||
|
|
const sheetXml = entryText(sheetEntry);
|
|||
|
|
const patched = patchSheetData(sheetXml, rows);
|
|||
|
|
|
|||
|
|
let next = replaceEntryText(entries, 'xl/worksheets/sheet1.xml', patched.xml);
|
|||
|
|
|
|||
|
|
const ssEntry = next.find(e => e.name === 'xl/sharedStrings.xml');
|
|||
|
|
let titleResult = { replacedItem: false, replacedCode: false };
|
|||
|
|
if (ssEntry) {
|
|||
|
|
const ssXml = entryText(ssEntry);
|
|||
|
|
const r = patchSharedStringsTitle(ssXml, meta || {});
|
|||
|
|
titleResult = { replacedItem: r.replacedItem, replacedCode: r.replacedCode };
|
|||
|
|
if (r.replacedItem || r.replacedCode) {
|
|||
|
|
next = replaceEntryText(next, 'xl/sharedStrings.xml', r.xml);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
buffer: writeZip(next),
|
|||
|
|
dataRows: patched.dataRows,
|
|||
|
|
tailRow: patched.tailRow,
|
|||
|
|
title: titleResult,
|
|||
|
|
styles: patched.styles,
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ==================== 数据构造（纯函数） ====================
|
|||
|
|
|
|||
|
|
/** 分数归一：null/''/NaN → null；字符串数字 → number */
|
|||
|
|
function toScore(v) {
|
|||
|
|
if (v === null || v === undefined || v === '') return null;
|
|||
|
|
const n = Number(v);
|
|||
|
|
return isFinite(n) ? Math.round(n * 100) / 100 : null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 把「小组」数组排成公示表的行。
|
|||
|
|
*
|
|||
|
|
* 一行 = 一个小组（用户裁定：一组一人参赛、成绩全组共享 ⇒ 同一分数不该占多行）。
|
|||
|
|
* 排序：有分的按分数**由高到低**，未评分的排最后；同分按团队编号升序保证稳定。
|
|||
|
|
* 获奖等级不在此处产生（永远留空）。
|
|||
|
|
*
|
|||
|
|
* @param {Array<{teamUid?:number|string, teamId?:string, org?:string,
|
|||
|
|
* players?:string[]|string, teachers?:string[]|string, score?:number|string}>} teams
|
|||
|
|
* @returns {Array<{seq:number, org:string, players:string, teachers:string, rank:(number|null), score:(number|null), teamUid:any, teamId:string}>}
|
|||
|
|
*/
|
|||
|
|
function buildPublicityRows(teams) {
|
|||
|
|
const list = Array.isArray(teams) ? teams : [];
|
|||
|
|
const norm = list.map(t => {
|
|||
|
|
const players = Array.isArray(t.players) ? t.players : (t.players ? [t.players] : []);
|
|||
|
|
const teachers = Array.isArray(t.teachers) ? t.teachers : (t.teachers ? [t.teachers] : []);
|
|||
|
|
return {
|
|||
|
|
teamUid: t.teamUid === undefined ? null : t.teamUid,
|
|||
|
|
teamId: t.teamId === undefined || t.teamId === null ? '' : String(t.teamId),
|
|||
|
|
org: t.org === undefined || t.org === null ? '' : String(t.org),
|
|||
|
|
players: uniqKeepOrder(players).join('、'),
|
|||
|
|
teachers: uniqKeepOrder(teachers).join('、'),
|
|||
|
|
score: toScore(t.score),
|
|||
|
|
};
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
norm.sort((a, b) => {
|
|||
|
|
const sa = a.score === null ? -Infinity : a.score;
|
|||
|
|
const sb = b.score === null ? -Infinity : b.score;
|
|||
|
|
if (sb !== sa) return sb - sa;
|
|||
|
|
// 未评分的两个也按编号定序，避免顺序随查询波动
|
|||
|
|
if (a.teamId < b.teamId) return -1;
|
|||
|
|
if (a.teamId > b.teamId) return 1;
|
|||
|
|
return 0;
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
let rank = 0;
|
|||
|
|
return norm.map((t, i) => {
|
|||
|
|
// 未评分不参与排名（否则会出现「0 分并列第 N」的假排名）
|
|||
|
|
if (t.score !== null) rank++;
|
|||
|
|
return {
|
|||
|
|
seq: i + 1,
|
|||
|
|
org: t.org,
|
|||
|
|
players: t.players,
|
|||
|
|
teachers: t.teachers,
|
|||
|
|
rank: t.score === null ? null : rank,
|
|||
|
|
score: t.score,
|
|||
|
|
teamUid: t.teamUid,
|
|||
|
|
teamId: t.teamId,
|
|||
|
|
};
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 去重保序（同组同名只留一份） */
|
|||
|
|
function uniqKeepOrder(arr) {
|
|||
|
|
const seen = new Set();
|
|||
|
|
const out = [];
|
|||
|
|
for (const x of arr) {
|
|||
|
|
const s = String(x === null || x === undefined ? '' : x).trim();
|
|||
|
|
if (!s || seen.has(s)) continue;
|
|||
|
|
seen.add(s);
|
|||
|
|
out.push(s);
|
|||
|
|
}
|
|||
|
|
return out;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
module.exports = {
|
|||
|
|
TEMPLATE_HEADERS,
|
|||
|
|
DATA_FIRST_ROW,
|
|||
|
|
DATA_LAST_ROW,
|
|||
|
|
SIGN_ROWS,
|
|||
|
|
RESERVED_DATA_ROWS,
|
|||
|
|
TEMPLATE_TAIL_ROW,
|
|||
|
|
TAIL_OFFSET,
|
|||
|
|
MIN_DATA_ROWS_FOR_NOTE,
|
|||
|
|
DATA_COLS,
|
|||
|
|
TITLE_PLACEHOLDER_ITEM,
|
|||
|
|
TITLE_PLACEHOLDER_CODE,
|
|||
|
|
crc32,
|
|||
|
|
findEOCD,
|
|||
|
|
readZip,
|
|||
|
|
writeZip,
|
|||
|
|
entryText,
|
|||
|
|
replaceEntryText,
|
|||
|
|
rowNumber,
|
|||
|
|
rowStyles,
|
|||
|
|
cellXml,
|
|||
|
|
pickDataRowStyles,
|
|||
|
|
patchSheetData,
|
|||
|
|
patchSharedStringsTitle,
|
|||
|
|
buildXlsx,
|
|||
|
|
toScore,
|
|||
|
|
buildPublicityRows,
|
|||
|
|
uniqKeepOrder,
|
|||
|
|
};
|