Excel C# Com Chart Multiple Series Problem

Question

Hello All,

Please can someone help me with this as it's driving me nuts!

I'm creating an excel chart using C# and the COM interface pragmatically.

I've created the chart using the chart wizard.

I want to then add more series to this chart. I can add the series but the extra data is on new columns and they are not automatically created.

Am I going about this the wrong way?

Add Chart:

public void MakeExcelChart(string startRange, string endRange, string chartTitle, string seriesName)
    {
        ExcelChart = (Excel.Chart)ExcelWBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        ExcelApp.Visible = true;
        ExcelChart.HasTitle = true;
        ExcelChart.ChartTitle.Text = chartTitle;

        ExcelRange = ExcelWSheet.get_Range(startRange, endRange);

        ExcelChart.ChartWizard(ExcelRange, Excel.XlChartType.xlColumnClustered, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        ExcelChart.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        ExcelChart.ChartArea.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 1, 1);

        GetSeriesCollection();

        ExcelSeries = ExcelSeriesCollection.Item(1);
        ExcelSeries.Name = seriesName;
    }

And to add series:

public void AddSeries(string col1, string col2, string startRange, string endRange, string seriesName)
    {
        ExcelSeries = ExcelSeriesCollection.NewSeries();

        ExcelSeries.HasDataLabels = true;

        ExcelRange = ExcelWSheet.get_Range(col1+startRange, col1+endRange);

        ExcelSeries.XValues = ExcelRange;

        ExcelRange = ExcelWSheet.get_Range(col2+startRange, col2+endRange);

        ExcelSeries.Values = ExcelRange;

        ExcelChart.HasLegend = true;

        ExcelSeries.Name = seriesName;

       }

Answer 1

You need to extend the Excel table that is defining the data. Good example is at http://blogs.msdn.com/vsod/archive/2009/06/15/creating-charts-in-word-and-powerpoint-using-newly-introduced-object-model-in-office-2007-service-pack-2.aspx