build_test.mjs - master - eup/khsm

build_test.mjs
145 строк5 KB
eup
Create: build_random_html_test.mjs, build_test.mjs, inspect_questions.mjs, verify_html_test.mjs, verify_test.mjs, Тест_КХСМ_рандом.html
07 июн 2026, 12:06
Верифицирован
07 июн 2026, 12:06d2942e3
import fs from "node:fs/promises";
import { FileBlob, SpreadsheetFile, Workbook } from "@oai/artifact-tool";

const inputPath = "C:/Users/egoru/Downloads/База КХСМ.xlsx";
const outputDir = "C:/Users/egoru/Documents/Codex/2026-05-02/files-mentioned-by-the-user-xlsx/outputs";
const outputPath = `${outputDir}/Тест_КХСМ_15_вопросов.xlsx`;

const input = await FileBlob.load(inputPath);
const sourceWorkbook = await SpreadsheetFile.importXlsx(input);

const table = [];
for (let start = 1; start <= 10000; start += 100) {
  const end = start + 99;
  const raw = await sourceWorkbook.inspect({
    kind: "table",
    range: `База_1!A${start}:G${end}`,
    include: "values",
    tableMaxRows: 100,
    tableMaxCols: 7,
  });
  const values = JSON.parse(raw.ndjson).values;
  table.push(...values);
  const meaningfulRows = values.filter((row) => row.some((cell) => cell !== null && cell !== ""));
  if (start > 1 && meaningfulRows.length === 0) break;
}
const rows = table
  .slice(5)
  .map((row, idx) => ({
    sourceRow: idx + 6,
    level: Number(row[0]),
    question: String(row[1] ?? "").trim(),
    a: String(row[2] ?? "").trim(),
    b: String(row[3] ?? "").trim(),
    c: String(row[4] ?? "").trim(),
    d: String(row[5] ?? "").trim(),
    answer: String(row[6] ?? "").trim().toUpperCase(),
  }))
  .filter((row) =>
    Number.isFinite(row.level) &&
    row.question &&
    row.a &&
    row.b &&
    row.c &&
    row.d &&
    ["A", "B", "C", "D"].includes(row.answer)
  );

const byLevel = new Map();
for (const row of rows) {
  if (!byLevel.has(row.level)) byLevel.set(row.level, []);
  byLevel.get(row.level).push(row);
}

const wantedLevels = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15];
const selected = wantedLevels
  .map((level) => byLevel.get(level)?.[0])
  .filter(Boolean)
  .slice(0, 15);

if (selected.length < 15) {
  throw new Error(`Only selected ${selected.length} questions from База_1.`);
}

const workbook = Workbook.create();
const test = workbook.worksheets.add("Тест");
const key = workbook.worksheets.add("Ответы");

const testRows = [
  ["Тест КХСМ: 15 вопросов", "", "", "", "", ""],
  ["Все вопросы взяты из листа «База_1» файла «База КХСМ.xlsx».", "", "", "", "", ""],
  [null, null, null, null, null, null],
  ["№", "Уровень", "Вопрос", "A", "B", "C", "D"],
  ...selected.map((row, i) => [
    i + 1,
    row.level,
    row.question,
    row.a,
    row.b,
    row.c,
    row.d,
  ]),
];

test.getRange(`A1:G${testRows.length}`).values = testRows;
test.getRange("A1:G1").merge();
test.getRange("A2:G2").merge();
test.getRange("A1:G1").format = {
  font: { bold: true, size: 16, color: "#FFFFFF" },
  fill: "#1F4E79",
  horizontalAlignment: "center",
  verticalAlignment: "middle",
};
test.getRange("A2:G2").format = {
  font: { italic: true, color: "#44546A" },
  fill: "#EAF2F8",
};
test.getRange("A4:G4").format = {
  font: { bold: true, color: "#FFFFFF" },
  fill: "#5B9BD5",
  horizontalAlignment: "center",
};
test.getRange(`A5:G${testRows.length}`).format = {
  wrapText: true,
  verticalAlignment: "top",
};
test.getRange(`A5:B${testRows.length}`).format = {
  horizontalAlignment: "center",
  verticalAlignment: "top",
};
test.getRange(`A5:G${testRows.length}`).format.rowHeightPx = 58;
test.getRange("A1:G1").format.rowHeightPx = 30;
test.getRange("A:A").format.columnWidthPx = 42;
test.getRange("B:B").format.columnWidthPx = 70;
test.getRange("C:C").format.columnWidthPx = 440;
test.getRange("D:G").format.columnWidthPx = 155;
test.freezePanes.freezeRows(4);

const keyRows = [
  ["Ключ ответов", "", "", ""],
  ["№", "Уровень", "Ответ", "Исходная строка"],
  ...selected.map((row, i) => [i + 1, row.level, row.answer, row.sourceRow]),
];
key.getRange(`A1:D${keyRows.length}`).values = keyRows;
key.getRange("A1:D1").merge();
key.getRange("A1:D1").format = {
  font: { bold: true, size: 14, color: "#FFFFFF" },
  fill: "#1F4E79",
  horizontalAlignment: "center",
};
key.getRange("A2:D2").format = {
  font: { bold: true, color: "#FFFFFF" },
  fill: "#5B9BD5",
  horizontalAlignment: "center",
};
key.getRange(`A3:D${keyRows.length}`).format = {
  horizontalAlignment: "center",
};
key.getRange("A:D").format.columnWidthPx = 115;
key.freezePanes.freezeRows(2);

await fs.mkdir(outputDir, { recursive: true });
const output = await SpreadsheetFile.exportXlsx(workbook);
await output.save(outputPath);

console.log(JSON.stringify({ outputPath, selectedCount: selected.length, rowsInBase: rows.length }, null, 2));