/**
* 原始 Excel 宣发数据 → 漂亮 HTML 单页
* 输入:
* shuhua/舒华跑步机四月宣发报备KOC发布数据.xlsx
* majiyong/【新鲜菜项目】马记永4月宣发数据汇总(1).xlsx
* 输出:
* shuhua/shuhua-raw-data.html
* majiyong/majiyong-raw-data.html
*
* 视觉风格：与 *-closing-report.html 一致（暗色 + ECharts）
*/
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const ROOT = path.resolve(__dirname, '..');
const SHUHUA_XLSX = path.join(ROOT, 'shuhua', '舒华跑步机四月宣发报备KOC发布数据.xlsx');
const MAJI_XLSX = path.join(ROOT, 'majiyong', '【新鲜菜项目】马记永4月宣发数据汇总(1).xlsx');
const SHUHUA_OUT = path.join(ROOT, 'shuhua', 'shuhua-raw-data.html');
const MAJI_OUT = path.join(ROOT, 'majiyong', 'majiyong-raw-data.html');
// ============== utils ==============
const num = (v) => {
const n = Number(v);
return Number.isFinite(n) ? n : 0;
};
const esc = (s) => String(s == null ? '' : s)
.replace(/&/g, '&').replace(//g, '>')
.replace(/"/g, '"').replace(/'/g, ''');
const fmt = (n) => {
n = Number(n) || 0;
if (Math.abs(n) >= 1e8) return (n / 1e8).toFixed(2) + ' 亿';
if (Math.abs(n) >= 1e4) return (n / 1e4).toFixed(2) + ' 万';
return n.toLocaleString('zh-CN', { maximumFractionDigits: 2 });
};
const fmtInt = (n) => (Number(n) || 0).toLocaleString('zh-CN', { maximumFractionDigits: 0 });
const fmtMoney = (n) => '¥' + (Number(n) || 0).toLocaleString('zh-CN', { maximumFractionDigits: 0 });
// excel serial → yyyy-MM-dd
const excelDate = (v) => {
if (!v) return '';
if (typeof v === 'string' && /\d{4}-\d{1,2}-\d{1,2}/.test(v)) return v;
const n = Number(v);
if (!Number.isFinite(n) || n < 30000 || n > 80000) return String(v);
const ms = (n - 25569) * 86400 * 1000;
const d = new Date(ms);
const Y = d.getUTCFullYear();
const M = String(d.getUTCMonth() + 1).padStart(2, '0');
const D = String(d.getUTCDate()).padStart(2, '0');
return `${Y}-${M}-${D}`;
};
const safeHref = (u) => {
const s = String(u || '').trim();
if (!s || !/^https?:\/\//i.test(s)) return '';
return s;
};
// ============== shared CSS / shell ==============
const COMMON_HEAD = (title) => `
${esc(title)}
`;
const COMMON_FOOT = (footText) => `
`;
// ============== ECharts options ==============
const echartsBaseTheme = `
textStyle:{color:'#A8A8A8',fontFamily:'Inter,PingFang SC'},
legend:{textStyle:{color:'#A8A8A8'}},
tooltip:{backgroundColor:'#1A1A1A',borderColor:'rgba(255,255,255,0.1)',textStyle:{color:'#F5F5F5'}},
grid:{left:60,right:30,top:50,bottom:50},
xAxis:{axisLine:{lineStyle:{color:'rgba(255,255,255,0.1)'}},axisLabel:{color:'#A8A8A8',fontSize:11},splitLine:{lineStyle:{color:'rgba(255,255,255,0.04)'}}},
yAxis:{axisLine:{lineStyle:{color:'rgba(255,255,255,0.1)'}},axisLabel:{color:'#A8A8A8',fontSize:11},splitLine:{lineStyle:{color:'rgba(255,255,255,0.04)'}}}
`;
// ============== SHUHUA builder ==============
function buildShuhua() {
const wb = XLSX.readFile(SHUHUA_XLSX);
const sheet = wb.Sheets[wb.SheetNames[0]];
const rows = XLSX.utils.sheet_to_json(sheet, { defval: '' });
const items = rows.map((r) => ({
idx: num(r['序号']),
type: String(r['类型'] || '').trim(),
name: String(r['名称'] || '').trim(),
homepage: String(r['主页链接'] || '').trim(),
accountId: String(r['账号ID'] || '').trim(),
followers: num(r['粉丝']),
liked: num(r['赞藏']),
imgPrice: num(r['图文']),
imgOrderPrice: num(r['图文下单价']),
textPrice: num(r['写文']),
priceTotal: num(r['合计含税直发价格']),
publishDate: excelDate(r['发布时间']),
contentType: String(r['内容类型'] || '').trim() || '未分类',
publishLink: String(r['发布链接'] || '').trim(),
impressions: num(r['曝光量']),
reads: num(r['阅读量']),
likes: num(r['点赞']),
collects: num(r['收藏']),
comments: num(r['评论']),
engagement: num(r['互动总量']),
}));
const sum = items.reduce((a, r) => ({
spend: a.spend + r.priceTotal,
imp: a.imp + r.impressions,
reads: a.reads + r.reads,
likes: a.likes + r.likes,
collects: a.collects + r.collects,
comments: a.comments + r.comments,
eng: a.eng + r.engagement,
followers: a.followers + r.followers,
}), { spend: 0, imp: 0, reads: 0, likes: 0, collects: 0, comments: 0, eng: 0, followers: 0 });
const total = {
kocs: items.length,
...sum,
engRate: sum.reads ? (sum.eng / sum.reads * 100) : 0,
cpe: sum.eng ? (sum.spend / sum.eng) : 0,
cpm: sum.imp ? (sum.spend * 1000 / sum.imp) : 0,
avgFans: items.length ? Math.round(sum.followers / items.length) : 0,
};
// 内容类型聚合
const byCt = {};
for (const r of items) {
const k = r.contentType || '未分类';
if (!byCt[k]) byCt[k] = { count: 0, eng: 0, reads: 0, spend: 0 };
byCt[k].count++;
byCt[k].eng += r.engagement;
byCt[k].reads += r.reads;
byCt[k].spend += r.priceTotal;
}
const ctStats = Object.entries(byCt).map(([k, v]) => ({ name: k, ...v })).sort((a, b) => b.eng - a.eng);
// 类型聚合（达人级别）
const byTp = {};
for (const r of items) {
const k = r.type || '未分类';
if (!byTp[k]) byTp[k] = { count: 0, eng: 0, spend: 0 };
byTp[k].count++;
byTp[k].eng += r.engagement;
byTp[k].spend += r.priceTotal;
}
const tpStats = Object.entries(byTp).map(([k, v]) => ({ name: k, ...v }));
const ctOptions = [...new Set(items.map(r => r.contentType))].filter(Boolean).sort();
const tpOptions = [...new Set(items.map(r => r.type))].filter(Boolean).sort();
const tableRows = items.slice().sort((a, b) => b.engagement - a.engagement).map((r) => {
const href = safeHref(r.publishLink);
const homeHref = safeHref(r.homepage);
return `
| ${r.idx} |
${esc(r.type)} |
${homeHref ? `${esc(r.name)}` : esc(r.name)} |
${esc(r.contentType)} |
${esc(r.publishDate)} |
${fmtInt(r.followers)} |
${fmtInt(r.liked)} |
${fmtMoney(r.priceTotal)} |
${fmtInt(r.impressions)} |
${fmtInt(r.reads)} |
${fmtInt(r.likes)} |
${fmtInt(r.collects)} |
${fmtInt(r.comments)} |
${fmtInt(r.engagement)} |
${href ? `打开` : '—'} |
`;
}).join('');
const scatterData = JSON.stringify(items.map(r => [r.followers, r.engagement, r.name, r.contentType]));
const ctNames = JSON.stringify(ctStats.map(x => x.name));
const ctEng = JSON.stringify(ctStats.map(x => x.eng));
const ctCnt = JSON.stringify(ctStats.map(x => x.count));
const tpPie = JSON.stringify(tpStats.map(x => ({ name: x.name, value: x.count })));
const html = COMMON_HEAD('舒华跑步机 · 四月 KOC 宣发原始数据') + `
SHUHUA · APRIL 2025 · RAW DATA
舒华跑步机
四月 KOC 宣发 · 原始数据
数据源：舒华跑步机四月宣发报备KOC发布数据.xlsx，共 ${total.kocs} 位 KOC 达人。本页对原始 Excel 字段进行结构化重构，所有指标可即时筛选、排序、跳转原帖。
01 / OVERVIEW
核心指标总览
KOC 达人
${total.kocs}位
平均粉丝 ${fmtInt(total.avgFans)}
总曝光量
${fmt(total.imp)}
CPM ${total.cpm.toFixed(2)} 元
总阅读量
${fmt(total.reads)}
点赞 ${fmtInt(total.likes)} · 收藏 ${fmtInt(total.collects)} · 评论 ${fmtInt(total.comments)}
总互动量
${fmt(total.eng)}
互动率 ${total.engRate.toFixed(2)}% · CPE ${total.cpe.toFixed(2)} 元
总投放金额
${fmtMoney(total.spend)}
单 KOC 均价 ${fmtMoney(total.spend / total.kocs)}
03 / TABLE
明细数据表
点击表头即可排序，支持全字段关键词搜索 + 类型/内容类型筛选。默认按 互动总量 降序。
` + COMMON_FOOT('舒华跑步机 · 四月 KOC 宣发原始数据 · Generated from 舒华跑步机四月宣发报备KOC发布数据.xlsx');
fs.writeFileSync(SHUHUA_OUT, html, 'utf8');
console.log('✅ 舒华原始数据页生成:', SHUHUA_OUT, `(${(fs.statSync(SHUHUA_OUT).size / 1024).toFixed(1)} KB)`);
}
// ============== MAJIYONG builder ==============
function buildMajiyong() {
const wb = XLSX.readFile(MAJI_XLSX);
// 数据汇总
const summaryRows = XLSX.utils.sheet_to_json(wb.Sheets['数据汇总'], { defval: '' });
const summary = summaryRows.filter(r => r['合作量级']).map(r => ({
tier: String(r['合作量级']).trim(),
count: num(r['合作数量']),
eng: num(r['互动量']),
imp: num(r['曝光量']),
spend: num(r['达人采购费用']),
adSpend: num(r['投流费用']),
cpe: num(r['单次互动成本']),
cpm: num(r['千次曝光成本']),
}));
const grandTotal = summary.find(r => /合计|总计|总和/.test(r.tier)) || summary.reduce((a, r) => ({
tier: '合计', count: a.count + r.count, eng: a.eng + r.eng, imp: a.imp + r.imp,
spend: a.spend + r.spend, adSpend: a.adSpend + r.adSpend, cpe: 0, cpm: 0,
}), { tier: '合计', count: 0, eng: 0, imp: 0, spend: 0, adSpend: 0, cpe: 0, cpm: 0 });
const totalSpend = grandTotal.spend + grandTotal.adSpend;
grandTotal.cpe = grandTotal.eng ? totalSpend / grandTotal.eng : 0;
grandTotal.cpm = grandTotal.imp ? totalSpend * 1000 / grandTotal.imp : 0;
const summaryList = summary.filter(r => !/合计|总计|总和/.test(r.tier));
// KOL
const kolRowsRaw = XLSX.utils.sheet_to_json(wb.Sheets['KOL合作表'], { defval: '' });
const kol = kolRowsRaw.filter(r => r['序号'] && typeof r['序号'] === 'number').map(r => ({
idx: num(r['序号']),
tier: String(r['量级'] || '').trim(),
ctype: String(r['类型'] || '').trim() || '未分类',
name: String(r['小红书昵称'] || '').trim(),
homepage: String(r['主页链接'] || '').trim(),
followers: Math.round(num(r['粉丝数/w']) * 10000),
region: String(r['地区'] || '').trim(),
form: String(r['合作形式'] || '').trim(),
price: num(r['下单价格']),
sync: String(r['可同步平台'] || '').trim(),
publishDate: excelDate(r['发布时间']),
link: String(r['发布链接'] || '').trim(),
likes: num(r['点赞']),
collects: num(r['收藏']),
comments: num(r['评论']),
eng: num(r['总互动']),
imp: num(r['曝光量']),
syncEng: num(r['同步互动量']),
syncImp: num(r['同步曝光量']),
engTotal: num(r['互动量小计']),
impTotal: num(r['曝光量小计']),
}));
// KOC
const kocRowsRaw = XLSX.utils.sheet_to_json(wb.Sheets['KOC合作表'], { defval: '' });
const koc = kocRowsRaw.filter(r => r['序号'] && typeof r['序号'] === 'number').map(r => ({
idx: num(r['序号']),
tier: String(r['量级'] || '').trim(),
ctype: String(r['类型'] || '').trim() || '未分类',
name: String(r['小红书昵称'] || '').trim(),
homepage: String(r['主页链接'] || '').trim(),
followers: Math.round(num(r['粉丝数/w']) * 10000),
region: String(r['地区'] || '').trim(),
publishDate: excelDate(r['发布时间']),
link: String(r['发布链接'] || '').trim(),
likes: num(r['点赞']),
collects: num(r['收藏']),
comments: num(r['评论']),
eng: num(r['总互动']),
imp: num(r['曝光量']),
engTotal: num(r['互动量小计']),
impTotal: num(r['曝光量小计']),
}));
// KOL/KOC 聚合
const aggBy = (arr, key) => {
const m = {};
for (const r of arr) {
const k = r[key] || '未分类';
if (!m[k]) m[k] = { count: 0, eng: 0, imp: 0, spend: 0 };
m[k].count++; m[k].eng += r.engTotal || r.eng; m[k].imp += r.impTotal || r.imp;
m[k].spend += (r.price || 0);
}
return Object.entries(m).map(([k, v]) => ({ name: k, ...v })).sort((a, b) => b.eng - a.eng);
};
const kolByType = aggBy(kol, 'ctype');
const kocByType = aggBy(koc, 'ctype');
const kocByRegion = (() => {
const m = {};
for (const r of koc) {
const k = (String(r.region).slice(0, 20)) || '未知';
if (!m[k]) m[k] = 0; m[k] += r.engTotal || r.eng;
}
return Object.entries(m).map(([k, v]) => ({ name: k, value: v })).sort((a, b) => b.value - a.value).slice(0, 12);
})();
const buildKolRow = (r) => {
const href = safeHref(r.link), homeHref = safeHref(r.homepage);
return `
| ${r.idx} |
${esc(r.tier)} |
${esc(r.ctype)} |
${homeHref ? `${esc(r.name)}` : esc(r.name)} |
${fmtInt(r.followers)} |
${esc(r.region)} |
${esc(r.form)} |
${fmtMoney(r.price)} |
${esc(r.publishDate)} |
${fmtInt(r.likes)} |
${fmtInt(r.collects)} |
${fmtInt(r.comments)} |
${fmtInt(r.engTotal)} |
${fmtInt(r.impTotal)} |
${href ? `打开` : '—'} |
`;
};
const buildKocRow = (r) => {
const href = safeHref(r.link), homeHref = safeHref(r.homepage);
return `
| ${r.idx} |
${esc(r.tier)} |
${esc(r.ctype)} |
${homeHref ? `${esc(r.name)}` : esc(r.name)} |
${fmtInt(r.followers)} |
${esc(r.region)} |
${esc(r.publishDate)} |
${fmtInt(r.likes)} |
${fmtInt(r.collects)} |
${fmtInt(r.comments)} |
${fmtInt(r.engTotal)} |
${fmtInt(r.impTotal)} |
${href ? `打开` : '—'} |
`;
};
const kolRows = kol.slice().sort((a, b) => b.engTotal - a.engTotal).map(buildKolRow).join('');
const kocRows = koc.slice().sort((a, b) => b.engTotal - a.engTotal).map(buildKocRow).join('');
const kolCtOpts = [...new Set(kol.map(r => r.ctype))].filter(Boolean).sort();
const kolTierOpts = [...new Set(kol.map(r => r.tier))].filter(Boolean).sort();
const kocCtOpts = [...new Set(koc.map(r => r.ctype))].filter(Boolean).sort();
const kocTierOpts = [...new Set(koc.map(r => r.tier))].filter(Boolean).sort();
const kocRegionOpts = [...new Set(koc.map(r => r.region))].filter(Boolean).sort();
const sumNames = JSON.stringify(summaryList.map(r => r.tier));
const sumEng = JSON.stringify(summaryList.map(r => r.eng));
const sumImp = JSON.stringify(summaryList.map(r => r.imp));
const sumSpend = JSON.stringify(summaryList.map(r => r.spend + r.adSpend));
const ctMix = JSON.stringify([...kolByType, ...kocByType].reduce((arr, x) => {
const e = arr.find(y => y.name === x.name);
if (e) e.value += x.eng; else arr.push({ name: x.name, value: x.eng });
return arr;
}, []).sort((a, b) => b.value - a.value));
const regionData = JSON.stringify(kocByRegion);
const html = COMMON_HEAD('马记永 · 四月新鲜菜宣发原始数据') + `
MAJIYONG · APRIL 2025 · RAW DATA
马记永兰州牛肉面
四月新鲜菜宣发 · 原始数据
数据源：【新鲜菜项目】马记永4月宣发数据汇总.xlsx，包含数据汇总 / KOL 合作表 / KOC 合作表 三张工作表。本页对原始 Excel 字段进行结构化重构，所有指标可即时筛选、排序、跳转原帖。
01 / OVERVIEW
核心指标总览
合作达人
${grandTotal.count}位
KOL ${kol.length} · KOC ${koc.length}
总曝光量
${fmt(grandTotal.imp)}
CPM ${grandTotal.cpm.toFixed(2)} 元
总互动量
${fmt(grandTotal.eng)}
CPE ${grandTotal.cpe.toFixed(2)} 元
总投放金额
${fmtMoney(totalSpend)}
达人采购 ${fmtMoney(grandTotal.spend)} · 投流 ${fmtMoney(grandTotal.adSpend)}
02 / SUMMARY
数据汇总（按合作量级）
| 合作量级 | 数量 | 互动量 | 曝光量 |
采购费用 | 投流费用 | 单次互动 | 千次曝光 |
${summary.map(r => `
| ${esc(r.tier)} |
${fmtInt(r.count)} |
${fmtInt(r.eng)} |
${fmtInt(r.imp)} |
${fmtMoney(r.spend)} |
${fmtMoney(r.adSpend)} |
${r.cpe ? r.cpe.toFixed(2) + ' 元' : '—'} |
${r.cpm ? r.cpm.toFixed(2) + ' 元' : '—'} |
`).join('')}
04 / KOL
KOL 合作明细（${kol.length}）
默认按互动量小计降序。可搜索 / 按级别 / 按内容类型筛选。
05 / KOC
KOC 合作明细（${koc.length}）
默认按互动量小计降序。可搜索 / 按级别 / 按内容类型 / 按地区筛选。
` + COMMON_FOOT('马记永 · 四月新鲜菜宣发原始数据 · Generated from 【新鲜菜项目】马记永4月宣发数据汇总.xlsx');
fs.writeFileSync(MAJI_OUT, html, 'utf8');
console.log('✅ 马记永原始数据页生成:', MAJI_OUT, `(${(fs.statSync(MAJI_OUT).size / 1024).toFixed(1)} KB)`);
}
buildShuhua();
buildMajiyong();