openclaw-voc-skill/tools/build_centrum_douyin_live_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/live-full");
const dataPath = path.join(outputDir, "live-analysis-data.json");
const data = JSON.parse(await fs.readFile(dataPath, "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("、");
    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 safeSheetRange(rowCount, colCount) {
  const letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  const col = colCount <= 26 ? letters[colCount - 1] : "Z";
  return `A1:${col}${Math.max(1, rowCount)}`;
}

const workbook = Workbook.create();

const summary = workbook.worksheets.add("执行摘要");
summary.getRange("A1:H1").merge();
summary.getRange("A1").values = [["善存抖音稿件审核润色与评论 VOC Live 版"]];
summary.getRange("A1").format = {
  fill: "#0B1F33",
  font: { bold: true, color: "#FFFFFF", size: 16 },
  horizontalAlignment: "center",
};

const generatedAtText = `生成于 ${String(data.summary.generatedAt || "").replace("T", " ").slice(0, 19)}`;
const summaryRows = [
  ["生成时间", generatedAtText, "去重视频样本", data.summary.videoCount],
  ["去重评论样本", data.summary.commentCount, "请求关键词", data.summary.requestedKeywordCount],
  ["有效返回关键词", data.summary.effectiveKeywordCount, "接口警告/错误", `${data.summary.warningCount} / ${data.summary.errorCount}`],
  ["核心结论", "生活场景/达人内容/选购答疑是主要过审路径", "主要风险", "精力、气血、代谢、甲流、大脑、一粒补全"],
  ["方法/限制", "抖音关键词采集并去重；脚本拆解基于标题、标签、描述、评论和可获取文本", "审核差异口径", "未取得审核前原稿/驳回流水；润色路径为基于已发布稿件和 Rule Pack 的反推"],
];
writeRows(summary, 2, 0, summaryRows);

const riskRows = [["风险等级", "视频数"], ...data.summary.riskCounts.map(row => [row.riskLevel, row.videoCount])];
writeRows(summary, 8, 0, riskRows);
styleHeader(summary.getRange("A9:B9"));
const riskChart = summary.charts.add("bar", summary.getRange(`A9:B${8 + riskRows.length}`));
riskChart.title = "视频风险等级分布";
riskChart.hasLegend = false;
riskChart.setPosition("D9", "H24");

const skuRows = [["SKU/方向", "视频数"], ...data.summary.skuCounts.slice(0, 10).map(row => [row.skuGroup, row.videoCount])];
writeRows(summary, 26, 0, skuRows);
styleHeader(summary.getRange("A27:B27"));
styleSheet(summary, "A3:H40", [["A", 150], ["B", 260], ["C", 130], ["D", 180], ["E", 130], ["F", 180], ["G", 130], ["H", 180]]);

const videos = 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: "score" },
  { label: "风险等级", value: "riskLevel" },
  { label: "命中规则", value: "ruleHits" },
  { label: "规则名称", value: "ruleNames" },
  { label: "抓取评论数", value: "commentCaptured" },
  { label: "高赞评论", value: "topComment" },
  { label: "链接", value: "url" },
];
const videoRows = rowsFromObjects(data.videoRows, videoHeaders);
writeRows(videos, 0, 0, videoRows);
styleHeader(videos.getRangeByIndexes(0, 0, 1, videoHeaders.length));
styleSheet(videos, `A1:P${videoRows.length}`, [["A", 145], ["B", 150], ["C", 200], ["D", 130], ["E", 330], ["F", 75], ["G", 75], ["H", 75], ["I", 75], ["J", 85], ["K", 90], ["L", 160], ["M", 260], ["N", 90], ["O", 330], ["P", 360]]);
videos.freezePanes.freezeRows(1);

const themes = workbook.worksheets.add("评论主题");
const themeHeaders = [
  { label: "主题", value: "theme" },
  { label: "评论数", value: "commentCount" },
  { label: "有赞评论数", value: "likedCommentCount" },
  { label: "总点赞", value: "totalLikes" },
  { label: "典型评论", value: row => (row.sampleComments || []).slice(0, 5).join("\n") },
  { label: "改进动作", value: "opportunity" },
];
const themeRows = rowsFromObjects(data.themeSummary, themeHeaders);
writeRows(themes, 0, 0, themeRows);
styleHeader(themes.getRange("A1:F1"));
styleSheet(themes, `A1:F${themeRows.length}`, [["A", 180], ["B", 85], ["C", 95], ["D", 85], ["E", 420], ["F", 460]]);
themes.freezePanes.freezeRows(1);

