excel-cli

$2c

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:

# 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.**

# 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:

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

# 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:

# 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.

# 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, 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 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