sn-da-excel-workflow

Excel Data Analysis Workflow

End-to-end workflow for structured Excel analysis. Each step maps to a capability sub-skill that can be loaded for detailed patterns.

Workflow

Step 1 — Count rows across all sheets (lightweight, no full load)

Count rows per sheet without loading data into memory. Use openpyxl read_only mode — this works for any file size.

import openpyxl, gc

wb = openpyxl.load_workbook(file_path, read_only=True, data_only=True) total_rows = 0 sheet_info = {} for name in wb.sheetnames: ws = wb[name] row_count = sum(1 for _ in ws.iter_rows(min_row=2, values_only=True)) total_rows += row_count sheet_info[name] = row_count print(f"Sheet '{name}': {row_count} rows") wb.close() print(f"Total rows={total_rows}")

⚠️ **Do NOT use `pd.read_excel()` to count rows** — it loads all data into
memory, which will OOM on large files.

→ capability: `excel-reading/multi-sheet-reading`

### Step 2 — Large file gate (CRITICAL — choose strategy by row count)

| total_rows | Strategy | What to do |
|-----------|----------|------------|
| < 10k | Direct read | `df = pd.read_excel(file_path, sheet_name=target_sheet)` |
| 10k – 100k | Parquet cache | `pd.read_excel()` once → `df.to_parquet()` → all later reads from Parquet |
| **>= 100k** | **STOP. Load `sn-da-large-file-analysis` skill** | Read its SKILL.md, then follow its streaming read + Parquet pattern. **Do NOT use `pd.read_excel()` at all** — it will OOM or timeout on 100k+ rows. |

**For >= 100k rows:**

read_file(path="<skills_base>/sn-da-large-file-analysis/SKILL.md")

Then use `stream_excel_to_parquet()` from that skill — it reads via
openpyxl `iter_rows` in 50k-row chunks with constant memory.

