openclaw-voc-skill/tools/build_centrum_transcript_workbook.mjs

import fs from "node:fs/promises";
import path from "node:path";
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";

const outputDir = path.resolve("outputs/centrum_douyin_audit_2026-06-08/transcript-full");
const data = JSON.parse(await fs.readFile(path.join(outputDir, "transcript-analysis-data.json"), "utf8"));
const runLogPath = path.join(outputDir, "transcription-run-log.json");
const poolPath = path.join(outputDir, "transcript-sample-pool.json");
const runLog = JSON.parse(await fs.readFile(runLogPath, "utf8"));
const pool = JSON.parse(await fs.readFile(poolPath, "utf8"));

function rowsFromObjects(items, headers) {
  return [headers.map(h => h.label), ...items.map(item => headers.map(h => {
    const value = typeof h.value === "function" ? h.value(item) : item[h.value];
    if (Array.isArray(value)) return value.join("\n");
    return value ?? "";
  }))];
}

function writeRows(sheet, row, col, rows) {
  if (!rows.length || !rows[0].length) return;
  sheet.getRangeByIndexes(row, col, rows.length, rows[0].length).values = rows;
}

function styleRange(range) {
  range.format = {
    font: { name: "Microsoft YaHei", size: 10 },
    wrapText: true,
    verticalAlignment: "top",
    borders: { preset: "all", style: "thin", color: "#D9E2EC" },
  };
}

function styleHeader(range) {
  range.format = {
    fill: "#1F4E79",
    font: { bold: true, color: "#FFFFFF" },
    wrapText: true,
    verticalAlignment: "middle",
  };
}

function setWidths(sheet, widths) {
  widths.forEach(([col, px]) => {
    sheet.getRange(`${col}:${col}`).format.columnWidthPx = px;
  });
}

function styleSheet(sheet, rangeAddress, widths) {
  sheet.showGridLines = false;
  if (rangeAddress) styleRange(sheet.getRange(rangeAddress));
  setWidths(sheet, widths);
}

function validTranscriptRows() {
  return data.transcriptRows
    .filter(row => row.validTranscript)
    .sort((a, b) => (b.likeCount || 0) - (a.likeCount || 0));
}

const workbook = Workbook.create();
const summary = workbook.worksheets.add("执行摘要_逐字稿");
summary.getRange("A1:H1").merge();
summary.getRange("A1").values = [["善存抖音逐字稿话术审核分析"]];
summary.getRange("A1").format = {
  fill: "#0B1F33",
  font: { bold: true, color: "#FFFFFF", size: 16 },
  horizontalAlignment: "center",
};

const s = data.summary;
const summaryRows = [
  ["生成时间", String(s.generatedAt || "").replace("T", " ").slice(0, 19), "live 视频样本", s.liveVideoCount],
  ["live 评论样本", s.liveCommentCount, "转写优先样本池", s.transcriptAttemptSelectedCount],
  ["转写文件数", s.transcriptFileCount, "有效口播逐字稿", s.validTranscriptCount],
  ["低信息/背景音乐", s.invalidTranscriptCount, "本轮转写失败", s.runLogFailedCount],
  ["核心结论", "逐字稿确认主要风险集中在一包/一粒解决感、状态功效、促销 CTA、明星背书和版本边界", "使用限制", "ASR 存在同音误识别，合规定稿需结合原视频/原稿复核"],
];
writeRows(summary, 2, 0, summaryRows);

const moduleRows = [["话术模块", "有效逐字稿数"], ...data.moduleSummary.map(row => [row.module, row.count])];
writeRows(summary, 9, 0, moduleRows);
styleHeader(summary.getRange("A10:B10"));
const moduleChart = summary.charts.add("bar", summary.getRange(`A10:B${9 + moduleRows.length}`));
moduleChart.title = "话术模块分布";
moduleChart.hasLegend = false;
moduleChart.setPosition("D9", "H25");
styleSheet(summary, "A3:H35", [["A", 150], ["B", 280], ["C", 150], ["D", 180], ["E", 130], ["F", 170], ["G", 120], ["H", 180]]);

