Excel Multi Sheet Dynamic Analysis
用于分析包含多个Sheet的Excel文件，动态判断数据量级以决定是否转换为Parquet进行大文件处理，并支持跨Sheet的特定字段统计、数据清洗、交叉分析与可视化，最终生成...
Install
npx skills add OpenSenseNova/SenseNova-Skills --skill excel-multi-sheet-dynamic-analysis -g -a claude-code -y- Repository
- OpenSenseNova/SenseNova-Skills6k
- Category
- Engineering
- Updated
- 6d ago
- License
- MIT
- Context cost
- Lightabout 1,278 tokens
- Runs scripts
- No, instructions only
What it does
用于分析包含多个Sheet的Excel文件，动态判断数据量级以决定是否转换为Parquet进行大文件处理，并支持跨Sheet的特定字段统计、数据清洗、交叉分析与可视化，最终生成带下载链接的汇总报告。
Step1 遍历所有sheet，灵活定位目标列并统计特定类型字段的数量。
target_col_keyword = 'type' # 占位示例
target_val_keyword = 'varchar' # 占位示例
total_target_count = 0
target_details = []
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
raw_data = list(ws.iter_rows(values_only=True))
# 实用技巧：灵活策略定位目标列，通过扫描前几行数据内容定位表头行
header_row_idx = None
for i, row in enumerate(raw_data):
if any(cell and isinstance(cell, str) and target_col_keyword in str(cell).lower() for cell in row):
header_row_idx = i
break
if header_row_idx is not None:
header = raw_data[header_row_idx]
type_col_idx = next((j for j, col in enumerate(header) if col and target_col_keyword in str(col).lower()), None)
if type_col_idx is not None:
target_count = 0
target_fields = []
for i in range(header_row_idx + 1, len(raw_data)):
row = raw_data[i]
if len(row) <= type_col_idx:
continue
cell_val = row[type_col_idx]
if cell_val and isinstance(cell_val, str) and target_val_keyword in cell_val.lower():
target_count += 1
field_name = row[0] if len(row) > 0 else None
if field_name and field_name not in target_fields:
target_fields.append(field_name)
total_target_count += target_count
target_details.append({
'sheet': sheet_name,
'target_count': target_count,
'target_fields': target_fields[:10]
})
Step2 对特定Sheet进行数据清洗、分类映射、多维度评分及交叉聚合分析。
import pandas as pd
import re
# 读取特定Sheet并处理列名
sheet1_df = pd.read_excel(file_path, sheet_name='Sheet1', engine='openpyxl', header=None, skiprows=1)
sheet1_df.columns = ['id_col', 'name_col', 'year_col', 'value_col', 'group_col'] # 占位示例
