Excel Generation Editing Tool

---
name: Excel文档处理
name_en: excel-generation-editing-tool
version: 1.0.0
description: "Excel电子表格创建、编辑、读取与分析，支持.xlsx/.xlsm/.csv/.tsv格式。覆盖新建表格、数据读取、公式验证、透视表及专业格式化等操作。"
category: general
---
# Excel文档处理

Handle the request directly. Do NOT spawn sub-agents. Always write the output file the user requests.

## When NOT to use

This skill handles Excel/spreadsheet file creation, editing, and analysis. Do NOT trigger when:

| Scenario | Reason | Redirect |
|----------|--------|----------|
| User asks to generate charts, graphs, or data visualization images | Not a charting tool | Image generation skill |
| User asks to create a Word document, PDF, or presentation | Wrong file format | docx/pdf/pptx skills |
| User asks to build a database, web app, or API that outputs data | Software engineering task | General coding |
| User asks "what is a pivot table" or "how to use VLOOKUP" | Excel usage tutorial, no file operation | Answer directly |
| User says "convert my JSON to xlsx" without attaching a file or providing inline data | No data source to convert | Ask for the data first |
| User mentions Excel only as a passing reference (e.g. "send the Excel later") | No actual file task | Do not trigger |

When in doubt, check: **is the user asking to open/create/edit/validate a specific spreadsheet file or tabular dataset?** If not, do not trigger.

## Task Routing

| Task | Method | Guide |
|------|--------|-------|
| **READ** — analyze existing data | `xlsx_reader.py` + pandas | `references/read-analyze.md` |
| **CREATE** — new xlsx from scratch | XML template | `references/create.md` + `references/format.md` |
| **EDIT** — modify existing xlsx | XML unpack→edit→pack | `references/edit.md` (+ `format.md` if styling needed) |
| **FIX** — repair broken formulas in existing xlsx | XML unpack→fix `<f>` nodes→pack | `references/fix.md` |
| **VALIDATE** — check formulas | `formula_check.py` | `references/validate.md` |

## READ — Analyze data (read `references/read-analyze.md` first)

Start with `xlsx_reader.py` for structure discovery, then pandas for custom analysis. Never modify the source file.

**Formatting rule**: When the user specifies decimal places (e.g. "2 decimal places"), apply that format to ALL numeric values — use `f'{v:.2f}'` on every number. Never output `12875` when `12875.00` is required.

**Aggregation rule**: Always compute sums/means/counts directly from the DataFrame column — e.g. `df['Revenue'].sum()`. Never re-derive column values before aggregation.

## CREATE — XML template (read `references/create.md` + `references/format.md`)

Copy `templates/minimal_xlsx/` → edit XML directly → pack with `xlsx_pack.py`. Every derived value MUST be an Excel formula (`<f>SUM(B2:B9)</f>`), never a hardcoded number. Apply font colors per `format.md`.

## EDIT — XML direct-edit (read `references/edit.md` first)

**CRITICAL — EDIT INTEGRITY RULES:**
1. **NEVER create a new `Workbook()`** for edit tasks. Always load the original file.
2. The output MUST contain the **same sheets** as the input (same names, same data).
3. Only modify the specific cells the task asks for — everything else must be untouched.
4. **After saving output.xlsx, verify it**: open with `xlsx_reader.py` or `pandas` and confirm the original sheet names and a sample of original data are present. If verification fails, you wrote the wrong file — fix it before delivering.

Never use openpyxl round-trip on existing files (corrupts VBA, pivots, sparklines). Instead: unpack → use helper scripts → repack.

**"Fill cells" / "Add formulas to existing cells" = EDIT task.** If the input file already exists and you are told to fill, update, or add formulas to specific cells, you MUST use the XML edit path. Never create a new `Workbook()`. Example — fill B3 with a cross-sheet SUM formula:
```bash
python3 SKILL_DIR/scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/
# Find the target sheet's XML via xl/workbook.xml → xl/_rels/workbook.xml.rels
# Then use the Edit tool to add <f> inside the target <c> element:
#   <c r="B3"><f>SUM('Sales Data'!D2:D13)</f><v></v></c>
python3 SKILL_DIR/scripts/xlsx_pack.py /tmp/xlsx_work/ output.xlsx
```

