from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Border, Side, Alignment
import pandas as pd

</Technology Stack>

<External Data in Excel>

When creating Excel files with externally fetched data:

**Source Citation (MANDATORY):**
- ALL external data MUST have source citations in final Excel
- **🚨 This applies to ALL external tools**: `datasource`, `web_search`, API calls, or any fetched data
- Use **two separate columns**: `Source Name` | `Source URL`
- Do NOT use HYPERLINK function (use plain text to avoid formula errors)
- **⛔ FORBIDDEN**: Delivering Excel with external data but NO source citations
- Example:

| Data Content | Source Name | Source URL |
|--------------|-------------|------------|
| Apple Revenue | Yahoo Finance | https://finance.yahoo.com/... |
| China GDP | World Bank API | world_bank_open_data |

- If citation per-row is impractical, create a dedicated "Sources" sheet

</External Data in Excel>


<Tool script list>
You have **two types of tools** for Excel tasks:

**1. Python (openpyxl/pandas)** - For Excel file creation, styling, formulas, charts
**2. KimiXlsx CLI Tool** - For validation, error checking, and PivotTable creation

The KimiXlsx tool has **6 commands** that can be called using the shell tool:

**Executable Path**: `/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx`

**Base Command**: `/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx <command> [arguments]`

---

1. **recheck** ⚠️ RUN FIRST for formula errors

- description：This tool detects:
  - **Formula errors**: \#VALUE!, \#DIV/0!, \#REF!, \#NAME?, \#NULL!, \#NUM!, \#N/A
  - **Zero-value cells**: Formula cells with 0 result (often indicates reference errors)
  - **Implicit array formulas**: Formulas that work in LibreOffice but show \#N/A in MS Excel (e.g., `MATCH(TRUE(), range>0, 0)`)

- **Implicit Array Formula Detection**:
  - Patterns like `MATCH(TRUE(), range>0, 0)` require CSE (Ctrl+Shift+Enter) in MS Excel
  - LibreOffice handles these automatically, so they pass LibreOffice recalculation but fail in Excel
  - When detected, rewrite the formula using alternatives:
    - ❌ `=MATCH(TRUE(), A1:A10>0, 0)` → shows \#N/A in Excel
    - ✅ `=SUMPRODUCT((A1:A10>0)*ROW(A1:A10))-ROW(A1)+1` → works in all Excel versions
    - ✅ Or use helper column with explicit TRUE/FALSE values

- how to use:
```bash
/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx recheck output.xlsx

/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx reference-check output.xlsx

undefined

---

4. **pivot** 🚨 REQUIRES pivot-table.md

- description: **Create PivotTable with optional chart** using pure OpenXML SDK. This is the ONLY supported method for PivotTable creation. Automatically creates a chart (bar/line/pie) alongside the PivotTable.
- **⚠️ CRITICAL**: Before using this command, you MUST read `/app/.kimi/skills/kimi-xlsx/pivot-table.md` for full documentation.
- required parameters:
  - `input.xlsx` - Input Excel file (positional)
  - `output.xlsx` - Output Excel file (positional)
  - `--source "Sheet!A1:Z100"` - Source data range
  - `--location "Sheet!A3"` - Where to place PivotTable
  - `--values "Field:sum"` - Value fields with aggregation (sum/count/avg/max/min)
- optional parameters:
  - `--rows "Field1,Field2"` - Row fields
  - `--cols "Field1"` - Column fields
  - `--filters "Field1"` - Filter/page fields
  - `--name "PivotName"` - PivotTable name (default: PivotTable1)
  - `--style "monochrome"` - Style theme: `monochrome` (default) or `finance`
  - `--chart "bar"` - Chart type: `bar` (default), `line`, or `pie`
- how to use:
```bash

---

5. **chart-verify**

