excel-cli - Claude MCP Skill

# Excel Automation with excelcli

## Preconditions

- Windows host with Microsoft Excel installed (2016+)
- Uses COM interop — does NOT work on macOS or Linux
- GitHub Copilot `excel-cli` plugin auto-downloads the latest Windows runtime on first use
- Direct skill-only installs require `excelcli.exe` on PATH

## Workflow Checklist

| Step | Command | When |
|------|---------|------|
| 1. Session | `session create/open` | Always first |
| 2. Sheets | `worksheet create/rename` | If needed |
| 3. Write data | See below | If writing values |
| 4. Save & close | `session close --save` | Always last |

> **10+ commands?** Use `excelcli -q batch --input commands.json` — sends all commands in one process with automatic session management. See Rule 8.

**Writing Data (Step 3):**
- `--values` takes a JSON 2D array string: `--values '[["Header1","Header2"],[1,2]]'`
- Write **one row at a time** for reliability: `--range-address A1:B1 --values '[["Name","Age"]]'`
- Strings MUST be double-quoted in JSON: `"text"`. Numbers are bare: `42`
- Always wrap the entire JSON value in single quotes to protect special characters

## CRITICAL RULES (MUST FOLLOW)

> **⚡ Building dashboards or bulk operations?** Skip to **Rule 8: Batch Mode** — it eliminates per-command process overhead and auto-manages session IDs.

### Rule 1: NEVER Ask Clarifying Questions

Execute commands to discover the answer instead:

| DON'T ASK | DO THIS INSTEAD |
|-----------|-----------------|
| "Which file should I use?" | `excelcli -q session list` |
| "What table should I use?" | `excelcli -q table list --session <id>` |
| "Which sheet has the data?" | `excelcli -q worksheet list --session <id>` |

**You have commands to answer your own questions. USE THEM.**

### Rule 2: Always End With a Text Summary

**NEVER end your turn with only a command execution.** After completing all operations, always provide a brief text message confirming what was done. Silent command-only responses are incomplete.

### Rule 3: Session Lifecycle

**Creating vs Opening Files:**
```powershell
# NEW file - use session create
excelcli -q session create C:\path\newfile.xlsx  # Creates file + returns session ID

# EXISTING file - use session open
excelcli -q session open C:\path\existing.xlsx   # Opens file + returns session ID
```

**CRITICAL: Use `session create` for new files. `session open` on non-existent files will fail!**

**CRITICAL: ALWAYS use the session ID returned by `session create` or `session open` in subsequent commands. NEVER guess or hardcode session IDs. The session ID is in the JSON output (e.g., `{"sessionId":"abc123"}`). Parse it and use it.**

```powershell
# Example: capture session ID from output, then use it
excelcli -q session create C:\path\file.xlsx     # Returns JSON with sessionId
excelcli -q range set-values --session <returned-session-id> ...
excelcli -q session close --session <returned-session-id> --save
```

**Unclosed sessions leave Excel processes running, locking files.**

### Rule 4: Data Model Prerequisites

DAX operations require tables in the Data Model:

```powershell
excelcli -q table add-to-data-model --session <id> --table-name Sales  # Step 1
excelcli -q datamodel create-measure --session <id> ...               # Step 2 - NOW works
```

### Rule 5: Power Query Development Lifecycle

**BEST PRACTICE: Test M code before creating permanent queries**

```powershell
# Step 1: Create/open a session and capture the session ID
$session = excelcli -q session create C:\path\file.xlsx | ConvertFrom-Json
$sessionId = $session.sessionId

# Step 2: Test M code without persisting (catches errors early)
excelcli -q powerquery evaluate --session $sessionId --m-code-file query.m

# Step 3: Create permanent query with validated code
excelcli -q powerquery create --session $sessionId --query-name Q1 --m-code-file query.m

# Step 4: Load data to destination
excelcli -q powerquery refresh --session $sessionId --query-name Q1

# Step 5: Close session
excelcli -q session close --session $sessionId --save
```

### Rule 6: Report File Errors Immediately

If you see "File not found" or "Path not found" - STOP and report to user. Don't retry.

