import type { NormalizedCandidate } from './recommendation.service.ts'; function escapeHtml(value: string): string { return value .replaceAll('&', '&') .replaceAll('<', '<') .replaceAll('>', '>') .replaceAll('"', '"'); } /** * 生成媒体资源推荐表 Excel (HTML table format compatible with MS Excel) * 按模板生成：账号名称、ID、账号类型、地区、链接、粉丝数、赞藏数、 * 图文/视频报价、推荐等级、推荐理由等字段 */ export function generateExcelBuffer(candidates: NormalizedCandidate[], taskFileName?: string): Buffer { const title = taskFileName ? taskFileName.replace(/\.[^.]+$/, '') : '达人推荐表'; const now = new Date().toLocaleDateString('zh-CN'); const evaluationHeaders = buildEvaluationHeaders(candidates); const colCount = 28 + evaluationHeaders.length; const headerRow = ` 序号 平台 账号名称 账号ID 地区 主页链接 粉丝数 图文报价 视频报价 最低报价 内容标签 综合评分 风格匹配 推荐等级 推荐理由 风险提示 数据来源 昵称 性别 粉丝数（万） 赞藏数（万） 内容类型 城市 地理位置 小红书主页 合作方式 图文笔记报价（含平台服务费） 视频笔记报价（含平台服务费） ${evaluationHeaders.map((header) => `${escapeHtml(header)}`).join('')} `; const rows = candidates.map((c, index) => ` ${index + 1} ${escapeHtml(mapPlatformDisplay(c.platform))} ${escapeHtml(c.displayName)} ${escapeHtml(c.platformUserId)} ${escapeHtml(c.location || '-')} ${escapeHtml(c.profileUrl)} ${formatFans(c.fansCount)} ${c.imagePrice > 0 ? `¥${c.imagePrice}` : '-'} ${c.videoPrice > 0 ? `¥${c.videoPrice}` : '-'} ${c.minPrice > 0 ? `¥${c.minPrice}` : '-'} ${escapeHtml(c.contentTags.join('、') || '-')} ${c.score} ${c.styleMatch}% ${escapeHtml(c.recommendStatus)} ${escapeHtml(c.recommendReason || '-')} ${escapeHtml(c.riskNote || '-')} ${escapeHtml(c.sourceProvider)} ${escapeHtml(c.displayName)} ${escapeHtml(c.gender || '-')} ${formatWan(c.fansCount)} ${formatWan(c.likedCollectCount || 0)} ${escapeHtml(c.contentType || c.contentTags.join('、') || '-')} ${escapeHtml(c.city || c.location || '-')} ${escapeHtml(c.geoLocation || c.location || '-')} ${escapeHtml(c.xiaohongshuUrl || (c.platform === 'xiaohongshu' ? c.profileUrl : '-'))} ${escapeHtml(c.cooperationMethod || '-')} ${c.imagePrice > 0 ? `¥${c.imagePrice}` : '-'} ${c.videoPrice > 0 ? `¥${c.videoPrice}` : '-'} ${buildEvaluationCells(c, evaluationHeaders)} ` ).join(''); const html = ` ${headerRow} ${rows}
${escapeHtml(title)} - 媒体资源推荐表
生成时间：${now} | 候选总数：${candidates.length} | 强推荐：${candidates.filter(c => c.recommendStatus === '强推荐').length} | 备选：${candidates.filter(c => c.recommendStatus === '备选').length}
`; return Buffer.from(html, 'utf-8'); } function buildEvaluationHeaders(candidates: NormalizedCandidate[]): string[] { const headers: string[] = []; const has = (predicate: (candidate: NormalizedCandidate) => boolean) => candidates.some(predicate); if (has((candidate) => Boolean(candidate.evaluation?.cpmCpe))) headers.push('CPM/CPE判断', '图文CPM', '视频CPM', '图文CPE', '视频CPE'); if (has((candidate) => Boolean(candidate.evaluation?.commercialStability))) headers.push('商单稳定性判断', '商单数', '近30天商单数', '商单阅读中位数', '商单互动中位数'); if (has((candidate) => Boolean(candidate.evaluation?.recentPerformance))) headers.push('近期数据判断', '近样本互动中位数', '近期/前期互动比', '互动波动倍数'); if (has((candidate) => Boolean(candidate.evaluation?.updateFrequency))) headers.push('更新频率判断', '近30天样本数'); if (has((candidate) => Boolean(candidate.evaluation?.commentQuality))) headers.push('评论区判断', '评论中位数'); if (has((candidate) => Boolean(candidate.evaluation?.publicSentiment))) headers.push('舆情风险词', '舆情判断'); if (has((candidate) => Boolean(candidate.evaluation?.audienceGender))) headers.push('女性粉丝占比', '粉丝性别判断'); if (has((candidate) => Boolean(candidate.evaluation?.notes?.length))) headers.push('按需评估摘要'); return headers; } function buildEvaluationCells(candidate: NormalizedCandidate, headers: string[]): string { const evaluation = candidate.evaluation; return headers.map((header) => { const value = evaluationCellValue(candidate, header, evaluation); return `${escapeHtml(value)}`; }).join(''); } function evaluationCellValue( _candidate: NormalizedCandidate, header: string, evaluation?: NormalizedCandidate['evaluation'], ): string { if (!evaluation) return '-'; const cpm = evaluation.cpmCpe; const commercial = evaluation.commercialStability; const recent = evaluation.recentPerformance; const update = evaluation.updateFrequency; const comment = evaluation.commentQuality; const sentiment = evaluation.publicSentiment; const audience = evaluation.audienceGender; const values: Record = { 'CPM/CPE判断': cpm?.verdict || '-', '图文CPM': formatOptionalNumber(cpm?.pictureCpm), '视频CPM': formatOptionalNumber(cpm?.videoCpm), '图文CPE': formatOptionalNumber(cpm?.pictureCpe), '视频CPE': formatOptionalNumber(cpm?.videoCpe), '商单稳定性判断': commercial?.verdict || '-', '商单数': formatOptionalNumber(commercial?.businessNoteCount), '近30天商单数': formatOptionalNumber(commercial?.coopNoteNum30d), '商单阅读中位数': formatOptionalNumber(commercial?.readMidCoop30), '商单互动中位数': formatOptionalNumber(commercial?.interMidCoop30), '近期数据判断': recent?.verdict || '-', '近样本互动中位数': formatOptionalNumber(recent?.recentMedianInteraction || recent?.medianInteraction), '近期/前期互动比': formatOptionalNumber(recent?.trendRatio), '互动波动倍数': formatOptionalNumber(recent?.volatility), '更新频率判断': update?.verdict || '-', '近30天样本数': formatOptionalNumber(update?.recent30DayNotes), '评论区判断': comment?.verdict || '-', '评论中位数': formatOptionalNumber(comment?.commentMedian), '舆情风险词': sentiment?.riskKeywordHits?.join('、') || '-', '舆情判断': sentiment?.verdict || '-', '女性粉丝占比': audience?.femaleRatio !== undefined ? `${Math.round(audience.femaleRatio * 100)}%` : '-', '粉丝性别判断': audience?.verdict || '-', '按需评估摘要': evaluation.notes?.join('；') || '-', }; return values[header] || '-'; } function formatOptionalNumber(value?: number): string { return value === undefined || value === null || Number.isNaN(value) ? '-' : String(value); } function mapPlatformDisplay(platform: string): string { const map: Record = { xiaohongshu: '小红书', douyin: '抖音', bilibili: 'B站', weibo: '微博', weixin: '微信/公众号', }; return map[platform] || platform; } function formatFans(count: number): string { if (count === 0) return '-'; if (count >= 10000) return `${(count / 10000).toFixed(1)}万`; return String(count); } function formatWan(count: number): string { if (!count) return '-'; const value = count >= 10000 ? count / 10000 : count; return Number.isInteger(value) ? String(value) : value.toFixed(2).replace(/0+$/, '').replace(/\.$/, ''); }