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

Files

T

Stefan Broenner b00ee54dfe perf: Optimize PivotTable RefreshTable() calls (#286 )

* perf: Remove unnecessary RefreshTable() calls and optimize test fixtures

Performance optimizations:

1. Remove unnecessary RefreshTable() from visual-only operations:
   - SetFieldFormat (OLAP + Regular) - NumberFormat is visual only
   - SetSubtotals (OLAP + Regular) - removed 2 calls each
   - SetLayout (OLAP + Regular) - layout changes don't need refresh
   - SetGrandTotals (OLAP + Regular) - visual setting
   - SortField (OLAP + Regular) - sorting is visual
   - SetFieldName (Regular only) - OLAP throws exception

2. Fix SetFieldFunction measure lookup bug:
   - Added NormalizeMeasureName() helper to extract measure name from [Measures].[Name] format
   - Fixed lookup to use normalized name

3. Optimize test fixture sharing:
   - Changed from IClassFixture (1 fixture per class) to ICollectionFixture (1 fixture for ALL classes)
   - Created DataModelTestsDefinition collection for 6 test classes
   - Reduces fixture setup from ~9 min to ~1.5 min

4. Fix test measure name conflict:
   - Changed AddValueField test to use 'Regional Sales Total' instead of 'Total Sales'

Total RefreshTable() calls removed: 13
Test classes now sharing fixture: 6

* test: Add diagnostic tests for RefreshTable() behavior

Add 9 raw Excel COM API tests to empirically determine:
- Which PivotTable operations require RefreshTable()
- Which operations work immediately without RefreshTable()
- Persistence behavior with and without RefreshTable()

Key findings documented:
- Structural changes (Orientation) need RefreshTable() for persistence
- Visual properties (NumberFormat) work after structure is refreshed
- In-memory operations don't need refresh for immediate effect

* perf: Remove 2 more unnecessary RefreshTable() calls

Based on diagnostic tests proving persistence works without refresh:
- SetFieldFunction: Function changes persist without RefreshTable()
- SetFieldFilter: Filter changes persist without RefreshTable()

Added 2 new diagnostic tests:
- FunctionChange_Persistence_WithoutRefresh_VerifySaveAndReopen
- FilterChange_Persistence_WithoutRefresh_VerifySaveAndReopen

Total RefreshTable() calls removed: 15 (13 initial + 2 additional)

---------

Co-authored-by: Stefan Broenner <stefan.broenner@microsoft.comm>

2025-12-06 12:18:36 +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

perf: Optimize PivotTable RefreshTable() calls (#286 )

2025-12-06 12:18:36 +00:00

ExcelMcp.McpServer.Tests

feat: Add Docker support for Glama.ai MCP registry integration (#284 )

2025-12-06 07:18: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