Stefan Broenner
299ee68703
* feat(range): Add locale-aware number formatting with separate actions PROBLEM: - Format codes like '\$#,##0.00' interpreted incorrectly on non-US locales - German Excel treated '.' as thousands separator, ',' as decimal - LLM API had confusing design with 'formatCode OR numberFormatCategory' SOLUTION: 1. Use NumberFormatLocal instead of NumberFormat (interprets locale correctly) 2. Use LocaleAwareFormatBuilder to generate format codes using CultureInfo 3. Separate into two clean actions for better LLM usability: - SetNumberFormat: Structured params only (numberFormatCategory required) - SetNumberFormatCustom: Raw formatCode only (expert use) NEW FILES: - NumberFormatOptions.cs: Enums and LocaleAwareFormatBuilder MODIFIED: - RangeCommands.NumberFormat.cs: NumberFormatLocal + SetNumberFormatStructured - ExcelRangeTool.cs: Two separate actions, cleaner parameter routing - ToolActions.cs: SetNumberFormatCustom enum added - ActionExtensions.cs: set-number-format-custom mapping - audit-core-coverage.ps1: Added SetNumberFormatCustom to known exceptions - check-mcp-core-implementations.ps1: Added SetNumberFormatCustom to known exceptions VERIFIED: - German locale shows correct formats: EUR#.##0,00, 0,0%, #.##0,00 - Build passes with 0 warnings - All pre-commit checks pass * docs(range): Add LLM guidance for number formatting actions - Add [Description] attribute to excel_range tool with number formatting guidance - Create excel_range.md prompt with detailed action comparison - Create ExcelRangePrompts.cs to expose prompt via MCP Guidance helps LLMs choose between: - SetNumberFormat: RECOMMENDED, locale-aware, structured parameters - SetNumberFormatCustom: Expert use, raw format codes, locale-sensitive * docs: Update instructions to use XML documentation instead of [Description] MCP SDK now extracts tool descriptions from XML documentation (/// <summary>) rather than [Description] attributes. Updated files: - mcp-server-guide.instructions.md: XML docs guidance and examples - critical-rules.instructions.md: Rule 18 now shows XML doc patterns - bug-fixing-checklist.instructions.md: Reference XML docs not [Description] - ExcelRangeTool.cs: Move description to XML docs, remove [Description] * docs: Add guidance to ask user before closing when showExcel=true When Excel is visible (showExcel=true), the user is actively watching. LLM should ask before closing instead of auto-closing, since user may want to inspect results, make manual changes, or continue working. Updated files: - Program.cs: Server instructions with new 'ASK BEFORE CLOSING' section - ExcelFileTool.cs: XML docs with same guidance * feat: Add operation tracking to prevent premature session close - Add BeginOperation/EndOperation tracking in SessionManager - Block Close action when operations are still running - Add ValidateClose method with CloseValidationResult record - Enhanced List action to return activeOperations, canClose, isExcelVisible - Add force parameter to CloseSession for emergency override - Track showExcel flag per session for visibility status - Updated server instructions about automatic operation tracking - Fixed missing FileAction.List mapping in ActionExtensions Tests: - 18 new unit tests for SessionManager operation tracking - 4 new MCP integration tests for ExcelFileTool operation tracking This prevents data loss from closing sessions while parallel operations are still running. The server now automatically tracks operations and returns a blocking error with activeOperations count if close is attempted while operations are in progress. * feat(datamodel): add DeleteTable action to remove orphaned Data Model tables - Add DeleteTable to DataModelAction enum and ToActionString mapping - Add DeleteTable method to IDataModelCommands interface - Implement DeleteTable using SourceWorkbookConnection.Delete() pattern (ModelTable is read-only, must delete via associated connection) - Add DeleteTable handler in ExcelDataModelTool - Add 3 integration tests covering success and error scenarios This allows cleanup of orphaned tables when Power Query is deleted and recreated with loadDestination: data-model. * fix(connection): correct error message for Power Query connection deletion Changed error message from outdated 'pq-delete' to correct 'excel_powerquery with action Delete' when user attempts to delete a Power Query connection via excel_connection tool. * Simplify number formatting: auto-LCID for date/time, streamlined docs - Add EnsureDateTimeLcid() to automatically prefix date/time formats with [$-409] - LLMs no longer need to know about LCID - just use 'mm/dd/yyyy' directly - Currency symbols ($, €, £, ¥) are literal - no special handling needed - Simplified excel_range.md from 95 lines to 28 lines (one table, essentials only) * Simplify telemetry: remove redundant TrackPageView - Remove TrackPageView call (Usage blades don't work for console apps) - Keep TrackEvent for tool usage analytics (customEvents table) - Keep TrackRequest for performance metrics (Performance blade) - Update documentation to reflect simplified telemetry model * Track unhandled exceptions and file context in telemetry - Add try-catch in Program.Main() to track MCP SDK/transport errors - Pass excelPath to TrackToolInvocation for file-level analytics grouping - Ensures telemetry is flushed before exit on unhandled exceptions * docs: fix showExcel default guidance and incorrect action names - Fix ServerInstructions to clarify showExcel:false is DEFAULT (not showExcel:true) - Update tool description to emphasize default is hidden/faster mode - Fix incorrect pivottable actions: 'create' with sourceType -> 'create-from-table'/'create-from-datamodel' - Remove non-existent 'create-bulk' action reference from namedrange examples - Fix NamedRangeCommands comment to match actual exposed operations - Update VS Code extension copilot-instructions with correct defaults and actions * docs: enhance copilot instructions for Excel MCP usage clarity * Merge origin/main: keep operation tracking features from this branch * fix: address GitHub Advanced Security warnings - Fix constant condition in ConnectionCommands (check HasValue before switch) - Add using statement for SessionManager in Dispose_CleansUpAllTracking test - Suppress CA3003 (Path.Combine) in test code - controlled inputs - Suppress CA1031 (generic catch) in test cleanup - best effort disposal - Use .Where() instead of if-in-foreach for filtering
ExcelMcp - MCP Server for Microsoft Excel
Automate Excel with AI - A Model Context Protocol (MCP) server for comprehensive Excel automation through conversational AI.
MCP Server for Excel enables AI assistants (GitHub Copilot, Claude, ChatGPT) to automate Excel through natural language commands. Automate Power Query, DAX measures, VBA macros, PivotTables, Charts, formatting, and data transformations - no Excel programming knowledge required.
🛡️ 100% Safe - Uses Excel's Native COM API - Zero risk of file corruption. Unlike third-party libraries that manipulate .xlsx files directly, this project uses Excel's official API ensuring complete safety and compatibility.
💡 Interactive Development - See results instantly in Excel. Create a query, run it, inspect the output, refine and repeat. Excel becomes your AI-powered workspace for rapid development and testing.
Optional CLI Tool: For advanced users who prefer command-line scripting, ExcelMcp includes a CLI interface for RPA workflows, CI/CD pipelines, and batch automation. Both interfaces share the same 172 operations.
🚀 Quick Start (1 Minute)
Requirements: Windows OS + Microsoft Excel 2016+
⭐ Recommended: VS Code Extension (One-Click Setup)
Fastest way to get started - everything configured automatically: Install from Marketplace
The extension opens automatically after installation with a quick start guide!
For Visual Studio, Claude Desktop, Cursor, Windsurf, or other MCP clients:
📖 Complete Installation Guide → - Step-by-step setup for all AI assistants with ready-to-use config files
⚠️ Important: Close all Excel files before using ExcelMcp. The server requires exclusive access to workbooks during automation (Excel COM limitation).
🎯 What You Can Do
12 specialized tools with 172 operations:
- 🔄 Power Query (9 ops) - Atomic workflows, M code management, load destinations
- 📊 Data Model/DAX (14 ops) - Measures, relationships, model structure
- 🎨 Excel Tables (24 ops) - Lifecycle, filtering, sorting, structured references
- 📈 PivotTables (25 ops) - Creation, fields, aggregations, data extraction
- 📉 Charts (14 ops) - Create, configure, manage series and formatting
- 📝 VBA (6 ops) - Modules, execution, version control
- 📋 Ranges (42 ops) - Values, formulas, formatting, validation, protection
- 📄 Worksheets (16 ops) - Lifecycle, colors, visibility, cross-workbook moves
- 🔌 Connections (9 ops) - OLEDB/ODBC management and refresh
- 🏷️ Named Ranges (6 ops) - Parameters and configuration
- 📁 Files (5 ops) - Session management and workbook creation
- 🎨 Conditional Formatting (2 ops) - Rules and clearing
📚 Complete Feature Reference → - Detailed documentation of all 172 operations
💬 Example Prompts
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"
- "Create a data model from the following tables ... "
- "Build a DAX measure calculating year-over-year growth with proper time intelligence"
- "Filter this table by Column Product = Sushi"
- *"Create a treemap chart from this table".
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"
👥 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)
📋 Additional Information
CLI for Direct Automation
xcelMcp includes a CLI interface for Excel automation without AI assistance. This is useful for RPA workflows, CI/CD pipelines, or batch processing scripts. 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.
🔧 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
💡 Tip: Watch Excel While AI Works By default, Excel runs hidden for faster automation. To see changes in real-time, just ask:
- "Show me Excel while you work"
- "Let me watch what you're doing"
- "Open Excel so I can see the changes"
The AI will display the Excel window so you can watch every operation happen live - great for learning or verifying changes!
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)
📚 Complete MCP Server Guide → - Detailed tool documentation and examples
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 - mainly with Claude but lately with Auto-mode.
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