const statusSheet = workbook.worksheets.add("转写状态_45");
const poolMap = new Map((pool.selected || []).map(row => [String(row.videoId), row]));
const statusRows = runLog.map(row => ({
  ...row,
  ...(poolMap.get(String(row.videoId)) || {}),
  candidateAttemptsText: (row.candidateAttempts || []).map(item => `${item.keyPath}: ${item.transcriptStatus || item.stderr || item.exitCode}`).join("\n"),
}));
const statusHeaders = [
  { label: "videoId", value: "videoId" },
  { label: "作者", value: "author" },
  { label: "标题", value: "title" },
  { label: "SKU/方向", value: "skuGroup" },
  { label: "风险等级", value: "riskLevel" },
  { label: "转写状态", value: "transcriptStatus" },
  { label: "文本长度", value: "textLength" },
  { label: "媒体关键词", value: "mediaKeyword" },
  { label: "候选尝试", value: "candidateAttemptsText" },
];
const statusTable = rowsFromObjects(statusRows, statusHeaders);
writeRows(statusSheet, 0, 0, statusTable);
styleHeader(statusSheet.getRange("A1:I1"));
styleSheet(statusSheet, `A1:I${statusTable.length}`, [["A", 145], ["B", 120], ["C", 360], ["D", 220], ["E", 90], ["F", 100], ["G", 80], ["H", 170], ["I", 420]]);
statusSheet.freezePanes.freezeRows(1);

const transcriptSheet = workbook.worksheets.add("逐字稿明细_35");
const transcriptHeaders = [
  { label: "videoId", value: "videoId" },
  { label: "作者", value: "author" },
  { label: "标题", value: "title" },
  { label: "SKU/方向", value: "skuGroup" },
  { label: "赞", value: "likeCount" },
  { label: "评", value: "commentCount" },
  { label: "话术模块", value: "moduleText" },
  { label: "命中规则", value: "ruleHits" },
  { label: "规则名称", value: "ruleNames" },
  { label: "逐字稿清洗文本", value: "cleanText" },
  { label: "改写方向", value: "rewriteDirection" },
  { label: "链接", value: "url" },
];
const transcriptTable = rowsFromObjects(validTranscriptRows(), transcriptHeaders);
writeRows(transcriptSheet, 0, 0, transcriptTable);
styleHeader(transcriptSheet.getRange("A1:L1"));
styleSheet(transcriptSheet, `A1:L${transcriptTable.length}`, [["A", 145], ["B", 120], ["C", 330], ["D", 220], ["E", 70], ["F", 70], ["G", 210], ["H", 160], ["I", 260], ["J", 520], ["K", 420], ["L", 360]]);
transcriptSheet.freezePanes.freezeRows(1);

const moduleSheet = workbook.worksheets.add("话术模块");
const moduleDetailRows = [["话术模块", "有效逐字稿数", "过审处理方向"], ...data.moduleSummary.map(row => [
  row.module,
  row.count,
  ({
    "明星/达人背书": "达人分享仅代表个人体验；产品信息以包装、标签和官方说明为准。",
    "年龄/人群场景": "按具体 SKU、年龄、人群和标签说明选择，避免跨人群泛化。",
    "功效/状态表达": "改为日常营养支持，不承诺精力、气血、代谢、焕活等结果。",
    "成分/剂型说明": "保留成分/剂型事实，不推导全部所需或明显改善。",
    "一包/一粒解决感": "避免一包解决/一粒补全，回到按标签建议补充。",
    "版本/渠道信任": "以购买页、实物标签和客服说明解释版本/渠道。",
    "促销/购买引导": "活动价格以当前页面为准，CTA 不与功效绑定。",
    "比较/榜单/避坑": "避免红黑榜和竞品贬损，改为中性选购维度。",
  })[row.module] || "保留低风险信息，减少强承诺。"
])];
writeRows(moduleSheet, 0, 0, moduleDetailRows);
styleHeader(moduleSheet.getRange("A1:C1"));
styleSheet(moduleSheet, `A1:C${moduleDetailRows.length}`, [["A", 180], ["B", 100], ["C", 520]]);

const ruleSheet = workbook.worksheets.add("Rule命中");
const ruleRows = [["Rule Pack", "命中逐字稿数"], ...data.ruleSummary.map(row => [row.rule, row.count])];
writeRows(ruleSheet, 0, 0, ruleRows);
styleHeader(ruleSheet.getRange("A1:B1"));
styleSheet(ruleSheet, `A1:B${ruleRows.length}`, [["A", 150], ["B", 120]]);