- description: **Verify that all charts have actual data content**. Use this after creating charts to ensure they are not empty.
- how to use:
```bash
/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx chart-verify output.xlsx

/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx validate output.xlsx

For each sheet in workbook:
    1. PLAN   → Design this sheet's structure, formulas, references
    2. CREATE → Write data, formulas, styling for this sheet
    3. SAVE   → Save the workbook (wb.save())
    4. CHECK  → Run recheck + reference-check → Fix until 0 errors
    5. NEXT   → Only proceed to next sheet after current sheet has 0 errors

After ALL sheets pass:
    6. VALIDATE → Run `validate` command → Fix until exit code 0
    7. DELIVER  → Only deliver files that passed ALL validations

undefined

undefined

/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx validate output.xlsx

ws['C2'] = '=A2+B2'           # Sum
ws['D2'] = '=C2/B2*100'       # Percentage
ws['E2'] = '=SUM(A2:A100)'    # Aggregation

result = value_a + value_b
ws['C2'] = result    # BAD: Static value, not a formula

Sheet 1: Plan (write detailed design) → Create → Save → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
Sheet 2: Plan (write detailed design) → Create → Save → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
Sheet 3: Plan (write detailed design) → Create → Save → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
...

1. Create Sheet 1 → Create Sheet 2 → Create Sheet 3 → Run Recheck once at end
   ❌ WRONG: Errors accumulate, debugging becomes exponentially harder
   ✅ CORRECT: Check after EACH sheet, fix before moving to next

2. Skip planning for any sheet
   ❌ WRONG: Causes 80%+ of reference errors
   ✅ CORRECT: Plan each sheet's structure before creating it

3. Recheck shows errors → Ignore and deliver anyway
   ❌ ABSOLUTELY FORBIDDEN - errors must be fixed, not ignored!

4. Recheck shows errors → Proceed to create next sheet anyway
   ❌ WRONG: Errors in Sheet 1 will cascade to Sheet 2, 3...
   ✅ CORRECT: Fix ALL errors in current sheet before creating next sheet

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

undefined

**⛔ FORBIDDEN**:
- Creating PivotTable manually with openpyxl code
- Skipping the `inspect` step
- Not reading pivot-table.md before creating PivotTable
- **🚨 NEVER modify pivot output file with openpyxl** - openpyxl will corrupt pivotCache paths!

**⚠️ CRITICAL: Workflow Order for PivotTable**
If you need to add extra sheets (Cover, Summary, etc.) to a file that will have PivotTable:
1. **FIRST**: Create ALL sheets with openpyxl (data sheets, cover sheet, styling, etc.)
2. **THEN**: Run `pivot` command as the **FINAL STEP**
3. **NEVER**: Open the pivot output file with openpyxl again - this corrupts the file!

</PivotTable Module>

<Baseline error>
**Forbidden Formula Errors**:
1. Formula errors: #VALUE!, #DIV/0!, #REF!, #NAME?, #NULL!, #NUM!, #N/A - NEVER include
2. Off-by-one references (wrong cell/row/column)
3. Text starting with `=` interpreted as formula
4. Static values instead of formulas (use formulas for calculations)
5. Placeholder text: "TBD", "Pending", "Manual calculation required" - FORBIDDEN
6. Missing units in headers; Inconsistent units in calculations
7. Currency without format symbols (¥/$)
8. Result of 0 must be verified - often indicates reference error

**🚨 FORBIDDEN FUNCTIONS (Incompatible with older Excel versions)**:

The following functions are **NOT supported** in Excel 2019 and earlier. Files using these functions will **FAIL to open** in older Excel versions. Use traditional alternatives instead.

| ❌ Forbidden Function | ✅ Alternative |
|----------------------|----------------|
| `FILTER()` | Use AutoFilter, or SUMIF/COUNTIF/INDEX-MATCH |
| `UNIQUE()` | Use Remove Duplicates feature, or helper column with COUNTIF |
| `SORT()`, `SORTBY()` | Use Excel's Sort feature (Data → Sort) |
| `XLOOKUP()` | Use `INDEX()` + `MATCH()` combination |
| `XMATCH()` | Use `MATCH()` |
| `SEQUENCE()` | Use ROW() or manual fill |
| `LET()` | Define intermediate calculations in helper cells |
| `LAMBDA()` | Use named ranges or VBA |
| `RANDARRAY()` | Use `RAND()` with fill-down |
| `ARRAYFORMULA()` | Google Sheets only - use Ctrl+Shift+Enter array formulas |
| `QUERY()` | Google Sheets only - use SUMIF/COUNTIF/PivotTable |
| `IMPORTRANGE()` | Google Sheets only - copy data manually |

**Why these are forbidden**:
- These are Excel 365/2021+ dynamic array functions or Google Sheets functions
- Older Excel versions (2019, 2016, etc.) cannot parse these formulas
- The file will crash or show errors when opened in older Excel
- The `validate` command will detect and reject files using these functions

**Example - Converting FILTER to INDEX-MATCH**:

**⚠️ Off-By-One Prevention**: Before saving, verify each formula references correct cells. Run `reference-check` tool. Common errors: referencing headers, wrong row/column offset. If result is 0 or unexpected → check references first.

**💰 Financial Values**: Store in smallest unit (15000000 not 1.5M). Use Excel format for display: `"¥#,##0"`. Never use scaled units requiring conversion in formulas.

</Baseline error>

</Analyze rule>

<Style Rules>

Use python-openpyxl package to design the style of excel. Apply styling directly in openpyxl code.

**🎨 Overall Visual Design Principles**
- **⚠️ MANDATORY: Hide Gridlines** - ALL sheets MUST have gridlines hidden (see code below)
- Start at B2 (top-left padding), not A1
- **Title Row Height**: Since content starts at B2, row 2 is typically the title row with larger font. Always increase row 2 height to prevent text clipping: `ws.row_dimensions[2].height = 30` (adjust based on font size)
- **Professionalism First**: Adopt business-style color schemes, avoid over-decoration that impairs data readability
- **Consistency**: Use uniform formatting, fonts, and color schemes for similar data types
- **Clear Hierarchy**: Establish information hierarchy through font size, weight, and color intensity
- **Appropriate White Space**: Use reasonable margins and row heights to avoid content crowding
- Please arrange the appropriate width and height dimensions for each cell, and do not have a cell that is not wide enough and too high, resulting in a display scale imbalance

---

**⚠️ How to Hide Gridlines (openpyxl)**

```python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

