skills/sbroenne__mcp-server-excel
1
0
Fork 0
mirror of https://github.com/sbroenne/mcp-server-excel.git synced 2026-09-19 07:53:08 +08:00
Code Issues Packages Projects Releases Wiki Activity
219 Commits 13 Branches 184 Tags
v1.5.2
Commit Graph

5 Commits

Author SHA1 Message Date
Stefan Broenner 937b5681cf feat: JSON token optimization, LLM tests, and .NET 10 upgrade (#341) (#342) 
* 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>
 2026-01-09 13:52:17 +01:00
Stefan Broenner c009bd04c4 feat: Add badges section to homepage and update README with NuGet downloads (#222) 2025-11-20 15:33:11 +00:00
Stefan Broenner df928e95f4 Refactor (#188) 
* feat: align CLI with MCP Server session API pattern

MAJOR CHANGE: CLI now uses MCP-aligned session terminology
- Old: batch-begin, batch-commit, batch-list
- New: open, save, list (primary commands)
- Legacy batch commands still work but map to new session commands

IMPLEMENTATION:
✓ BatchCommands: Renamed Begin() → Open(), Commit() → Save()
✓ Program.cs: Updated routing for new command names
✓ Program.cs: Added backward compatibility for old batch commands
✓ Program.cs: Updated help text to show session lifecycle
✓ Program.cs: Banner now says 'MCP-aligned Session API'
✓ CommandHelper: Updated --batch-id → --session-id parameter detection
✓ Error messages: Updated terminology from 'batch' to 'session'
✓ All file operations: Updated comments and output messages

BACKWARD COMPATIBILITY:
✓ Old batch-begin/batch-commit/batch-list commands still work
✓ --batch-id flag still accepted (mapped to --session-id internally)
✓ Both terminologies coexist during transition

TESTING:
✓ CLI builds: 0 warnings, 0 errors
✓ MCP smoke test passed
✓ Help text displays new session commands and deprecation notice

DOCUMENTATION:
✓ CLI README already updated in previous commit with session API examples
✓ Help output now clearly shows primary commands and deprecation path

This aligns the CLI completely with the MCP Server's session-based API,
making both interfaces consistent and simplifying user experience.

* feat: split save/close commands - save keeps session open, close discards changes

* refactor: remove backward compatibility layer - legacy GetBatch method and usage

* fix: improve Range command error messages - use RangeHelpers specific errors

- All Range operations now use RangeHelpers.ResolveRange() with specificError out parameter
- Instead of cryptic '0x800A03EC' COM error, users now get helpful messages like:
  * 'Sheet \\'Validation_Summary\\' not found. Available sheets: Sheet1, Sheet2'
  * 'Named range \\'Sales\\' not found. Available named ranges: Parameters, Region'
- Includes suggestions to use excel_worksheet or excel_namedrange tools
- Affects: Get/Set Values/Formulas, Copy, Clear, Insert, Delete, Find, Replace, Sort, Hyperlinks, NumberFormat operations

* fix: simplify COM error messages - remove LLM guidance

Removed verbose step-by-step guidance from error messages. LLMs don't need to be told what to do, just what failed.

Changed from:
  'This usually means: (1) Sheet doesn't exist, (2) Range invalid, or (3) Session not open.
   Use excel_worksheet(action: list) to verify...'

To:
  'Cannot read range X on sheet Y: Insufficient memory to continue the execution of the program'

Files changed:
- RangeCommands.Values.cs: Simplified GetValuesAsync and SetValuesAsync COM error messages
- RangeCommands.Formulas.cs: Simplified GetFormulasAsync and SetFormulasAsync COM error messages
- ExcelToolsBase.cs: Simplified session validation error - just state facts, no instructions

* docs: add error message style guidance - facts not instructions

Added Best Practice #9 and Error Message Style section to mcp-server-guide.

Key principle: Error messages should state facts (what failed, why), not prescribe solutions.
LLMs are intelligent agents that determine next steps - they don't need step-by-step guidance.

Example:
  ❌ Wrong: 'This usually means: (1) Sheet doesn't exist... Use excel_worksheet(list)...'
  ✅ Correct: 'Cannot read range X on sheet Y: Insufficient memory'

This matches the simplifications in commit 21d4c58.

* fix: return JSON errors from OpenSessionAsync instead of throwing

Changed OpenSessionAsync to catch exceptions from CreateSessionAsync and return JSON with success=false instead of letting them bubble up as unhandled exceptions.

Fixes the generic 'An error occurred invoking excel_file' message shown when file was already open or other session creation errors occurred.

Files changed:
- ExcelFileTool.cs: Wrapped CreateSessionAsync in try-catch, return JSON errors

* refactor: Replace McpException with standard .NET exceptions

CRITICAL INSIGHT (Phase 8): Discovered that McpException usage was unnecessary complexity.
Since we catch ALL exceptions before the MCP SDK sees them and return JSON responses,
the SDK never encounters McpException - it gets our JSON error response instead.

CHANGES:
- Removed McpException catch blocks from all 11 tool files
- Updated ExcelToolsBase to throw ArgumentException (validation) and InvalidOperationException (state)
- Replaced McpException throws in:
  * ExcelFileTool (5 throws)
  * ExcelConnectionTool (17 throws)
  * All 9 'unknown action' switch branches
  * ExcelWorksheetTool invalid visibility

ARCHITECTURAL BENEFIT:
- Removed unnecessary coupling to ModelContextProtocol.McpException
- Single catch (Exception ex) block now handles all cases
- Uses standard .NET exception types for clearer semantics:
  * ArgumentException for parameter validation
  * InvalidOperationException for invalid state
- Simpler, more idiomatic C# code
- Same error handling quality (descriptive JSON responses)

REMAINING:
- ~86 parameter validation throws still using McpException (will be caught as Exception)
- These still work correctly but should be migrated to ArgumentException in future PRs
- Build: 0 warnings, 0 errors

RATIONALE:
The MCP SDK wraps our tool invocations with try-catch. Our code catches exceptions
BEFORE the SDK sees them and returns JSON with isError=true. Therefore, whether we
throw McpException, ArgumentException, or any other Exception is irrelevant to the
SDK - it receives JSON in all cases. Using standard .NET exceptions is cleaner,
doesn't add SDK coupling, and makes the code more maintainable.

* refactor: Complete migration to ArgumentException for ALL parameter validations

Complete removal of McpException pattern across all 11 tool files:
- ExcelDataModelTool: 23 throws (all measure/table/relationship parameters)
- ExcelWorksheetTool: 16 throws (all sheet/color/visibility parameters)
- ExcelVbaTool: 10 throws (all module/path parameters)
- ExcelTableTool: 5 throws (all table/column/format parameters)
- ExcelQueryTableTool: 10 throws (all query table parameters)
- ExcelRangeTool: 2 throws (shift direction validation)
- ExcelPivotTableTool: 4 throws (aggregation/sort direction validation)
- ExcelPowerQueryTool: 12 throws (completed in prior batch)
- ExcelNamedRangeTool: 9 throws (completed in prior batch)
- ExcelConnectionTool: 17 throws (completed in prior batch)
- ExcelFileTool: 5 throws (completed in prior batch)

TOTAL MIGRATION: 117/~117 parameter validation throws replaced

Architecture Insight:
- Standard .NET exceptions (ArgumentException for invalid input)
- Single catch (Exception ex) block handles all cases
- No SDK coupling - clean separation of concerns
- Build: 0 warnings, 0 errors (maintained throughout)

Breaking Simplification:
- Removed unnecessary McpException abstraction layer
- SDK-side exception handling wraps all tool invocations
- No behavior change to callers (still returns JSON with isError=true)
- Cleaner, more maintainable code with standard .NET patterns

* refactor: Simplify async command invocation by removing unnecessary lambda expressions

* fix: update .gitignore to exclude Jekyll generated files

* Fix MCP Server tests: tool discovery and enum completeness

- Fixed ToolDiscoveryTests: Removed non-existent 'excel_batch' from expected tools list
- Fixed ActionEnumCompletenessTests: Added FileAction.Open/Save/Close mappings to ActionExtensions
- Fixed tool naming: Changed 'excel_conditional_format' to 'excel_conditionalformat' (matches naming convention)
- Enum cleanup: Removed unused BatchAction enum and its ToActionString() mapping

Test results: 60 passing (was 58), 9 failing (was 11)
Remaining failures are pre-existing issues (Power Query COM errors, invalid session handling)

* Update documentation: 12 tools with 168 operations

- Added excel_conditionalformat tool (2 actions: add-rule, clear-rules)
- Removed excel_batch from tool lists (doesn't exist)
- Updated operation counts: 166 → 168
- Updated tool counts: 11 → 12
- Added conditional formatting examples to CLI README
- Updated all READMEs: main, MCP Server, CLI, VS Code extension, GitHub pages
- Updated range action counts: 45 → 43 (moved conditional formatting to dedicated tool)
- Added excel_querytable (8 actions) to complete tool list
- Updated excel_file action count: 3 → 6 (open, save, close, create-empty, close-workbook, test)

* Delete incorrect MCP Server tests expecting exceptions

Removed tests that expected McpException for invalid sessions/missing parameters.
Per CRITICAL-RULES.md Rule 17, MCP tools should return JSON with success=false
for business errors, NOT throw exceptions.

Tests deleted:
- DetailedErrorMessageTests.cs (entire file - 8 tests)
- ExcelMcpServerTests.ExcelWorksheet_InvalidSession_ShouldThrowError

Test results: 59 passing (was 60), 2 failing (was 9)
Remaining 2 failures are legitimate Power Query COM errors (RPC failures)

* Fix ExcelBatch async disposal and timeout handling

- Replace synchronous Task.Wait() with async DisposeAsync pattern
- Add proper CancellationTokenSource disposal in finally blocks
- Use ConfigureAwait(false) for library code
- Fix timeout handling to properly cancel operations
- Add comprehensive disposal tests with timeout scenarios

* Remove ExportAsync methods - consolidate with ViewAsync

All three command types (PowerQuery, VBA, Connection) now use ViewAsync
instead of ExportAsync to return code/data directly rather than writing
to files. This simplifies the API by eliminating duplicate functionality.

Changes:
- Removed ExportAsync from IPowerQueryCommands, IVbaCommands, IConnectionCommands interfaces
- Removed ExportAsync implementations from all three command files
- Updated CLI commands to use ViewAsync instead (return code directly)
- Updated MCP Server tools to use ViewAsync instead (return JSON with code)
- Updated VBA test to use ViewAsync instead of ExportAsync
- Updated POWERQUERY-FUTURE-STATE-SPEC.md to remove ExportAsync references

Tests now verify code is returned in ViewAsync result rather than written to file.

* WIP: CLI async-to-sync conversion attempt - reverting to clean state

* Save WIP

* Align MCP tooling with updated actions

* - Refactored tools to reduce cognizant load on LLM
- Fix stability issues when working on multiple files at the same time

* Increase STA thread cleanup timeout from 3 to 10 seconds to prevent potential deadlocks during disposal

* Refactor QueryTable handling to use unified PowerQueryHelpers

- Updated QueryTableCommand to utilize PowerQueryHelpers for creating QueryTables.
- Introduced ConnectionHelpers for retrieving connection strings and command texts.
- Consolidated QueryTable creation options into PowerQueryHelpers.QueryTableCreateOptions.
- Removed redundant methods and classes related to QueryTable options.
- Enhanced error handling and validation for connection strings.
- Updated tests to reflect changes in QueryTable creation and options application.

* Revert "Refactor QueryTable handling to use unified PowerQueryHelpers"

This reverts commit 3603afcb71.

* Switch PowerQuery flows to inline M code

* Fix formula string formatting in JSON deserialization tests

* Fix test failures: formula syntax, PowerQuery Update, and QueryTable API usage

- Fixed JSON syntax in formula tests (removed extra quotes in array)
- Fixed raw string literal quote handling in Excel formulas
- Fixed PowerQuery Update to preserve worksheet data:
  * Capture QueryTable destination and ResultRange before deletion
  * Clear only ResultRange (not entire sheet)
  * Recreate at same location with clearEntireSheet: false
  * Eliminates legacy clearEntireSheet anti-pattern
- Fixed QueryTable tests to pass M code directly instead of file paths:
  * Removed file I/O operations (7 occurrences)
  * Aligned with PowerQueryCommands.Create() API signature

All 9 originally failing tests now pass (2 formula + 7 QueryTable/PowerQuery).

* Include mcp.json in version control

- Added .vscode/mcp.json to .gitignore exceptions
- Tracks MCP server configuration for project

* Add sheet movement and cross-workbook operations: Move, CopyToWorkbook, MoveToWorkbook
 2025-11-17 09:27:59 +00:00
Copilot c0474b350d Add comprehensive MCP client discovery support for Claude, Cursor, Cline, and Windsurf (#175) 
* Initial plan

* Add comprehensive MCP client discovery support

- Add example configs for Claude, Cursor, Cline, Windsurf, VS Code
- Update README with MCP Registry badge and multi-client setup
- Expand INSTALLATION.md with detailed per-client instructions
- Create examples/mcp-configs/ with ready-to-use config files
- Add setup guide covering all major MCP clients

Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>

---------

Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>
 2025-11-13 18:34:16 +01:00
Copilot b156c6af2d Add batch mode to CLI for RPA workflows (75-90% faster multi-operation execution) (#153) 
* Initial plan

* Add basic batch commands to CLI (begin, commit, list)

Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>

* Add batch-id parameter support to CLI commands with CommandHelper

Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>

* Add batch mode documentation to CLI README

Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>

* Add batch mode example scripts and documentation

Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>

---------

Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: sbroenne <3026464+sbroenne@users.noreply.github.com>
 2025-11-09 14:51:17 +01:00