using System.Runtime.InteropServices;
using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Models;
namespace Sbroenne.ExcelMcp.Core.Commands.Table;
/// <summary>
/// Table slicer operations (CreateTableSlicer, ListTableSlicers, SetTableSlicerSelection, DeleteTableSlicer)
/// </summary>
public partial class TableCommands
{
/// <inheritdoc />
public SlicerResult CreateTableSlicer(IExcelBatch batch, string tableName,
string columnName, string slicerName, string destinationSheet, string position)
{
// Security: Validate table name
ValidateTableName(tableName);
return batch.Execute((ctx, ct) =>
{
dynamic? table = null;
dynamic? slicerCaches = null;
dynamic? slicerCache = null;
dynamic? slicers = null;
dynamic? slicer = null;
dynamic? destSheet = null;
dynamic? destRange = null;
try
{
table = FindTable(ctx.Book, tableName);
slicerCaches = ctx.Book.SlicerCaches;
// Validate the column exists in the table before creating slicer
if (!TableColumnExists(table, columnName))
{
return new SlicerResult
{
Success = false,
ErrorMessage = $"Column '{columnName}' not found in table '{tableName}'"
};
}
// Check if a SlicerCache already exists for this column on this table
slicerCache = FindExistingTableSlicerCache(slicerCaches, table, columnName);
if (slicerCache == null)
{
// Create new SlicerCache for this column
// Use the deprecated SlicerCaches.Add(source, sourceField) method for Table slicers
// The Add method (without SlicerCacheType) accepts ListObject as source
// Note: Add2 does NOT accept ListObject per Microsoft documentation
slicerCache = slicerCaches.Add(table, columnName);
}
// Get destination sheet and calculate position from cell reference
destSheet = ctx.Book.Worksheets.Item(destinationSheet);
destRange = destSheet.Range[position];
// Get position in points from the cell reference
double top = Convert.ToDouble(destRange.Top);
double left = Convert.ToDouble(destRange.Left);
// Add visual Slicer to the cache
// Slicers.Add(SlicerDestination, Level, Name, Caption, Top, Left, Width, Height)
// For non-OLAP sources, Level should be Type.Missing or omitted
slicers = slicerCache.Slicers;
slicer = slicers.Add(destSheet, Type.Missing, slicerName, slicerName, top, left);
// Build result
var result = BuildTableSlicerResult(slicer, slicerCache, columnName, tableName);
result.Success = true;
result.WorkflowHint = $"Slicer '{slicerName}' created for column '{columnName}' in table '{tableName}'. Use SetTableSlicerSelection to filter data.";
return result;
}
finally
{
ComUtilities.Release(ref destRange);
ComUtilities.Release(ref destSheet);
ComUtilities.Release(ref slicer);
ComUtilities.Release(ref slicers);
ComUtilities.Release(ref slicerCache);
ComUtilities.Release(ref slicerCaches);
ComUtilities.Release(ref table);
}
});
}
/// <inheritdoc />
public SlicerListResult ListTableSlicers(IExcelBatch batch, string? tableName = null)
{
// Security: Validate table name if provided
if (!string.IsNullOrEmpty(tableName))
{
ValidateTableName(tableName);
}
return batch.Execute((ctx, ct) =>
{
var result = new SlicerListResult { Success = true };
dynamic? slicerCaches = null;
dynamic? targetTable = null;
try
{
slicerCaches = ctx.Book.SlicerCaches;
// If filtering by Table, find it first
if (!string.IsNullOrEmpty(tableName))
{
targetTable = FindTable(ctx.Book, tableName);
}
for (int cacheIndex = 1; cacheIndex <= slicerCaches.Count; cacheIndex++)
{
dynamic? cache = null;
dynamic? slicers = null;
try
{
cache = slicerCaches.Item(cacheIndex);
// Check if this is a Table slicer using the List boolean property
// SlicerCache.List returns true if the slicer is connected to a ListObject (Table)
bool isTableSlicer = false;
try
{
isTableSlicer = cache.List == true;
}
catch (COMException)
{
// List property not available - not a Table slicer
isTableSlicer = false;
}
if (!isTableSlicer)
{
continue; // Skip non-Table slicers (e.g., PivotTable slicers)
}
// If filtering by Table, check if this cache is connected to it
if (targetTable != null && !IsSlicerCacheConnectedToTable(cache, targetTable))
{
continue;
}
// Get the connected table name
string connectedTableName = GetSlicerCacheTableName(cache);
slicers = cache.Slicers;
for (int slicerIndex = 1; slicerIndex <= slicers.Count; slicerIndex++)
{
dynamic? slicer = null;
try
{
slicer = slicers.Item(slicerIndex);
var slicerInfo = BuildTableSlicerInfo(slicer, cache, connectedTableName);
result.Slicers.Add(slicerInfo);
}
finally
{
ComUtilities.Release(ref slicer);
}
}
}
finally
{
ComUtilities.Release(ref slicers);
ComUtilities.Release(ref cache);
}
}
return result;
}
finally
{
ComUtilities.Release(ref targetTable);
ComUtilities.Release(ref slicerCaches);
}
});
}
/// <inheritdoc />
public SlicerResult SetTableSlicerSelection(IExcelBatch batch, string slicerName,
List<string> selectedItems, bool clearFirst = true)
{
return batch.Execute((ctx, ct) =>
{
dynamic? slicerCaches = null;
dynamic? targetCache = null;
dynamic? targetSlicer = null;
dynamic? slicerItems = null;
try
{
slicerCaches = ctx.Book.SlicerCaches;
// Find the slicer by name (searching Table slicers only)
var searchResult = FindTableSlicerByName(slicerCaches, slicerName);
targetCache = searchResult.Cache;
targetSlicer = searchResult.Slicer;
if (targetSlicer == null || targetCache == null)
{
return new SlicerResult
{
Success = false,
ErrorMessage = $"Table slicer '{slicerName}' not found in workbook"
};
}
// Get slicer items from the cache
slicerItems = targetCache.SlicerItems;
// Build set of items to select for fast lookup
var itemsToSelect = new HashSet<string>(selectedItems, StringComparer.OrdinalIgnoreCase);
// If no items specified, select all (clear filter)
bool selectAll = selectedItems.Count == 0;
// Iterate through slicer items and set selection
for (int i = 1; i <= slicerItems.Count; i++)
{
dynamic? item = null;
try
{
item = slicerItems.Item(i);
string itemName = item.Name?.ToString() ?? string.Empty;
if (selectAll)
{
item.Selected = true;
}
else if (clearFirst)
{
// Clear first mode: select only specified items
item.Selected = itemsToSelect.Contains(itemName);
}
else
{
// Additive mode: add to existing selection
if (itemsToSelect.Contains(itemName))
{
item.Selected = true;
}
}
}
finally
{
ComUtilities.Release(ref item);
}
}
// Build result with updated state
string columnName = GetSlicerCacheColumnName(targetCache);
string connectedTableName = GetSlicerCacheTableName(targetCache);
var result = BuildTableSlicerResult(targetSlicer, targetCache, columnName, connectedTableName);
result.Success = true;
result.WorkflowHint = selectAll
? $"Table slicer '{slicerName}' filter cleared - all items are now visible."
: $"Table slicer '{slicerName}' selection updated to {selectedItems.Count} item(s).";
return result;
}
finally
{
ComUtilities.Release(ref slicerItems);
ComUtilities.Release(ref targetSlicer);
ComUtilities.Release(ref targetCache);
ComUtilities.Release(ref slicerCaches);
}
});
}
/// <inheritdoc />
public OperationResult DeleteTableSlicer(IExcelBatch batch, string slicerName)
{
return batch.Execute((ctx, ct) =>
{
dynamic? slicerCaches = null;
dynamic? targetCache = null;
dynamic? targetSlicer = null;
try
{
slicerCaches = ctx.Book.SlicerCaches;
// Find the slicer by name (searching Table slicers only)
var searchResult = FindTableSlicerByName(slicerCaches, slicerName);
targetCache = searchResult.Cache;
targetSlicer = searchResult.Slicer;
if (targetSlicer == null)
{
return new OperationResult
{
Success = false,
ErrorMessage = $"Table slicer '{slicerName}' not found in workbook"
};
}
// Delete the visual slicer
targetSlicer.Delete();
// Note: The SlicerCache will be automatically deleted if this was the last slicer
// connected to it. Excel handles this automatically.
return new OperationResult { Success = true };
}
finally
{
ComUtilities.Release(ref targetSlicer);
ComUtilities.Release(ref targetCache);
ComUtilities.Release(ref slicerCaches);
}
});
}
#region Table Slicer Helper Methods
/// <summary>
/// Checks if a column with the given name exists in the table
/// </summary>
/// <param name="table">The table (ListObject) to check</param>
/// <param name="columnName">The column name to search for</param>
/// <returns>True if the column exists, false otherwise</returns>
private static bool TableColumnExists(dynamic table, string columnName)
{
dynamic? listColumns = null;
try
{
listColumns = table.ListColumns;
for (int i = 1; i <= listColumns.Count; i++)
{
dynamic? column = null;
try
{
column = listColumns.Item(i);
string name = column.Name?.ToString() ?? string.Empty;
if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase))
{
return true;
}
}
finally
{
ComUtilities.Release(ref column);
}
}
return false;
}
finally
{
ComUtilities.Release(ref listColumns);
}
}
/// <summary>
/// Result of searching for a table slicer by name
/// </summary>
private readonly struct TableSlicerSearchResult
{
public dynamic? Cache { get; init; }
public dynamic? Slicer { get; init; }
}
/// <summary>
/// Finds an existing SlicerCache for a column on a specific Table
/// </summary>
private static dynamic? FindExistingTableSlicerCache(dynamic slicerCaches, dynamic table, string columnName)
{
for (int i = 1; i <= slicerCaches.Count; i++)
{
dynamic? cache = null;
try
{
cache = slicerCaches.Item(i);
// Check if this is a Table slicer using the List boolean property
bool isTableSlicer = false;
try
{
isTableSlicer = cache.List == true;
}
catch (COMException)
{
// List property doesn't exist on PivotTable slicer caches
isTableSlicer = false;
}
if (!isTableSlicer)
{
ComUtilities.Release(ref cache);
continue;
}
// Check if cache is for the same column
string cacheColumnName = GetSlicerCacheColumnName(cache);
if (!string.Equals(cacheColumnName, columnName, StringComparison.OrdinalIgnoreCase))
{
ComUtilities.Release(ref cache);
continue;
}
// Check if this cache is connected to our Table
if (IsSlicerCacheConnectedToTable(cache, table))
{
return cache; // Don't release - returning to caller
}
ComUtilities.Release(ref cache);
}
catch (COMException)
{
// COM access may fail for certain cache types - continue searching
ComUtilities.Release(ref cache);
}
}
return null;
}
/// <summary>
/// Gets the source column name from a Table SlicerCache.
/// Precondition: cache.List == true (verified by caller)
/// </summary>
private static string GetSlicerCacheColumnName(dynamic cache)
{
// Per MS docs: SourceName returns the field name for table slicers
// https://learn.microsoft.com/en-us/office/vba/api/excel.slicercache.sourcename
string? sourceName = cache.SourceName?.ToString();
if (!string.IsNullOrEmpty(sourceName))
return sourceName;
// Fallback: parse from cache name (usually "Slicer_ColumnName" format)
string cacheName = cache.Name?.ToString() ?? string.Empty;
if (cacheName.StartsWith("Slicer_", StringComparison.OrdinalIgnoreCase))
{
return cacheName[7..]; // Remove "Slicer_" prefix
}
return cacheName;
}
/// <summary>
/// Gets the connected Table name from a SlicerCache.
/// Precondition: cache.List == true (verified by caller)
/// </summary>
private static string GetSlicerCacheTableName(dynamic cache)
{
// Per MS docs: ListObject returns the Table for table slicers
// https://learn.microsoft.com/en-us/office/vba/api/excel.slicercache.listobject
dynamic? listObject = null;
try
{
listObject = cache.ListObject;
return listObject?.Name?.ToString() ?? string.Empty;
}
finally
{
ComUtilities.Release(ref listObject);
}
}
/// <summary>
/// Checks if a SlicerCache is connected to a specific Table.
/// Precondition: cache.List == true (verified by caller)
/// </summary>
private static bool IsSlicerCacheConnectedToTable(dynamic cache, dynamic table)
{
// Per MS docs: ListObject returns the Table for table slicers
dynamic? cacheListObject = null;
try
{
cacheListObject = cache.ListObject;
if (cacheListObject == null)
return false;
string cacheTableName = cacheListObject.Name?.ToString() ?? string.Empty;
string targetTableName = table.Name?.ToString() ?? string.Empty;
return string.Equals(cacheTableName, targetTableName, StringComparison.OrdinalIgnoreCase);
}
finally
{
ComUtilities.Release(ref cacheListObject);
}
}
/// <summary>
/// Finds a Table slicer by name
/// </summary>
private static TableSlicerSearchResult FindTableSlicerByName(dynamic slicerCaches, string slicerName)
{
for (int cacheIndex = 1; cacheIndex <= slicerCaches.Count; cacheIndex++)
{
dynamic? cache = null;
dynamic? slicers = null;
try
{
cache = slicerCaches.Item(cacheIndex);
// Check if this is a Table slicer using the List boolean property
bool isTableSlicer = false;
try
{
isTableSlicer = cache.List == true;
}
catch (COMException)
{
// List property doesn't exist on PivotTable slicer caches
isTableSlicer = false;
}
if (!isTableSlicer)
{
ComUtilities.Release(ref cache);
continue;
}
slicers = cache.Slicers;
for (int slicerIndex = 1; slicerIndex <= slicers.Count; slicerIndex++)
{
dynamic? slicer = null;
try
{
slicer = slicers.Item(slicerIndex);
string name = slicer.Name?.ToString() ?? string.Empty;
if (string.Equals(name, slicerName, StringComparison.OrdinalIgnoreCase))
{
// Don't release cache and slicer - returning to caller
ComUtilities.Release(ref slicers);
return new TableSlicerSearchResult { Cache = cache, Slicer = slicer };
}
ComUtilities.Release(ref slicer);
}
catch (COMException)
{
// COM access failed for this slicer, continue searching
ComUtilities.Release(ref slicer);
}
}
ComUtilities.Release(ref slicers);
ComUtilities.Release(ref cache);
}
catch (COMException)
{
// COM access failed for this cache, continue searching
ComUtilities.Release(ref slicers);
ComUtilities.Release(ref cache);
}
}
return new TableSlicerSearchResult { Cache = null, Slicer = null };
}
/// <summary>
/// Builds a SlicerInfo object for a Table slicer
/// </summary>
private static SlicerInfo BuildTableSlicerInfo(dynamic slicer, dynamic cache, string tableName)
{
dynamic? parent = null;
dynamic? slicerItems = null;
try
{
parent = slicer.Parent;
slicerItems = cache.SlicerItems;
var info = new SlicerInfo
{
Name = slicer.Name?.ToString() ?? string.Empty,
Caption = slicer.Caption?.ToString() ?? string.Empty,
FieldName = GetSlicerCacheColumnName(cache),
SheetName = parent?.Name?.ToString() ?? string.Empty,
Position = GetSlicerPosition(slicer),
ColumnCount = Convert.ToInt32(slicer.NumberOfColumns ?? 1),
ConnectedTable = tableName,
SourceType = "Table"
};
// Collect selected and available items
for (int i = 1; i <= slicerItems.Count; i++)
{
dynamic? item = null;
try
{
item = slicerItems.Item(i);
string itemName = item.Name?.ToString() ?? string.Empty;
info.AvailableItems.Add(itemName);
bool isSelected = item.Selected;
if (isSelected)
{
info.SelectedItems.Add(itemName);
}
}
finally
{
ComUtilities.Release(ref item);
}
}
return info;
}
finally
{
ComUtilities.Release(ref slicerItems);
ComUtilities.Release(ref parent);
}
}
/// <summary>
/// Builds a SlicerResult object for a Table slicer operation
/// </summary>
private static SlicerResult BuildTableSlicerResult(dynamic slicer, dynamic cache, string columnName, string tableName)
{
dynamic? parent = null;
dynamic? slicerItems = null;
try
{
parent = slicer.Parent;
slicerItems = cache.SlicerItems;
var result = new SlicerResult
{
Name = slicer.Name?.ToString() ?? string.Empty,
Caption = slicer.Caption?.ToString() ?? string.Empty,
FieldName = columnName,
SheetName = parent?.Name?.ToString() ?? string.Empty,
Position = GetSlicerPosition(slicer),
ConnectedTable = tableName,
SourceType = "Table"
};
// Collect selected and available items
for (int i = 1; i <= slicerItems.Count; i++)
{
dynamic? item = null;
try
{
item = slicerItems.Item(i);
string itemName = item.Name?.ToString() ?? string.Empty;
result.AvailableItems.Add(itemName);
bool isSelected = item.Selected;
if (isSelected)
{
result.SelectedItems.Add(itemName);
}
}
finally
{
ComUtilities.Release(ref item);
}
}
return result;
}
finally
{
ComUtilities.Release(ref slicerItems);
ComUtilities.Release(ref parent);
}
}
/// <summary>
/// Gets the position string for a slicer (e.g., "H2")
/// </summary>
private static string GetSlicerPosition(dynamic slicer)
{
// Per Microsoft docs: TopLeftCell is on Shape object, not Slicer
// https://learn.microsoft.com/en-us/office/vba/api/excel.shape.topleftcell
dynamic? shape = null;
dynamic? topLeftCell = null;
try
{
shape = slicer.Shape;
topLeftCell = shape.TopLeftCell;
return topLeftCell?.Address?.ToString()?.Replace("$", "") ?? string.Empty;
}
finally
{
ComUtilities.Release(ref topLeftCell);
ComUtilities.Release(ref shape);
}
}
#endregion
}
