Add Control to ChartSheet in codebehind using c# and Excel Interop

0

I'm trying to add a ComboBox control to a ChartSheet. I can add one to a normal worksheet, but whenever I run the following code and use a ChartSheet I get the error under it.

// Create the original worksheet
Worksheet sampleGraphSheet = workbook.Worksheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);

// Create the graph
ChartObjects sampleCharts = sampleGraphSheet.ChartObjects(Type.Missing);
ChartObject sampleChart = sampleCharts.Add(0, 0, 800, 500);
Chart sampleChartPage = sampleChart.Chart;
sampleChartPage.Legend.Position = XlLegendPosition.xlLegendPositionBottom;

sampleChartPage.HasTitle = true;
sampleChartPage.ChartTitle.Text = "Simple Sample Graph";

sampleChartPage.ChartType = XlChartType.xlXYScatterLines;

// Transfer the chart to a sheet named 'Simple Sample Graph'
Chart sampleSheet = sampleChartPage.Location(XlChartLocation.xlLocationAsNewSheet);
sampleSheet.Name = "Simple Sample Graph";

// Try and get the sheet
sampleGraphSheet = workbook.Sheets["Simple Sample Graph"]; // mark

// Convert it into a VSTO thingo and add a control
Tools.Worksheet vstoSheet= Globals.Factory.GetVstoObject(sampleGraphSheet);
ComboBox cBox = new ComboBox();
vstoSheet.Controls.AddControl(cBox, 10, 10, 100, 50, "cBoxSampleSelect");
'Microsoft.Office.Interop.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).'

... which makes sense. I'm trying to do sheet things to a not sheet.

If I take out the line marked, then instead I get my chart in a ChartSheet and another sheet with the control in it - very annoying.

Any help would be greatly appreciated!

Cheers.

Edit

After some playing around I've ended up with the following code:

Chart shapeSheet = workbook.Sheets["Simple Sample Graph"];

Shape dropDownShape = shapeSheet.Shapes.AddFormControl(XlFormControl.xlDropDown, 10, 10, 100, 40);

DropDown dropDown = (DropDown)dropDownShape.OLEFormat.Object;

dropDown.Name = "cBoxSamples";
for (int sample = 2; sample <= data.UsedRange.Columns.Count; sample++)
{
    dropDown.AddItem(data.Cells[4, sample].Value.ToString());
}

This works, but I can't seem to access any kind of 'SelectionIndexChanged' event, or any event for that matter. Do I need to somehow connect this to a VBA macro?

c#
vsto
excel-interop
asked on Stack Overflow Feb 15, 2021 by Anemony • edited Feb 16, 2021 by Anemony

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0