using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Models;
namespace Sbroenne.ExcelMcp.Core.Commands.PivotTable;
/// <summary>
/// PivotTable field management operations
/// </summary>
public partial class PivotTableCommands
{
/// <summary>
/// Lists all fields in a PivotTable
/// </summary>
public PivotFieldListResult ListFields(IExcelBatch batch, string pivotTableName)
{
return batch.Execute((ctx, ct) =>
{
dynamic? pivot = null;
dynamic? pivotFields = null;
dynamic? cubeFields = null;
pivot = FindPivotTable(ctx.Book, pivotTableName);
// Check if this is an OLAP/Data Model PivotTable
bool isOlap = PivotTableHelpers.TryGetCubeFields(pivot, out cubeFields);
try
{
// For OLAP PivotTables, use CubeFields instead of PivotFields
if (isOlap)
{
return ListCubeFieldsAsync(cubeFields, batch.WorkbookPath);
}
else
{
// Regular PivotTable - use PivotFields
pivotFields = pivot.PivotFields;
return ListRegularFieldsAsync(pivotFields, batch.WorkbookPath);
}
}
finally
{
ComUtilities.Release(ref cubeFields);
ComUtilities.Release(ref pivotFields);
ComUtilities.Release(ref pivot);
}
});
}
/// <summary>
/// Lists fields from OLAP/Data Model PivotTable using CubeFields
/// </summary>
private static PivotFieldListResult ListCubeFieldsAsync(dynamic cubeFields, string filePath)
{
var fields = new List<PivotFieldInfo>();
int fieldCount = cubeFields.Count;
for (int i = 1; i <= fieldCount; i++)
{
dynamic? cubeField = null;
try
{
cubeField = cubeFields.Item(i);
// Get field name
string fieldName;
try
{
fieldName = cubeField.Name?.ToString() ?? $"CubeField{i}";
}
catch (System.Runtime.InteropServices.COMException)
{
fieldName = $"CubeField{i}";
}
// Get orientation - for CubeFields, check if it has a corresponding PivotField
int orientation = XlPivotFieldOrientation.xlHidden;
try
{
dynamic? pivotField = cubeField.PivotFields?.Item(1);
if (pivotField != null)
{
orientation = Convert.ToInt32(pivotField.Orientation);
ComUtilities.Release(ref pivotField);
}
}
catch (System.Runtime.InteropServices.COMException)
{
orientation = XlPivotFieldOrientation.xlHidden;
}
var fieldInfo = new PivotFieldInfo
{
Name = fieldName,
Area = orientation switch
{
XlPivotFieldOrientation.xlRowField => PivotFieldArea.Row,
XlPivotFieldOrientation.xlColumnField => PivotFieldArea.Column,
XlPivotFieldOrientation.xlPageField => PivotFieldArea.Filter,
XlPivotFieldOrientation.xlDataField => PivotFieldArea.Value,
_ => PivotFieldArea.Hidden
},
CustomName = string.Empty,
Position = 0,
DataType = "Cube"
};
fields.Add(fieldInfo);
}
catch (System.Runtime.InteropServices.COMException)
{
// Skip field if COM access fails - continue with other fields
}
finally
{
ComUtilities.Release(ref cubeField);
}
}
return new PivotFieldListResult
{
Success = true,
Fields = fields,
FilePath = filePath
};
}
/// <summary>
/// Lists fields from regular PivotTable using PivotFields
/// </summary>
private static PivotFieldListResult ListRegularFieldsAsync(dynamic pivotFields, string filePath)
{
var fields = new List<PivotFieldInfo>();
int fieldCount = pivotFields.Count;
for (int i = 1; i <= fieldCount; i++)
{
dynamic? field = null;
try
{
field = pivotFields.Item(i);
// Get field name with defensive handling (can throw on Data Model fields)
string fieldName;
try
{
fieldName = field.SourceName?.ToString() ?? field.Name?.ToString() ?? $"Field{i}";
}
catch (System.Runtime.InteropServices.COMException)
{
fieldName = $"Field{i}";
}
// Get orientation with defensive handling
int orientation;
try
{
orientation = Convert.ToInt32(field.Orientation);
}
catch (System.Runtime.InteropServices.COMException)
{
orientation = XlPivotFieldOrientation.xlHidden;
}
var fieldInfo = new PivotFieldInfo
{
Name = fieldName,
Area = orientation switch
{
XlPivotFieldOrientation.xlRowField => PivotFieldArea.Row,
XlPivotFieldOrientation.xlColumnField => PivotFieldArea.Column,
XlPivotFieldOrientation.xlPageField => PivotFieldArea.Filter,
XlPivotFieldOrientation.xlDataField => PivotFieldArea.Value,
_ => PivotFieldArea.Hidden
}
};
// CustomName - defensive
try
{
fieldInfo.CustomName = field.Caption?.ToString() ?? string.Empty;
}
catch (System.Runtime.InteropServices.COMException)
{
fieldInfo.CustomName = string.Empty;
}
// Position - defensive
try
{
fieldInfo.Position = orientation != XlPivotFieldOrientation.xlHidden ? Convert.ToInt32(field.Position) : 0;
}
catch (System.Runtime.InteropServices.COMException)
{
fieldInfo.Position = 0;
}
// DataType - defensive
try
{
fieldInfo.DataType = PivotTableHelpers.DetectFieldDataType(field);
}
catch (System.Runtime.InteropServices.COMException)
{
fieldInfo.DataType = "Unknown";
}
// Get function for value fields - defensive
if (orientation == XlPivotFieldOrientation.xlDataField)
{
try
{
int comFunction = Convert.ToInt32(field.Function);
fieldInfo.Function = PivotTableHelpers.GetAggregationFunctionFromCom(comFunction);
}
catch (System.Runtime.InteropServices.COMException)
{
fieldInfo.Function = AggregationFunction.Sum; // Default
}
}
fields.Add(fieldInfo);
}
catch (System.Runtime.InteropServices.COMException)
{
// Skip field if COM access fails - continue with other fields
}
finally
{
ComUtilities.Release(ref field);
}
}
return new PivotFieldListResult
{
Success = true,
Fields = fields,
FilePath = filePath
};
}
/// <summary>
/// Adds a field to the Row area
/// </summary>
public PivotFieldResult AddRowField(IExcelBatch batch, string pivotTableName,
string fieldName, int? position = null)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.AddRowField(pivot, fieldName, position, batch.WorkbookPath));
/// <summary>
/// Adds a field to the Column area
/// </summary>
public PivotFieldResult AddColumnField(IExcelBatch batch, string pivotTableName,
string fieldName, int? position = null)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.AddColumnField(pivot, fieldName, position, batch.WorkbookPath));
/// <summary>
/// Adds a field to the Values area with aggregation
/// </summary>
public PivotFieldResult AddValueField(IExcelBatch batch, string pivotTableName,
string fieldName, AggregationFunction aggregationFunction = AggregationFunction.Sum,
string? customName = null)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.AddValueField(pivot, fieldName, aggregationFunction, customName, batch.WorkbookPath));
/// <summary>
/// Adds a field to the Filter area
/// </summary>
public PivotFieldResult AddFilterField(IExcelBatch batch, string pivotTableName,
string fieldName)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.AddFilterField(pivot, fieldName, batch.WorkbookPath));
/// <summary>
/// Removes a field from any area
/// </summary>
public PivotFieldResult RemoveField(IExcelBatch batch, string pivotTableName,
string fieldName)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.RemoveField(pivot, fieldName, batch.WorkbookPath));
/// <summary>
/// Sets the aggregation function for a value field
/// </summary>
public PivotFieldResult SetFieldFunction(IExcelBatch batch, string pivotTableName,
string fieldName, AggregationFunction aggregationFunction)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.SetFieldFunction(pivot, fieldName, aggregationFunction, batch.WorkbookPath));
/// <summary>
/// Sets custom name for a field
/// </summary>
public PivotFieldResult SetFieldName(IExcelBatch batch, string pivotTableName,
string fieldName, string customName)
=> ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
(strategy, pivot) => strategy.SetFieldName(pivot, fieldName, customName, batch.WorkbookPath));
/// <summary>
/// Sets number format for a value field
/// </summary>
public PivotFieldResult SetFieldFormat(IExcelBatch batch, string pivotTableName,
string fieldName, string numberFormat)
{
return batch.Execute((ctx, ct) =>
{
dynamic? pivot = null;
pivot = FindPivotTable(ctx.Book, pivotTableName);
try
{
// Translate US format codes to locale-specific codes
var translatedFormat = ctx.FormatTranslator.TranslateToLocale(numberFormat);
// Use Strategy Pattern to delegate to appropriate implementation
var strategy = PivotTableFieldStrategyFactory.GetStrategy(pivot);
return strategy.SetFieldFormat(pivot, fieldName, translatedFormat, batch.WorkbookPath);
}
finally
{
ComUtilities.Release(ref pivot);
}
});
}
}
