biss
f264103417
Build and publish Docker images / Test, build and publish (push) Successful in 14m13s
志愿台账覆盖未填报、已填报、已锁定、成绩未齐及不可补录考生。 支持按考试、生源学校、状态和关键词筛选。 录取台账支持按考试、招生学校、录取状态和关键词筛选搜索。 两类 Excel 均按页面当前筛选条件导出，并包含学校名称、报名号、轮次、报到状态等完整信息。 全市台账仅超级管理员可导出。
402 lines
25 KiB
JavaScript
402 lines
25 KiB
JavaScript
import ExcelJS from 'exceljs';
|
||
|
||
const resourceSpecs = {
|
||
classes: {
|
||
title: '班级台账', sheet: '班级',
|
||
columns: [
|
||
['schoolCode', '学校代码*', 16, 'HZ01'], ['grade', '年级*', 14, '高三'],
|
||
['name', '班级名称*', 22, '高三（3）班'], ['status', '状态*', 12, '启用']
|
||
],
|
||
validations: { status: ['启用', '停用'] }
|
||
},
|
||
class_admins: {
|
||
title: '班级管理员台账', sheet: '班级管理员',
|
||
columns: [
|
||
['schoolCode', '学校代码*', 16, 'HZ01'], ['className', '班级名称*', 22, '高三（1）班'],
|
||
['displayName', '管理员姓名*', 18, '张老师'], ['username', '登录账号*', 20, 'hz01_g301'],
|
||
['initialPassword', '初始密码（新建必填）', 24, 'ChangeMe123!'], ['status', '状态*', 12, '启用']
|
||
],
|
||
validations: { status: ['启用', '停用'] }
|
||
},
|
||
account_quotas: {
|
||
title: '批量报名号申领配额', sheet: '班级配额',
|
||
columns: [['className', '班级名称*', 24, '高三（1）班'], ['count', '申领数量*', 16, 30]],
|
||
numberColumns: ['count']
|
||
},
|
||
account_results: {
|
||
title: '报名号下发清单', sheet: '账号结果',
|
||
columns: [
|
||
['batchId', '批次编号', 30, ''], ['className', '班级', 22, ''],
|
||
['candidateNumber', '固定报名号', 26, ''], ['initialPassword', '初始密码', 22, '']
|
||
]
|
||
},
|
||
candidates: {
|
||
title: '考生资料台账', sheet: '考生资料',
|
||
columns: [
|
||
['candidateNumber', '报名号*', 26, '2026-HZ01-X-0001'], ['name', '姓名*', 16, '李明'],
|
||
['gender', '性别*', 10, '男'], ['idNumber', '证件号码*', 24, '320101200801011234'],
|
||
['phone', '手机号*', 18, '13800138000'], ['email', '邮箱', 24, 'student@example.com'],
|
||
['nativePlace', '籍贯', 18, '江苏海州'],
|
||
['provinceCode', '省级代码*', 14, '320000'], ['provinceName', '省份', 18, '江苏省'],
|
||
['cityCode', '市级代码*', 14, '320700'], ['cityName', '城市', 18, '连云港市'],
|
||
['districtCode', '区县代码*', 14, '320706'], ['districtName', '区县', 18, '海州区'],
|
||
['address', '详细住址*', 32, '示例路 1 号'],
|
||
['className', '班级*', 22, '高三（1）班'], ['ethnicity', '民族', 12, '汉族'],
|
||
['birthDate', '出生日期', 16, '2008-01-01'], ['postalCode', '邮编', 14, '222000'],
|
||
['guardianName', '监护人', 16, '李家长'], ['guardianPhone', '监护人电话', 18, '13900139000']
|
||
],
|
||
validations: { gender: ['男', '女'] }
|
||
},
|
||
payments: {
|
||
title: '考试缴费名单', sheet: '缴费名单',
|
||
columns: [
|
||
['examCode', '考试代码', 18, ''], ['examName', '考试名称', 28, ''],
|
||
['schoolName', '学校', 24, ''], ['className', '班级', 20, ''],
|
||
['candidateNumber', '报名号', 26, ''], ['candidateName', '考生姓名', 16, ''],
|
||
['subjectNames', '报考科目', 34, ''], ['amountDue', '应缴金额（元）', 18, ''],
|
||
['paymentStatus', '缴费状态', 14, ''], ['paidAt', '确认时间', 24, ''],
|
||
['paidByName', '确认人', 16, '']
|
||
],
|
||
numberColumns: ['amountDue'],
|
||
numberFormats: { amountDue: '0.00' }
|
||
},
|
||
centers: {
|
||
title: '考点考场档案', sheet: '考点考场',
|
||
columns: [
|
||
['schoolCode', '学校代码*', 14, 'HZ01'], ['centerCode', '考点代码*', 18, 'HZ01-C02'],
|
||
['centerName', '考点名称*', 24, '第一中学东区考点'],
|
||
['provinceCode', '省级代码*', 14, '320000'], ['provinceName', '省份', 18, '江苏省'],
|
||
['cityCode', '市级代码*', 14, '320700'], ['cityName', '城市', 18, '连云港市'],
|
||
['districtCode', '区县代码*', 14, '320706'], ['districtName', '区县', 18, '海州区'],
|
||
['address', '详细地址*', 30, '示例路 8 号'],
|
||
['managerName', '负责人', 16, '王老师'], ['managerPhone', '负责人手机', 18, '13800138000'],
|
||
['contact', '值班电话', 18, '0518-86020000'], ['emergencyPhone', '应急电话', 18, '0518-120'],
|
||
['gateOpenTime', '开放时间', 14, '07:00'], ['transport', '交通提示', 30, '东门入场'],
|
||
['centerStatus', '考点状态*', 14, '启用'], ['centerNotes', '考点备注', 26, ''],
|
||
['roomCode', '考场代码*', 16, '001'], ['roomName', '考场名称*', 22, '第 001 考场'],
|
||
['building', '楼栋*', 18, '教学楼 A'], ['floor', '楼层', 12, '1 层'],
|
||
['capacity', '容量*', 12, 30], ['seatPlan', '座位编排说明', 28, '按教室现场座次表编排'],
|
||
['roomType', '考场类型*', 16, '标准考场'], ['roomStatus', '考场状态*', 14, '启用'],
|
||
['roomNotes', '考场备注', 26, '']
|
||
],
|
||
validations: { centerStatus: ['启用', '停用'], roomStatus: ['启用', '停用'], roomType: ['标准考场', '机考考场', '无障碍考场', '备用考场'] },
|
||
numberColumns: ['capacity']
|
||
},
|
||
results: {
|
||
title: '考试成绩台账', sheet: '成绩',
|
||
columns: [
|
||
['candidateNumber', '报名号*', 26, '2026-HZ01-X-0001'], ['cardNumber', '准考证号（只读参考）', 24, ''],
|
||
['candidateName', '姓名（只读参考）', 16, ''], ['schoolName', '学校（只读参考）', 24, ''], ['className', '班级（只读参考）', 20, ''],
|
||
['examCode', '考试代码*', 20, 'EX-2026-AUT'], ['examName', '考试名称（只读参考）', 28, ''],
|
||
['subjectName', '科目*', 16, '语文'], ['fullScore', '科目满分（只读参考）', 18, ''],
|
||
['passRule', '单科及格规则（只读参考）', 26, ''], ['passScore', '实际及格分（只读参考）', 20, ''],
|
||
['score', '成绩*', 12, 120], ['rank', '本科排名（导出计算）', 18, ''],
|
||
['rankPercent', '排名百分位（导出计算）', 20, ''], ['qualified', '单科达线（导出计算）', 16, ''], ['grade', '排名等级（自动计算）', 18, ''],
|
||
['published', '发布状态*', 14, '发布'], ['updatedAt', '更新时间（只读参考）', 24, '']
|
||
],
|
||
validations: { published: ['发布', '不发布'] },
|
||
numberColumns: ['fullScore', 'passScore', 'score', 'rank', 'rankPercent'],
|
||
numberFormats: { fullScore: '0.00', passScore: '0.00', score: '0.00', rank: '0', rankPercent: '0.00' }
|
||
},
|
||
admit_cards: {
|
||
title: '准考证信息台账', sheet: '准考证信息',
|
||
columns: [
|
||
['schoolName', '考生学校', 24, ''], ['className', '班级', 20, ''],
|
||
['candidateNumber', '报名号', 24, ''], ['candidateName', '姓名', 14, ''], ['idNumber', '证件号码', 22, ''],
|
||
['examCode', '考试代码', 18, ''], ['examName', '考试名称', 28, ''], ['cardNumber', '准考证号', 22, ''],
|
||
['centerCode', '考点代码', 16, ''], ['centerName', '考点名称', 26, ''], ['centerAddress', '考点详细地址', 38, ''],
|
||
['subjectName', '科目', 14, ''], ['subjectDate', '日期', 14, ''], ['subjectTime', '时间', 16, ''],
|
||
['examRoomCode', '考试考场序号', 16, ''], ['roomName', '考场通用名称', 20, ''], ['roomCode', '物理场地代码', 16, ''],
|
||
['building', '楼栋', 16, ''], ['floor', '楼层', 12, ''], ['seat', '座位号', 12, '']
|
||
]
|
||
},
|
||
admitted_candidates: {
|
||
title: '录取考生信息表', sheet: '录取考生',
|
||
columns: [
|
||
['candidateNumber', '报名号', 26, ''], ['name', '姓名', 14, ''], ['gender', '性别', 10, ''],
|
||
['idNumber', '证件号码', 24, ''], ['phone', '手机号', 18, ''], ['email', '邮箱', 24, ''],
|
||
['birthDate', '出生日期', 14, ''], ['ethnicity', '民族', 12, ''], ['nativePlace', '籍贯', 18, ''],
|
||
['sourceSchoolCode', '生源学校代码', 16, ''], ['sourceSchool', '生源学校', 26, ''], ['className', '班级', 18, ''],
|
||
['address', '家庭住址', 36, ''], ['guardianName', '监护人', 14, ''], ['guardianPhone', '监护人电话', 18, ''],
|
||
['specialty', '特长生资格', 20, ''], ['specialtyCertificate', '特长证明编号', 20, ''], ['policyEligibility', '政策资格说明', 24, ''],
|
||
['featureScore', '特征分', 12, ''], ['subjectScores', '各科成绩', 42, ''], ['totalScore', '考生总成绩', 14, ''],
|
||
['admittedSchool', '录取学校', 26, ''], ['categoryName', '录取类别', 20, ''], ['preferenceOrder', '志愿序号', 12, '']
|
||
],
|
||
numberColumns: ['featureScore', 'totalScore', 'preferenceOrder'],
|
||
numberFormats: { featureScore: '0.00', totalScore: '0.00', preferenceOrder: '0' }
|
||
},
|
||
admission_preferences: {
|
||
title: '考生志愿填报实时台账', sheet: '志愿填报情况',
|
||
columns: [
|
||
['examCode', '考试代码', 20, 'EX-2026-ZK'], ['examName', '考试名称', 30, '初中学业水平考试'],
|
||
['round', '填报轮次', 12, 1], ['fillStatus', '填报状态', 16, '已填报'], ['lockStatus', '锁定状态', 16, '未锁定'],
|
||
['submissionCount', '已提交次数', 14, 1], ['maxSubmissions', '提交次数上限', 16, 3],
|
||
['candidateNumber', '报名号', 26, '2026-HZ01-F-0001'], ['candidateName', '姓名', 14, '张同学'],
|
||
['sourceSchoolCode', '生源学校代码', 18, 'HZ01'], ['sourceSchoolName', '生源学校', 26, '海州市第一中学'], ['className', '班级', 18, '九年级一班'],
|
||
['specialty', '特长生资格', 22, '普通生'], ['indicatorStatus', '指标分配资格', 18, '有资格'],
|
||
['preferenceOrder', '志愿顺序', 14, 1], ['preferenceType', '志愿类型', 14, '普通志愿'],
|
||
['targetSchoolCode', '志愿学校代码', 18, 'AD01'], ['targetSchoolName', '志愿学校', 28, '海州市高级中学'],
|
||
['categoryName', '招生类别', 20, '普通生'], ['submittedAt', '最近提交时间', 24, '2026-07-22 10:18']
|
||
],
|
||
numberColumns: ['round', 'submissionCount', 'maxSubmissions', 'preferenceOrder'],
|
||
numberFormats: { round: '0', submissionCount: '0', maxSubmissions: '0', preferenceOrder: '0' }
|
||
},
|
||
admission_placements: {
|
||
title: '招生录取情况台账', sheet: '录取情况',
|
||
columns: [
|
||
['examCode', '考试代码', 20, 'EX-2026-ZK'], ['examName', '考试名称', 30, '初中学业水平考试'], ['round', '录取轮次', 12, 1],
|
||
['candidateNumber', '报名号', 26, '2026-HZ01-F-0001'], ['candidateName', '姓名', 14, '张同学'],
|
||
['sourceSchoolCode', '生源学校代码', 18, 'HZ01'], ['sourceSchoolName', '生源学校', 26, '海州市第一中学'], ['className', '班级', 18, '九年级一班'],
|
||
['specialty', '特长生资格', 22, '普通生'], ['culturalScore', '文化课总分', 14, 560], ['featureScore', '特征分', 12, 0], ['totalScore', '投档总分', 14, 560],
|
||
['preferenceOrder', '命中志愿序号', 16, 1], ['admissionSchoolCode', '招生学校代码', 18, 'AD01'], ['admissionSchoolName', '招生学校', 28, '海州市高级中学'],
|
||
['categoryName', '招生类别', 20, '普通生'], ['quotaBucket', '计划类型', 18, '普通计划'], ['admissionStatus', '录取状态', 18, '正式录取'],
|
||
['reportingStatus', '报到状态', 16, '已报到'], ['noticeNumber', '录取通知书编号', 34, 'AD01-EX-2026-ZK-000001'],
|
||
['withdrawalReason', '退档或放弃原因', 34, ''], ['updatedAt', '状态更新时间', 24, '2026-07-22 10:18']
|
||
],
|
||
numberColumns: ['round', 'culturalScore', 'featureScore', 'totalScore', 'preferenceOrder'],
|
||
numberFormats: { round: '0', culturalScore: '0.00', featureScore: '0.00', totalScore: '0.00', preferenceOrder: '0' }
|
||
},
|
||
admission_reporting: {
|
||
title: '录取考生报到状态维护表', sheet: '考生报到',
|
||
columns: [
|
||
['noticeNumber', '录取通知书编号*', 34, 'AD01-EX-2026-ZK-000001'],
|
||
['candidateNumber', '报名号*', 26, '2026-HZ01-F-0001'], ['name', '姓名（只读）', 14, '张同学'],
|
||
['examCode', '考试代码（只读）', 20, 'EX-2026-ZK'], ['schoolCode', '招生学校代码（只读）', 18, 'AD01'],
|
||
['categoryName', '录取类别（只读）', 20, '普通生'],
|
||
['reportingStatusCode', '报到状态码*（Y/N/P）', 22, 'P'], ['reportingNote', '报到备注', 36, '']
|
||
],
|
||
validations: { reportingStatusCode: ['Y', 'N', 'P'] }
|
||
}
|
||
};
|
||
|
||
export function hasExcelResource(resource) {
|
||
return Boolean(resourceSpecs[resource]);
|
||
}
|
||
|
||
function cellValue(cell) {
|
||
const value = cell.value;
|
||
if (value == null) return '';
|
||
if (value instanceof Date) return value.toISOString().slice(0, 10);
|
||
if (typeof value === 'object') {
|
||
if ('text' in value) return String(value.text || '').trim();
|
||
if ('result' in value) return String(value.result ?? '').trim();
|
||
}
|
||
return typeof value === 'number' ? value : String(value).trim();
|
||
}
|
||
|
||
export async function parseWorkbook(resource, buffer) {
|
||
const spec = resourceSpecs[resource];
|
||
if (!spec) throw Object.assign(new Error('不支持的 Excel 数据类型'), { status: 404 });
|
||
const workbook = new ExcelJS.Workbook();
|
||
try {
|
||
await workbook.xlsx.load(buffer);
|
||
} catch {
|
||
throw Object.assign(new Error('无法读取 Excel 文件，请使用系统下载的 .xlsx 模板'), { status: 400 });
|
||
}
|
||
const sheet = workbook.getWorksheet(spec.sheet) || workbook.worksheets[0];
|
||
if (!sheet) throw Object.assign(new Error('Excel 文件中没有可读取的工作表'), { status: 400 });
|
||
const headerMap = new Map();
|
||
sheet.getRow(2).eachCell((cell, col) => headerMap.set(String(cell.value || '').replace(/\*/g, '').trim(), col));
|
||
const missing = spec.columns.filter(([, label]) => !headerMap.has(label.replace(/\*/g, '').trim()));
|
||
if (missing.length) throw Object.assign(new Error(`模板列不完整：缺少 ${missing.map(([, label]) => label).join('、')}`), { status: 400 });
|
||
const rows = [];
|
||
for (let rowNumber = 3; rowNumber <= sheet.rowCount; rowNumber += 1) {
|
||
const row = sheet.getRow(rowNumber);
|
||
const item = { __row: rowNumber };
|
||
let populated = false;
|
||
for (const [key, label] of spec.columns) {
|
||
const value = cellValue(row.getCell(headerMap.get(label.replace(/\*/g, '').trim())));
|
||
item[key] = value;
|
||
if (value !== '') populated = true;
|
||
}
|
||
if (populated) rows.push(item);
|
||
}
|
||
if (!rows.length) throw Object.assign(new Error('Excel 中没有可导入的数据行'), { status: 400 });
|
||
return rows;
|
||
}
|
||
|
||
export async function buildWorkbook(resource, rows = [], { template = false, subtitle = '' } = {}) {
|
||
const spec = resourceSpecs[resource];
|
||
if (!spec) throw Object.assign(new Error('不支持的 Excel 数据类型'), { status: 404 });
|
||
const workbook = new ExcelJS.Workbook();
|
||
workbook.creator = '衡准考试信息管理系统';
|
||
workbook.created = new Date();
|
||
const sheet = workbook.addWorksheet(spec.sheet, { views: [{ state: 'frozen', ySplit: 2, showGridLines: false }] });
|
||
const lastColumn = spec.columns.length;
|
||
sheet.columns = spec.columns.map(([key, , width]) => ({ key, width }));
|
||
spec.columns.forEach(([key], index) => {
|
||
sheet.getColumn(index + 1).numFmt = spec.numberFormats?.[key] || (spec.numberColumns?.includes(key) ? '0' : '@');
|
||
});
|
||
sheet.mergeCells(1, 1, 1, lastColumn);
|
||
const titleCell = sheet.getCell(1, 1);
|
||
titleCell.value = subtitle ? `${spec.title}｜${subtitle}` : spec.title;
|
||
titleCell.font = { name: '微软雅黑', size: 16, bold: true, color: { argb: 'FFFFFFFF' } };
|
||
titleCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } };
|
||
titleCell.alignment = { vertical: 'middle', horizontal: 'left' };
|
||
sheet.getRow(1).height = 34;
|
||
const header = sheet.getRow(2);
|
||
header.values = spec.columns.map(([, label]) => label);
|
||
header.height = 25;
|
||
header.font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFFFFF' } };
|
||
header.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF2C7080' } };
|
||
header.alignment = { vertical: 'middle', horizontal: 'center' };
|
||
const outputRows = rows.length ? rows : template ? [Object.fromEntries(spec.columns.map(([key, , , example]) => [key, example]))] : [];
|
||
for (const item of outputRows) {
|
||
const row = sheet.addRow(Object.fromEntries(spec.columns.map(([key]) => [key, resource === 'admission_reporting' && key === 'reportingNote' && !item[key] ? null : item[key] ?? ''])));
|
||
row.height = 23;
|
||
row.font = { name: '微软雅黑', size: 10, color: { argb: 'FF243B4A' } };
|
||
row.alignment = { vertical: 'middle' };
|
||
row.eachCell(cell => {
|
||
cell.border = { bottom: { style: 'hair', color: { argb: 'FFD8E2E7' } } };
|
||
});
|
||
}
|
||
if (resource === 'admission_reporting') {
|
||
const statusColumn = spec.columns.findIndex(([key]) => key === 'reportingStatusCode') + 1;
|
||
const noteColumn = spec.columns.findIndex(([key]) => key === 'reportingNote') + 1;
|
||
for (let row = 3; row <= Math.max(202, sheet.rowCount); row += 1) {
|
||
for (const column of [statusColumn, noteColumn]) sheet.getCell(row, column).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFF3CD' } };
|
||
}
|
||
sheet.getCell('A1').value = `${spec.title}｜仅修改黄色列；Y=已报到，N=未报到，P=待确认`;
|
||
}
|
||
sheet.autoFilter = { from: { row: 2, column: 1 }, to: { row: Math.max(2, sheet.rowCount), column: lastColumn } };
|
||
for (const [key, values] of Object.entries(spec.validations || {})) {
|
||
const col = spec.columns.findIndex(([columnKey]) => columnKey === key) + 1;
|
||
for (let row = 3; row <= Math.max(202, sheet.rowCount); row += 1) {
|
||
sheet.getCell(row, col).dataValidation = { type: 'list', allowBlank: false, formulae: [`"${values.join(',')}"`] };
|
||
}
|
||
}
|
||
const requiredColumns = spec.columns.map(([, label], index) => label.includes('*') ? index + 1 : 0).filter(Boolean);
|
||
for (const col of requiredColumns) header.getCell(col).font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFE7A3' } };
|
||
return workbook.xlsx.writeBuffer();
|
||
}
|
||
|
||
function addOperationalSheet(workbook, name, title, columns, rows, { landscape = true } = {}) {
|
||
const sheet = workbook.addWorksheet(name, { views: [{ state: 'frozen', ySplit: 2, showGridLines: false }] });
|
||
sheet.columns = columns.map(([key, , width]) => ({ key, width }));
|
||
sheet.mergeCells(1, 1, 1, columns.length);
|
||
const titleCell = sheet.getCell(1, 1);
|
||
titleCell.value = title;
|
||
titleCell.font = { name: '微软雅黑', size: 16, bold: true, color: { argb: 'FFFFFFFF' } };
|
||
titleCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } };
|
||
titleCell.alignment = { vertical: 'middle', horizontal: 'left' };
|
||
sheet.getRow(1).height = 34;
|
||
const header = sheet.getRow(2);
|
||
header.values = columns.map(([, label]) => label);
|
||
header.height = 28;
|
||
header.font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFFFFF' } };
|
||
header.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF2C7080' } };
|
||
header.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
|
||
rows.forEach((item, index) => {
|
||
const row = sheet.addRow(Object.fromEntries(columns.map(([key]) => [key, item[key] ?? ''])));
|
||
row.height = 28;
|
||
row.font = { name: '微软雅黑', size: 10, color: { argb: 'FF243B4A' } };
|
||
row.alignment = { vertical: 'middle', wrapText: true };
|
||
if (index % 2 === 1) row.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF3F7F8' } };
|
||
row.eachCell(cell => { cell.border = { bottom: { style: 'hair', color: { argb: 'FFD8E2E7' } } }; });
|
||
});
|
||
sheet.autoFilter = { from: { row: 2, column: 1 }, to: { row: Math.max(2, sheet.rowCount), column: columns.length } };
|
||
sheet.pageSetup = { orientation: landscape ? 'landscape' : 'portrait', paperSize: 9, fitToPage: true, fitToWidth: 1, fitToHeight: 0, margins: { left: .25, right: .25, top: .4, bottom: .4, header: .2, footer: .2 } };
|
||
sheet.printTitlesRow = '1:2';
|
||
return sheet;
|
||
}
|
||
|
||
function addDeskStickerSheet(workbook, rows) {
|
||
const sheet = workbook.addWorksheet('桌贴', { views: [{ showGridLines: false, zoomScale: 85 }] });
|
||
const labelColumns = [14.5, 14.5, 14.5, 3, 14.5, 14.5, 14.5];
|
||
labelColumns.forEach((width, index) => { sheet.getColumn(index + 1).width = width; });
|
||
sheet.pageSetup = {
|
||
orientation: 'portrait', paperSize: 9, fitToPage: true, fitToWidth: 1, fitToHeight: 0,
|
||
margins: { left: .25, right: .25, top: .25, bottom: .25, header: 0, footer: 0 },
|
||
horizontalCentered: true, verticalCentered: false
|
||
};
|
||
sheet.headerFooter = { oddFooter: '&C第 &P / &N 页' };
|
||
const rowsPerLabel = 8;
|
||
const labelsPerPage = 6;
|
||
const rowsPerPage = rowsPerLabel * 3;
|
||
const borderColor = { argb: 'FF173F60' };
|
||
const paleBlue = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFEAF1F5' } };
|
||
const paleGold = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFF1C7' } };
|
||
const mergeLine = (row, startColumn) => {
|
||
sheet.mergeCells(row, startColumn, row, startColumn + 2);
|
||
return sheet.getCell(row, startColumn);
|
||
};
|
||
const styleLine = (cell, { size = 10, bold = false, color = 'FF243B4A', fill = null } = {}) => {
|
||
cell.font = { name: '微软雅黑', size, bold, color: { argb: color } };
|
||
cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true, shrinkToFit: true };
|
||
if (fill) cell.fill = fill;
|
||
};
|
||
|
||
rows.forEach((item, index) => {
|
||
const page = Math.floor(index / labelsPerPage);
|
||
const slot = index % labelsPerPage;
|
||
const startRow = page * rowsPerPage + Math.floor(slot / 2) * rowsPerLabel + 1;
|
||
const startColumn = slot % 2 === 0 ? 1 : 5;
|
||
for (let row = startRow; row < startRow + rowsPerLabel; row += 1) sheet.getRow(row).height = 32.5;
|
||
const cells = Array.from({ length: rowsPerLabel }, (_, offset) => mergeLine(startRow + offset, startColumn));
|
||
cells[0].value = item.examName;
|
||
styleLine(cells[0], { size: 11, bold: true, color: 'FFFFFFFF', fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } } });
|
||
cells[1].value = `${item.subjectName}｜${item.subjectDate} ${item.subjectTime}`;
|
||
styleLine(cells[1], { size: 9, bold: true, fill: paleBlue });
|
||
cells[2].value = '考试考场序号';
|
||
styleLine(cells[2], { size: 9, bold: true, color: 'FF2C7080' });
|
||
cells[3].value = { richText: [{ text: String(item.examRoomCode || '') }] };
|
||
cells[3].numFmt = '@';
|
||
styleLine(cells[3], { size: 25, bold: true, color: 'FF173F60' });
|
||
cells[4].value = `座位号 ${item.seat}`;
|
||
styleLine(cells[4], { size: 20, bold: true, color: 'FF8A4B08', fill: paleGold });
|
||
cells[5].value = `${item.roomName}（场地 ${item.roomCode}）｜${item.building} ${item.floor}`;
|
||
styleLine(cells[5], { size: 9, bold: true });
|
||
cells[6].value = `${item.candidateName}｜${item.schoolName} ${item.className}`;
|
||
styleLine(cells[6], { size: 10, bold: true, fill: paleBlue });
|
||
cells[7].value = `${item.centerName}｜准考证号 ${item.cardNumber}`;
|
||
styleLine(cells[7], { size: 8, color: 'FF526576' });
|
||
|
||
for (let row = startRow; row < startRow + rowsPerLabel; row += 1) {
|
||
for (let column = startColumn; column < startColumn + 3; column += 1) {
|
||
const cell = sheet.getCell(row, column);
|
||
cell.border = {
|
||
top: { style: row === startRow ? 'medium' : 'hair', color: borderColor },
|
||
bottom: { style: row === startRow + rowsPerLabel - 1 ? 'medium' : 'hair', color: borderColor },
|
||
left: { style: column === startColumn ? 'medium' : 'hair', color: borderColor },
|
||
right: { style: column === startColumn + 2 ? 'medium' : 'hair', color: borderColor }
|
||
};
|
||
}
|
||
}
|
||
if ((index + 1) % labelsPerPage === 0 && index + 1 < rows.length) sheet.getRow(startRow + rowsPerLabel - 1).addPageBreak();
|
||
});
|
||
if (rows.length) sheet.pageSetup.printArea = `A1:G${Math.ceil(rows.length / labelsPerPage) * rowsPerPage}`;
|
||
return sheet;
|
||
}
|
||
|
||
export async function buildCenterMaterialsWorkbook(rows, subtitle = '') {
|
||
const workbook = new ExcelJS.Workbook();
|
||
workbook.creator = '衡准考试信息管理系统';
|
||
workbook.created = new Date();
|
||
const sorted = [...rows].sort((a, b) => String(a.centerCode).localeCompare(String(b.centerCode))
|
||
|| String(a.subjectDate).localeCompare(String(b.subjectDate)) || String(a.subjectTime).localeCompare(String(b.subjectTime))
|
||
|| String(a.examRoomCode).localeCompare(String(b.examRoomCode)) || String(a.seat).localeCompare(String(b.seat)));
|
||
const baseTitle = subtitle ? `｜${subtitle}` : '';
|
||
addDeskStickerSheet(workbook, sorted);
|
||
|
||
const doorMap = new Map();
|
||
for (const item of sorted) {
|
||
const key = [item.examCode, item.subjectName, item.centerCode, item.examRoomCode, item.roomCode].join('|');
|
||
const current = doorMap.get(key) || { ...item, candidateCount: 0 };
|
||
current.candidateCount += 1;
|
||
doorMap.set(key, current);
|
||
}
|
||
addOperationalSheet(workbook, '门贴', `考场门贴${baseTitle}`, [
|
||
['examName', '考试', 28], ['subjectName', '科目', 14], ['subjectDate', '日期', 14], ['subjectTime', '时间', 16],
|
||
['centerName', '考点', 24], ['examRoomCode', '考试考场序号', 16], ['roomName', '考场通用名称', 20],
|
||
['roomCode', '场地代码', 14], ['building', '楼栋', 16], ['floor', '楼层', 10], ['candidateCount', '人数', 10]
|
||
], [...doorMap.values()]);
|
||
|
||
addOperationalSheet(workbook, '考场签名单', `考场考生签名单${baseTitle}`, [
|
||
['examName', '考试', 26], ['subjectName', '科目', 14], ['subjectDate', '日期', 14], ['subjectTime', '时间', 16],
|
||
['examRoomCode', '考试考场序号', 16], ['roomName', '考场通用名称', 20], ['building', '楼栋', 16], ['floor', '楼层', 10],
|
||
['seat', '座位号', 10], ['cardNumber', '准考证号', 22], ['candidateName', '姓名', 14],
|
||
['schoolName', '考生学校', 24], ['className', '班级', 18], ['signature', '考生签名', 18]
|
||
], sorted.map(item => ({ ...item, signature: '' })));
|
||
return workbook.xlsx.writeBuffer();
|
||
}
|