xlsx-processing-manus

What this skill does

# Excel Generator Skill

## Goal

Make the user able to use the Excel immediately and gain insights upon opening.

## Core Principle

Enrich visuals as much as possible, while ensuring content clarity and not adding cognitive burden. Every visual element should be meaningful and purposeful—serving the content, not decorating it.

---

## Part 1: User Needs & Feature Matching

Before creating any Excel, think through:
1. **What does the user need?** — Not "an Excel file", but what problem are they solving?
2. **What can I provide?** — Which features will help them?
3. **How to match?** — Select the right combination for this specific scenario.

### Feature ↔ User Value Pairs

#### Help Users「Understand Data」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Bar/Column Chart | See comparisons at a glance | Comparing values across categories |
| Line Chart | See trends at a glance | Time series data |
| Pie Chart | See proportions at a glance | Part-to-whole (≤6 categories) |
| Data Bars | Compare magnitude without leaving the cell | Numeric columns needing quick comparison |
| Color Scale | Heatmap effect, patterns pop out | Matrices, ranges, distributions |
| Sparklines | See trend within a single cell | Summary rows with historical context |

#### Help Users「Find What Matters」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Pre-sorting | Most important data comes first | Rankings, Top N, priorities |
| Conditional Highlighting | Key data stands out automatically | Outliers, thresholds, Top/Bottom N |
| Icon Sets | Status visible at a glance | KPI status, categorical states (use sparingly) |
| Bold/Color Emphasis | Visual distinction between primary and secondary | Summary rows, key metrics |
| KEY INSIGHTS Section | Conclusions delivered directly | Analytical reports |

#### Help Users「Save Time」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Overview Sheet | Summary on first page, no hunting | All multi-sheet files |
| Pre-calculated Summaries | Results ready, no manual calculation | Data requiring statistics |
| Consistent Number Formats | No format adjustments needed | All numeric data |
| Freeze Panes | Headers visible while scrolling | Tables with >10 rows |
| Sheet Index with Links | Quick navigation, no guessing | Files with >3 sheets |

#### Help Users「Use Directly」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Filters | Users can explore data themselves | Exploratory analysis needs |
| Hyperlinks | Click to navigate, no manual switching | Cross-sheet references, external sources |
| Print-friendly Layout | Ready to print or export to PDF | Reports for sharing |
| Formulas (not hardcoded) | Change parameters, results update | Models, forecasts, adjustable scenarios |
| Data Validation Dropdowns | Prevent input errors | Templates requiring user input |

#### Help Users「Trust the Data」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Data Source Attribution | Know where data comes from | All external data |
| Generation Date | Know data freshness | Time-sensitive reports |
| Data Time Range | Know what period is covered | Time series data |
| Professional Formatting | Looks reliable | All external-facing files |
| Consistent Precision | No doubts about accuracy | All numeric values |

#### Help Users「Gain Insights」

| Feature | User Value | When to Use |
|---------|-----------|-------------|
| Comparison Columns (Δ, %) | No manual calculation for comparisons | YoY, MoM, A vs B |
| Rank Column | Position visible directly | Competitive analysis, performance |
| Grouped Summaries | Aggregated results by dimension | Segmented analysis |
| Trend Indicators (↑↓) | Direction clear at a glance | Change direction matters |
| Insight Text | The "so what" is stated explicitly | Analytical reports |

---

## Part 2: Four-Layer Implementation

### Layer 1: Structure (How It's Organized)

**Goal**: Logical, easy to navigate, user finds what they need immediately.

#### Sheet Organization

| Guideline | Recommendation |
|-----------|----------------|
| Sheet count | 3-5 ideal, max 7 |
| First sheet | Always "Overview" with summary and navigation |
| Sheet order | General → Specific (Overview → Data → Analysis) |
| Naming | Clear, concise (e.g., "Revenue Data", not "Sheet1") |

#### Information Architecture

- **Overview sheet must stand alone**: User should understand the main message without opening other sheets
- **Progressive disclosure**: Summary first, details available for those who want to dig deeper
- **Consistent structure across sheets**: Same layout patterns, same starting positions

#### Layout Rules

| Element | Position |
|---------|----------|
| Left margin | Column A empty (width 3) |
| Top margin | Row 1 empty |
| Content start | Cell B2 |
| Section spacing | 1 empty row between sections |
| Table spacing | 2 empty rows between tables |
| Charts | Below all tables (2 rows gap), or right of related table |

**Chart placement**:
- Default: below all tables, left-aligned with content
- Alternative: right of a single related table
- Charts must never overlap each other or tables

#### Standalone Text Rows

For rows with a single text cell (titles, descriptions, notes, bullet points), text will naturally extend into empty cells to the right. However, text is **clipped** if right cells contain any content (including spaces).

**Decision logic**:

| Condition | Action |
|-----------|--------|
| Right cells guaranteed empty | No action needed—text extends naturally |
| Right cells may have content | Merge cells to content width, or wrap text |
| Text exceeds content area width | Wrap text + set row height manually |

**Technical note**: Fill and border alone do NOT block text overflow—only actual cell content (including space characters) blocks it.

#### Navigation

For files with 3+ sheets, include a Sheet Index on Overview:

```python
# Sheet Index with hyperlinks
ws['B5'] = "CONTENTS"
ws['B5'].font = Font(name=SERIF_FONT, size=14, bold=True, color=THEME['accent'])

sheets = ["Overview", "Data", "Analysis"]
for i, sheet_name in enumerate(sheets, start=6):
    cell = ws.cell(row=i, column=2, value=sheet_name)
    cell.hyperlink = f"#'{sheet_name}'!A1"
    cell.font = Font(color=THEME['accent'], underline='single')
```

---

### Layer 2: Information (What They Learn)

**Goal**: Accurate, complete, insightful—user gains knowledge, not just data.

#### Number Formats

**Critical rules**:
1. **Every numeric cell must have `number_format` set** — both input values AND formula results
2. **Same column = same precision** — never mix `0.1074` and `1.0` in one column
3. **Formula results have no default format** — they display raw precision unless explicitly formatted

| Data Type | Format Code | Example |
|-----------|-------------|---------|
| Integer | `#,##0` | 1,234,567 |
| Decimal (1) | `#,##0.0` | 1,234.6 |
| Decimal (2) | `#,##0.00` | 1,234.56 |
| Percentage | `0.0%` | 12.3% |
| Currency | `$#,##0.00` | $1,234.56 |

**Common mistake**: Setting format only for input cells, forgetting formula cells.

```python
# WRONG: Formula cell without number_format
ws['C10'] = '=C7-C9'  # Will display raw precision like 14.123456789

# CORRECT: Always set number_format for formula cells
ws['C10'] = '=C7-C9'
ws['C10'].number_format = '#,##0.0'  # Displays as 14.1

# Best practice: Define format by column/data type, apply to ALL cells
for row in range(data_start, data_end + 1):
    cell = ws.cell(row=row, column=value_col)
    cell.number_format = '#,##0.0'  # Applies to both values and formulas
```

#### Data Context

Every data set needs context:

| Element | Location | Example |
|---------|----------|---------|
| Data source | Overview or sheet footer | "Source: Company Annual Report 2024" |
| Time range | Near title or in subtitle | "Data from Jan 2020 - Dec 2024" |
| Generation date | Overview footer