### Rule 7: Use Calculation Mode for Bulk Writes

When writing many values/formulas (10+ cells), disable auto-recalc for performance:

```powershell
# 1. Create/open a session and capture the session ID
$session = excelcli -q session create C:\path\file.xlsx | ConvertFrom-Json
$sessionId = $session.sessionId

# 2. Set manual mode
excelcli -q calculationmode set-mode --session $sessionId --mode manual

# 3. Write data row by row for reliability
excelcli -q range set-values --session $sessionId --sheet-name Sheet1 --range-address A1:B1 --values '[["Name","Amount"]]'
excelcli -q range set-values --session $sessionId --sheet-name Sheet1 --range-address A2:B2 --values '[["Salary",5000]]'

# 4. Recalculate once at end
excelcli -q calculationmode calculate --session $sessionId --scope workbook

# 5. Restore automatic mode
excelcli -q calculationmode set-mode --session $sessionId --mode automatic

# 6. Close session
excelcli -q session close --session $sessionId --save
```

### Rule 8: Use Batch Mode for Bulk Operations (10+ commands)

When executing 10+ commands on the same file, use `excelcli batch` to send all commands in a single process launch. This avoids per-process startup overhead and terminal buffer saturation.

```powershell
# Create a JSON file with all commands
@'
[
  {"command": "session.open", "args": {"filePath": "C:\\path\\file.xlsx"}},
  {"command": "range.set-values", "args": {"sheetName": "Sheet1", "rangeAddress": "A1", "values": [["Hello"]]}},
  {"command": "range.set-values", "args": {"sheetName": "Sheet1", "rangeAddress": "A2", "values": [["World"]]}},
  {"command": "session.close", "args": {"save": true}}
]
'@ | Set-Content commands.json

# Execute all commands at once
excelcli -q batch --input commands.json
```

**Key features:**
- **Session auto-capture**: `session.open`/`create` result sessionId auto-injected into subsequent commands — no need to parse and pass session IDs
- **NDJSON output**: One JSON result per line: `{"index": 0, "command": "...", "success": true, "result": {...}}`
- **`--stop-on-error`**: Exit on first failure (default: continue all)
- **`--session <id>`**: Pre-set session ID for all commands (skip session.open)

**Input formats:**
- JSON array from file: `excelcli -q batch --input commands.json`
- NDJSON from stdin: `Get-Content commands.ndjson | excelcli -q batch`

## CLI Command Reference

**Full reference:** See [CLI command reference and common pitfalls](./references/cli-commands.md), or run `excelcli <command> --help` for live help from the installed runtime.

**Syntax rule:** CLI commands use `excelcli -q <command> <action> --session <id> --kebab-case-flags ...`. Do not use MCP call syntax such as `range(action: ...)`, snake_case parameters, or underscore tool names. The CLI command names remove MCP underscores: `calculation_mode` becomes `calculationmode`, `range_format` becomes `rangeformat`, `chart_config` becomes `chartconfig`, and `data_model` becomes `datamodel`.

Available command groups:

`session`, `batch`, `service`, `calculationmode`, `chart`, `chartconfig`, `conditionalformat`, `connection`, `datamodel`, `datamodelrelationship`, `diag`, `namedrange`, `pivottable`, `pivottablecalc`, `pivottablefield`, `powerquery`, `range`, `rangeedit`, `rangeformat`, `rangelink`, `screenshot`, `sheet`, `worksheetstyle`, `slicer`, `table`, `tablecolumn`, `vba`, `window`

## Common Pitfalls

See [CLI command reference and common pitfalls](./references/cli-commands.md#common-pitfalls) for examples. Key issues:

- `--values-file` expects a path to an existing file; use `--values` for inline JSON.
- `--timeout` must be a positive integer; omit it to use the default timeout.
- `--values` takes a 2D JSON array such as `'[["Name","Age"],["Alice",30]]'`.
- List parameters such as `--selected-items` require JSON arrays.
- Power Query operations can take 30+ seconds; use generous timeouts.

## Reference Documentation

- [CLI command reference and common pitfalls](./references/cli-commands.md)