const goldenSheet = workbook.worksheets.add("黄金改写集_逐字稿");
const goldenHeaders = [
  { label: "ID", value: "id" },
  { label: "模块", value: "module" },
  { label: "来源视频", value: row => `${row.author}｜${row.sourceTitle}` },
  { label: "命中规则", value: "riskRules" },
  { label: "原逐字稿片段", value: "originalTranscriptSnippet" },
  { label: "建议通过版", value: "suggestedApprovedWording" },
  { label: "依据", value: "why" },
];
const goldenTable = rowsFromObjects(data.goldenTranscriptRows, goldenHeaders);
writeRows(goldenSheet, 0, 0, goldenTable);
styleHeader(goldenSheet.getRange("A1:G1"));
styleSheet(goldenSheet, `A1:G${goldenTable.length}`, [["A", 105], ["B", 160], ["C", 320], ["D", 160], ["E", 430], ["F", 480], ["G", 300]]);
goldenSheet.freezePanes.freezeRows(1);

const commentSheet = workbook.worksheets.add("评论主题复核");
const commentRows = [["主题", "评论数", "典型评论", "改进动作"], ...data.commentThemeSummary.map(row => [
  row.theme,
  row.commentCount,
  (row.sampleComments || []).slice(0, 5).join("\n"),
  row.opportunity,
])];
writeRows(commentSheet, 0, 0, commentRows);
styleHeader(commentSheet.getRange("A1:D1"));
styleSheet(commentSheet, `A1:D${commentRows.length}`, [["A", 180], ["B", 90], ["C", 500], ["D", 520]]);
commentSheet.freezePanes.freezeRows(1);

const videoSheet = workbook.worksheets.add("视频样本_78");
const videoHeaders = [
  { label: "videoId", value: "videoId" },
  { label: "关键词", value: "keyword" },
  { label: "SKU/方向", value: "skuGroup" },
  { label: "作者", value: "author" },
  { label: "标题/文案", value: "title" },
  { label: "赞", value: "likeCount" },
  { label: "评", value: "commentCount" },
  { label: "转", value: "shareCount" },
  { label: "藏", value: "collectCount" },
  { label: "风险等级", value: "riskLevel" },
  { label: "命中规则", value: "ruleHits" },
  { label: "规则名称", value: "ruleNames" },
  { label: "链接", value: "url" },
];
const videoTable = rowsFromObjects(data.videoRows, videoHeaders);
writeRows(videoSheet, 0, 0, videoTable);
styleHeader(videoSheet.getRange("A1:M1"));
styleSheet(videoSheet, `A1:M${videoTable.length}`, [["A", 145], ["B", 145], ["C", 210], ["D", 130], ["E", 360], ["F", 75], ["G", 75], ["H", 75], ["I", 75], ["J", 90], ["K", 160], ["L", 270], ["M", 360]]);
videoSheet.freezePanes.freezeRows(1);

const commentDetailSheet = workbook.worksheets.add("评论明细_471");
const commentHeaders = [
  { label: "commentId", value: "commentId" },
  { label: "videoId", value: "videoId" },
  { label: "视频标题", value: "videoTitle" },
  { label: "作者", value: "videoAuthor" },
  { label: "关键词", value: "keyword" },
  { label: "主题", value: "themeText" },
  { label: "评论", value: "text" },
  { label: "点赞", value: "likeCount" },
  { label: "回复数", value: "replyCount" },
  { label: "IP", value: "ipLocation" },
];
const commentDetailTable = rowsFromObjects(data.commentRows, commentHeaders);
writeRows(commentDetailSheet, 0, 0, commentDetailTable);
styleHeader(commentDetailSheet.getRange("A1:J1"));
styleSheet(commentDetailSheet, `A1:J${commentDetailTable.length}`, [["A", 160], ["B", 145], ["C", 320], ["D", 120], ["E", 145], ["F", 190], ["G", 460], ["H", 70], ["I", 70], ["J", 90]]);
commentDetailSheet.freezePanes.freezeRows(1);

const inspect = await workbook.inspect({
  kind: "match",
  searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A",
  options: { useRegex: true, maxResults: 100 },
  summary: "formula error scan",
});

for (const sheetName of ["执行摘要_逐字稿", "转写状态_45", "逐字稿明细_35", "话术模块", "Rule命中", "黄金改写集_逐字稿", "评论主题复核", "视频样本_78", "评论明细_471"]) {
  const preview = await workbook.render({ sheetName, autoCrop: "all", scale: 1, format: "png" });
  await fs.writeFile(path.join(outputDir, `${sheetName}.png`), new Uint8Array(await preview.arrayBuffer()));
}

const xlsx = await SpreadsheetFile.exportXlsx(workbook);
const xlsxPath = path.join(outputDir, "善存抖音逐字稿话术审核分析.xlsx");
await xlsx.save(xlsxPath);

console.log(JSON.stringify({
  status: "ok",
  xlsxPath,
  formulaScan: inspect.ndjson,
}, null, 2));