const comments = 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 commentRows = rowsFromObjects(data.commentRows, commentHeaders);
writeRows(comments, 0, 0, commentRows);
styleHeader(comments.getRange("A1:J1"));
styleSheet(comments, `A1:J${commentRows.length}`, [["A", 160], ["B", 145], ["C", 320], ["D", 120], ["E", 145], ["F", 190], ["G", 460], ["H", 70], ["I", 70], ["J", 90]]);
comments.freezePanes.freezeRows(1);

const goldens = workbook.worksheets.add("黄金改写集");
const goldenHeaders = [
  { label: "ID", value: "id" },
  { label: "风险原型", value: "riskPrototype" },
  { label: "命中规则", value: "ruleHits" },
  { label: "风险表达", value: "beforeExample" },
  { label: "建议通过版", value: "afterExample" },
  { label: "为什么这样改", value: "why" },
  { label: "样本依据", value: "sourceEvidence" },
];
const goldenRows = rowsFromObjects(data.goldenRows, goldenHeaders);
writeRows(goldens, 0, 0, goldenRows);
styleHeader(goldens.getRange("A1:G1"));
styleSheet(goldens, `A1:G${goldenRows.length}`, [["A", 105], ["B", 250], ["C", 150], ["D", 300], ["E", 370], ["F", 310], ["G", 220]]);
goldens.freezePanes.freezeRows(1);

const scripts = workbook.worksheets.add("脚本拆解");
const scriptHeaders = [
  { label: "videoId", value: "videoId" },
  { label: "标题", value: "title" },
  { label: "作者", value: "author" },
  { label: "SKU/方向", value: "skuGroup" },
  { label: "钩子类型", value: "hookType" },
  { label: "当前结构", value: "currentStructure" },
  { label: "风险规则", value: "riskRules" },
  { label: "可复用改法", value: "reusableFix" },
];
const scriptRows = rowsFromObjects(data.scriptBreakdowns, scriptHeaders);
writeRows(scripts, 0, 0, scriptRows);
styleHeader(scripts.getRange("A1:H1"));
styleSheet(scripts, `A1:H${scriptRows.length}`, [["A", 145], ["B", 320], ["C", 120], ["D", 210], ["E", 180], ["F", 330], ["G", 180], ["H", 380]]);
scripts.freezePanes.freezeRows(1);

const keywords = workbook.worksheets.add("关键词与采集日志");
const keywordRows = [
  ["请求关键词", data.requestedKeywords.join("、")],
  ["有效返回关键词", data.effectiveKeywords.join("、")],
  ["警告数", data.summary.warningCount],
  ["错误数", data.summary.errorCount],
  ["说明", "部分关键词/单条评论页 fetch failed，已保留成功采集的视频与评论；未写入 VOC token。"],
  ["限制", "未取得品牌审核前原稿、驳回原因、修改版本流水和最终通过稿的一一对应关系；本轮输出不等同逐条原稿差异审计。"],
];
writeRows(keywords, 0, 0, keywordRows);
const errorsStart = keywordRows.length + 2;
const errorRows = [["stage", "keyword", "videoId", "message", "httpStatus"], ...data.errors.map(err => [
  err.stage || "",
  err.keyword || "",
  err.videoId || "",
  err.message || "",
  err.httpStatus || "",
])];
writeRows(keywords, errorsStart, 0, errorRows);
styleHeader(keywords.getRangeByIndexes(errorsStart, 0, 1, 5));
styleSheet(keywords, `A1:E${errorsStart + errorRows.length}`, [["A", 160], ["B", 260], ["C", 150], ["D", 480], ["E", 90]]);

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 ["执行摘要", "视频样本_78", "评论主题", "黄金改写集", "脚本拆解", "关键词与采集日志"]) {
  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, "善存抖音稿件审核润色与评论VOC-live.xlsx");
await xlsx.save(xlsxPath);

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