**Add a column** (formulas, numfmt, styles auto-copied from adjacent column):
```bash
python3 SKILL_DIR/scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/
python3 SKILL_DIR/scripts/xlsx_add_column.py /tmp/xlsx_work/ --col G \
    --sheet "Sheet1" --header "% of Total" \
    --formula '=F{row}/$F$10' --formula-rows 2:9 \
    --total-row 10 --total-formula '=SUM(G2:G9)' --numfmt '0.0%' \
    --border-row 10 --border-style medium
python3 SKILL_DIR/scripts/xlsx_pack.py /tmp/xlsx_work/ output.xlsx
```
The `--border-row` flag applies a top border to ALL cells in that row (not just the new column). Use it when the task requires accounting-style borders on total rows.

**Insert a row** (shifts existing rows, updates SUM formulas, fixes circular refs):
```bash
python3 SKILL_DIR/scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/
# IMPORTANT: Find the correct --at row by searching for the label text
# in the worksheet XML, NOT by using the row number from the prompt.
# The prompt may say "row 5 (Office Rent)" but Office Rent might actually
# be at row 4. Always locate the row by its text label first.
python3 SKILL_DIR/scripts/xlsx_insert_row.py /tmp/xlsx_work/ --at 5 \
    --sheet "Budget FY2025" --text A=Utilities \
    --values B=3000 C=3000 D=3500 E=3500 \
    --formula 'F=SUM(B{row}:E{row})' --copy-style-from 4
python3 SKILL_DIR/scripts/xlsx_pack.py /tmp/xlsx_work/ output.xlsx
```
**Row lookup rule**: When the task says "after row N (Label)", always find the row by searching for "Label" in the worksheet XML (`grep -n "Label" /tmp/xlsx_work/xl/worksheets/sheet*.xml` or check sharedStrings.xml). Use the actual row number + 1 for `--at`. Do NOT call `xlsx_shift_rows.py` separately — `xlsx_insert_row.py` calls it internally.

**Apply row-wide borders** (e.g. accounting line on a TOTAL row):
After running helper scripts, apply borders to ALL cells in the target row, not just newly added cells. In `xl/styles.xml`, append a new `<border>` with the desired style, then append a new `<xf>` in `<cellXfs>` that clones each cell's existing `<xf>` but sets the new `borderId`. Apply the new style index to every `<c>` in the row via the `s` attribute:
```xml
<!-- In xl/styles.xml, append to <borders>: -->
<border>
  <left/><right/><top style="medium"/><bottom/><diagonal/>
</border>
<!-- Then append to <cellXfs> an xf clone with the new borderId for each existing style -->
```
**Key rule**: When a task says "add a border to row N", iterate over ALL cells A through the last column, not just newly added cells.

