mirror of https://github.com/sbroenne/mcp-server-excel.git synced 2026-09-19 07:53:08 +08:00

Files

T

Stefan Broenner 0bb29be3be feat: DAX formula locale handling for European locales (#282 )

* Fix #279: Power Query lifecycle cleanup for Data Model connections

BREAKING CHANGES:
- Unload now removes Data Model connections (not just worksheet tables)
- Delete now removes Data Model connections (prevents orphan connections)

IMPROVEMENTS:
- List correctly reports IsConnectionOnly for Data Model-only queries
- Update uses connection.Refresh() fallback for Data Model-only queries
  (no worksheet table to refresh)

NEW TESTS:
- PowerQueryCommandsTests.LifecycleCleanup.cs: 8 tests for Issue #279
- PowerQueryCommandsTests.UpdateErrors.cs: 3 tests for error handling
- DataModelComApiBehaviorTests.cs: 11 diagnostic scenarios
- PowerQueryComApiBehaviorTests.cs: 15 diagnostic scenarios

DOCUMENTATION:
- docs/COM-API-BEHAVIOR-FINDINGS.md: Empirical COM API behavior findings
- excel_powerquery.md: Updated prompt docs for Data Model cleanup

DELETED:
- PowerQueryCommandsTests.ErrorQueries.cs: Referenced non-existent local file

All 53 PowerQuery and DataModel tests pass.

* fix: Use locale-aware format translation instead of global Excel settings

- Renamed DateFormatTranslator to NumberFormatTranslator
- Extended translator to handle decimal/thousands separators (. and ,)
- Removed global UseSystemSeparators/DecimalSeparator/ThousandsSeparator
  settings from ExcelSession and ExcelBatch (these modified Excel app defaults)
- Added IsEnglishNumberLocale property to detect US-style number separators
- Format codes are now translated at point of use, not by changing app defaults
- Updated all references from DateFormatter to FormatTranslator
- Removed unused WithUSFormatSeparators helper from RangeCommands

This approach translates US format codes (like $#,##0.00) to locale-specific
codes (like $#.##0,00 on German Excel) without modifying Excel's global settings.
Previous approach of changing Application.DecimalSeparator affected all
workbooks in the Excel instance, which is not desirable.

* feat: DAX formula locale handling for European locales (#281)

- Add DaxFormulaTranslator to convert US comma separators to semicolons on European locales
- Integrate translator in CreateMeasure and UpdateMeasure operations
- Power Pivot uses system culture (CultureInfo.CurrentCulture), not Excel International settings
- Preserve commas inside string literals and column references
- Add 5 integration tests for DAX locale translation

2025-12-05 13:55:49 +00:00

ExcelMcp.CLI.Tests

perf: replace CreateUniqueTestFile with fixture-based file creation (#266 )

2025-12-04 09:06:26 +00:00

ExcelMcp.ComInterop.Tests

feat: Locale-aware number formatting & LLM guidance improvements (#260 )

2025-12-03 12:26:39 +00:00

ExcelMcp.Core.Tests

feat: DAX formula locale handling for European locales (#282 )

2025-12-05 13:55:49 +00:00

ExcelMcp.McpServer.Tests

feat: Locale-aware number formatting & LLM guidance improvements (#260 )

2025-12-03 12:26:39 +00:00

Directory.Build.props

refactor: simplify telemetry - always enabled, build-time connection string (#246 )

2025-12-01 13:34:39 +00:00

README.md

fix: CodeQL useless-assignment violation in ExcelRangeTool.SetStyleAsync + scripts cleanup (#120 )

2025-11-04 06:33:59 +00:00

README.md

ExcelMcp Tests

⚠️ No Traditional Unit Tests: ExcelMcp has no unit tests. Integration tests ARE our unit tests because Excel COM cannot be meaningfully mocked. See docs/ADR-001-NO-UNIT-TESTS.md for full architectural rationale.

Quick Start

# Development (fast feedback - excludes VBA tests)
dotnet test --filter "Category=Integration&RunType!=OnDemand&Feature!=VBA&Feature!=VBATrust"

# Pre-commit (comprehensive - excludes VBA tests)
dotnet test --filter "Category=Integration&RunType!=OnDemand&Feature!=VBA&Feature!=VBATrust"

# Session/batch changes (MANDATORY when modifying session/batch code)
dotnet test --filter "RunType=OnDemand"

# VBA tests (manual only - requires VBA trust enabled)
dotnet test --filter "(Feature=VBA|Feature=VBATrust)&RunType!=OnDemand"

Documentation

For complete testing guidance, see:

Testing Strategy - Quick reference, templates, common mistakes
Critical Rules - Mandatory development rules (Rule 14: SaveAsync)

Test Architecture

tests/
├── ExcelMcp.Core.Tests/      # Core business logic (Unit + Integration)
├── ExcelMcp.McpServer.Tests/ # MCP protocol layer (Unit + Integration)
├── ExcelMcp.CLI.Tests/        # CLI wrapper (Unit + Integration)
└── ExcelMcp.ComInterop.Tests/ # COM utilities (Unit + OnDemand)

Test Categories

Category	Speed	Requirements	Run By Default
Unit	Fast (2-5 sec)	None	✅ Yes (CI/CD)
Integration	Medium (10-20 min)	Excel + Windows	✅ Yes (local)
OnDemand	Slow (3-5 min)	Excel + Windows	❌ No (explicit only)

Feature-Specific Tests

# Test specific feature only
dotnet test --filter "Feature=PowerQuery&RunType!=OnDemand"
dotnet test --filter "Feature=DataModel&RunType!=OnDemand"
dotnet test --filter "Feature=Tables&RunType!=OnDemand"
dotnet test --filter "Feature=PivotTables&RunType!=OnDemand"
dotnet test --filter "Feature=Ranges&RunType!=OnDemand"
dotnet test --filter "Feature=Connections&RunType!=OnDemand"

When to Run Which Tests

Scenario	Command
Daily development	`dotnet test --filter "Category=Integration&RunType!=OnDemand&Feature!=VBA"`
Before commit	`dotnet test --filter "Category=Integration&RunType!=OnDemand&Feature!=VBA"`
Modified session/batch code	`dotnet test --filter "RunType=OnDemand"` (see Rule 3)
VBA development	`dotnet test --filter "(Feature=VBA\|Feature=VBATrust)&RunType!=OnDemand"`

VBA Testing

Why VBA Tests Are Excluded by Default

VBA tests are excluded from normal test runs because:

Stable codebase - VBA features are mature with minimal changes
Performance - Excluding VBA tests makes integration tests ~25% faster (10-15 min vs 15-20 min)
Special requirements - VBA tests require VBA trust enabled in Excel settings
Opt-in model - Explicit testing when VBA code changes, rather than every commit

When to Run VBA Tests

Run VBA tests manually when:

Modifying VBA-related code (ScriptCommands, VbaTrustDetection)
Adding new VBA features
Before releasing VBA-related changes
Troubleshooting VBA-specific issues

How to Run VBA Tests

# Run ONLY VBA tests
dotnet test --filter "(Feature=VBA|Feature=VBATrust)&RunType!=OnDemand"

# Run ALL tests including VBA (takes longer)
dotnet test --filter "Category=Integration&RunType!=OnDemand"

VBA Test Files

All VBA tests are tagged with [Trait("Feature", "VBA")] or [Trait("Feature", "VBATrust")]:

tests/ExcelMcp.Core.Tests/Integration/Commands/Script/
  - ScriptCommandsTests.cs
  - ScriptCommandsTests.Lifecycle.cs
  - VbaTrustDetectionTests.ScriptCommands.cs
  - VbaTrustDetectionTests.cs

tests/ExcelMcp.CLI.Tests/Integration/Commands/
  - ScriptAndSetupCommandsTests.cs

VBA Trust Setup

VBA tests require VBA trust enabled in Excel:

# Enable VBA trust (required for VBA tests)
Set-ItemProperty -Path "HKCU:\Software\Microsoft\Office\16.0\Excel\Security" -Name "AccessVBOM" -Value 1

# Verify setting
Get-ItemProperty -Path "HKCU:\Software\Microsoft\Office\16.0\Excel\Security" -Name "AccessVBOM"

Security Note: Only enable VBA trust in development environments. Production systems should keep this disabled.

Key Principles

✅ File Isolation - Each test creates unique file (no sharing)
✅ Binary Assertions - Pass OR fail, never "accept both"
✅ Verify Excel State - Always verify actual Excel state after operations
❌ No SaveAsync - Unless testing persistence (see Rule 14)

Getting Help

Test failures: Check test output for detailed error messages
Excel issues: Ensure Excel 2016+ installed and activated
Session/batch issues: Run OnDemand tests to verify cleanup
Writing tests: See Testing Strategy