Excel Automation with excelcli

Workflow Checklist

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)

Rule 1: NEVER Ask Clarifying Questions

Execute commands to discover the answer instead:

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: Test M code without persisting (catches errors early)
excelcli -q powerquery evaluate --session 1 --m-code-file query.m

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

# Step 3: Load data to destination
excelcli -q powerquery refresh --session 1 --query-name Q1

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. Set manual mode
excelcli -q calculationmode set-mode --session 1 --mode manual

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

# 3. Recalculate once at end
excelcli -q calculationmode calculate --session 1 --scope workbook

# 4. Restore automatic mode
excelcli -q calculationmode set-mode --session 1 --mode automatic

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

chart

Chart lifecycle – create, read, move, and delete embedded charts. CRITICAL – AVOID OVERLAPPING DATA: 1. Check used range first with range get-used-range 2. Position chart BELOW or to the RIGHT of data 3. NEVER place charts at default position (0,0) – it overlaps data! POSITIONING: left/top in points (72 points = 1 inch). Use fit-to-range to position chart within a cell range like ‘F2:K15’. 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

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

conditionalformat

Conditional formatting – visual rules based on cell values. TYPES: cellValue (requires operatorType+formula1), expression (formula only). FORMAT: interiorColor/fontColor as #RRGGBB, fontBold/Italic, borderStyle/Color. OPERATORS: equal, notEqual, greater, less, greaterEqual, lessEqual, between, notBetween. For ‘between’ and ‘notBetween’, both formula1 and formula2 are required.

Actions: add-rule, clear-rules

connection

Data connections (OLEDB, ODBC, ODC import). TEXT/WEB/CSV: Use powerquery instead. Power Query connections auto-redirect to powerquery. TIMEOUT: 5 min auto-timeout for refresh/load-to.

Actions: list, view, create, refresh, delete, load-to, get-properties, set-properties, test

datamodel

Data Model (Power Pivot) – DAX measures and table management. CRITICAL: WORKSHEET TABLES AND DATA MODEL ARE SEPARATE! – After table append changes, Data Model still has OLD data – MUST call refresh to sync changes – Power Query refresh auto-syncs (no manual refresh needed) PREREQUISITE: Tables must be added to the Data Model first. Use table add-to-datamodel for worksheet tables, or powerquery to import and load data directly to the Data Model. DAX MEASURES: – Create with DAX formulas like ‘SUM(Sales[Amount])’ – DAX formulas are auto-formatted on CREATE/UPDATE via Dax.Formatter (SQLBI) – Read operations return raw DAX as stored DAX EVALUATE QUERIES: – Use evaluate to execute DAX EVALUATE queries against the Data Model – Returns tabular results from queries like ‘EVALUATE TableName’ – Supports complex DAX: SUMMARIZE, FILTER, CALCULATETABLE, TOPN, etc. DMV (DYNAMIC MANAGEMENT VIEW) QUERIES: – Use execute-dmv to query Data Model metadata via SQL-like syntax – Syntax: SELECT * FROM $SYSTEM.SchemaRowset (ONLY SELECT * supported) – Use DISCOVER_SCHEMA_ROWSETS to list all available DMVs Use datamodelrel for relationships between tables.

Actions: list-tables, list-columns, read-table, read-info, list-measures, read, delete-measure, delete-table, rename-table, refresh, create-measure, update-measure, evaluate, execute-dmv

datamodelrel

Data Model relationships – link tables for cross-table DAX calculations. CRITICAL: Deleting or recreating tables removes ALL their relationships. Use list-relationships before table operations to backup, then recreate relationships after schema changes. RELATIONSHIP REQUIREMENTS: – Both tables must exist in the Data Model first – Columns must have compatible data types – fromTable/fromColumn = many-side (detail table, foreign key) – toTable/toColumn = one-side (lookup table, primary key) ACTIVE VS INACTIVE: – Only ONE active relationship can exist between two tables – Use active=false when creating alternative paths – DAX USERELATIONSHIP() activates inactive relationships

Actions: list-relationships, read-relationship, create-relationship, update-relationship, delete-relationship

unknown

Diagnostic commands for testing CLI/MCP infrastructure without Excel. These commands validate parameter parsing, routing, JSON serialization, and error handling — no Excel COM session needed.

Actions: ping, echo, validate-params

namedrange

Named ranges for formulas/parameters. CREATE/UPDATE: value is cell reference (e.g., ‘Sheet1!$A$1’). WRITE: value is data to store. TIP: range(rangeAddress=namedRangeName) for bulk data read/write.

Actions: list, write, read, update, create, delete

pivottable