---

**📐 Merged Cells Guide**

Use `ws.merge_cells()` for titles, headers spanning columns, or grouped labels. Apply style to **top-left cell only**.

```python

**Rules**:
- ✅ Use for: titles, section headers, category labels spanning columns
- ❌ Avoid in: data areas, formula ranges, PivotTable source data
- Always set `alignment` on merged cells for proper text positioning

---

**🎨 Style Selection Guide**
- **Minimalist Monochrome Style**: Default for ALL non-financial tasks (Black/White/Grey + Blue accent only)
- **Professional Finance Style**: For financial/fiscal analysis (stock, GDP, salary, public finance)

---

<Minimalist_Monochrome_Style>

undefined

</Minimalist_Monochrome_Style>

<Professional_Finance_Style>

undefined

</Professional_Finance_Style>

---

<Conditional_Formatting>

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

**Best Practices**: Apply to 2-4 key columns per sheet; use consistent color meanings; combine Data Bars + Icons for impact.

</Conditional_Formatting>

---

**📝 Text Color Style (MUST FOLLOW)**
- **Blue font**: Fixed values/input values
- **Black font**: Cells with calculation formulas
- **Green font**: Cells referencing other sheets
- **Red font**: Cells with external reference

---

**📏 Border Styles**
- In general cases, do not add borders to cells to make the whole content appear more focused
- Do not use a table border line unless you need to use a border line to reflect the calculation results
- Sometimes, you can use 1px borders within models, thicker for section breaks


<Cover Page Design>

**Every Excel deliverable MUST include a Cover Page as the FIRST sheet.**

| Sheet Name | Description |
|------------|-------------|
| Raw Data | Original dataset (100 rows) |
| Analysis | Sales breakdown by region |
| Pivot Summary | Interactive pivot analysis |

⚠️ IMPORTANT: This workbook contains PivotTables.
   Please refresh data after opening:
   - Windows: Select PivotTable → Right-click → Refresh
   - Mac: Select PivotTable → PivotTable Analyze → Refresh
   - Or press Ctrl+Alt+F5 to refresh all

1. Create Excel with openpyxl (data, styling)
2. Add charts using openpyxl.chart module
3. Save file
4. Run chart-verify to confirm charts exist and have data
5. If chart-verify returns exit code 1 → FIX before delivering

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

undefined

from openpyxl.chart import LineChart, Reference

chart = LineChart()
chart.title = "Trend Analysis"
chart.style = 13
chart.y_axis.title = 'Value'
chart.x_axis.title = 'Month'

data = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=3)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=13)
chart.set_categories(cats)

ws.add_chart(chart, "E2")

from openpyxl.chart import PieChart, Reference

pie = PieChart()
pie.title = "Market Share"

data = Reference(ws, min_col=2, min_row=1, max_row=5)
labels = Reference(ws, min_col=1, min_row=2, max_row=5)

pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)

ws.add_chart(pie, "E2")

/app/.kimi/skills/kimi-xlsx/scripts/KimiXlsx chart-verify output.xlsx

Phase 1: DESIGN
    → Plan all sheets structure, formulas, cross-references before coding

Phase 2: CREATE & VALIDATE (Per-Sheet Loop)
    For each sheet:
        1. Create sheet (data, formulas, styling, charts if needed)
        2. Save workbook
        3. Run: recheck output.xlsx
        4. Run: reference-check output.xlsx
        5. Run: chart-verify output.xlsx (if sheet contains charts)
        6. If errors found → Fix and repeat step 2-5
        7. Only proceed to next sheet when current sheet has 0 errors

Phase 3: FINAL VALIDATION
    → Run: validate output.xlsx
    → If exit code = 0: Safe to deliver
    → If exit code ≠ 0: Regenerate the file with corrected code

Phase 4: DELIVER
    → Only deliver files that passed ALL validations