**For 10k – 100k rows (only):**
```python
import pandas as pd
parquet_path = "/tmp/_auto_parquet.parquet"
df = pd.read_excel(file_path, sheet_name=target_sheet)
df.to_parquet(parquet_path, engine="pyarrow")
del df; gc.collect()
df = pd.read_parquet(parquet_path)

→ capability: excel-reading/large-excel-reading

Step 3 — Inspect schema & data types

Preview target sheet structure. For large files (>= 10k rows), only read a small sample — never full load just to inspect.

# For any file size — read only first N rows for inspection
df_head = pd.read_excel(file_path, sheet_name=target_sheet, nrows=20)
print(f"Columns: {df_head.columns.tolist()}")
print(f"Dtypes:\n{df_head.dtypes}")
print(df_head.head(10))

→ capability: excel-reading/range-reading

Step 4 — Data cleaning

Handle missing values, normalize text, clean invalid characters.

# Missing values
null_count = df[col].isna().sum()

# Text cleaning: keep only Chinese characters
import re
def clean_text(val):
    if pd.isna(val): return val
    return "".join(re.findall(r"[\u4e00-\u9fff]", str(val))) or ""

df[col] = df[col].apply(clean_text)

⚠️ Large file rule: When total_rows >= 100k, do NOT use df.apply(lambda...). Use vectorized operations or np.where() instead. See sn-da-large-file-analysis skill for the vectorized cheat sheet.

→ capabilities:

• excel-data-cleaning/missing-value-handling
• excel-data-cleaning/invalid-data-cleaning
• excel-data-cleaning/text-normalization

Step 5 — Filter & extract

Apply condition or category filters, aggregate results.

# Condition filter
mask = df[col].astype(str).str.strip() == target_value
filtered = df[mask]

# Category extraction (for headerless layouts)
df_raw = pd.read_excel(file_path, sheet_name=sheet, header=None)
# Walk rows to find category markers, collect items until next marker

→ capabilities:

• excel-data-filtering/condition-filtering
• excel-data-filtering/category-filtering
• excel-data-filtering/threshold-filtering

Step 6 — Export results

Save filtered/cleaned data as Excel or CSV. Provide download link.

output_path = "/mnt/data/result.xlsx"
result_df.to_excel(output_path, index=False)
print(f"[Download](sandbox:{output_path})")

→ capabilities:

• excel-result-export/single-sheet-export
• excel-result-export/formatted-export

Key rules

• Always count rows first — gate large-file logic on the 10k threshold.
• >= 100k rows → MUST load sn-da-large-file-analysis skill — do not attempt to handle with pd.read_excel().
• Column names may contain spaces (e.g. '是否通 过') — use exact string indexing.
• Headerless sheets — use header=None and positional indexing.
• Prohibited on large files (>= 100k rows):
  • pd.read_excel() for full load (use streaming read → Parquet)
  • df.apply(lambda...) or df.iterrows() (use vectorized ops or itertuples())
  • fc-list, find ... fonts, subprocess to search fonts, or pip install (use fixed font paths below)
  • Printing all unique values or full DataFrames (use .head(), .value_counts().head())

CJK Font Setup (mandatory for charts)

When generating charts with matplotlib, copy this block as-is. Do NOT search for fonts.

import os
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm

_FONT_PATHS = [
    '/mnt/afs_agents/SimHei.ttf',
    '/mnt/afs_agents/mnt/data/SimHei.ttf',
    os.path.expanduser('~/.fonts/SimHei.ttf'),
    '/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc',
    '/usr/share/fonts/SimHei.ttf',
]
for _p in _FONT_PATHS:
    if os.path.exists(_p):
        fm.fontManager.addfont(_p)
        matplotlib.rcParams['font.family'] = fm.FontProperties(fname=_p).get_name()
        break
matplotlib.rcParams['axes.unicode_minus'] = False

How to load sub-skills

Each workflow step references one or more capability sub-skills. When you need the detailed code pattern for a step, load the sub-skill on demand:

read_file(path="<base_path>/<category>/<sub-skill-name>/SKILL.md")

Rules:

• Only load the sub-skill(s) needed for your current step.
• Do NOT load all sub-skills at once — it wastes context.
• The top-level workflow (this file) is your guide; sub-skills provide detailed implementation patterns.

Available capability sub-skills

Base path: <skills_root>/sn-da-excel-workflow/capability/{category}/{sub-skill}/SKILL.md

excel-reading — Reading and parsing

Sub-skill	Features
single-sheet-reading	Read a single worksheet, supporting merged cell handling, cross-analysis, and multi-dimensional visualization
multi-sheet-reading	Read multiple worksheets, dynamically assess data volume to enable Parquet optimization, supporting regex cleaning, categorical summarization, and linear fitting
range-reading	Extract data from specific regions, dynamically selecting processing strategies based on data volume
large-excel-reading	Large Excel file processing, supporting Parquet conversion for speedup, generating formatted reports with conditional highlighting
multi-file-reading	Multi-file reading and statistics, supporting large file Parquet conversion and visualization reports
specific-sheet-reading	Cross-sheet statistics for specific fields, data cleaning, and cross-analysis, generating summary reports with download links
structured-header-reading	Dynamically identify target columns for statistics, regex-clean text fields to extract Chinese characters

excel-data-cleaning — Data cleaning

Sub-skill	Features
missing-value-handling	Multi-sheet intelligent cleaning, cross-table verification, and visualization analysis
duplicate-removal	Multi-sheet deduplication statistics, generating summary and detail reports
invalid-data-cleaning	Regex-clean specified text columns (e.g., retain Chinese characters), automatic Parquet acceleration for large files
text-normalization	Text standardization cleaning (removing abnormal prefixes, extracting pure Chinese characters, etc.)
numeric-format-normalization	Numeric format standardization, supporting verification of key metric totals and export of result files
outlier-detection	IQR outlier detection, combining skewness/kurtosis to analyze data distribution, suitable for non-normal data preprocessing

excel-data-filtering — Data filtering

Sub-skill	Features
condition-filtering	Dynamically select processing strategies for conditional filtering based on data scale
category-filtering	Custom categorical statistics and cross-analysis, supporting comprehensive scoring and grading based on text length/term density/regex matching, etc.
range-filtering	Filter and export based on multi-dimensional numeric conditions, supporting automatic performance optimization for large-scale data
threshold-filtering	Numeric column cleaning and conditional filtering, using openpyxl to apply style marking to cells that meet the conditions

excel-data-analysis — Data analysis

Sub-skill	Features
comparison-analysis	Comparative analysis of two categories of categorical data, calculating quantity differences and proportional relationships and generating visualizations
group-by-analysis	Multi-sheet data cleaning and grouped aggregation analysis, generating styled statistics tables and charts
kpi-metric-analysis	Extract key metrics for unit consistency verification and ranking analysis
pivot-table-analysis	Cross-tabulation and heatmaps for multi-dimensional proportion analysis, suitable for award distribution/performance evaluation/market share
time-series-analysis	Time series trend analysis, percentage cleaning, performance grading modeling and forecasting, generating high-resolution visualization reports
trend-analysis	Multi-dimensional grading evaluation and trend forecasting, differentiated growth rate calculation, suitable for performance evaluation/goal setting

excel-data-statistics — Statistical Computation

Sub-skill	Features
basic-statistics	Basic statistics, supports conditional filtering to calculate means, extracts data from specified row ranges for deduplication and summation
category-statistics	Counts and proportions for each category, generates combined visualization reports such as bar charts/pie charts
grouped-statistics	Multi-sheet data merging and forward filling, grouped statistics
percentage-calculation	Row-by-row scanning or column matching to extract key metrics and calculate proportions/means, outputs structured reports and charts

excel-data-visualization — Data Visualization

Sub-skill	Features
bar-chart-visualization	Handles merged cells, cross-grouped statistics, generates beautified bar charts supporting Chinese and English fonts
histogram-visualization	Numeric distribution analysis and outlier detection, supports regex extraction of error terms, generates box plots and histograms
line-chart-visualization	Feature cleaning and clustering analysis, generates multi-dimensional charts for trend comparison/distribution characteristics/parameter sensitivity
pie-chart-visualization	Categorical summary statistics, automatically identifies key fields to generate beautified pie charts containing proportions/values
scatter-plot-visualization	Multi-dimensional statistical analysis and scatter plot visualization
stacked-chart-visualization	Percentage string data processing, fills in missing dimensions, generates stacked bar charts to show composition change trends

excel-cell-coloring — Cell Coloring

Sub-skill	Features
category-coloring	Extracts target metrics to calculate the maximum value, highlights specific rows
duplicate-value-coloring	Compares specific coefficients across multiple sheets and color-marks outliers
outlier-coloring	Identifies out-of-range values and erroneous cells and highlights them
threshold-cell-coloring	Calculates time series means, uses openpyxl to output reports with conditional formatting (e.g., green for below mean)
top-value-coloring	Dynamically selects strategies based on data scale, multi-sheet merging, statistical filtering, automated style highlighting of key metrics

excel-conditional-formatting — Conditional Formatting

Sub-skill	Features
data-bar-formatting	Extracts and cleans numeric values from string columns with units, generates histograms/pie charts/bar charts/cumulative distribution charts

excel-result-export — Result Export

Sub-skill	Features
single-sheet-export	Multi-sheet data exploration and conditional filtering export, renames fields and generates Excel with download links
formatted-export	Filters records by condition and exports Excel with entire rows highlighted in red
chart-embedded-export	Categorical distribution cleaning and statistics, generates multi-dimensional cross-analysis and high-resolution embedded chart reports
report-generation-export	Extracts multiple types of data from Excel, generates comprehensive analysis reports containing visualization charts and download links

excel-table-styling — Table Styling

Sub-skill	Features
table-theme-styling	Accelerated reading of large files via Parquet, conditional filtering/categorical summarization and result export