**Manual XML edit** (for anything the helper scripts don't cover):
```bash
python3 SKILL_DIR/scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/
# ... edit XML with the Edit tool ...
python3 SKILL_DIR/scripts/xlsx_pack.py /tmp/xlsx_work/ output.xlsx
```

## FIX — Repair broken formulas (read `references/fix.md` first)

This is an EDIT task. Unpack → fix broken `<f>` nodes → pack. Preserve all original sheets and data.

## VALIDATE — Check formulas (read `references/validate.md` first)

Run `formula_check.py` for static validation. For runtime errors, open the file in Excel/WPS (auto-recalculation on open).

## Financial Color Standard

| Cell Role | Font Color | Hex Code |
|-----------|-----------|----------|
| Hard-coded input / assumption | Blue | `0000FF` |
| Formula / computed result | Black | `000000` |
| Cross-sheet reference formula | Green | `00B050` |

## Compliance Boundaries

**Refuse** the following requests — respond with a brief explanation and do NOT produce any file:

| Prohibited Request | Reason |
|--------------------|--------|
| Falsifying financial statements, invoices, or receipts | Fraud / forgery |
| Generating fake bank statements, pay stubs, or tax documents | Financial fraud |
| Creating documents that impersonate any organization or individual | Identity fraud |
| Filling in official government/tax/legal forms on behalf of the user | Unauthorized legal filing |
| Any spreadsheet intended to deceive, defraud, or misrepresent data to third parties | Ethical violation |

For requests involving sensitive or regulated data (financial, personal, medical), remind the user of data privacy obligations but proceed with the technical task if the request is otherwise legitimate.

## Output Standard

This skill follows the `excel-human` profile of the Richee Output Standard. When generating spreadsheets for human reading:

### GATE rules (block release if violated)

**OUT-COM-001 — AI Disclaimer**
When the output is a formal legal deliverable, insert an AI disclaimer in a visible location (footer row, watermark column, or cell comment on A1): *"本文件由 AI 辅助生成，仅供参考，不构成正式法律意见。"* For non-legal spreadsheets, this is optional.

**OUT-COM-003 — No Absolute Legal Conclusions**
Never include absolute legal claims such as "保证胜诉"、"绝无风险"、"完全合规"、"一定合法". Replace with qualified language: "根据当前资料初步判断"、"建议进一步核实"、"存在以下风险".

**OUT-COM-004 — Text Labels for Status/Risk**
Risk level, status, and priority cells MUST contain text values. Color is decoration only — never the sole carrier of meaning.

| Status | Display |
|--------|---------|
| 高风险 | `🔴 高风险` or `高风险` |
| 中风险 | `🟡 中风险` or `中风险` |
| 低风险 | `🟢 低风险` or `低风险` |

> Note: Emoji indicators are permitted in data cells for readability. The text label is mandatory; the emoji/color is optional decoration.

**OUT-COM-005 — Authority Tags**
When citing legal basis, use only these closed-set tags: `【法律依据】`、`【法规参考】`、`【司法案例】`、`【行政规范】`、`【行业标准】`. Do NOT invent new tag categories at runtime.

**OUT-EXCEL-001 — No Emoji**
The final delivered `.xlsx` file must contain **zero emoji characters**. If emoji markers were used during drafting, strip them before packaging.

**OUT-EXCEL-002 — Text Values for Risk Cells**
Risk and status cells must always carry a text value. Color formatting is auxiliary — if a cell shows "red" but no text, it fails this rule.

### REQUIRED rules (must be corrected if missing)

**OUT-COM-006 — Filename**
Output filenames must NOT contain emoji. Use recognizable naming: `[主题]_[日期/版本].xlsx`.

**OUT-EXCEL-003 — Table Formatting**
- Header row: black background, white text, freeze first row, enable auto-filter
- Data cells: vertical center aligned, wrap text enabled, reasonable row height
- Do NOT merge cells that the user may later filter or sort

**OUT-EXCEL-004 — Column Width**
Set column widths based on content — auto-fit each column individually. For wide tables, do NOT forcibly compress to one page if it renders the data unreadable.

## Key Rules

1. **Formula-First**: Every calculated cell MUST use an Excel formula, not a hardcoded number
2. **CREATE → XML template**: Copy minimal template, edit XML directly, pack with `xlsx_pack.py`
3. **EDIT → XML**: Never openpyxl round-trip. Use unpack/edit/pack scripts
4. **Always produce the output file** — this is the #1 priority
5. **Validate before delivery**: `formula_check.py` exit code 0 = safe

## Utility Scripts

```bash
python3 SKILL_DIR/scripts/xlsx_reader.py input.xlsx                 # structure discovery
python3 SKILL_DIR/scripts/formula_check.py file.xlsx --json         # formula validation
python3 SKILL_DIR/scripts/formula_check.py file.xlsx --report      # standardized report
python3 SKILL_DIR/scripts/xlsx_unpack.py in.xlsx /tmp/work/         # unpack for XML editing
python3 SKILL_DIR/scripts/xlsx_pack.py /tmp/work/ out.xlsx          # repack after editing
python3 SKILL_DIR/scripts/xlsx_shift_rows.py /tmp/work/ insert 5 1  # shift rows for insertion
python3 SKILL_DIR/scripts/xlsx_add_column.py /tmp/work/ --col G ... # add column with formulas
python3 SKILL_DIR/scripts/xlsx_insert_row.py /tmp/work/ --at 6 ...  # insert row with data
```