using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Models;
namespace Sbroenne.ExcelMcp.Core.Commands.Chart;
/// <summary>
/// Chart lifecycle operations - list, read, create, delete, move/resize.
/// </summary>
public partial class ChartCommands : IChartCommands, IChartConfigCommands
{
private readonly RegularChartStrategy _regularStrategy = new();
private readonly PivotChartStrategy _pivotStrategy = new();
/// <inheritdoc />
public List<ChartInfo> List(IExcelBatch batch)
{
return batch.Execute((ctx, ct) =>
{
var charts = new List<ChartInfo>();
dynamic worksheets = ctx.Book.Worksheets;
int wsCount = Convert.ToInt32(worksheets.Count);
for (int i = 1; i <= wsCount; i++)
{
dynamic? worksheet = null;
dynamic? shapes = null;
try
{
worksheet = worksheets.Item(i);
string sheetName = worksheet.Name?.ToString() ?? $"Sheet{i}";
shapes = worksheet.Shapes;
int shapeCount = Convert.ToInt32(shapes.Count);
for (int j = 1; j <= shapeCount; j++)
{
dynamic? shape = null;
dynamic? chart = null;
try
{
shape = shapes.Item(j);
// Check if this is a chart (msoChart = 3)
if (Convert.ToInt32(shape.Type) != 3)
{
continue;
}
chart = shape.Chart;
string chartName = shape.Name?.ToString() ?? $"Chart{j}";
// Determine strategy and get info
IChartStrategy strategy = _pivotStrategy.CanHandle(chart) ? _pivotStrategy : _regularStrategy;
#pragma warning disable CS8604 // CodeQL false positive: Both strategies implement IChartStrategy.GetInfo with dynamic parameters
var chartInfo = strategy.GetInfo(chart, chartName, sheetName, shape);
#pragma warning restore CS8604
charts.Add(chartInfo);
}
finally
{
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref shape!);
}
}
}
finally
{
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
}
}
ComUtilities.Release(ref worksheets!);
return charts;
});
}
/// <inheritdoc />
public ChartInfoResult Read(IExcelBatch batch, string chartName)
{
return batch.Execute((ctx, ct) =>
{
// Find chart by name across all worksheets
dynamic worksheets = ctx.Book.Worksheets;
int wsCount = Convert.ToInt32(worksheets.Count);
for (int i = 1; i <= wsCount; i++)
{
dynamic? worksheet = null;
dynamic? shapes = null;
try
{
worksheet = worksheets.Item(i);
string sheetName = worksheet.Name?.ToString() ?? $"Sheet{i}";
shapes = worksheet.Shapes;
int shapeCount = Convert.ToInt32(shapes.Count);
for (int j = 1; j <= shapeCount; j++)
{
dynamic? shape = null;
dynamic? chart = null;
try
{
shape = shapes.Item(j);
// Check if this is a chart and name matches
if (Convert.ToInt32(shape.Type) != 3)
{
continue;
}
string shapeName = shape.Name?.ToString() ?? string.Empty;
if (!shapeName.Equals(chartName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
chart = shape.Chart;
// Determine strategy and get detailed info
IChartStrategy strategy = _pivotStrategy.CanHandle(chart) ? _pivotStrategy : _regularStrategy;
#pragma warning disable CS8604 // CodeQL false positive: Both strategies implement IChartStrategy.GetDetailedInfo with dynamic parameters
var result = strategy.GetDetailedInfo(chart, chartName, sheetName, shape);
#pragma warning restore CS8604
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref shape!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
ComUtilities.Release(ref worksheets!);
return result;
}
catch (System.Runtime.InteropServices.COMException)
{
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref shape!);
throw;
}
}
}
finally
{
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
}
}
ComUtilities.Release(ref worksheets!);
// Chart not found
throw new InvalidOperationException($"Chart '{chartName}' not found in workbook.");
});
}
/// <inheritdoc />
public ChartCreateResult CreateFromRange(
IExcelBatch batch,
string sheetName,
string sourceRangeAddress,
ChartType chartType,
double left = 0,
double top = 0,
double width = 400,
double height = 300,
string? chartName = null,
string? targetRange = null)
{
return batch.Execute((ctx, ct) =>
{
dynamic? worksheet = null;
dynamic? shapes = null;
dynamic? shape = null;
dynamic? chart = null;
dynamic? targetRangeObj = null;
try
{
worksheet = ctx.Book.Worksheets[sheetName];
shapes = worksheet.Shapes;
// Resolve final position: targetRange > explicit left/top > auto-position
double finalLeft = left;
double finalTop = top;
double finalWidth = width;
double finalHeight = height;
if (!string.IsNullOrWhiteSpace(targetRange))
{
// targetRange takes precedence — resolve range geometry
targetRangeObj = worksheet.Range[targetRange];
finalLeft = Convert.ToDouble(targetRangeObj.Left);
finalTop = Convert.ToDouble(targetRangeObj.Top);
finalWidth = Convert.ToDouble(targetRangeObj.Width);
finalHeight = Convert.ToDouble(targetRangeObj.Height);
}
else if (left == 0 && top == 0)
{
// No explicit position — auto-position below content
// Cast explicitly to avoid dynamic dispatch losing named tuple members
(double Left, double Top) autoPos = ChartPositionHelpers.FindAvailablePosition(worksheet, width, height);
finalLeft = autoPos.Left;
finalTop = autoPos.Top;
}
// Create chart using AddChart
shape = shapes.AddChart(
XlChartType: (int)chartType,
Left: finalLeft,
Top: finalTop,
Width: finalWidth,
Height: finalHeight
);
chart = shape.Chart;
// Set data source - need to get Range object from string address
dynamic? sourceRangeObj = null;
try
{
// Get the range object from the address string
// If sourceRangeAddress doesn't include sheet name, prefix it
// Sheet names with spaces or special characters must be quoted: 'Sheet Name'!A1:D6
string fullRangeAddress = sourceRangeAddress.Contains('!')
? sourceRangeAddress
: $"'{sheetName}'!{sourceRangeAddress}";
sourceRangeObj = ctx.Book.Application.Range[fullRangeAddress];
try
{
chart.SetSourceData(sourceRangeObj);
}
catch (System.Runtime.InteropServices.COMException ex)
when (ex.HResult == unchecked((int)0x800A03EC))
{
throw new InvalidOperationException(
$"Cannot set chart data source to '{sourceRangeAddress}'. " +
"The range must be contiguous, non-empty, and accessible. " +
"If the data is not in a table, consider creating a table first with " +
"table(action='create'), then use chart(action='create-from-table').", ex);
}
}
finally
{
if (sourceRangeObj != null)
{
ComUtilities.Release(ref sourceRangeObj!);
}
}
// Set custom name if provided
if (!string.IsNullOrWhiteSpace(chartName))
{
shape.Name = chartName;
}
string finalName = shape.Name?.ToString() ?? "Chart";
// Collision detection — warn about overlaps after positioning
var warnings = ChartPositionHelpers.DetectCollisions(
worksheet, finalLeft, finalTop, finalWidth, finalHeight, finalName);
int chartCount = ChartPositionHelpers.CountCharts(worksheet);
var result = new ChartCreateResult
{
Success = true,
ChartName = finalName,
SheetName = sheetName,
ChartType = chartType,
IsPivotChart = false,
Left = finalLeft,
Top = finalTop,
Width = finalWidth,
Height = finalHeight,
Message = ChartPositionHelpers.FormatCollisionWarnings(warnings, chartCount)
};
return result;
}
finally
{
ComUtilities.Release(ref targetRangeObj!);
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref shape!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
}
});
}
/// <inheritdoc />
public ChartCreateResult CreateFromTable(
IExcelBatch batch,
string tableName,
string sheetName,
ChartType chartType,
double left = 0,
double top = 0,
double width = 400,
double height = 300,
string? chartName = null,
string? targetRange = null)
{
return batch.Execute((ctx, ct) =>
{
dynamic? table = null;
dynamic? tableRange = null;
dynamic? worksheet = null;
dynamic? shapes = null;
dynamic? shape = null;
dynamic? chart = null;
dynamic? targetRangeObj = null;
try
{
// Find the table using CoreLookupHelpers
table = CoreLookupHelpers.FindTable(ctx.Book, tableName);
// Get the table's data range (includes headers)
tableRange = table.Range;
// Get target worksheet
worksheet = ctx.Book.Worksheets[sheetName];
shapes = worksheet.Shapes;
// Resolve final position: targetRange > explicit left/top > auto-position
double finalLeft = left;
double finalTop = top;
double finalWidth = width;
double finalHeight = height;
if (!string.IsNullOrWhiteSpace(targetRange))
{
targetRangeObj = worksheet.Range[targetRange];
finalLeft = Convert.ToDouble(targetRangeObj.Left);
finalTop = Convert.ToDouble(targetRangeObj.Top);
finalWidth = Convert.ToDouble(targetRangeObj.Width);
finalHeight = Convert.ToDouble(targetRangeObj.Height);
}
else if (left == 0 && top == 0)
{
// Cast explicitly to avoid dynamic dispatch losing named tuple members
(double Left, double Top) autoPos = ChartPositionHelpers.FindAvailablePosition(worksheet, width, height);
finalLeft = autoPos.Left;
finalTop = autoPos.Top;
}
// Create chart using AddChart
shape = shapes.AddChart(
XlChartType: (int)chartType,
Left: finalLeft,
Top: finalTop,
Width: finalWidth,
Height: finalHeight
);
chart = shape.Chart;
// Set data source to table's range
chart.SetSourceData(tableRange);
// Set custom name if provided
if (!string.IsNullOrWhiteSpace(chartName))
{
shape.Name = chartName;
}
string finalName = shape.Name?.ToString() ?? "Chart";
// Collision detection
var warnings = ChartPositionHelpers.DetectCollisions(
worksheet, finalLeft, finalTop, finalWidth, finalHeight, finalName);
int chartCount = ChartPositionHelpers.CountCharts(worksheet);
var result = new ChartCreateResult
{
Success = true,
ChartName = finalName,
SheetName = sheetName,
ChartType = chartType,
IsPivotChart = false,
Left = finalLeft,
Top = finalTop,
Width = finalWidth,
Height = finalHeight,
Message = ChartPositionHelpers.FormatCollisionWarnings(warnings, chartCount)
};
return result;
}
finally
{
ComUtilities.Release(ref targetRangeObj!);
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref shape!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
ComUtilities.Release(ref tableRange!);
ComUtilities.Release(ref table!);
}
});
}
/// <inheritdoc />
public ChartCreateResult CreateFromPivotTable(
IExcelBatch batch,
string pivotTableName,
string sheetName,
ChartType chartType,
double left = 0,
double top = 0,
double width = 400,
double height = 300,
string? chartName = null,
string? targetRange = null)
{
return batch.Execute((ctx, ct) =>
{
dynamic? worksheet = null;
dynamic? pivotChartShape = null;
dynamic? chart = null;
dynamic? pivotTable = null;
dynamic? tableRange = null;
dynamic? shapes = null;
dynamic? targetRangeObj = null;
try
{
// Find PivotTable
pivotTable = FindPivotTable(ctx.Book, pivotTableName);
if (pivotTable == null)
{
throw new InvalidOperationException($"PivotTable '{pivotTableName}' not found in workbook.");
}
// Get target worksheet
worksheet = ctx.Book.Worksheets[sheetName];
// Resolve final position: targetRange > explicit left/top > auto-position
double finalLeft = left;
double finalTop = top;
double finalWidth = width;
double finalHeight = height;
if (!string.IsNullOrWhiteSpace(targetRange))
{
targetRangeObj = worksheet.Range[targetRange];
finalLeft = Convert.ToDouble(targetRangeObj.Left);
finalTop = Convert.ToDouble(targetRangeObj.Top);
finalWidth = Convert.ToDouble(targetRangeObj.Width);
finalHeight = Convert.ToDouble(targetRangeObj.Height);
}
else if (left == 0 && top == 0)
{
// Cast explicitly to avoid dynamic dispatch losing named tuple members
(double Left, double Top) autoPos = ChartPositionHelpers.FindAvailablePosition(worksheet, width, height);
finalLeft = autoPos.Left;
finalTop = autoPos.Top;
}
// Create a chart via Shapes.AddChart and set source to PivotTable's range.
// This approach works for both OLAP (Data Model) and non-OLAP PivotTables,
// unlike PivotCache.CreatePivotChart which has parameter issues in dynamic
// COM and throws DISP_E_UNKNOWNNAME for OLAP sources.
shapes = worksheet.Shapes;
// Create chart using AddChart
pivotChartShape = shapes.AddChart(
XlChartType: (int)chartType,
Left: finalLeft,
Top: finalTop,
Width: finalWidth,
Height: finalHeight
);
chart = pivotChartShape.Chart;
// Get the PivotTable's data range and set it as the chart's source
tableRange = pivotTable.TableRange1;
chart.SetSourceData(tableRange);
// Set custom name if provided
if (!string.IsNullOrWhiteSpace(chartName))
{
pivotChartShape.Name = chartName;
}
string finalName = pivotChartShape.Name?.ToString() ?? "Chart";
// Collision detection
var warnings = ChartPositionHelpers.DetectCollisions(
worksheet, finalLeft, finalTop, finalWidth, finalHeight, finalName);
int chartCount = ChartPositionHelpers.CountCharts(worksheet);
var result = new ChartCreateResult
{
Success = true,
ChartName = finalName,
SheetName = sheetName,
ChartType = chartType,
IsPivotChart = true,
LinkedPivotTable = pivotTableName,
Left = finalLeft,
Top = finalTop,
Width = finalWidth,
Height = finalHeight,
Message = ChartPositionHelpers.FormatCollisionWarnings(warnings, chartCount)
};
return result;
}
finally
{
ComUtilities.Release(ref targetRangeObj!);
ComUtilities.Release(ref chart!);
ComUtilities.Release(ref pivotChartShape!);
ComUtilities.Release(ref tableRange!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
ComUtilities.Release(ref pivotTable!);
}
});
}
/// <inheritdoc />
public OperationResult Delete(IExcelBatch batch, string chartName)
{
return batch.Execute((ctx, ct) =>
{
// Find and delete chart
dynamic worksheets = ctx.Book.Worksheets;
int wsCount = Convert.ToInt32(worksheets.Count);
for (int i = 1; i <= wsCount; i++)
{
dynamic? worksheet = null;
dynamic? shapes = null;
try
{
worksheet = worksheets.Item(i);
shapes = worksheet.Shapes;
int shapeCount = Convert.ToInt32(shapes.Count);
for (int j = 1; j <= shapeCount; j++)
{
dynamic? shape = null;
try
{
shape = shapes.Item(j);
// Check if this is a chart and name matches
if (Convert.ToInt32(shape.Type) != 3)
{
continue;
}
string shapeName = shape.Name?.ToString() ?? string.Empty;
if (!shapeName.Equals(chartName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
// Delete the chart
shape.Delete();
ComUtilities.Release(ref shape!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
ComUtilities.Release(ref worksheets!);
return new OperationResult { Success = true, FilePath = batch.WorkbookPath }; // Success
}
finally
{
ComUtilities.Release(ref shape!);
}
}
}
finally
{
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
}
}
ComUtilities.Release(ref worksheets!);
// Chart not found
throw new InvalidOperationException($"Chart '{chartName}' not found in workbook.");
});
}
/// <inheritdoc />
public OperationResult Move(
IExcelBatch batch,
string chartName,
double? left = null,
double? top = null,
double? width = null,
double? height = null)
{
return batch.Execute((ctx, ct) =>
{
// Find chart and update position/size
dynamic worksheets = ctx.Book.Worksheets;
int wsCount = Convert.ToInt32(worksheets.Count);
for (int i = 1; i <= wsCount; i++)
{
dynamic? worksheet = null;
dynamic? shapes = null;
try
{
worksheet = worksheets.Item(i);
shapes = worksheet.Shapes;
int shapeCount = Convert.ToInt32(shapes.Count);
for (int j = 1; j <= shapeCount; j++)
{
dynamic? shape = null;
try
{
shape = shapes.Item(j);
// Check if this is a chart and name matches
if (Convert.ToInt32(shape.Type) != 3)
{
continue;
}
string shapeName = shape.Name?.ToString() ?? string.Empty;
if (!shapeName.Equals(chartName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
// Update position and size
if (left.HasValue) shape.Left = left.Value;
if (top.HasValue) shape.Top = top.Value;
if (width.HasValue) shape.Width = width.Value;
if (height.HasValue) shape.Height = height.Value;
// Collision detection after repositioning
double finalLeft = Convert.ToDouble(shape.Left);
double finalTop = Convert.ToDouble(shape.Top);
double finalWidth = Convert.ToDouble(shape.Width);
double finalHeight = Convert.ToDouble(shape.Height);
var warnings = ChartPositionHelpers.DetectCollisions(
worksheet, finalLeft, finalTop, finalWidth, finalHeight, shapeName);
int chartCount = ChartPositionHelpers.CountCharts(worksheet);
ComUtilities.Release(ref shape!);
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
ComUtilities.Release(ref worksheets!);
return new OperationResult
{
Success = true,
FilePath = batch.WorkbookPath,
Message = ChartPositionHelpers.FormatCollisionWarnings(warnings, chartCount)
};
}
finally
{
ComUtilities.Release(ref shape!);
}
}
}
finally
{
ComUtilities.Release(ref shapes!);
ComUtilities.Release(ref worksheet!);
}
}
ComUtilities.Release(ref worksheets!);
// Chart not found
throw new InvalidOperationException($"Chart '{chartName}' not found in workbook.");
});
}
/// <summary>
/// Finds a PivotTable by name across all worksheets.
/// Delegates to CoreLookupHelpers.TryFindPivotTable for the actual lookup.
/// </summary>
private static dynamic? FindPivotTable(dynamic workbook, string pivotTableName)
{
CoreLookupHelpers.TryFindPivotTable(workbook, pivotTableName, out dynamic? pivotTable);
return pivotTable;
}
}
