Excel Automation with excelcli

Preconditions

• Windows host with Microsoft Excel installed (2016+)
• Uses COM interop — does NOT work on macOS or Linux
• Install: Download excelcli.exe from https://github.com/sbroenne/mcp-server-excel/releases/latest and add to 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:

# 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

Auto-generated from excelcli --help. Use these exact parameter names.

calculationmode

Control Excel recalculation (automatic vs manual). Set manual mode before bulk writes for faster performance, then recalculate once at the end.

Actions: get-mode, set-mode, calculate

Parameter	Description
--mode	Target calculation mode (required for: set-mode)
--scope	Scope: Workbook, Sheet, or Range (required for: calculate)
--sheet-name	Sheet name (required for Sheet/Range scope)
--range-address	Range address (required for Range scope)

chart

Chart lifecycle - create, read, move, and delete embedded charts. POSITIONING (choose one): - targetRange (PREFERRED): Cell range like 'F2:K15' — positions chart within cells, no point math needed. - left/top: Manual positioning in points (72 points = 1 inch). - Neither: Auto-positions chart below all existing content (used range + other charts). COLLISION DETECTION: All create/move/fit-to-range operations automatically check for overlaps with data and other charts. Warnings are returned in the result message if collisions are detected. Always verify layout with screenshot(capture-sheet) after creating charts. CHART TYPES: 70+ types available including Column, Line, Pie, Bar, Area, XY Scatter. CREATE OPTIONS: - create-from-range: Create from cell range (e.g., 'A1:D10') - create-from-table: Create from Excel Table (uses table's data range) - create-from-pivottable: Create linked PivotChart Use chartconfig for series, titles, legends, styles, placement mode.

Actions: list, read, create-from-range, create-from-table, create-from-pivottable, delete, move, fit-to-range

Parameter	Description
--chart-name	Name of the chart (or shape name) (required for: read, delete, move, fit-to-range)
--sheet-name	Target worksheet name (required for: create-from-range, create-from-table, create-from-pivottable, fit-to-range)
--source-range-address	Data range for the chart (e.g., A1:D10) (required for: create-from-range)
--chart-type	Type of chart to create (required for: create-from-range, create-from-table, create-from-pivottable)
--left	Left position in points from worksheet edge
--top	Top position in points from worksheet edge
--width	Chart width in points
--height	Chart height in points
--target-range	Cell range to position chart within (e.g., 'F2:K15'). PREFERRED over left/top. When set, left/top are ignored.
--table-name	Name of the Excel Table (required for: create-from-table)
--pivot-table-name	Name of the source PivotTable (required for: create-from-pivottable)
--range-address	Range to fit the chart to (e.g., A1:D10) (required for: fit-to-range)

chartconfig

Chart configuration - data source, series, type, title, axis labels, legend, and styling. SERIES MANAGEMENT: - add-series: Add data series with valuesRange (required) and optional categoryRange - remove-series: Remove series by 1-based index - set-source-range: Replace entire chart data source TITLES AND LABELS: - set-title: Set chart title (empty string hides title) - set-axis-title: Set axis labels (Category, Value, CategorySecondary, ValueSecondary) CHART STYLES: 1-48 (built-in Excel styles with different color schemes) DATA LABELS: Show values, percentages, series/category names. Positions: Center, InsideEnd, InsideBase, OutsideEnd, BestFit. TRENDLINES: Linear, Exponential, Logarithmic, Polynomial (order 2-6), Power, MovingAverage. PLACEMENT MODE: - 1: Move and size with cells - 2: Move but don't size with cells - 3: Don't move or size with cells (free floating) Use chart for lifecycle operations (create, delete, move, fit-to-range).

Actions: set-source-range, add-series, remove-series, set-chart-type, set-title, set-axis-title, get-axis-number-format, set-axis-number-format, show-legend, set-style, set-placement, set-data-labels, get-axis-scale, set-axis-scale, get-gridlines, set-gridlines, set-series-format, list-trendlines, add-trendline, delete-trendline, set-trendline

Parameter	Description
--chart-name	Name of the chart (required)
--source-range	New data source range (e.g., Sheet1!A1:D10) (required for: set-source-range)
--series-name	Display name for the series (required for: add-series)
--values-range	Range containing series values (e.g., B2:B10) (required for: add-series)
--category-range	Optional range for category labels (e.g., A2:A10)
--series-index	1-based index of the series to remove (required for: remove-series, set-series-format, list-trendlines, add-trendline, delete-trendline, set-trendline)
--chart-type	New chart type to apply (required for: set-chart-type)
--title	Title text to display (required for: set-title, set-axis-title)
--axis	Which axis to set title for (Category, Value, SeriesAxis) (required for: set-axis-title, get-axis-number-format, set-axis-number-format, get-axis-scale, set-axis-scale, set-gridlines)
--number-format	Excel number format code (e.g., "$#,##0", "0.00%") (required for: set-axis-number-format)
--visible	True to show legend, false to hide (required for: show-legend)
--legend-position	Optional position for the legend
--style-id	Excel chart style ID (1-48 for most chart types) (required for: set-style)
--placement	Placement mode: 1=MoveAndSize, 2=Move, 3=FreeFloating (required for: set-placement)
--show-value	Show data values on labels
--show-percentage	Show percentage values. Only meaningful for pie and doughnut chart types; setting to true on other chart types has no visual effect.
--show-series-name	Show series name on labels
--show-category-name	Show category name on labels
--show-bubble-size	Show bubble size (bubble charts)
--separator	Separator string between label components
--label-position	Position of data labels relative to data points
--minimum-scale	Minimum axis value (null for auto)
--maximum-scale	Maximum axis value (null for auto)
--major-unit	Major gridline interval (null for auto)
--minor-unit	Minor gridline interval (null for auto)
--show-major	Show major gridlines (null to keep current)
--show-minor	Show minor gridlines (null to keep current)
--marker-style	Marker shape style
--marker-size	Marker size in points (2-72)
--marker-background-color	Marker fill color (#RRGGBB)
--marker-foreground-color	Marker border color (#RRGGBB)
--invert-if-negative	Invert colors for negative values
--trendline-type	Type of trendline (Linear, Exponential, etc.) (required for: add-trendline)