sbroenne__mcp-server-excel

mirror of https://github.com/sbroenne/mcp-server-excel.git synced 2026-09-19 07:53:08 +08:00
Files
T
Stefan Broenner ae71b7cff9 feat(pivottable): Complete PivotTable expansion (Issue #219 ) (#223 )
* feat(pivottable): Add date grouping by Days/Months/Quarters/Years (Task 1 of Issue #219)

Implements GroupByDate operation for PivotTable fields with support for all 4 date intervals.
Excel automatically creates hierarchical groupings (e.g., Months + Years) for proper time-based analysis.

CRITICAL DISCOVERY - NumberFormat Requirement:
- Source data MUST be formatted with date NumberFormat BEFORE creating PivotTable
- Without proper formatting, dates stored as serial numbers (e.g., 45672) with 'Standard' format
- Excel silently fails to group plain numbers - grouping requires date-formatted data
- Solution: Apply NumberFormat = 'm/d/yyyy' to source range before PivotTable creation

Implementation:
- Added DateGroupingInterval enum (Days, Months, Quarters, Years)
- Implemented GroupByDate in RegularPivotTableFieldStrategy using Range.Group()
- Uses PivotField.DataRange.Cells[1,1] for single cell grouping
- Periods array: [Seconds, Minutes, Hours, Days, Months, Quarters, Years]
- Hierarchical grouping: Months/Quarters automatically include Years
- MCP Server: Added GroupByDate action to ExcelPivotTableTool with dateGroupingInterval parameter

Infrastructure Changes:
- Added xUnit logging support via MartinCostello.Logging.XUnit package
- Added IExcelBatch.Logger property with proper STA thread affinity
- Logger passed through batch operations for diagnostics

Test Coverage (4 new tests, all passing):
- GroupByDate_DaysInterval_CreatesDailyGroups
- GroupByDate_MonthsInterval_CreatesMonthlyGroups
- GroupByDate_QuartersInterval_CreatesQuarterlyGroups
- GroupByDate_YearsInterval_CreatesYearlyGroups

All 38 PivotTable tests passing.

Technical Details:
- Date serial: 45672 (Double) = 2025-01-15 (days since 1900-01-01)
- DataRange for Row/Column fields = 'Items in the field'
- Group() called on single cell, not entire range
- Error logging preserved with #pragma CA1848 suppression

Documentation:
- XML docs on interface and implementation with NumberFormat requirement
- Code examples showing proper date formatting

Related: #219 (Task 1 complete)

* feat(pivottable): Add numeric grouping + remove misleading Async naming (Task 2 of Issue #219)

Implements GroupByNumeric operation for numeric field range grouping.
Creates groups like 0-100, 100-200, 200-300 for age groups, price ranges, score bands.

Implementation:
- Uses Range.Group() with Start, End, By parameters
- By parameter specifies interval size (e.g., 100)
- Start/End nullable → null = auto-detect field min/max
- Periods parameter ignored for numeric fields (only for dates)
- Type casting required: double? → object for COM interop
- VB.NET compatibility: Renamed 'end' parameter to 'endValue'

BREAKING CHANGE (Code Quality Improvement):
- Removed misleading 'Async' suffix from 21 synchronous methods in ExcelPivotTableTool
- Methods are synchronous (no async/await/Task) but were incorrectly named
- User observation: 'why do we have Async - everything is Sync!' (correct!)
- Affected: List, Read, Create*, Delete, Refresh, *Field, GroupBy* methods
- Impact: Code now accurately reflects synchronous implementation
- No API change: Method names were internal/private

Documentation Updates:
- Rewrote mcp-server-guide.instructions.md to remove all Async references
- Clarified that all action methods are synchronous
- Tool signatures are 'async Task<string>' only for MCP SDK requirements
- Updated all code examples to show synchronous patterns

Test Coverage (3 new tests, all passing):
- GroupByNumeric_AutoRange_CreatesNumericGroups (interval=100, auto min/max)
- GroupByNumeric_CustomRange_CreatesNumericGroups (0-1000, interval=200)
- GroupByNumeric_SmallInterval_CreatesFineGrainedGroups (interval=50)
- Fixed aggregation function: Use AggregationFunction.Count for text fields

Technical Details:
- Type casting: start.HasValue ? (object)start.Value : true
- COM interop: Excel expects object type for Group() parameters
- VB.NET interop: 'end' keyword conflict → renamed to 'endValue'
- OLAP: Not supported (returns clear error with Power Pivot guidance)

All 41 PivotTable tests passing (38 existing + 3 new).

Related: #219 (Task 2 complete)

* feat(pivottable): Add calculated fields with formulas

Implements Task 3 of Issue #219: CreateCalculatedField operation

FEATURES:
- Regular PivotTables: Full support via CalculatedFields.Add() API
- OLAP PivotTables: NotSupported (use DAX measures instead)
- Formula syntax: =FieldName1 operator FieldName2 (e.g., =Revenue-Cost)
- Operators: + - * / ^ ()

IMPLEMENTATION:
- IPivotTableFieldStrategy: CreateCalculatedField interface method
- RegularPivotTableFieldStrategy: Uses pivot.CalculatedFields().Add()
- OlapPivotTableFieldStrategy: Returns NotSupported with DAX workflow hint
- PivotTableCommands.CalculatedFields.cs: Routes to correct strategy
- PivotTableAction.CreateCalculatedField: New enum value
- ExcelPivotTableTool: MCP Server integration with formula parameter
- PivotFieldResult: Added Formula property for result tracking

TESTS:
- 4 new regular PivotTable tests (multiplication, subtraction, complex, addition)
- 1 new OLAP test using DataModelTestsFixture
- Tests cover: formulas with different operators and parentheses
- OLAP test verifies NotSupported behavior with proper error messages

COMPATIBILITY:
- All 41 existing PivotTable tests passing (no regressions)
- Total: 46 passing tests (41 existing + 5 new)

Closes Task 3 of #219

* feat(pivottable): Add layout and subtotals control (Task 4 of Issue #219)

Implements Task 4 of Issue #219: SetLayout and SetSubtotals operations

FEATURES:
- Layout control: Compact (default), Tabular, Outline forms
- Subtotals control: Show/hide automatic subtotals per field
- Full support for both regular and OLAP PivotTables

LAYOUT FORMS:
- Compact (0): All row fields in single column with indentation
- Tabular (1): Each field in separate column, subtotals at bottom
- Outline (2): Each field in separate column, subtotals at top

IMPLEMENTATION:
- IPivotTableFieldStrategy: SetLayout and SetSubtotals interface methods
- RegularPivotTableFieldStrategy: Uses RowAxisLayout() and field.Subtotals[1]
- OlapPivotTableFieldStrategy: Full support for both operations
- PivotTableCommands.Layout.cs: Routes to correct strategy
- PivotTableCommands.Subtotals.cs: Routes to correct strategy
- PivotTableAction: SetLayout and SetSubtotals enum values
- ExcelPivotTableTool: MCP Server integration with layout and subtotalsVisible parameters

TESTS:
- 6 new tests covering all layout forms and subtotals scenarios
- Tests for both regular and OLAP PivotTables
- Round-trip verification tests

COMPATIBILITY:
- All 46 existing PivotTable tests passing (no regressions)
- Total: 52 passing tests (46 existing + 6 new)

CLOSES #219: All 4 tasks complete
- Task 1: Date grouping (committed 52d1a6d)
- Task 2: Numeric grouping (committed 2d58a45)
- Task 3: Calculated fields (committed 6b6d683)
- Task 4: Layout & Subtotals (this commit)

Issue #219 complete - PivotTable automation fully expanded

* feat(pivottable): Add grand totals control (Task 5 of Issue #219)

Implements feature #8 from Issue #219: SetGrandTotals operation for independent row/column grand totals control in PivotTables.

FEATURES:
- Row grand totals control: Show/hide bottom summary row
- Column grand totals control: Show/hide right summary column
- Independent control: Configure row and column separately
- Full support: Both regular and OLAP PivotTables

IMPLEMENTATION:
- IPivotTableFieldStrategy: SetGrandTotals interface method using pivot.RowGrand/ColumnGrand properties
- RegularPivotTableFieldStrategy: Direct COM property assignment
- OlapPivotTableFieldStrategy: Same COM properties (full OLAP support)
- PivotTableCommands.GrandTotals.cs: Routes to appropriate strategy via factory
- PivotTableAction: SetGrandTotals enum value added
- ExcelPivotTableTool: MCP Server integration with showRowGrandTotals/showColumnGrandTotals parameters

TESTS:
- 6 new tests covering all show/hide combinations
- Test scenarios: show both, hide both, show row only, show column only, multiple sequential changes, round-trip persistence
- All tests passing (58 total: 52 existing + 6 new)

VERIFICATION:
- Build: 0 warnings, 0 errors
- Tests: 58/58 passing (all PivotTable tests)
- Pre-commit: All checks passed (COM leaks, coverage, naming, switch completeness, success flag, MCP smoke test)

CLOSES #219: All required features complete
- Priority 1 (Tasks 1-4 from commits 2417b84, 3cd4a15, c93e44b, 4826ca5): Date grouping, numeric grouping, calculated fields, layout & subtotals ✅
- Feature #8 (Task 5 - this commit): Grand totals control ✅
- Features #6-7 not needed per requirements (OLAP drill, connection management)

* docs: Update operation counts and shorten release notes for Issue #219

- Updated PivotTable actions: 20 → 21 (added SetGrandTotals operation)
- Updated total operations: 163 → 164, total actions: 155 → 156
- Added CHANGELOG entry with Task 5 features (grand totals, grouping, calculated fields, layout/subtotals)
- Shortened release workflow notes ~70% to reduce redundancy with main README
- Files updated: README.md, vscode-extension/README.md, gh-pages/index.md, ExcelMcp.McpServer/README.md, CHANGELOG.md, release-mcp-server.yml

* Merge remote-tracking branch 'origin/main' into feature/pivottable-expansion-219