Skip to content

Latest commit

 

History

History
506 lines (394 loc) · 15.2 KB

FEATURES.md

File metadata and controls

506 lines (394 loc) · 15.2 KB

ExcelMcp - Complete Feature Reference

25 specialized tools with 225 operations for comprehensive Excel automation

📁 File Operations (6 operations)

  • List Sessions: View all active Excel sessions
  • Open: Open workbook and create session (returns session ID for all subsequent operations). IRM/AIP-protected files are automatically detected and opened read-only with Excel visible for credential authentication — no extra parameters needed.
  • Close: Close session with optional save
  • Close Workbook: Close workbook without closing Excel
  • Create Empty: Create new .xlsx or .xlsm workbook
  • Test: Verify workbook can be opened and is accessible. Returns isIrmProtected flag for IRM/AIP-protected files.

🧮 Calculation Mode (3 operations)

  • Get Mode: Query current calculation mode and calculation state
  • Set Mode: Switch between automatic, manual, and semi-automatic modes
  • Calculate: Explicitly recalculate workbook, sheet, or range

🔄 Power Query & M Code (12 operations)

Atomic Operations - Single-call workflows:

  • List: List all Power Query queries in workbook
  • View: View the M code of a Power Query
  • Create: Import + load in one operation (atomic workflow) with automatic formatting
  • Update: Update M code with automatic formatting and auto-refresh
  • Rename: Rename a Power Query (trim + case-insensitive uniqueness check)
  • Refresh: Refresh a Power Query with timeout detection
  • Refresh All: Batch refresh all queries in workbook
  • Load To: Configure load destination and refresh (atomic)
  • Get Load Config: Get current load configuration
  • Unload: Remove data from all destinations (keeps query definition)
  • Delete: Remove Power Query from workbook
  • Evaluate: Execute M code directly and return results (without creating a permanent query)

Automatic M-Code Formatting: M code is automatically formatted on write operations (Create, Update) using the powerqueryformatter.com API (by mogularGmbH, MIT License). Read operations return M code as stored in Excel. Formatting adds ~100-500ms network latency but dramatically improves readability with proper indentation, spacing, and line breaks. Graceful fallback returns original M code if formatting fails.

📊 Data Model & DAX (Power Pivot) (19 operations)

  • List Tables: Discover all tables in the Data Model
  • Read Table: Get specific table information
  • Rename Table: Rename a Data Model table (best-effort via Power Query; returns clear error if not supported)
  • List Columns: List columns for a table
  • List Measures: List all DAX measures with formula previews
  • Read Info: Get comprehensive model information
  • Create Measure: Create new DAX measure with automatic formatting (format types: Currency, Percentage, Decimal, General)
  • Update Measure: Modify existing measure with automatic formatting
  • Delete Measure: Remove measure from model
  • Delete Table: Remove table from Data Model
  • List Relationships: View all table relationships
  • Read Relationship: Get specific relationship info
  • Create Relationship: Create relationship between tables
  • Update Relationship: Modify relationship (toggle active/inactive)
  • Delete Relationship: Remove relationship
  • Refresh: Refresh entire Data Model
  • List Workbook Connections: List Power Query sources available for integration
  • Evaluate: Execute DAX EVALUATE queries and return tabular results (for ad-hoc analysis)
  • Execute DMV: Execute SQL-like DMV (Dynamic Management View) queries for metadata discovery

Automatic DAX Formatting: DAX formulas are automatically formatted on write operations (CreateMeasure, UpdateMeasure) using the official Dax.Formatter library (SQLBI). Read operations return raw DAX as stored in Excel. Formatting adds ~100-500ms network latency but dramatically improves readability. Graceful fallback returns original DAX if formatting fails.

Note: DAX calculated columns not supported - use Excel UI for calculated columns

🎨 Excel Tables (ListObjects) (27 operations)

Lifecycle:

  • List, read, create, rename, resize, delete tables

Styling & Formatting:

  • Apply table styles
  • Toggle totals row
  • Set column totals

Data Operations:

  • Append rows
  • Get table data (with optional visible-only filtering)
  • Add to Data Model

DAX-Backed Tables:

  • Create from DAX (create Excel Table populated by DAX EVALUATE query)
  • Update DAX (change the DAX query of an existing DAX-backed table)
  • Get DAX (retrieve DAX query info from a table)

Filter Operations:

  • Apply filter (criteria)
  • Apply filter (values)
  • Clear filters
  • Get filter state

Column Management:

  • Add, remove, rename columns

Structured References:

  • Get structured reference (formula syntax for table columns/ranges)

Sorting:

  • Single-column sort
  • Multi-column sort (up to 3 levels)

Number Formatting:

  • Get column number formats
  • Set column number formats

📈 PivotTables (30 operations)

Creation:

  • Create from range
  • Create from Excel Table
  • Create from Data Model

Field Management:

  • List all fields (row, column, value, filter areas)
  • Add row field, column field, value field, filter field
  • Remove field

Field Configuration:

  • Set field aggregation function (Sum, Average, Count, Min, Max, etc.)
  • Set custom field name
  • Set field number format
  • Set field filter criteria
  • Sort field (ascending/descending)

Calculated Fields (Regular PivotTables):

  • List calculated fields
  • Create calculated field
  • Delete calculated field

Calculated Members (OLAP/Data Model PivotTables):

  • List calculated members
  • Create calculated member
  • Delete calculated member

Layout & Formatting:

  • Set layout (table or outline)
  • Set subtotals display
  • Set grand totals display

Data Operations:

  • Get PivotTable data as 2D array
  • Refresh PivotTable

Lifecycle:

  • List PivotTables
  • Read PivotTable info
  • Delete PivotTable

📉 Charts (28 operations)

Creation:

  • Create from range
  • Create from PivotTable

Series Management:

  • Add series
  • Remove series
  • Update series data

Configuration:

  • Set data source range
  • Set chart type
  • Show/hide legend
  • Set style

Formatting:

  • Set chart title
  • Set axis title
  • Set axis number format
  • Get axis number format

Data Labels:

  • Configure data labels (show values, percentages, category names, etc.)
  • Set label position (Center, InsideEnd, OutsideEnd, etc.)
  • Apply to all series or specific series

Axis Scale:

  • Get axis scale settings
  • Set minimum/maximum scale
  • Set major/minor units

Gridlines:

  • Get gridlines configuration
  • Set major/minor gridlines visibility

Series Formatting:

  • Set marker style (Circle, Square, Diamond, Triangle, etc.)
  • Set marker size
  • Set marker colors

Trendlines:

  • Add trendline (Linear, Exponential, Logarithmic, Polynomial, Power, MovingAverage)
  • List trendlines on series
  • Delete trendline
  • Configure trendline (forecast forward/backward, display equation, display R²)

Placement & Positioning:

  • Set chart placement (move/size with cells options)
  • Fit to range (position and size to match a range)

Lifecycle:

  • List charts
  • Read chart info
  • Move chart (to different worksheet or new sheet)
  • Delete chart

📋 Ranges (42 operations)

Data Operations:

  • Get values
  • Set values
  • Get formulas
  • Set formulas
  • Clear all
  • Clear contents
  • Clear formats
  • Copy
  • Copy values
  • Copy formulas
  • Insert cells
  • Delete cells
  • Insert rows
  • Delete rows
  • Insert columns
  • Delete columns
  • Find
  • Replace
  • Sort

Discovery & Utilities:

  • Get used range
  • Get current region
  • Get range info (address, dimensions)

Hyperlinks:

  • Add hyperlink
  • Remove hyperlink
  • List hyperlinks
  • Get specific hyperlink

Number Formatting:

  • Get number formats (as 2D array)
  • Set number format (uniform)
  • Set number formats (individual)

Visual Formatting:

  • Get style
  • Set style (built-in Excel styles)
  • Format range (font, color, borders, alignment, orientation)

Data Validation:

  • Add validation rules (dropdowns, number/date/text rules)
  • Get validation info
  • Remove validation

Merge Operations:

  • Merge cells
  • Unmerge cells
  • Get merge info

Cell Protection:

  • Set cell lock status
  • Get cell lock status

Auto-Sizing:

  • Auto-fit columns
  • Auto-fit rows

📄 Worksheets (16 operations)

Lifecycle:

  • List worksheets
  • Create worksheet
  • Rename worksheet
  • Copy worksheet
  • Move worksheet
  • Delete worksheet

Cross-Workbook Operations:

  • Copy worksheet to file (atomic)
  • Move worksheet to file (atomic)

Tab Colors:

  • Set tab color (RGB)
  • Get tab color
  • Clear tab color

Visibility:

  • Show worksheet
  • Hide worksheet
  • Very hide worksheet (hidden from UI)
  • Get visibility status
  • Set visibility status

🔌 Data Connections (9 operations)

  • List: View all data connections
  • View: Get connection details
  • Create: Create OLEDB/ODBC connections (requires provider installed)
  • Test: Verify connection validity
  • Refresh: Refresh connection data
  • Delete: Remove connection
  • Load To: Load connection data to worksheet (when supported)
  • Get Properties: Get connection string and metadata
  • Set Properties: Update connection string, command text, and settings

Supported Types:

  • OLEDB (requires Microsoft.ACE.OLEDB.16.0 or similar)
  • ODBC (requires ODBC driver installed)
  • Power Query connections (atomic redirect to powerquery)

Automatic Fallback:

  • TEXT/WEB connections automatically redirect to powerquery for reliable imports

🏷️ Named Ranges (Parameters) (6 operations)

  • List: List all named ranges with references
  • Read: Get value of a named range
  • Write: Set value of a named range (ideal for parameter automation)
  • Create: Create new named range
  • Update: Modify existing named range
  • Delete: Remove named range

Use Cases:

  • Workbook parameter management without touching worksheets
  • Ideal for automation: update parameter → Power Query refreshes automatically

📝 VBA Macros (6 operations)

  • List: List all VBA modules and procedures
  • View: Display module code without exporting
  • Import: Add VBA module from file
  • Update: Modify existing VBA module
  • Delete: Remove VBA module
  • Run: Execute macro with optional parameters

Features:

  • Version control through file exports
  • Parameter passing to macros
  • Full module lifecycle management

�️ Slicers (8 operations)

PivotTable Slicers:

  • Create Slicer: Add slicer for PivotTable field with optional position
  • List Slicers: List all PivotTable slicers in workbook
  • Set Selection: Filter PivotTable by slicer selection (single or multi-select)
  • Delete Slicer: Remove PivotTable slicer

Table Slicers:

  • Create Table Slicer: Add slicer for Excel Table column
  • List Table Slicers: List all Table slicers in workbook
  • Set Table Selection: Filter Table by slicer selection
  • Delete Table Slicer: Remove Table slicer

Use Cases:

  • Interactive data filtering without modifying PivotTable/Table structure
  • Dashboard creation with visual filter controls
  • Multi-slicer filtering for complex data analysis

�🎨 Conditional Formatting (2 operations)

  • Add Rule: Create conditional formatting rules
    • Cell value comparisons (>, <, =, etc.)
    • Expression-based formulas (custom DAX/Excel formulas)
    • Color scales, data bars, icons
  • Clear Rules: Remove formatting from ranges

📸 Screenshot (2 operations)

  • Capture Range: Capture a specific range as a PNG image
  • Capture Sheet: Capture the entire used area of a worksheet as a PNG image
    • Uses Excel's built-in rendering (CopyPicture) — captures formatting, charts, conditional formatting
    • MCP: Returns image directly as ImageContent (base64 PNG)
    • CLI: Returns JSON with base64-encoded image data

🪧 Window Management (9 operations)

  • Show: Makes Excel visible and brings it to the foreground
  • Hide: Hides the Excel window
  • Bring to Front: Brings Excel to the foreground without changing visibility
  • Get Info: Gets current window state (visibility, position, size, foreground status)
  • Set State: Sets window state to normal, minimized, or maximized
  • Set Position: Sets window position and size in points (left, top, width, height)
  • Arrange: Arranges Excel window using preset layouts
  • Set Status Bar: Displays custom text in Excel's status bar for real-time feedback
  • Clear Status Bar: Restores the default status bar text

Arrange Presets:

  • left-half / right-half — Side-by-side with other applications
  • top-half / bottom-half — Stacked view
  • center — Centered window (60% of screen)
  • full-screen — Maximized

Use Cases:

  • Interactive "agent mode" where users watch Excel respond to AI commands in real-time
  • Side-by-side: Excel on one half, AI assistant on the other
  • Visibility changes are reflected in session metadata (session list shows updated state)

📊 Total Operations Summary

Category Operations
File Operations 6
Power Query 12
Data Model/DAX 19
Excel Tables 27
PivotTables 30
Charts 28
Ranges 42
Worksheets 16
Connections 9
Named Ranges 6
VBA Macros 6
Slicers 8
Conditional Formatting 2
Screenshot 2
Calculation Mode 3
Window Management 9
Total 225

🚀 Key Capabilities

Data Transformation:

  • Comprehensive Power Query M code management
  • Atomic import + load workflows
  • Calculated fields and members for analysis

Data Model:

  • Full DAX measure lifecycle
  • Relationship management
  • Multi-table integration

Analysis & Visualization:

  • PivotTable creation and configuration
  • Chart automation
  • Custom calculations

Automation:

  • VBA macro execution and management
  • Named range parameter automation
  • Conditional formatting rules

Data Loading:

  • Multiple connection type support
  • OLEDB/ODBC management
  • Power Query atomic workflows

🔧 Tool Selection Quick Reference

Task Tool
Import data powerquery or connection
Create analysis pivottable (data model-based for OLAP)
Visualize data chart
Update parameters namedrange (write operation)
Manage formulas range (set-formulas)
Format data range (format-range, validate-range)
Script automation vba (run macro)

📚 Documentation