LeoYeAI/agent-master-skills / skills/amazon-sorftime-research-category-skill
category-selection
Verified亚马逊品类自动化选品分析技能。通过五维评分模型对亚马逊品类进行深度市场调研,生成Markdown分析报告。当用户使用 /category-selection 命令或提出'分析XX品类'、'XX品类市场调研'、'XX品类选品'等需求时触发此技能。支持配置分析数量,默认Top20。
scripts/generate_excel_report.py
View on GitHub#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
品类选品 Excel 报告生成脚本
功能: 根据收集的数据生成包含12个sheets的Excel报告
"""
import json
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from datetime import datetime
import os
# 样式定义
HEADER_FILL = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
TITLE_FONT = Font(bold=True, size=14)
DATA_FONT = Font(size=10)
CENTER_ALIGN = Alignment(horizontal="center", vertical="center")
LEFT_ALIGN = Alignment(horizontal="left", vertical="center")
BORDER = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
class CategoryReportGenerator:
def __init__(self, category_data, output_path):
"""
初始化报告生成器
Args:
category_data: dict, 包含所有收集的数据
output_path: str, 输出文件路径
"""
self.data = category_data
self.output_path = output_path
self.wb = openpyxl.Workbook()
def generate(self):
"""生成完整报告"""
print("开始生成 Excel 报告...")
# 生成12个sheets
self._create_overview_sheet() # Sheet 1: 市场概览
self._create_top100_sheet() # Sheet 2: Top100产品
self._create_brand_sheet() # Sheet 3: 品牌分析
self._create_price_dist_sheet() # Sheet 4: 价格分布
self._create_rating_dist_sheet() # Sheet 5: 评分分布
self._create_seller_sheet() # Sheet 6: 卖家来源
self._create_new_product_sheet() # Sheet 7: 新产品分析
self._create_trend_sales_sheet() # Sheet 8: 销量趋势
self._create_trend_price_sheet() # Sheet 9: 价格趋势
self._create_trend_score_sheet() # Sheet 10: 评分趋势
self._create_trend_brand_sheet() # Sheet 11: 品牌数趋势
self._create_supply_chain_sheet() # Sheet 12: 供应链
# 保存文件
self.wb.save(self.output_path)
print(f"Excel 报告已生成: {self.output_path}")
def _create_overview_sheet(self):
"""Sheet 1: 市场概览 - 五维评分模型 + KPI指标"""
ws = self.wb.active
ws.title = "市场概览"
row = 1
# 报告标题
ws.cell(row, 1, f"{self.data.get('category_name', '')} 品类市场调研报告")
ws.cell(row, 1).font = TITLE_FONT
row += 2
# 五维评分模型
ws.cell(row, 1, "五维评分模型")
ws.cell(row, 1).font = HEADER_FONT
ws.cell(row, 1).fill = HEADER_FILL
row += 1
scores = self.data.get('five_dimension_score', {})
score_data = [
["评分维度", "得分", "满分", "占比"],
["市场规模", scores.get("market_size", 0), 20, f"{scores.get('market_size', 0)/20*100:.0f}%"],
["增长潜力", scores.get("growth_potential", 0), 25, f"{scores.get('growth_potential', 0)/25*100:.0f}%"],
["竞争烈度", scores.get("competition", 0), 20, f"{scores.get('competition', 0)/20*100:.0f}%"],
["进入壁垒", scores.get("entry_barrier", 0), 20, f"{scores.get('entry_barrier', 0)/20*100:.0f}%"],
["利润空间", scores.get("profit_margin", 0), 15, f"{scores.get('profit_margin', 0)/15*100:.0f}%"],
["", "", "", ""],
["总分", scores.get("total", 0), 100, "100%"],
]
for r in score_data:
for c, val in enumerate(r, 1):
cell = ws.cell(row, c, val)
cell.font = DATA_FONT
cell.border = BORDER
if c == 1:
cell.alignment = LEFT_ALIGN
else:
cell.alignment = CENTER_ALIGN
row += 1
row += 2
# 评级和建议
total_score = scores.get("total", 0)
rating = self._get_rating(total_score)
ws.cell(row, 1, f"评级: {rating}")
ws.cell(row, 1).font = Font(bold=True, size=12, color="FF0000" if total_score < 40 else "008000")
row += 1
ws.cell(row, 1, f"建议: {self._get_recommendation(total_score)}")
row += 3
# KPI指标
ws.cell(row, 1, "关键指标 (KPI)")
ws.cell(row, 1).font = HEADER_FONT
ws.cell(row, 1).fill = HEADER_FILL
row += 1
kpi = self.data.get('kpi', {})
kpi_data = [
["指标", "数值"],
["产品总数", kpi.get("total_products", 0)],
["平均价格", f"${kpi.get("avg_price", 0):.2f}"],
["平均月销量", f"{kpi.get("avg_sales", 0):.0f}"],
["平均评分", f"{kpi.get("avg_rating", 0):.2f}"],
["总月销额", f"${kpi.get("total_sales", 0):,.0f}"],
["CR3集中度", f"{kpi.get("cr3", 0):.2f}%"],
["HHI指数", f"{kpi.get("hhi", 0):.2f}"],
]
for r in kpi_data:
for c, val in enumerate(r, 1):
cell = ws.cell(row, c, val)
cell.font = DATA_FONT
cell.border = BORDER
row += 1
self._adjust_column_width(ws)
def _create_top100_sheet(self):
"""Sheet 2: Top100产品"""
ws = self.wb.create_sheet(title="Top100产品")
# 表头
headers = ["排名", "ASIN", "产品标题", "品牌", "价格", "评分", "评论数",
"月销量", "月销额($)", "市场份额", "卖家", "卖家来源"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
# 数据行
products = self.data.get('top100_products', [])
for idx, p in enumerate(products, 2):
ws.cell(idx, 1, idx - 1) # 排名
ws.cell(idx, 2, p.get("asin", ""))
ws.cell(idx, 3, p.get("title", ""))
ws.cell(idx, 4, p.get("brand", ""))
ws.cell(idx, 5, p.get("price", 0))
ws.cell(idx, 6, p.get("rating", 0))
ws.cell(idx, 7, p.get("review_count", 0))
ws.cell(idx, 8, p.get("monthly_sales", 0))
ws.cell(idx, 9, p.get("monthly_revenue", 0))
ws.cell(idx, 10, f"{p.get("market_share", 0):.2f}%")
ws.cell(idx, 11, p.get("seller", ""))
ws.cell(idx, 12, p.get("seller_source", ""))
# 应用样式
for c in range(1, 13):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
if c in [1]: # 排名列居中
cell.alignment = CENTER_ALIGN
elif c in [3]: # 标题左对齐
cell.alignment = LEFT_ALIGN
self._adjust_column_width(ws)
def _create_brand_sheet(self):
"""Sheet 3: 品牌分析"""
ws = self.wb.create_sheet(title="品牌分析")
# 表头
headers = ["排名", "品牌", "产品数", "月销量", "月销额($)", "市场份额", "平均评分"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
# 数据行
brands = self.data.get('brand_analysis', [])
for idx, b in enumerate(brands, 2):
ws.cell(idx, 1, idx - 1)
ws.cell(idx, 2, b.get("brand", ""))
ws.cell(idx, 3, b.get("product_count", 0))
ws.cell(idx, 4, b.get("monthly_sales", 0))
ws.cell(idx, 5, b.get("monthly_revenue", 0))
ws.cell(idx, 6, f"{b.get("market_share", 0):.2f}%")
ws.cell(idx, 7, b.get("avg_rating", 0))
for c in range(1, 8):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_price_dist_sheet(self):
"""Sheet 4: 价格分布"""
ws = self.wb.create_sheet(title="价格分布")
headers = ["价格区间", "产品数", "占比", "销量", "销额", "平均评分"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
price_dist = self.data.get('price_distribution', [])
for idx, d in enumerate(price_dist, 2):
ws.cell(idx, 1, d.get("range", ""))
ws.cell(idx, 2, d.get("count", 0))
ws.cell(idx, 3, f"{d.get("percentage", 0):.1f}%")
ws.cell(idx, 4, d.get("sales", 0))
ws.cell(idx, 5, d.get("revenue", 0))
ws.cell(idx, 6, d.get("avg_rating", 0))
for c in range(1, 7):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_rating_dist_sheet(self):
"""Sheet 5: 评分分布"""
ws = self.wb.create_sheet(title="评分分布")
headers = ["评分区间", "产品数", "占比", "销量占比"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
rating_dist = self.data.get('rating_distribution', [])
for idx, d in enumerate(rating_dist, 2):
ws.cell(idx, 1, d.get("range", ""))
ws.cell(idx, 2, d.get("count", 0))
ws.cell(idx, 3, f"{d.get("percentage", 0):.1f}%")
ws.cell(idx, 4, f"{d.get("sales_percentage", 0):.1f}%")
for c in range(1, 5):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_seller_sheet(self):
"""Sheet 6: 卖家来源"""
ws = self.wb.create_sheet(title="卖家来源")
headers = ["来源地", "卖家数", "产品数", "占比", "销额"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
seller_dist = self.data.get('seller_distribution', [])
for idx, d in enumerate(seller_dist, 2):
ws.cell(idx, 1, d.get("source", ""))
ws.cell(idx, 2, d.get("seller_count", 0))
ws.cell(idx, 3, d.get("product_count", 0))
ws.cell(idx, 4, f"{d.get("percentage", 0):.1f}%")
ws.cell(idx, 5, d.get("revenue", 0))
for c in range(1, 6):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_new_product_sheet(self):
"""Sheet 7: 新产品分析"""
ws = self.wb.create_sheet(title="新产品分析")
headers = ["ASIN", "产品标题", "品牌", "价格", "评分", "评论数", "月销量", "上架天数"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
new_products = self.data.get('new_products', [])
for idx, p in enumerate(new_products, 2):
ws.cell(idx, 1, p.get("asin", ""))
ws.cell(idx, 2, p.get("title", ""))
ws.cell(idx, 3, p.get("brand", ""))
ws.cell(idx, 4, p.get("price", 0))
ws.cell(idx, 5, p.get("rating", 0))
ws.cell(idx, 6, p.get("review_count", 0))
ws.cell(idx, 7, p.get("monthly_sales", 0))
ws.cell(idx, 8, p.get("days_online", 0))
for c in range(1, 9):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
if c in [1, 8]: # ASIN和上架天数居中
cell.alignment = CENTER_ALIGN
elif c in [2]: # 标题左对齐
cell.alignment = LEFT_ALIGN
self._adjust_column_width(ws)
def _create_trend_sales_sheet(self):
"""Sheet 8: 销量趋势"""
ws = self.wb.create_sheet(title="趋势-销量")
headers = ["日期", "月销量", "环比", "同比"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
trend = self.data.get('sales_trend', [])
for idx, t in enumerate(trend, 2):
ws.cell(idx, 1, t.get("date", ""))
ws.cell(idx, 2, t.get("sales", 0))
ws.cell(idx, 3, f"{t.get("mom", 0):.1f}%")
ws.cell(idx, 4, f"{t.get("yoy", 0):.1f}%")
for c in range(1, 5):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_trend_price_sheet(self):
"""Sheet 9: 价格趋势"""
ws = self.wb.create_sheet(title="趋势-价格")
headers = ["日期", "平均价格", "环比"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
trend = self.data.get('price_trend', [])
for idx, t in enumerate(trend, 2):
ws.cell(idx, 1, t.get("date", ""))
ws.cell(idx, 2, f"${t.get("avg_price", 0):.2f}")
ws.cell(idx, 3, f"{t.get("mom", 0):.1f}%")
for c in range(1, 4):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_trend_score_sheet(self):
"""Sheet 10: 评分趋势"""
ws = self.wb.create_sheet(title="趋势-评分")
headers = ["日期", "平均评分", "环比"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
trend = self.data.get('rating_trend', [])
for idx, t in enumerate(trend, 2):
ws.cell(idx, 1, t.get("date", ""))
ws.cell(idx, 2, f"{t.get("avg_score", 0):.2f}")
ws.cell(idx, 3, f"{t.get("mom", 0):.1f}%")
for c in range(1, 4):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_trend_brand_sheet(self):
"""Sheet 11: 品牌数趋势"""
ws = self.wb.create_sheet(title="趋势-品牌数")
headers = ["日期", "品牌数", "新品牌数", "退出品牌数"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
trend = self.data.get('brand_count_trend', [])
for idx, t in enumerate(trend, 2):
ws.cell(idx, 1, t.get("date", ""))
ws.cell(idx, 2, t.get("brand_count", 0))
ws.cell(idx, 3, t.get("new_brands", 0))
ws.cell(idx, 4, t.get("exited_brands", 0))
for c in range(1, 5):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _create_supply_chain_sheet(self):
"""Sheet 12: 供应链-1688"""
ws = self.wb.create_sheet(title="供应链-1688")
headers = ["产品名称", "1688价格", "Amazon均价", "预估毛利率", "1688链接"]
row = 1
for c, h in enumerate(headers, 1):
cell = ws.cell(row, c, h)
cell.font = HEADER_FONT
cell.fill = HEADER_FILL
cell.alignment = CENTER_ALIGN
cell.border = BORDER
supply = self.data.get('supply_chain_1688', [])
for idx, s in enumerate(supply, 2):
ws.cell(idx, 1, s.get("product_name", ""))
ws.cell(idx, 2, f"¥{s.get('price_1688', 0):.2f}")
ws.cell(idx, 3, f"${s.get('price_amazon', 0):.2f}")
ws.cell(idx, 4, f"{s.get('margin', 0):.1f}%")
ws.cell(idx, 5, s.get("link", ""))
for c in range(1, 6):
cell = ws.cell(idx, c)
cell.font = DATA_FONT
cell.border = BORDER
if c in [5]: # 链接列左对齐
cell.alignment = LEFT_ALIGN
else:
cell.alignment = CENTER_ALIGN
self._adjust_column_width(ws)
def _get_rating(self, score):
"""根据分数获取评级"""
if score >= 80:
return "优秀"
elif score >= 60:
return "良好"
elif score >= 40:
return "一般"
else:
return "较差"
def _get_recommendation(self, score):
"""根据分数获取建议"""
if score >= 80:
return "强烈推荐进入"
elif score >= 60:
return "可以考虑进入"
elif score >= 40:
return "谨慎进入"
else:
return "不建议进入"
def _adjust_column_width(self, ws):
"""自动调整列宽"""
column_widths = {
'A': 8, 'B': 15, 'C': 40, 'D': 15, 'E': 10, 'F': 10,
'G': 12, 'H': 12, 'I': 15, 'J': 12, 'K': 15, 'L': 12
}
for col, width in column_widths.items():
ws.column_dimensions[col].width = width
def main():
"""示例用法"""
# 示例数据结构
category_data = {
"category_name": "Sofas",
"five_dimension_score": {
"market_size": 18,
"growth_potential": 22,
"competition": 15,
"entry_barrier": 16,
"profit_margin": 11,
"total": 82
},
"kpi": {
"total_products": 100,
"avg_price": 125.50,
"avg_sales": 1245,
"avg_rating": 4.2,
"total_sales": 12500000,
"cr3": 12.39,
"hhi": 167.71
},
"top100_products": [],
"brand_analysis": [],
"price_distribution": [],
"rating_distribution": [],
"seller_distribution": [],
"new_products": [],
"sales_trend": [],
"price_trend": [],
"rating_trend": [],
"brand_count_trend": [],
"supply_chain_1688": []
}
generator = CategoryReportGenerator(
category_data=category_data,
output_path="category-reports/2026/03/category_sofas_US_20260303.xlsx"
)
generator.generate()
if __name__ == "__main__":
main()
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |