mirror of
https://github.com/sbroenne/mcp-server-excel.git
synced 2026-09-19 07:53:08 +08:00
Stefan Broenner
6b3a557870
* Refactor MCP tools to share error handling
* Refactor Core Commands to remove exception suppression
Per CRITICAL-RULES.md Rule 1b:
- Removed catch blocks that suppress exceptions by returning OperationResult { Success = false }
- Let exceptions propagate naturally through batch.Execute()
- Kept finally blocks for COM resource cleanup
- Removed orphaned try blocks from methods with no COM cleanup needs
Modified files:
- RangeCommands.AutoFit.cs (2 methods)
- RangeCommands.Advanced.cs (5 methods)
- RangeCommands.Validation.cs (3 methods)
- RangeCommands.Formatting.cs (3 methods)
- PivotTableCommands.GrandTotals.cs (1 method)
- OlapPivotTableFieldStrategy.cs (2 methods)
- RegularPivotTableFieldStrategy.cs (1 method)
Architecture: batch.Execute() ALREADY captures exceptions via TaskCompletionSource.
Inner try-catch suppresses exceptions and loses stack context.
Exception propagation now happens at correct layer (batch, not method).
* Update copilot-instructions: Add surgical testing guidance and exception propagation pattern
CRITICAL additions:
- Integration tests take 45+ MINUTES for full suite
- ALWAYS use surgical testing with Feature filters (2-5 min per feature)
- Core Commands NEVER wrap batch.Execute() in catch blocks
- Let exceptions propagate naturally - batch.Execute() handles via TaskCompletionSource
- ONLY use finally blocks for COM cleanup, NO catch blocks returning OperationResult
This prevents 45+ minute test runs and documents correct exception handling pattern per CRITICAL-RULES.md Rule 1b.
* Remove additional violating catch blocks from PivotTable commands
Found and removed 7 more catch blocks that violated Rule 1b:
- PivotTableCommands.Subtotals.SetSubtotals
- PivotTableCommands.Grouping.GroupByDate
- PivotTableCommands.Grouping.GroupByNumeric
- PivotTableCommands.CalculatedFields.CreateCalculatedField
- PivotTableCommands.Analysis.GetData
- PivotTableCommands.Analysis.SetFieldFilter
- PivotTableCommands.Analysis.SortField
All were inside batch.Execute() and returning OperationResult { Success = false }.
Removed catch blocks, kept finally blocks for COM cleanup.
Exceptions now propagate naturally through batch.Execute().
* refactor(core): remove violating catch block from TableCommands.DataModel.cs
Removed catch block that violated Rule 1b (CRITICAL-RULES.md).
AddToDataModel now lets exceptions propagate through batch.Execute().
Pattern removed:
catch (Exception ex) {
return new OperationResult { Success = false, ErrorMessage = ... };
}
Rationale:
- batch.Execute() captures exceptions via TaskCompletionSource
- Inner catch suppresses exceptions and loses stack context
- Exceptions should originate from batch layer, not method layer
Part of comprehensive audit removing all violating catch blocks from Core Commands.
* test(core): fix Range style tests to expect exceptions after architectural changes
Updated 2 tests to expect exceptions instead of OperationResult { Success = false }:
- SetStyle_InvalidStyleName_ThrowsException (was ReturnsError)
- GetStyle_InvalidRange_ThrowsException (was ReturnsError)
Why Changed:
After removing violating catch blocks (Rule 1b), SetStyle() and GetStyle()
no longer catch Excel COM exceptions. batch.Execute() captures exceptions via
TaskCompletionSource and re-throws them.
Pattern:
Methods WITHOUT pre-validation → Excel COM throws → batch.Execute() captures →
Exception propagates to caller → Test must expect exception
Verified Clean:
- Tests with pre-validation (Sheet.Move, NamedRange.CreateBulk) still expect
Success=false correctly
- Tests with business rules (OLAP calculated fields) correctly expect Success=false
- Only methods letting Excel COM throw directly needed test updates
All 2 tests now pass with architectural fix in place.
* refactor: remove async wrappers and bulk operations
Remove ViewAsync, CreateBulk methods and update documentation
* refactor(chart): Convert Chart commands to exception-based pattern
- IChartCommands: Convert 14 method signatures (OperationResult/ChartSeriesResult/ChartListResult → void/SeriesInfo/List<ChartInfo>)
- IChartStrategy: Convert 3 data operation signatures (SetSourceRange→void, AddSeries→SeriesInfo, RemoveSeries→void)
- RegularChartStrategy: Remove OperationResult wrappers, return data types directly
- PivotChartStrategy: Convert error returns to throw NotSupportedException with helpful messages
- ChartCommands implementations: Update DataSource/Lifecycle/Appearance to match new signatures
- Remove unnecessary Models using directives
CRUD ops return void (throw on error), Query ops return data types, Exceptions replace Success/ErrorMessage wrappers.
Pattern validated: Core commands build successfully with 0 errors/warnings.
* fix(chart): Fix COM cleanup in PivotChartStrategy to use finally blocks
- SetSourceRange: Move COM Release to finally block ensuring cleanup even if exception occurs
- AddSeries: Move COM Release to finally block ensuring cleanup even if exception occurs
- RemoveSeries: Move COM Release to finally block ensuring cleanup even if exception occurs
- Declare COM objects as nullable dynamic? variables before try-catch-finally
Prevents COM object leaks when exceptions occur during PivotTable name retrieval.
Finally blocks guarantee cleanup happens regardless of try/catch outcome.
Follows proper COM interop resource management pattern from excel-com-interop.instructions.md.
* refactor(chart): Remove empty catch blocks from PivotChartStrategy
- GetInfo: Remove empty catch blocks, keep only try-finally for COM cleanup
- GetDetailedInfo: Remove empty catch block, keep only try-finally for COM cleanup
Empty catch blocks are unnecessary when finally blocks handle cleanup.
Finally blocks execute regardless of exception occurrence.
Cleaner code pattern: try { operations } finally { cleanup }
* refactor(chart): Fix COM cleanup in RegularChartStrategy
- GetInfo: Move seriesCollection to try-finally without empty catch
- GetDetailedInfo: Move seriesCollection to try-finally, keep only justified catch blocks for optional properties
- Remove COM Release from try block, move to finally block
- Keep catch blocks only where fallback values make sense (HasLegend=false, etc.)
Ensures COM objects always released even if exceptions occur.
Empty catch blocks removed where no fallback action needed.
* refactor(chart): Simplify PivotChartStrategy exception messages
- SetSourceRange: Remove COM object lookup, just throw NotSupportedException
- AddSeries: Remove COM object lookup, just throw NotSupportedException
- RemoveSeries: Remove COM object lookup, just throw NotSupportedException
LLM already knows context - no need for complex COM operations to get PivotTable name.
Simpler, cleaner, no COM cleanup needed for error-only code paths.
* refactor(chart): Convert CLI ChartCommand to exception-based pattern
- Removed Result wrapper pattern from all Execute methods
- Added try-catch exception handling with proper exit codes (0=success, 1=error)
- Query operations: return data via WriteJson
- CRUD operations: void with WriteInfo success messages
- Removed WriteResult helper method (no longer needed)
- Removed unused Models using statement
- All 14 Execute methods converted:
* ExecuteList (List<ChartInfo>)
* ExecuteRead (ChartInfoResult)
* ExecuteCreateFromRange (ChartCreateResult)
* ExecuteCreateFromPivotTable (ChartCreateResult)
* ExecuteDelete (void)
* ExecuteMove (void)
* ExecuteSetSourceRange (void)
* ExecuteAddSeries (SeriesInfo)
* ExecuteRemoveSeries (void)
* ExecuteSetChartType (void)
* ExecuteSetTitle (void)
* ExecuteSetAxisTitle (void)
* ExecuteShowLegend (void)
* ExecuteSetStyle (void)
Part of Phase 2.2: Chart CLI Updates
* refactor(chart): Convert MCP ExcelChartTool to exception-based pattern
- Removed Result wrapper anonymous object serialization
- Query operations: serialize data types directly (List<ChartInfo>, ChartInfoResult, ChartCreateResult, SeriesInfo)
- CRUD operations (void): return success message JSON after completion
- Exception handling: ExecuteToolAction wrapper catches and serializes exceptions
- All 14 actions converted:
* List → serialize List<ChartInfo>
* Read → serialize ChartInfoResult
* CreateFromRange → serialize ChartCreateResult
* CreateFromPivotTable → serialize ChartCreateResult
* Delete → success message JSON
* Move → success message JSON
* SetSourceRange → success message JSON
* AddSeries → serialize SeriesInfo
* RemoveSeries → success message JSON
* SetChartType → success message JSON
* SetTitle → success message JSON
* SetAxisTitle → success message JSON
* ShowLegend → success message JSON
* SetStyle → success message JSON
Part of Phase 2.3: Chart MCP Tool Updates
* refactor(chart): Convert MCP ExcelChartTool to exception-based pattern
- Removed Result wrapper anonymous object serialization
- Query operations: serialize data types directly (List<ChartInfo>, ChartInfoResult, ChartCreateResult, SeriesInfo)
- CRUD operations (void): return success message JSON after completion
- Exception handling: ExecuteToolAction wrapper catches and serializes exceptions
- All 14 actions converted:
* List → serialize List<ChartInfo>
* Read → serialize ChartInfoResult
* CreateFromRange → serialize ChartCreateResult
* CreateFromPivotTable → serialize ChartCreateResult
* Delete → success message JSON
* Move → success message JSON
* SetSourceRange → success message JSON
* AddSeries → serialize SeriesInfo
* RemoveSeries → success message JSON
* SetChartType → success message JSON
* SetTitle → success message JSON
* SetAxisTitle → success message JSON
* ShowLegend → success message JSON
* SetStyle → success message JSON
Part of Phase 2.3: Chart MCP Tool Updates
* refactor(namedrange): Convert NamedRange commands to exception pattern (all layers)
Core Changes:
- INamedRangeCommands: List returns List<NamedRangeInfo>, Read returns NamedRangeValue, CRUD operations void
- Added NamedRangeValue class (POCO for Read operation)
- All methods throw exceptions on error (InvalidOperationException, ArgumentException)
- Parameter validation enforced (255 char limit, no duplicates)
CLI Changes:
- All Execute methods use try-catch with exit codes (0=success, 1=exception, -1=validation)
- List/Get serialize data directly via WriteJson
- Create/Update/Delete/Write use WriteInfo for success messages
- Removed WriteResult helper method
- Removed unused using directive
MCP Server Changes:
- List serializes List<NamedRangeInfo> directly
- Read serializes NamedRangeValue directly
- Write/Create/Update/Delete wrap void operations in lambda with dummy return
- All CRUD ops return success message JSON with operation-specific messages
- Delete includes workflowHint about #NAME? errors
Test Changes (12 tests converted, all passing):
- Lifecycle.cs: 4 tests - List accesses data directly, Create/Delete void with verification
- Validation.cs: 6 tests - Parameter validation with Assert.Throws for errors
- Values.cs: 3 tests - Write/Read void/data operations with verification
All layers build successfully with 0 errors, 0 warnings
All 12 NamedRange tests passing (100%)
Phase 3 (NamedRange) complete - 6 Core methods + 12 tests converted
* refactor(conditionalformat): Convert ConditionalFormatting commands to exception pattern
Core Changes:
- IConditionalFormattingCommands: AddRule and ClearRules return void
- All methods throw exceptions on error (InvalidOperationException, ArgumentException)
- Removed OperationResult wrappers, use dummy return 0 for batch.Execute
- Removed unused using directives
CLI Changes:
- ExecuteAddRule/ExecuteClearRules use try-catch with exit codes (0=success, 1=exception, -1=validation)
- WriteInfo for success messages
- Removed WriteResult helper method
- Removed unused using directive
MCP Server Changes:
- AddRule/ClearRules wrap void operations in lambda with dummy return
- Return success message JSON with operation-specific messages
- Keep workflowHints for formatting verification guidance
All layers build successfully with 0 errors, 0 warnings
No tests exist for ConditionalFormatting commands
Phase 4.1 (ConditionalFormatting) complete - 2 Core methods converted
* Refactor: Convert Connection, File, VBA subsystems from OperationResult to exception-throwing pattern
- Connection: IConnectionCommands methods now return void, throw on error
- File: CreateEmpty returns void, throws on error
- VBA: Trust, Import, Delete, Run operations throw exceptions
- CLI layer: Updated error handling for void operations
- MCP layer: Responses serialized as {success: true} for void operations
- Test infrastructure: Updated 14 test helper files to check exceptions
Mutation operations now follow .NET convention of throwing exceptions instead of returning error-wrapped results. Read operations continue returning typed results.
All three layers (Core, CLI, MCP) coordinated for consistent behavior.
* Merge branch 'feature/shared-tool-error-handling' of https://github.com/sbroenne/mcp-server-excel into feature/shared-tool-error-handling
* Refactor: Convert Sheet subsystem from OperationResult to exception-throwing pattern
Convert 12 Sheet mutation methods to void signatures across all layers:
- ISheetCommands: Create, Rename, Copy, Delete, Move, CopyToWorkbook, MoveToWorkbook, SetTabColor, ClearTabColor, SetVisibility, Show, Hide, VeryHide
- SheetCommands (Core): All implementations use batch.Execute() with return 0, exceptions propagate
- SheetCommand (CLI): All void operations wrapped in try-catch with success/error messages
- ExcelWorksheetTool (MCP): All 10 void methods use WithSession() + return 0 + JSON serialization
Production code compiles cleanly. MCP smoke test: PASSED. Tests require separate refactoring for void method assertions.
* Refactor: Convert Table subsystem from OperationResult to exception-throwing pattern (17 void methods, Core+CLI+MCP layers)
- ITableCommands: 17 mutation methods converted to void signatures with exception documentation
- Core layer: 17 implementations across 7 partial files use batch.Execute() return 0 pattern
- CLI layer: 10 void handlers use try-catch-serialize pattern with success/error responses
- MCP layer: 10 void tool methods use try-catch-JSON pattern (fixed 8 broken WithSession<T> calls)
- All production layers compile cleanly: 0 errors, 0 warnings
- MCP smoke test PASSED: all 11 tools working correctly
- Test layer errors (44) deferred: void assertion issues to be fixed separately
- Pattern matches Sheet/Connection/File/VBA subsystem conversions
* feat: Convert PowerQuery subsystem mutation methods to void pattern (Delete, Create, Update, LoadTo, RefreshAll)
* Convert DataModel subsystem to exception-throwing pattern
- Interface: 7 void mutation methods (DeleteMeasure, DeleteRelationship, Refresh (2 overloads), CreateMeasure, UpdateMeasure, CreateRelationship, UpdateRelationship)
- Core: Converted Write.cs (6 mutations) and Refresh.cs (2 methods) to void pattern with batch.Execute((ctx, ct) => { ...code...; return 0; })
- CLI: 7 handlers with try-catch-console JSON output
- MCP: 7 tool methods with try-catch-JSON error handling
- Removed unused Core.Models imports from Write.cs and Refresh.cs
- Build: 0 errors, 0 warnings across Core, CLI, MCP layers
Note: Test failures expected (44 in Core, 50+ in other test files due to void assertion issues) - these are deferred per testing strategy (Rule 14)
* Merge branch 'feature/shared-tool-error-handling' of https://github.com/sbroenne/mcp-server-excel into feature/shared-tool-error-handling
* fix: Complete void test migration - 84/84 errors fixed
Complete migration of 84 test compilation errors to exception-based pattern.
This completes the integration of void Execute infrastructure with test layer updates.
✅ BUILD VERIFIED: 0 errors, 0 warnings
✅ PRE-COMMIT PASSED: All quality gates passing
Test Migrations (84 total):
- Sheet tests: 19 errors (Move, TabColor, Visibility, Lifecycle)
- Helper fixtures: 4 errors (PowerQuery, Table, DataModel, PivotTable)
- PowerQueryCommandsTests: 19 errors (Create, Update, Delete, LoadTo calls)
- DataModelCommandsTests: 12 errors (CreateMeasure, UpdateMeasure, CreateRelationship, DeleteRelationship)
- DataModelTestsFixture: 1 error (var assignment from void CreateMeasure)
- TableCommandsTests: 2 errors (Create calls in tests and fixture)
- PivotTableCommandsTests: 3 errors (Create calls from Table/DataModel)
- PivotTableCommandsTests.OlapFields: 2 errors (CreateMeasure calls)
Pattern Applied (Two types):
1. CS0815 'Cannot assign void': Remove 'var result =' and 'Assert.True(result.Success)' lines
2. Remove unnecessary success checks - void methods throw on error instead
Infrastructure:
- void Execute(Action<ExcelContext, CancellationToken>) in IExcelBatch/ExcelBatch
- Delegates to Execute<T>(Func<...>) returning dummy 0
- Non-breaking enhancement for clean void method support
Verification:
✅ Build: 0 errors, 0 warnings
✅ Pre-commit checks: All passed
- COM leak detection: 0 leaks
- Success flag validation: All consistent
- MCP implementation coverage: 100%
- MCP server smoke test: Passed
✅ Feature branch active (feature/shared-tool-error-handling)
* docs: Add void test migration completion summary
* refactor: Convert 11 Range void mutations to fire-and-forget pattern across all layers
Complete refactoring of Range command mutations to void pattern:
CORE LAYER (11 void mutations):
- RangeCommands.Search.cs: Replace, Sort
- RangeCommands.Formatting.cs: SetStyle, FormatRange
- RangeCommands.Validation.cs: ValidateRange, RemoveValidation
- RangeCommands.AutoFit.cs: AutoFitColumns, AutoFitRows
- RangeCommands.Advanced.cs: MergeCells, UnmergeCells, SetCellLock
INTERFACE:
- IRangeCommands.cs: All 11 signatures changed from OperationResult to void
MCP TOOL LAYER:
- ExcelRangeTool.cs: All 11 calls updated to use WithSession<object?>() pattern
* Explicit type parameter resolves CS0411 type inference on void returns
* Returns { Success = true } JSON directly for all void mutations
* Lambda formatting fixed (IDE0055 compliance)
CLI LAYER:
- RangeCommand.cs: All 11 command handlers adapted
* Removed var result = ... assignments
* Call void methods directly
* Return { Success = true } JSON on completion
TEST LAYER (14+ files):
- RangeCommandsTests: All test files updated
* Removed var result = ... assignments
* Removed Assert.True(result.Success, ...) assertions
* Void methods throw on failure; silence = success
BUILD STATUS: ✅ Clean - 0 errors, 0 warnings
LEGITIMATE OperationResult METHODS (NOT CONVERTED):
- SetValues, SetFormulas, Copy, SetNumberFormat, AddHyperlink
- These data mutations need validation and result reporting
PATTERN:
- Void mutations: Fire-and-forget with exception-based error handling
- Exceptions propagate naturally through batch.Execute() layer
- No OperationResult wrapping needed for simple write operations
* chore: Remove unused imports and temporary documentation files
- Clean up unused using directives across Table and PivotTable commands
- Remove temporary documentation files
- Maintain code style consistency
* fix: Update MartinCostello.Logging.XUnit package version to 0.7.0
* fix(chart): Suppress CodeQL false positives for dynamic COM interop in Strategy pattern
- Add pragma warning disable CS8604 for all Strategy method calls
- CodeQL cannot verify dynamic Excel COM objects have required methods at compile-time
- All methods verified to exist in both RegularChartStrategy and PivotChartStrategy
- False positives: SetSourceRange, AddSeries, RemoveSeries, GetInfo, GetDetailedInfo
- Addresses code scanning alerts #2631-2635
ExcelMcp - MCP Server for Microsoft Excel
Automate Excel with AI - A Model Context Protocol (MCP) server for comprehensive Excel automation through conversational AI.
ExcelMcp is a comprehensive Excel automation toolkit with two interfaces:
- MCP Server: Enables AI assistants (GitHub Copilot, Claude, ChatGPT) to automate Excel through natural language commands
- CLI Tool: Provides direct command-line control for scripting, RPA, and CI/CD workflows
Both share the same core functionality: automate Power Query, DAX measures, VBA macros, PivotTables, formatting, and data transformations. Choose MCP for AI-powered conversations or CLI for programmatic control - no Excel programming knowledge required. Built on Excel's native COM API for zero risk of file corruption.
💡 Interactive Development: Unlike file-based tools, ExcelMcp lets you see results instantly in Excel - create a query, run it, inspect the output, refine and repeat. Excel becomes your interactive workspace for rapid development and testing.
🤔 What is This?
Use natural language OR command-line to automate complex Excel tasks - your choice.
Stop manually clicking through Excel menus for repetitive tasks. Instead, describe what you want in plain English:
Data Transformation & Analysis:
- "Optimize all my Power Queries in this workbook for better performance"
- "Create a PivotTable from SalesData table showing top 10 products by region with sum and average"
- "Build a DAX measure calculating year-over-year growth with proper time intelligence"
Formatting & Styling (No Programming Required):
- "Format the revenue columns as currency, make headers bold with blue background, and add borders to the table"
- "Apply conditional formatting to highlight values above $10,000 in red and below $5,000 in yellow"
- "Convert this data range to an Excel Table with style TableStyleMedium2, add auto-filters, and create a totals row"
Workflow Automation:
- "Find all cells containing 'Q1 2024' and replace with 'Q1 2025', then sort the table by Date descending"
- "Add data validation dropdowns to the Status column with options: Active, Pending, Completed"
- "Merge the header cells, center-align them, and auto-fit all column widths to content"
The AI assistant analyzes your request, generates the proper Excel automation commands, and executes them directly in your Excel application - no formulas or programming knowledge required.
Real-World Example - Power Query Optimization:
You: "This Power Query is taking 5 minutes to refresh. Can you optimize it?"
AI Assistant (using ExcelMcp):
1. Exports the M code to analyze performance bottlenecks
2. Identifies queries that can use query folding
3. Refactors the M code to push operations to the data source
4. Updates the query in your workbook
5. Tests the refresh (now completes in 30 seconds)
Result: A professionally optimized Power Query with documented improvements
🛡️ 100% Safe - Uses Excel's Native COM API
Unlike third-party libraries that manipulate .xlsx files directly (risking file corruption), ExcelMcp uses Excel's official COM API. This ensures:
- ✅ Zero risk of document corruption - Excel handles all file operations safely
- ✅ Interactive development - See changes in real-time, create → test → refine → iterate instantly
- ✅ Comprehensive automation - Currently supports 154 operations across 11 specialized tools covering Power Query, Data Model/DAX, VBA, PivotTables, Excel Tables, ranges, conditional formatting, and more
💻 For Developers: Think of Excel as an AI-powered REPL - write code (Power Query M, DAX, VBA), execute instantly, inspect results visually in the live workbook. No more blind editing of .xlsx files.
🔧 Advanced Features:
- Batch Operations - Group multiple operations in a single Excel session for 75-90% faster execution
- Progress Logging - Real-time operation status updates via stderr (MCP protocol compatible)
- Error Recovery - Intelligent retry suggestions and operation-specific troubleshooting guidance
👥 Who Should Use This?
Perfect for:
- ✅ Data analysts automating repetitive Excel workflows
- ✅ Developers building Excel-based data solutions
- ✅ Business users managing complex Excel workbooks
- ✅ Teams maintaining Power Query/VBA/DAX code in Git
Not suitable for:
- ❌ Server-side data processing (use libraries like ClosedXML, EPPlus instead)
- ❌ Linux/macOS users (Windows + Excel installation required)
- ❌ High-volume batch operations (consider Excel-free alternatives)
🎯 What You Can Do
Development & Automation:
- 🔄 Power Query - 9 operations: atomic workflows (create, update-and-refresh, refresh-all), manage transformations, load configurations (worksheet, data model, connection only), M code evaluation
- 📊 Power Pivot (Data Model) - 14 operations: build DAX measures, manage relationships, discover model structure (tables, columns)
- 🎨 Excel Tables - 23 operations: automate formatting, filtering, sorting, structured references, number formats, column management
- 📈 PivotTables - 25 operations: create and configure PivotTables for interactive analysis, control grand totals
- 📝 VBA Macros - 6 operations: export/import/run VBA code, integrate with version control
- 📋 Ranges & Data - 42 operations: values, formulas, copy/paste, find/replace, formatting, validation, merge, conditional formatting, cell protection
- 📄 Worksheets - 16 operations: lifecycle management, copy/move between workbooks, tab colors, visibility controls
- 🔌 Connections - 9 operations: manage OLEDB, ODBC, Text, Web data sources
- 🏷️ Named Ranges - 7 operations: named range management and bulk operations
📚 See Complete Feature List (100+ Operations)
Power Query & M Code (9 operations)
✨ NEW: Atomic Operations - Single-call workflows replace multi-step patterns:
- Create - Import + load in one operation (replaces import → load workflow)
- Update & Refresh - Update M code + refresh data atomically
- Refresh All - Batch refresh all queries in workbook
- Update M Code - Stage code changes without refreshing data
- Unload - Convert loaded query to connection-only
Core Operations:
- List, view, delete Power Query transformations
- Manage query load destinations (worksheet/data model/connection-only/both)
- Get load configuration for existing queries
- List Excel workbook sources for Power Query integration
Data Model & DAX (Power Pivot) (14 operations)
- Create/update/delete DAX measures with format types (Currency, Percentage, Decimal, General)
- Manage table relationships (create, toggle active/inactive, delete)
- Discover model structure (tables, columns, measures, relationships)
- Get comprehensive model information
- Refresh data model
- Note: DAX calculated columns are not supported (use Excel UI for calculated columns)
Excel Tables (ListObjects) (23 operations)
- Lifecycle: create, resize, rename, delete, get info
- Styling: apply table styles, toggle totals row, set column totals
- Column management: add, remove, rename columns
- Data operations: append rows, apply filters (criteria/values), clear filters, get filter state
- Sorting: single-column sort, multi-column sort (up to 3 levels)
- Number formatting: get/set column number formats
- Advanced features: structured references, Data Model integration
PivotTables (25 operations)
- Creation: create from ranges or Excel Tables
- Field management: add/remove fields to Row, Column, Value, Filter areas
- Aggregation functions: Sum, Average, Count, Min, Max, etc. with validation
- Advanced features: field filters, sorting, custom field names, number formatting
- Data extraction: get PivotTable data as 2D arrays for further analysis
- Lifecycle: list, get info, delete, refresh
VBA Macros (6 operations)
- List all VBA modules and procedures
- View module code without exporting
- Export/import VBA modules to/from files
- Update existing modules
- Execute macros with parameters
- Delete modules
- Version control VBA code through file exports
Ranges & Worksheets
- Data Operations (10 actions): get/set values/formulas, clear (all/contents/formats), copy/paste (all/values/formulas), insert/delete rows/columns/cells, find/replace, sort
- Number Formatting (3 actions): get formats as 2D arrays, apply uniform format, set individual cell formats
- Visual Formatting (1 action): font (name, size, bold, italic, underline, color), fill color, borders (style, weight, color), alignment (horizontal, vertical, wrap text, orientation)
- Data Validation (3 actions): add validation rules (dropdowns, number/date/text rules), get validation info, remove validation
- Hyperlinks (4 actions): add, remove, list all, get specific hyperlink
- Smart Range Operations (3 actions): UsedRange, CurrentRegion, get range info (address, dimensions, format)
- Merge Operations (3 actions): merge cells, unmerge cells, get merge info
- Auto-Sizing (2 actions): auto-fit columns, auto-fit rows
- Conditional Formatting (2 actions): add conditional formatting, clear conditional formatting
- Cell Protection (2 actions): set cell lock status, get cell lock status
- Formatting & Styling (3 actions): get style, set style, format range
- 45 range operations total covering all common Excel range manipulation needs
- Worksheet management (16 actions): lifecycle (create, rename, copy, move, delete), copy/move between workbooks, tab colors (set, get, clear), visibility controls (show, hide, very-hide, get/set status)
Data Connections (11 operations)
- Create and manage OLEDB/ODBC connections when the provider is installed (e.g., Microsoft.ACE.OLEDB.16.0, SQLOLEDB)
- Refresh connections and test connectivity
- Load connection-only sources to worksheet tables
- Update connection strings, command text, and refresh properties
- Redirect TEXT/WEB scenarios to
excel_powerqueryautomatically (Power Query recommended)
Named Ranges (7 operations)
- List all named ranges with references
- Get/set single values
- Create/delete named ranges
- Update cell references
- Bulk create multiple parameters
🚀 Quick Start (2 Minutes)
Requirements: Windows OS + Microsoft Excel 2016+
⚠️ Important: Close all Excel files before using ExcelMcp. The server requires exclusive access to workbooks during automation (Excel COM limitation).
⭐ Recommended: VS Code Extension (One-Click Setup)
Fastest way to get started - everything configured automatically:
-
Install Extension
- Open VS Code → Extensions (
Ctrl+Shift+X) - Search for "ExcelMcp"
- Click Install
- Open VS Code → Extensions (
-
Automatic Setup (no manual steps!)
- ✅ Installs .NET 8 runtime
- ✅ Includes bundled MCP server
- ✅ Registers with AI assistants
- ✅ Shows quick start guide
-
Start Using It
The extension opens automatically after installation with a quick start guide!
Manual Installation (Advanced Users)
For non-VS Code environments or manual setup:
# Install .NET 8 SDK
winget install Microsoft.DotNet.SDK.8
# Install ExcelMcp MCP server as a global tool
dotnet tool install --global Sbroenne.ExcelMcp.McpServer
# To update to the latest version later:
dotnet tool update --global Sbroenne.ExcelMcp.McpServer
Configure Your AI Assistant
ExcelMcp works with multiple AI coding assistants. Choose your setup:
🎯 Quick Setup: Ready-to-use config files → examples/mcp-configs/
For GitHub Copilot in VS Code - Create .vscode/mcp.json in your workspace:
{
"servers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
For GitHub Copilot in Visual Studio - Create .mcp.json in your solution directory or %USERPROFILE%\.mcp.json:
{
"servers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
For Claude Desktop - Edit %APPDATA%\Claude\claude_desktop_config.json:
{
"mcpServers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
For Cursor - Add to settings or %APPDATA%\Cursor\User\globalStorage\mcp\mcp.json:
{
"mcpServers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
For Cline (VS Code Extension) - Use the MCP settings panel in Cline or edit settings:
{
"mcpServers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
For Windsurf - Add to MCP servers configuration:
{
"mcpServers": {
"excel": {
"command": "dotnet",
"args": ["tool", "run", "mcp-excel"]
}
}
}
📖 Detailed setup for each client: Installation Guide
Test It Out
Try a practical example - ask your AI assistant:
Create an empty Excel file called "test.xlsx" and add a Power Query that loads data from a CSV file
The AI will guide you through the process and execute the commands directly!
🔧 How It Works - COM Interop Architecture
ExcelMcp uses Windows COM automation to control the actual Excel application (not just .xlsx files).
This means you get:
- ✅ Full Excel Feature Access - Power Query engine, VBA runtime, Data Model, calculation engine, pivot tables
- ✅ True Compatibility - Works exactly like Excel UI, no feature limitations
- ✅ Live Data Operations - Refresh Power Query, connections, Data Model in real workbooks
- ✅ Interactive Development - Immediate Excel feedback as AI makes changes
- ✅ All File Formats - .xlsx, .xlsm, .xlsb, even legacy formats
Technical Requirements:
- ⚠️ Windows Only - COM interop is Windows-specific
- ⚠️ Excel Required - Microsoft Excel 2016 or later must be installed
- ⚠️ Desktop Environment - Controls actual Excel process (not for server-side processing)
🔟 MCP Tools Overview
11 specialized tools (154 operations) for comprehensive Excel automation:
- excel_powerquery (11 actions) - Power Query M code: create, view, update, delete, refresh, load configuration, list Excel sources
- excel_datamodel (14 actions) - Power Pivot (Data Model): CRUD DAX measures/relationships, discover structure (tables, columns), refresh
- excel_table (26 actions) - Excel Tables: lifecycle, columns, filters, sorts, structured references, totals, number formatting, Data Model integration
- excel_pivottable (20 actions) - PivotTables: create from ranges/tables, field management (row/column/value/filter), aggregations, filters, sorting, extract data
- excel_range (43 actions) - Ranges: get/set values/formulas, number formatting, visual formatting (font, fill, border, alignment), data validation, clear, copy, insert/delete, find/replace, sort, hyperlinks, merge, cell protection
- excel_conditionalformat (2 actions) - Conditional Formatting: add rules (cell value, expression-based), clear rules
- excel_vba (6 actions) - VBA: list, view, import, update, run, delete modules
- excel_connection (9 actions) - Connections: OLEDB/ODBC management, properties, refresh, test, load-to
- excel_worksheet (13 actions) - Worksheets: lifecycle (list, create, rename, copy, delete), tab colors (set-tab-color, get-tab-color, clear-tab-color), visibility (set-visibility, get-visibility, show, hide, very-hide)
- excel_namedrange (7 actions) - Named ranges: list, get, set, create, create-bulk, delete, update
- excel_file (6 actions) - File operations: create empty, open, save, close, close-workbook, test
Total: 11 tools with 154 actions
📚 Complete MCP Server Guide → - Detailed tool documentation and examples
📋 Additional Information
Testing Philosophy
Why No Unit Tests? ExcelMcp uses integration tests exclusively because Excel COM cannot be meaningfully mocked. Our integration tests ARE our unit tests. See ADR-001: No Unit Tests for full architectural rationale.
CLI for Direct Automation
ExcelMcp also provides a command-line interface for Excel automation (no AI required). Run excelcli --help for a categorized list of commands, or excelcli sheet --help (replace sheet) to view action-specific options. Always follow the session pattern: excelcli session open <file> → run commands with --session <id> → excelcli session save/close <id>. See CLI Guide for complete documentation.
Project Information
License: MIT License - see LICENSE file
Contributing: See CONTRIBUTING.md for guidelines
Built With: This entire project was developed using GitHub Copilot AI assistance
Acknowledgments:
- Microsoft Excel Team - For comprehensive COM automation APIs
- Model Context Protocol community - For the AI integration standard
- Open Source Community - For inspiration and best practices
SEO & Discovery
Excel Automation • Automate Excel with AI • MCP Server • Model Context Protocol • GitHub Copilot Excel • AI Excel Assistant • Power Query Automation • Power Query M Code • Power Pivot Automation • DAX Measures • DAX Automation • Data Model Automation • PivotTable Automation • VBA Automation • Excel Tables Automation • Excel AI Integration • COM Interop • Windows Excel Automation • Excel Development Tools • Excel Productivity • Excel Scripting • Conversational Excel • Natural Language Excel