mirror of
https://github.com/sbroenne/mcp-server-excel.git
synced 2026-09-19 07:53:08 +08:00
Stefan Broenner
937b5681cf
* spec(006): Add .NET 10 upgrade feature specification Complete speckit workflow for upgrading from .NET 8 to .NET 10: - Update constitution.md to v1.1.0 with .NET 10 SDK requirement - Create spec.md with 4 user stories and 15 functional requirements - Create plan.md with implementation strategy and file inventory - Create research.md with SDK version, package compatibility findings - Create tasks.md with 38 tasks organized by user story - Update copilot-instructions.md with .NET 10 context Covers: SDK (global.json), 8 csproj files, 5 workflows, Dockerfile, and 7+ documentation files. No code changes - configuration only. * Add LLM-based tests for MCP Server using agent-benchmark Closes #340 - Add LLM test infrastructure with PowerShell runner (Run-LLMTests.ps1) - Create test scenario for excel_file and excel_worksheet tools - Configure Azure OpenAI with gpt-4.1 and gpt-5.2-chat agents - Update testing-strategy.instructions.md with LLM test documentation - All 20 tests passing with 100% success rate Test results: 464,502 tokens consumed across 20 tests Average time: ~7.4s per test * feat: optimize JSON token usage for LLM efficiency (#341) - Set WriteIndented=false for compact JSON output - Add DefaultIgnoreCondition.WhenWritingNull to omit null properties - Add [JsonPropertyName] attributes with short names to result types: - ResultBase: ok, err, fp - OperationResult: act - WorksheetInfo: n, i, v - RangeValueResult: sn, ra, d, r, c - TableInfo: n, sn, ra, hh, st, r, c, cols, tot - And more... - Update SerializeToolError to use short property names - Fix UUID templating syntax in LLM test file Token usage reduced from 464,502 to 443,601 (~4.5% reduction) Closes #341 * feat(mcp): JSON token optimization - tool splitting and parameter abbreviation Issue #341: Reduce JSON token consumption for LLM efficiency ## Tool Splitting (12 → 21 tools) - excel_range → excel_range, excel_range_edit, excel_range_format, excel_range_link - excel_table → excel_table, excel_table_column - excel_pivottable → excel_pivottable, excel_pivottable_field, excel_pivottable_calc - excel_datamodel → excel_datamodel, excel_datamodel_rel - excel_chart → excel_chart, excel_chart_config - excel_worksheet → excel_worksheet, excel_worksheet_style ## Parameter Name Abbreviation - sessionId → sid, sheetName → sn, tableName → tn - rangeAddress → addr, values → vals, excelPath → path - pivotTableName → ptn, chartName → cn, and more ## XML Documentation Optimization - Removed redundant action lists (schema provides) - Standardized 'Related:' cross-tool reference pattern - Trimmed verbose descriptions (~500+ tokens saved) ## Test Updates - McpServerSmokeTests: Updated all parameter names - McpServerIntegrationTests: Updated expected tool count (12 → 21) - Other integration tests: Updated parameter names Note: Pre-commit coverage audit requires update to account for tool splitting Build: 0 errors, 0 warnings Smoke test: 15 steps passed * docs: Update documentation for 21 tools, backfill changelog, add LLM testing callout - Updated all docs to reflect 21 tools with 182 operations (was 12 tools) - Backfilled CHANGELOG.md with missing versions 1.4.35-1.4.49 - Added JSON Token Optimization and LLM Integration Testing to changelog - Added LLM-Tested Quality callout to README, gh-pages, and VS Code extension - Added LLM-Tested Quality feature card to gh-pages index - Updated testing-strategy.instructions.md with LLM test guidance - Updated architecture-patterns.instructions.md with 21 tools reference - Fixed audit-core-coverage.ps1 to handle sub-tool enum grouping Files updated: - README.md, FEATURES.md, gh-pages/*.md - vscode-extension/CHANGELOG.md, vscode-extension/README.md - src/ExcelMcp.McpServer/README.md, src/ExcelMcp.CLI/README.md - tests/README.md, tests/ExcelMcp.McpServer.LLM.Tests/README.md - .github/instructions/*.md - scripts/audit-core-coverage.ps1 * test(llm): consolidate and improve LLM modification pattern tests - Rename excel-incremental-update-test.yaml to excel-modification-patterns-test.yaml - Consolidate tests from 9 sessions down to 4 focused sessions: - Range Updates: Formula canary (D1=1004) proves targeted cell updates - Table Operations: Table count canary verifies no delete/recreate - Chart Modifications: Chart count canary confirms title change only - Sheet Structural Changes: ROW() formulas prove proper row shifting - Add canary verification to detect 'delete and rebuild' anti-patterns - Simplify other test files: - excel-file-worksheet-test.yaml: 11 tests -> 2 consolidated tests - excel-range-test.yaml: 10 tests -> 2 consolidated tests - excel-table-test.yaml: 12 tests -> 2 consolidated tests - Remove unsupported tool_param_contains assertions (agent-benchmark limitation) - Remove custom system_prompt and rate_limits (use defaults) - Increase server_delay to 30s for reliability - Clean up CHANGELOG.md (remove maintenance releases) Addresses user feedback about 'small step-by-step changes instead of frequently deleting and rebuild' behavior. All 10 LLM tests passing (4 modification patterns + 6 other scenarios). * feat: upgrade to .NET 10 - Update SDK to .NET 10.0.100 - Update all source and test projects to target net10.0 - Update package versions to latest stable for .NET 10: - Microsoft.Extensions packages remain at 10.0.1 (latest available) - Spectre.Console at 0.54.0 - ModelContextProtocol at 0.5.0-preview.1 - Fix IDE0060 unused parameter warning in test class - Build succeeds with 0 warnings, 0 errors All projects now target net10.0 and build successfully. * ci: update to .NET 10 and document changelog - Update GitHub Actions workflows to use .NET 10 SDK: - build-mcp-server.yml: 8.0.x → 10.0.x - build-cli.yml: 8.0.x → 10.0.x - Update build artifact paths from net8.0 to net10.0 - Update CHANGELOG.md with .NET 10 upgrade details GitHub Actions now build and test against .NET 10.0. * refactor: Upgrade to C# 14 and finalize .NET 10 migration - Explicitly set LangVersion=14 in Directory.Build.props (was: latest) - Refactor telemetry code to use modern Dictionary initializer syntax - Apply C# 14 collection expressions for empty list patterns - Update workflow artifacts from net8.0 to net10.0 paths - Update all remaining .NET 8.0 references to .NET 10 across workflow and docs Files modified: 16 - Directory.Build.props - Core refactoring: ExcelMcpTelemetry.cs, OlapPivotTableFieldStrategy.cs, TableCommands.Data.cs - Workflow/documentation: .github/workflows/*.yml, vscode-extension/*, examples/*, docs/* * refactor: remove dead code, use LINQ, and add proper exception suppression comments - DataModelCommands.RenameTable.cs: Remove dead try-catch for COM rename (always fails), add CA1031 pragmas with justification - PivotTableCommands.Fields.cs: Simplify ListCubeFieldsAsync and ListRegularFieldsAsync by removing redundant try-catch wrappers - RenameNameRules.cs: Convert foreach loop to LINQ expression per code review feedback --------- Co-authored-by: Stefan Broenner <stefan.broenner@microsoft.comm>
10 KiB
10 KiB
ExcelMcp - Complete Feature Reference
21 specialized tools with 182 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)
- 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
🔄 Power Query & M Code (10 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)
- Update: Update M code 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
- Delete: Remove Power Query from workbook
📊 Data Model & DAX (Power Pivot) (17 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
- Read Info: Get comprehensive model information
- Create Measure: Create new DAX measure (with format types: Currency, Percentage, Decimal, General)
- Update Measure: Modify existing measure
- 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
Note: DAX calculated columns not supported - use Excel UI for calculated columns
🎨 Excel Tables (ListObjects) (24 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
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 (14 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 properties
- Set plot area properties
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 excel_powerquery)
Automatic Fallback:
- TEXT/WEB connections automatically redirect to excel_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
🎨 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
📊 Total Operations Summary
| Category | Operations |
|---|---|
| File Operations | 6 |
| Power Query | 10 |
| Data Model/DAX | 17 |
| Excel Tables | 24 |
| PivotTables | 30 |
| Charts | 14 |
| Ranges | 42 |
| Worksheets | 16 |
| Connections | 9 |
| Named Ranges | 6 |
| VBA Macros | 6 |
| Conditional Formatting | 2 |
| Total | 182 |
🚀 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 | excel_powerquery or excel_connection |
| Create analysis | excel_pivottable (data model-based for OLAP) |
| Visualize data | excel_chart |
| Update parameters | excel_namedrange (write operation) |
| Manage formulas | excel_range (set-formulas) |
| Format data | excel_range (format-range, validate-range) |
| Script automation | excel_vba (run macro) |
📚 Documentation
- Installation Guide - Setup for all AI assistants
- MCP Server Guide - Tool documentation and examples
- CLI Guide - Command-line reference
- Contributing - Development guidelines
- Releases - Latest updates and features