PivotTable lifecycle management: create from various sources, list, read details, refresh, and delete. Use pivottablefield for field operations, pivottablecalc for calculated fields and layout. BEST PRACTICE: Use ‘list’ before creating. Prefer ‘refresh’ or field modifications over delete+recreate. Delete+recreate loses field configurations, filters, sorting, and custom layouts. REFRESH: Call ‘refresh’ after configuring fields with pivottablefield to update the visual display. This is especially important for OLAP/Data Model PivotTables where field operations are structural only and don’t automatically trigger a visual refresh. CREATE OPTIONS: – ‘create-from-range’: Use source sheet and range address for data range – ‘create-from-table’: Use an Excel Table (ListObject) as source – ‘create-from-datamodel’: Use a Power Pivot Data Model table as source

Actions: list, read, create-from-range, create-from-table, create-from-datamodel, delete, refresh

pivottablecalc

PivotTable calculated fields/members, layout configuration, and data extraction. Use pivottable for lifecycle, pivottablefield for field placement. CALCULATED FIELDS (for regular PivotTables): – Create custom fields using formulas like ‘=Revenue-Cost’ or ‘=Quantity*UnitPrice’ – Can reference existing fields by name – After creating, use pivottablefield add-value-field to add to Values area – For complex multi-table calculations, prefer DAX measures with datamodel CALCULATED MEMBERS (for OLAP/Data Model PivotTables only): – Create using MDX expressions – Member types: Member, Set, Measure LAYOUT OPTIONS: – 0 = Compact (default, fields in single column) – 1 = Tabular (each field in separate column – best for export/analysis) – 2 = Outline (hierarchical with expand/collapse)

Actions: get-data, create-calculated-field, list-calculated-fields, delete-calculated-field, list-calculated-members, create-calculated-member, delete-calculated-member, set-layout, set-subtotals, set-grand-totals

pivottablefield

PivotTable field management: add/remove/configure fields, filtering, sorting, and grouping. Use pivottable for lifecycle, pivottablecalc for calculated fields and layout. IMPORTANT: Field operations modify structure only. Call pivottable refresh after configuring fields to update the visual display, especially for OLAP/Data Model PivotTables. FIELD AREAS: – Row fields: Group data by categories (add-row-field) – Column fields: Create column headers (add-column-field) – Value fields: Aggregate numeric data with Sum, Count, Average, etc. (add-value-field) – Filter fields: Add report-level filters (add-filter-field) AGGREGATION FUNCTIONS: Sum, Count, Average, Max, Min, Product, CountNumbers, StdDev, StdDevP, Var, VarP GROUPING: – Date fields: Group by Days, Months, Quarters, Years (group-by-date) – Numeric fields: Group by ranges with start/end/interval (group-by-numeric) NUMBER FORMAT: Use US format codes like ‘#,##0.00’ for currency or ‘0.00%’ for percentages.

Actions: list-fields, add-row-field, add-column-field, add-value-field, add-filter-field, remove-field, set-field-function, set-field-name, set-field-format, set-field-filter, sort-field, group-by-date, group-by-numeric

powerquery

Power Query M code and data loading. TEST-FIRST DEVELOPMENT WORKFLOW (BEST PRACTICE): 1. evaluate – Test M code WITHOUT persisting (catches syntax errors, validates sources, shows data preview) 2. create/update – Store VALIDATED query in workbook 3. refresh/load-to – Load data to destination Skip evaluate only for trivial literal tables. IF CREATE/UPDATE FAILS: Use evaluate to get the actual M engine error message, fix code, retry. DATETIME COLUMNS: Always include Table.TransformColumnTypes() in M code to set column types explicitly. Without explicit types, dates may be stored as numbers and Data Model relationships may fail. DESTINATIONS: ‘worksheet’ (default), ‘data-model’ (for DAX), ‘both’, ‘connection-only’. Use ‘data-model’ to load to Power Pivot, then use datamodel to create DAX measures. TARGET CELL: targetCellAddress places tables without clearing sheet. TIMEOUT: 5 min auto-timeout for refresh/load. For network queries, use timeout=120 or higher. timeout=0 is INVALID – must be greater than zero.

Actions: list, view, refresh, get-load-config, delete, create, update, load-to, refresh-all, rename, unload, evaluate

range

Core range operations: get/set values and formulas, copy ranges, clear content, and discover data regions. Use rangeedit for insert/delete/find/sort. Use rangeformat for styling/validation. Use rangelink for hyperlinks and cell protection. Calculation mode and explicit recalculation are handled by calculationmode. BEST PRACTICE: Use ‘get-values’ to check existing data before overwriting. Use ‘clear-contents’ (not ‘clear-all’) to preserve cell formatting when clearing data. set-values preserves existing formatting; use set-number-format after if format change needed. DATA FORMAT: values and formulas are 2D JSON arrays representing rows and columns. Example: [[row1col1, row1col2], [row2col1, row2col2]] Single cell returns [[value]] (always 2D). REQUIRED PARAMETERS: – sheetName + rangeAddress for cell operations (e.g., sheetName=’Sheet1′, rangeAddress=’A1:D10′) – For named ranges, use sheetName=” (empty string) and rangeAddress=’MyNamedRange’ COPY OPERATIONS: Specify source and target sheet/range for copy operations. NUMBER FORMATS: Use US locale format codes (e.g., ‘#,##0.00’, ‘mm/dd/yyyy’, ‘0.00%’).

Actions: get-values, set-values, get-formulas, set-formulas, clear-all, clear-contents, clear-formats, copy, copy-values, copy-formulas, get-number-formats, set-number-format, set-number-formats, get-used-range, get-current-region, get-info

rangeedit

Range editing operations: insert/delete cells, rows, and columns; find/replace text; sort data. Use range for values/formulas/copy/clear operations. INSERT/DELETE CELLS: Specify shift direction to control how surrounding cells move. – Insert: ‘Down’ or ‘Right’ – Delete: ‘Up’ or ‘Left’ INSERT/DELETE ROWS: Use row range like ‘5:10’ to insert/delete rows 5-10. INSERT/DELETE COLUMNS: Use column range like ‘B:D’ to insert/delete columns B-D. FIND/REPLACE: Search within the specified range with optional case/cell matching. – Find returns up to 10 matching cell addresses with total count. – Replace modifies all matches by default. SORT: Specify sortColumns as array of {columnIndex: 1, ascending: true} objects. Column indices are 1-based relative to the range.

Actions: insert-cells, delete-cells, insert-rows, delete-rows, insert-columns, delete-columns, find, replace, sort

rangeformat

Range formatting operations: apply styles, set fonts/colors/borders, add data validation, merge cells, auto-fit dimensions. Use range for values/formulas/copy/clear operations. STYLES: Use built-in style names like ‘Heading 1’, ‘Good’, ‘Bad’, ‘Currency’, ‘Percent’, etc. For consistent, professional formatting, prefer set-style with built-in styles over format-range. FONT/COLOR FORMATTING: Specify individual formatting properties: – Colors as hex ‘#RRGGBB’ (e.g., ‘#FF0000’ for red, ‘#00FF00’ for green) – Font sizes as points (e.g., 12, 14, 16) – Alignment: ‘left’, ‘center’, ‘right’ (horizontal), ‘top’, ‘middle’, ‘bottom’ (vertical) DATA VALIDATION: Restrict cell input with validation rules: – Types: ‘list’, ‘whole’, ‘decimal’, ‘date’, ‘time’, ‘textLength’, ‘custom’ – For list validation, formula1 is the list source (e.g., ‘=$A$1:$A$10’ or ‘”Option1,Option2,Option3″‘) – Operators: ‘between’, ‘notBetween’, ‘equal’, ‘notEqual’, ‘greaterThan’, ‘lessThan’, ‘greaterThanOrEqual’, ‘lessThanOrEqual’ MERGE: Combines cells into one. Only top-left cell value is preserved.

Actions: set-style, get-style, format-range, validate-range, get-validation, remove-validation, auto-fit-columns, auto-fit-rows, merge-cells, unmerge-cells, get-merge-info

rangelink

Hyperlink and cell protection operations for Excel ranges. Use range for values/formulas, rangeformat for styling. HYPERLINKS: – ‘add-hyperlink’: Add a clickable hyperlink to a cell (URL can be web, file, or mailto) – ‘remove-hyperlink’: Remove hyperlink(s) from cells while keeping the cell content – ‘list-hyperlinks’: Get all hyperlinks on a worksheet – ‘get-hyperlink’: Get hyperlink details for a specific cell CELL PROTECTION: – ‘set-cell-lock’: Lock or unlock cells (only effective when sheet protection is enabled) – ‘get-cell-lock’: Check if cells are locked Note: Cell locking only takes effect when the worksheet is protected.

Actions: add-hyperlink, remove-hyperlink, list-hyperlinks, get-hyperlink, set-cell-lock, get-cell-lock

worksheet

Worksheet lifecycle management: create, rename, copy, delete, move, list sheets. Use range for data operations. Use sheetstyle for tab colors and visibility. ATOMIC OPERATIONS: ‘copy-to-file’ and ‘move-to-file’ don’t require a session – they open/close files automatically. POSITIONING: For ‘move’, ‘copy-to-file’, ‘move-to-file’ – use ‘before’ OR ‘after’ (not both) to position the sheet relative to another. If neither specified, moves to end.

Actions: list, create, rename, copy, delete, move, copy-to-file, move-to-file

worksheetstyle

Worksheet styling operations for tab colors and visibility. Use sheet for lifecycle operations (create, rename, copy, delete, move). TAB COLORS: Use RGB values (0-255 each) to set custom tab colors for visual organization. VISIBILITY LEVELS: – ‘visible’: Normal visible sheet – ‘hidden’: Hidden but accessible via Format > Sheet > Unhide – ‘veryhidden’: Only accessible via VBA (protection against casual unhiding)

Actions: set-tab-color, get-tab-color, clear-tab-color, set-visibility, get-visibility, show, hide, very-hide

slicer

Slicer visual filters for PivotTables and Excel Tables. PIVOTTABLE SLICERS: create-slicer, list-slicers, set-slicer-selection, delete-slicer. TABLE SLICERS: create-table-slicer, list-table-slicers, set-table-slicer-selection, delete-table-slicer. NAMING: Auto-generate descriptive names like {FieldName}Slicer (e.g., RegionSlicer). SELECTION: selectedItems as list of strings. Empty list clears filter (shows all items). Set clearFirst=false to add to existing selection.

Actions: create-slicer, list-slicers, set-slicer-selection, delete-slicer, create-table-slicer, list-table-slicers, set-table-slicer-selection, delete-table-slicer

table

Excel Tables (ListObjects) – lifecycle and data operations. Tables provide structured references, automatic formatting, and Data Model integration. BEST PRACTICE: Use ‘list’ to check existing tables before creating. Prefer ‘append’/’resize’/’rename’ over delete+recreate to preserve references. WARNING: Deleting tables used as PivotTable sources or in Data Model relationships will break those objects. DATA MODEL WORKFLOW: To analyze worksheet data with DAX/Power Pivot: 1. Create or identify an Excel Table on a worksheet 2. Use ‘add-to-datamodel’ to add the table to Power Pivot 3. Then use datamodel to create DAX measures on it DAX-BACKED TABLES: Create tables populated by DAX EVALUATE queries: – ‘create-from-dax’: Create a new table backed by a DAX query (e.g., SUMMARIZE, FILTER) – ‘update-dax’: Update the DAX query for an existing DAX-backed table – ‘get-dax’: Get the DAX query info for a table (check if it’s DAX-backed) Related: tablecolumn (filter/sort/columns), datamodel (DAX measures, evaluate queries)

Actions: list, create, rename, delete, read, resize, toggle-totals, set-column-total, append, get-data, set-style, add-to-data-model, create-from-dax, update-dax, get-dax

tablecolumn

Table column, filtering, and sorting operations for Excel Tables (ListObjects). Use table for table-level lifecycle and data operations. FILTERING: – ‘apply-filter’: Simple criteria filter (e.g., “>100”, “=Active”, “<>Closed”) – ‘apply-filter-values’: Filter by exact values (provide list of values to include) – ‘clear-filters’: Remove all active filters – ‘get-filters’: See current filter state SORTING: – ‘sort’: Single column sort (ascending/descending) – ‘sort-multi’: Multi-column sort (provide list of {columnName, ascending} objects) COLUMN MANAGEMENT: – ‘add-column’/’remove-column’/’rename-column’: Modify table structure NUMBER FORMATS: Use US locale format codes (e.g., ‘#,##0.00’, ‘0%’, ‘yyyy-mm-dd’)

Actions: apply-filter, apply-filter-values, clear-filters, get-filters, add-column, remove-column, rename-column, get-structured-reference, sort, sort-multi, get-column-number-format, set-column-number-format

vba

VBA scripts (requires .xlsm and VBA trust enabled). PREREQUISITES: – Workbook must be macro-enabled (.xlsm) – VBA trust must be enabled for automation RUN: procedureName format is ‘Module.Procedure’ (e.g., ‘Module1.MySub’).

Actions: list, view, import, update, run, delete

Common Pitfalls

–values-file Must Be an Existing File

--values-file expects a path to an existing JSON or CSV file on disk. Do NOT pass inline JSON as the value — the CLI will look for a file at that path and fail with “File not found”. If you don’t have a file, use --values with inline JSON instead.

–timeout Must Be Greater Than Zero

When using --timeout, the value must be a positive integer (seconds). --timeout 0 is invalid and will error. Omit --timeout entirely to use the default (300 seconds for most operations).

Power Query Operations Are Slow

powerquery create, powerquery refresh, and powerquery evaluate may take 30+ seconds depending on data volume. Either omit --timeout (uses 5-minute default) or set a generous value like --timeout 120.

JSON Values Format

--values takes a 2D JSON array wrapped in single quotes:

# CORRECT: 2D array with single-quote wrapper
--values '[["Name","Age"],["Alice",30],["Bob",25]]'

# WRONG: Not a 2D array
--values '["Alice",30]'

# WRONG: Object instead of array
--values '{"Name":"Alice","Age":30}'

Reference Documentation

• @references/behavioral-rules.md – Core execution rules and LLM guidelines
• @references/anti-patterns.md – Common mistakes to avoid
• @references/workflows.md – Data Model constraints and patterns

Installation

dotnet tool install --global Sbroenne.ExcelMcp.CLI