OpenSenseNova/SenseNova-Skills / skills/sn-da-excel-workflow/capability/excel-result-export/report-generation-export
excel-data-analysis-and-report-generation
Verified从Excel提取多类型数据,并生成包含可视化图表与下载链接的综合分析报告。
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.9 KB |
SKILL.md
View on GitHub---
name: excel-data-analysis-and-report-generation
description: "从Excel提取多类型数据,并生成包含可视化图表与下载链接的综合分析报告。"
---
## Skill Steps
> This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.
Step1 基于指定列提取有效代码或进行分类映射,生成包含占比与总计行的统计表,并支持交叉分析。
```python
# 分类映射函数骨架
def categorize_item(item_name):
category_a_keywords = ['keyword1', 'keyword2'] # 占位示例
if pd.isna(item_name):
return '未知'
if any(kw in str(item_name) for kw in category_a_keywords):
return '类别A'
return '其他'
target_col = '项目名称' # 替换为实际列名
group_col = '所属区域' # 替换为实际分组列名
if target_col in combined_df.columns:
combined_df['分类'] = combined_df[target_col].apply(categorize_item)
# value_counts + 占比 + 总计行
category_counts = combined_df['分类'].value_counts().reset_index()
category_counts.columns = ['类别', '数量']
total = category_counts['数量'].sum()
category_counts['占比'] = (category_counts['数量'] / total).apply(lambda x: f'{x:.2%}')
total_row = pd.DataFrame([{'类别': '总计', '数量': total, '占比': '100.00%'}])
category_counts = pd.concat([category_counts, total_row], ignore_index=True)
# 交叉分析 crosstab
if group_col in combined_df.columns:
cross_tb = pd.crosstab(combined_df[group_col], combined_df['分类'], margins=True, margins_name='总计')
print("交叉分析结果:\n", cross_tb)
```
Step2 识别目标值超过限值的行,基于关键字定位并反向搜索限值以确保数据关联。
```python
import re
exceed_rows = []
df_target = combined_df.copy()
for i, row in df_target.iterrows():
if '共计' in str(row.iloc[0]):
try:
target_val = float(row.iloc[8]) # 目标值所在列索引
except (ValueError, TypeError):
continue
limit_val = None
structure_name = "未知结构"
# 反向搜索限值
for j in range(i-1, max(0, i-15), -1):
check_row = df_target.iloc[j, :]
check_str = ' '.join([str(x) for x in check_row.values if pd.notna(x)])
if '限值' in check_str:
# 数据清洗正则表达式
match = re.search(r'限值([\d.]+)', check_str)
if match:
limit_val = float(match.group(1))
for k in range(j-1, max(0, j-5), -1):
name_row = df_target.iloc[k, 0]
if pd.notna(name_row) and '关键字' in str(name_row):
structure_name = str(name_row)
break
break
# 多维度评分/分级算法结构
if limit_val is not None and target_val > limit_val:
severity = '高' if (target_val - limit_val) > 10 else '中'
exceed_rows.append({
'row_index': i,
'structure_name': structure_name,
'target_val': target_val,
'limit': limit_val,
'exceed_value': target_val - limit_val,
'severity': severity
})
```
Step3 生成高分辨率可视化图表展示分类占比,保存统计结果并生成沙箱下载链接。
```python
import matplotlib.pyplot as plt
import matplotlib
# 中英文字体配置
matplotlib.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans']
matplotlib.rcParams['axes.unicode_minus'] = False
# 准备图表数据 (排除总计行)
plot_data = category_counts[category_counts['类别'] != '总计']
categories = plot_data['类别'].tolist()
counts = plot_data['数量'].tolist()
# 图表美化(dpi、颜色方案、标签位置)
fig, ax = plt.subplots(figsize=(10, 8))
colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#F9A826']
explode = [0.05] * len(categories)
wedges, texts, autotexts = ax.pie(
counts,
labels=categories,
autopct='%1.1f%%',
startangle=90,
colors=colors[:len(categories)],
explode=explode,
shadow=True,
textprops={'fontsize': 12}
)
ax.set_title('各类别数量占比分析', fontsize=16, fontweight='bold', pad=20)
ax.legend(wedges, categories, title="类别", loc="center left", bbox_to_anchor=(1, 0, 0.5, 1))
# 保存图表
chart_path = os.path.join(output_dir, 'category_analysis.png')
plt.savefig(chart_path, dpi=150, bbox_inches='tight')
# 保存统计结果并生成下载链接
output_path = os.path.join(output_dir, 'analysis_result.xlsx')
category_counts.to_excel(output_path, index=False)
print(f"统计结果已保存至: {output_path}")
print(f"下载链接: [下载统计结果](sandbox:{output_path})")
print(f"图表下载链接: [下载图表](sandbox:{chart_path})")
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| canvas-designanthropics/skillsCreate beautiful visual art in .png and .pdf documents using design philosophy. You should use this skill when the user asks to create a poster, piece of art, design, or other static piece. Create original visual designs, never copying existing artists' work to avoid copyright violations. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| docxanthropics/skillsUse this skill whenever the user wants to create, read, edit, or manipulate Word documents (.docx files) or Word templates (.dotx files). Triggers include: any mention of 'Word doc', 'word document', '.docx', '.dotx', or requests to produce professional documents with formatting like tables of contents, headings, page numbers, or letterheads. Also use when extracting or reorganizing content from .docx or .dotx files, inserting or replacing images in documents, performing find-and-replace in Word files, working with tracked changes or comments, or converting content into a polished Word document. If the user asks for a 'report', 'memo', 'letter', 'template', or similar deliverable as a Word or .docx file, use this skill. Do NOT use for PDFs, spreadsheets, Google Docs, or general coding tasks unrelated to document generation. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| pdfanthropics/skillsUse this skill whenever the user wants to do anything with PDF files. This includes reading or extracting text/tables from PDFs, combining or merging multiple PDFs into one, splitting PDFs apart, rotating pages, adding watermarks, creating new PDFs, filling PDF forms, encrypting/decrypting PDFs, extracting images, and OCR on scanned PDFs to make them searchable. If the user mentions a .pdf file or asks to produce one, use this skill. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| pptxanthropics/skillsUse this skill any time a .pptx or .potx file is involved in any way — as input, output, or both. This includes: creating slide decks, pitch decks, or presentations; reading, parsing, or extracting text from any .pptx or .potx file (even if the extracted content will be used elsewhere, like in an email or summary); editing, modifying, or updating existing presentations; combining or splitting slide files; working with templates (.potx), layouts, speaker notes, or comments. Trigger whenever the user mentions "deck," "slides," "presentation," or references a .pptx or .potx filename, regardless of what they plan to do with the content afterward. If a .pptx or .potx file needs to be opened, created, or touched, use this skill. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |