xiaoyi-xlsx

name

xiaoyi-xlsx

description

Create, edit, or fix spreadsheet files (.xlsx/.xlsm/.csv/.tsv). Trigger whenever the task produces or modifies a spreadsheet — even if the user never says 'Excel'. Covers: data analysis, sorting, filtering, pivot summaries, formulas (VLOOKUP/INDEX+MATCH/SUMIFS), charts, cross-sheet references, conditional formatting, financial models, budgets, trackers, and invoices. Implicit triggers include: '帮我做个表', '整理/分析/汇总/对比数据', 'organize this data', 'build a budget', 'chart this'. More broadly, if the user asks to analyze, summarize, compare, track, or visualize data and a spreadsheet is the natural output, use this skill. Always trigger for uploaded .xlsx/.csv/.tsv files. Keywords: 表格/数据分析/汇总/对比/图表/跨表/财务/dashboard/chart. Do NOT trigger when the primary deliverable is a Word doc, HTML report, standalone script, or database pipeline.

Excel File Creation: Python + openpyxl/pandas

Path Convention: All paths in this document are relative to the skill root (the directory containing this SKILL.md). Generated scripts must derive the skill root from the <location> tag and store it in SKILL_DIR:

import sys, os
SKILL_DIR = os.path.dirname('<this skill's location path>')  # e.g. /mnt/skills/user/xiaoyi-xlsx

Then reference sub-paths as os.path.join(SKILL_DIR, 'scripts'), os.path.join(SKILL_DIR, 'scripts/recalc.py'), etc.

✅ REQUIRED Technology Stack:

• Runtime: Python 3
• Primary Library: openpyxl (Excel creation, styling, formulas)
• Data Processing: pandas (manipulation, then export via openpyxl)
• Execution: Write code to .py file, run with python3 <filename>.py

---

1. Sheet-by-Sheet Iterative Workflow

When the workbook involves formulas, references, or calculations, follow this strict per-sheet pipeline. Never create all sheets first then check — errors cascade.

Per-Sheet Pipeline

For each sheet, execute in order:

1. PLAN — Design structure, data layout, formulas, cross-sheet references.
2. CREATE — Write data, formulas, and styles. Preserve original formatting.
3. SAVE — wb.save() immediately.
4. RECALC — python3 {SKILL_DIR}/scripts/recalc.py <file>.xlsx (where SKILL_DIR is the skill root). Check returned JSON:
   • status: success → proceed to CHECK
   • status: errors_found → fix locations in error_summary, re-run (max 3 retries, then report failure)
5. CHECK — No #VALUE!, #DIV/0!, #REF!, #NAME?, #N/A, no suspicious zeros.
6. NEXT — Only proceed when current sheet is error-free.

Workbook-Level Sequence

 ┌─ create_cover_page()          ← Phase 1: layout only, no formulas
 │
 │  ┌─ Sheet A: PLAN→CREATE→SAVE→RECALC→CHECK
 │  ├─ Sheet B: PLAN→CREATE→SAVE→RECALC→CHECK
 │  └─ Sheet C: ...
 │
 ├─ finalize_cover_page()        ← Phase 2: inject formulas + Sheet Index
 ├─ VALIDATE                     ← Full workbook audit (all cross-refs, Cover completeness)
 └─ DELIVER

Recalc Script Output Format

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": { "count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"] }
  }
}

Progress Reporting

Report progress between tool calls, not batched at the end:

Step	Report Format
GEN	⏳ 正在生成 [sheet名] — [简要描述内容，如"销售汇总表"]
DONE	✅ [sheet名] — 已完成
RECALC	🔄 公式重算中… 共 N 个公式，错误 N 个
CHECK	🔍 [sheet名] — PASS ✅ / FAIL ❌
Final	📊 完成！共 N 个工作表，M 个公式，0 错误

Rules

• ❌ Never batch-create all sheets then check afterward
• ❌ Never skip PLAN, RECALC, or CHECK for any sheet
• ❌ Never silently move to next sheet without reporting status
• ✅ Fix all errors before moving on; max 3 RECALC retries per sheet

---

2. Cover Page (Multi-Sheet Workbooks)

Sheet Count (excluding Cover)	Cover Page Behavior
≥ 3 sheets	Create by default unless user explicitly declines
2 sheets	Recommended but optional
1 sheet or plain data export	Do not create

Module Location

All Cover Page code and the PALETTE definition live in scripts/cover.py. Generated scripts must import:

import sys, os
SKILL_DIR = os.path.dirname('<this skill's location path>')
sys.path.insert(0, os.path.join(SKILL_DIR, 'scripts'))
from cover import PALETTE, create_cover_page, finalize_cover_page

⚠️ PALETTE is defined once and only once in cover.py. Data sheets pull colors from the same PALETTE. Never redefine PALETTE in generated scripts.

Two-Phase Creation

The Cover Page layout is built first, but formulas are injected later — because the data sheets referenced by Key Metrics do not exist yet during Phase 1.

Phase	When	What
create_cover_page()	Workflow START	Title, subtitle, style frame. Metrics rows left empty.
finalize_cover_page()	After all data sheets, before VALIDATE	Inject cross-sheet formulas, build Sheet Index.

API Signatures

create_cover_page(wb, title: str, subtitle: str, style: str = 'pure') -> Worksheet

• style: 'pure' (default) or 'finance'

finalize_cover_page(
    wb,
    metrics: list[dict],        # [{'label': str, 'ref': '=Sheet!B2'} | {'label': str, 'value': any}]
    sheet_desc: dict | None,    # {'SheetName': 'description'}, None to leave blank
    notes: str | None         # Optional footer text
) -> Worksheet

• Use 'ref' for cross-sheet formulas (dynamic); 'value' only for constants known at generation time

Typical Usage

from openpyxl import Workbook
from cover import PALETTE, create_cover_page, finalize_cover_page

wb = Workbook(); wb.remove(wb.active)
create_cover_page(wb, 'Q3 Sales Report', 'Regional KPI analysis', style='pure')

# ... build data sheets (use P = PALETTE['pure'] for colors) ...

finalize_cover_page(wb,
    metrics=[
        {'label': 'Total Revenue', 'ref': '=Analysis!B2'},
        {'label': 'Growth Rate',   'ref': '=Analysis!B3'},
    ],
    sheet_desc={'Analysis': 'Regional sales breakdown', 'Charts': 'Key trend charts'},
    notes='Data as of 2024-09-30.')
wb.save('output.xlsx')

Rules

• ❌ Never write cross-sheet formulas in Phase 1
• ❌ Never hardcode colors outside PALETTE
• ❌ Never redefine PALETTE in generated scripts — always from cover import PALETTE
• ✅ Single-sheet or simple data tasks may omit the Cover Page
• ✅ Always create_cover_page() first, finalize_cover_page() before VALIDATE
• ✅ Use 'ref' for cross-sheet metrics; 'value' only for generation-time constants

---

3. Formula-First Policy

Prefer Excel formulas over Python pre-calculation. Workbooks should remain dynamic.

Preferred

ws['C2'] = '=A2+B2'              # Dynamic formula
ws['B6'] = 0.05                  # Assumption cell — blue font, labelled
ws['C5'] = '=B5*(1+$B$6)'       # References assumption, not hardcoded 1.05

Avoid

ws['C2'] = value_a + value_b     # Static — loses dynamic capability
ws['C5'] = '=B5*1.05'           # Magic number — not updateable

Place ALL assumptions in dedicated cells with blue font. Never hardcode numeric constants in formulas.

When Static Values Are Acceptable

• External source data (web APIs, search results, user-provided constants)
• True constants (e.g. days in a week)
• Circular reference avoidance
• Complex pandas aggregations with no clean formula equivalent

Source Annotation (Mandatory for Static Values)

Source: [media/system], [date], [specific reference]

Examples: Source: 新华网, 2024-03, 2024年营收数据 · Source: web_search, 2025-07, IDC手机出货量报告

---

4. Formula Construction & Verification

Pre-Write Checklist

• Column mapping: DataFrame col index ≠ Excel column letter (off-by-one is common)
• Row offset: DataFrame 0-indexed → Excel 1-indexed (DF row 5 = Excel row 6)
• NaN handling: pd.notna() before writing to avoid broken formula chains
• Division by zero: Wrap with IFERROR when denominators may be zero
• Cross-sheet format: SheetName!$A$2:$C$100 — if the sheet name contains spaces, special characters, or is in non-ASCII (e.g. Chinese), must wrap in single quotes: '营收分析'!$A$2:$C$100

IFERROR Wrapper (use proactively)

ws['D2'] = '=IFERROR(B2/C2, "-")'
ws['E2'] = '=IFERROR(VLOOKUP(A2,$G$2:$I$50,3,FALSE), "N/A")'

⚠️ data_only=True Warning

Never open with data_only=True then save — permanently replaces all formulas with cached values.

# DANGER
wb = load_workbook('file.xlsx', data_only=True)
wb.save('file.xlsx')  # All formulas gone permanently

# SAFE
wb = load_workbook('file.xlsx')

---

5. VLOOKUP / INDEX+MATCH Rules

Trigger Keywords

"based on", "from another table", "match against", "lookup", shared keys, master-detail, code-to-name mapping, cross-sheet linkage.

Syntax

• Default: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
• Lookup column must be leftmost of table_array; if not → use INDEX/MATCH
• Always: FALSE for exact match, lock ranges with $, wrap with IFERROR

ws['D2'] = '=IFERROR(VLOOKUP(A2,$G$2:$I$50,3,FALSE),"N/A")'

---

6. Chart Creation Rules

When to Create Charts

If user asks for charts/visuals, MUST actively create them. Each prepared dataset should have at least one chart unless user says otherwise.

Trigger Keywords: "visual", "chart", "graph", "visualization", "diagram", "图表", "可视化"

Requirements

• ❌ Never create a "chart data" sheet with manual instructions
• ❌ Never tell the user to create charts themselves
• ✅ Use openpyxl.chart for embedded charts in .xlsx
• Standalone PNG/JPG only if user explicitly requests

Chart Configuration

Single source of truth for chart type selection, sizing, layout, rendering, and code examples: references/charts.md. Always read it before creating charts. This section only defines when to create charts; charts.md defines how.

---

7. Visual Style Guide — PALETTE Reference

PALETTE Definition Location

Single source of truth: the PALETTE dict in scripts/cover.py.

from cover import PALETTE

P = PALETTE['pure']     # Default for all non-financial tasks
P = PALETTE['finance']  # Financial / fiscal analysis tasks only

Style	When to Use
pure	Default for all non-financial tasks
finance	Financial / fiscal analysis only (stocks, GDP, salary, revenue, profit, budget, ROI, etc.)

7.1 Key Principles (Both Styles)

• Hide gridlines: ws.sheet_view.showGridLines = False
• Use openpyxl for all styling (not pandas)
• Preserve original styles when editing existing files

Text Color Convention (Mandatory)

Color	Meaning
Blue	Fixed / input values (including assumptions)
Black	Calculated formula cells
Green	Cross-worksheet references
Red	External link references

Border Style: Default no borders. Thin 1px for internal structure, 2px for section dividers. Only add borders to highlight results or sections.

7.2 Using PALETTE in Data Sheets

from cover import PALETTE
from openpyxl.styles import PatternFill, Font, Border, Side, Alignment

P = PALETTE['pure']  # or PALETTE['finance']

# ── Build style objects directly from P ──
thin_border = Border(
    left=Side(style='thin', color=P['border']),
    right=Side(style='thin', color=P['border']),
    top=Side(style='thin', color=P['border']),
    bottom=Side(style='thin', color=P['border']))

header_fill  = PatternFill(start_color=P['header_bg'], end_color=P['header_bg'], fill_type="solid")
header_font  = Font(color="FFFFFF", bold=True, name="Arial", size=11)
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
body_font    = Font(color=P['text_primary'], name="Arial", size=10)
alt_row_fill = PatternFill(start_color=P['bg_alt'], end_color=P['bg_alt'], fill_type="solid")

7.3 Pure Style — Color Quick Reference

Purpose	Key	Hex
Title / header fill	header_bg	#1B3A5C
Primary accent	accent	#2C6FAC
Body text	text_primary	#212529
Secondary text	text_secondary	#6C757D
Primary background	bg_main	#FFFFFF
Alternating rows	bg_alt	#F8F9FA
Borders / dividers	border	#DEE2E6
Light highlight	highlight_bg	#E3EDF7
Data bars	data_bar	#6BA3D6

Forbidden: ❌ Green, red, orange, purple, yellow, pink, or any non-blue chromatic color · ❌ Rainbow / multi-hue gradients · ❌ High-saturation colors

7.4 Finance Style — Color Quick Reference

Extends Pure with semantic gain/loss colors:

Purpose	Key	Hex
Header fill	header_bg	#0D1B2A
Body text	text_primary	#1A1A2E
Primary background	bg_main	#FAFBFD
Alternating rows	bg_alt	#F0F2F6
Key metric highlight	highlight_bg	#FFF3CD
Borders	border	#D6DAE0
Up / positive (international)	positive	#1B7A3D
Down / negative (international)	negative	#B71C1C
Neutral / warning	neutral	#E6A817
Up / gain (China Mainland)	up_cn	#B71C1C
Down / loss (China Mainland)	down_cn	#1B7A3D

Regional Up/Down Convention (Critical)

Region	Up / Gain	Down / Loss
China (Mainland)	🔴 Red P['up_cn']	🟢 Green P['down_cn']
International	🟢 Green P['positive']	🔴 Red P['negative']

P = PALETTE['finance']

# Select semantic colors by region:
# International (default)
gain_font = Font(color=P['positive'], bold=True)    # green = gain
loss_font = Font(color=P['negative'], bold=True)     # red = loss
# China Mainland — use dedicated keys, no ambiguity
gain_font_cn = Font(color=P['up_cn'], bold=True)     # red = gain (Chinese convention)
loss_font_cn = Font(color=P['down_cn'], bold=True)   # green = loss (Chinese convention)

Number Formatting

Data Type	Format	Notes
Years	Text "2024"	Never numeric
Currency	$#,##0	Units in header: Revenue ($mm)
Zero values	$#,##0;($#,##0);-	Dash for zero
Percentages	0.00%	Two decimal
Negatives	(123) parentheses	Never -123
Multiples	0.0x	EV/EBITDA, P/E

7.5 Conditional Formatting

Proactively apply to 2–4 key columns per sheet. Keep color semantics consistent across workbook.

from openpyxl.formatting.rule import DataBarRule, ColorScaleRule, IconSetRule, CellIsRule

P = PALETTE['finance']  # or PALETTE['pure']

# Data Bars
ws.conditional_formatting.add('C2:C100',
    DataBarRule(start_type='min', end_type='max', color=P['data_bar'], showValue=True))

# Three-Color Scale (Finance: red → amber → green, International)
ws.conditional_formatting.add('D2:D100',
    ColorScaleRule(start_type='min', start_color=P['negative'],
                   mid_type='percentile', mid_value=50, mid_color=P['neutral'],
                   end_type='max', end_color=P['positive']))

# Icon Sets
ws.conditional_formatting.add('E2:E100',
    IconSetRule('3Arrows', type='percent', values=[0, 33, 67], showValue=True))

Data Type	Recommended Rule
Numeric values	Data Bars
Distribution	Color Scales
KPIs / Status	Icon Sets
Thresholds	CellIsRule

Best practice: Data Bars + Icon Sets combo. Exclude header rows from ranges.

---

8. General Document Processing Strategy

Existing Files → Incremental Modification

Load original; replace, fill, or insert directly. Fully preserve structure, styles, and formatting.

⚠️ CRITICAL: When loading existing files, never use data_only=True if you intend to save afterward — this permanently replaces all formulas with their cached values. Always use load_workbook('file.xlsx') without data_only.

# DANGER — destroys all formulas on save
wb = load_workbook('file.xlsx', data_only=True)
wb.save('file.xlsx')

# SAFE
wb = load_workbook('file.xlsx')

New Document

Only when creating from scratch or purely referencing template styles.

Content Cleanup

Remove all instructional text, placeholders, ${variable} patterns — keep only formal content.

9. AIGC Marker

Rule: After every .xlsx file is saved, read the aigc_marker skill and follow its instructions to embed the AIGC identifier before VALIDATE. SAVE → view [aigc_marker path]/SKILL.md → APPLY → DELIVER

Constraints

• ❌ Never skip — applies to all generated or modified files
• ❌ Never assume the aigc_marker interface without reading its SKILL.md
• ✅ On failure: warn the user but do not block delivery

Progress reporting: 🏷️ AIGC marker — written ✅ / failed ⚠️