none
how to add multiple charts and multiple series with excel in C# RRS feed

  • Question

  • I'm trying to programmatically add 2 Excel charts to a workbook. Each chart is on a separate sheet.This code, and everything I try, adds the first chart, Ch0, with the two series, A1-A401,B1-B401,ok. BUT the second chart, Ch1, always adds a chart with 4 series,A1-A401,B1-B401,C1-C401,D1-D401. Can someone tell me and how to make Ch1 only have it's 2 series?

        public void MakeCharts()
        {
            Series[] oSeries = new Series[2];
            Chart[] ChartObject = new Chart[2];
            SeriesCollection[] oSeriesCollection = new SeriesCollection[2];
    
            ChartObject[0] = book.Charts.Add();
            ChartObject[0].ChartType = XlChartType.xlLine;
            ChartObject[0].Name = "Ch0";
            oSeriesCollection[0] = ChartObject[0].SeriesCollection();
            oSeries[0] = oSeriesCollection[0].NewSeries();
            oSeries[0].Values = workSheet.get_Range("A1", "A401");
            oSeries[1] = oSeriesCollection[0].NewSeries();
            oSeries[1].Values = workSheet.get_Range("B1", "B401");
    
            ChartObject[1] = book.Charts.Add();
            ChartObject[1].ChartType = XlChartType.xlLine;
            ChartObject[1].Name = "Ch1";
            oSeriesCollection[1] = ChartObject[1].SeriesCollection();
            oSeries[0] = oSeriesCollection[1].NewSeries();
            oSeries[0].Values = workSheet.get_Range("C1", "C401");
            oSeries[1] = oSeriesCollection[1].NewSeries();
            oSeries[1].Values = workSheet.get_Range("D1", "D401");
        }

    sdy

    Monday, August 11, 2014 6:56 PM

Answers

  • Hi,

    >>but then Ch0 is empty.<<

    I suspect you may also use the oSeriesCollection[0] for the ChartObject[1] as followed. So it will delete all the series of chart Ch0. 

    ChartObject[1] = book.Charts.Add();
    ChartObject[1].ChartType = XlChartType.xlLine;
    ChartObject[1].Name = "Ch1";
    oSeriesCollection[1] = ChartObject[1].SeriesCollection();
    foreach (Series series in oSeriesCollection[0])
    {
        series.Delete();
    }

    Here is the whole sample for your reference.

    public void MakeCharts()
    {
        Excel.Chart[] ChartObject = new Excel.Chart[2];
    
        ChartObject[0] = book.Charts.Add();
        ChartObject[0].ChartType = Excel.XlChartType.xlLine;
        ChartObject[0].Name = "Ch0";
    
        foreach (Excel.Series series in ChartObject[0].SeriesCollection())
        {
            series.Delete();
        }
        ChartObject[0].SeriesCollection().NewSeries();
        ChartObject[0].FullSeriesCollection(1).Values = "=Sheet1!$A$1:$A$4";
        ChartObject[0].SeriesCollection().NewSeries();
        ChartObject[0].FullSeriesCollection(2).Values = "=Sheet1!$B$1:$B$4";
    
        ChartObject[1] = book.Charts.Add();
        ChartObject[1].ChartType = Excel.XlChartType.xlLine;
        ChartObject[1].Name = "Ch1";
    
        foreach (Excel.Series series in ChartObject[1].SeriesCollection())
        {
            series.Delete();
        }
        ChartObject[1].SeriesCollection().NewSeries();
        ChartObject[1].FullSeriesCollection(1).Values = "=Sheet1!$C$1:$C$4";
        ChartObject[1].SeriesCollection().NewSeries();
        ChartObject[1].FullSeriesCollection(2).Values = "=Sheet1!$D$1:$D$4";
    
    }



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by sdeyoreo Sunday, August 17, 2014 3:24 PM
    Thursday, August 14, 2014 7:23 AM
    Moderator

All replies

  • Hi,

    Before adding new Series object for the Chart, you could remove all the existing Series objects with the Series.Delete method.

    oSeriesCollection[0] = ChartObject[0].SeriesCollection();
    foreach (Series series in oSeriesCollection[0])
    {
        series.Delete();
    }
    oSeries[0] = oSeriesCollection[0].NewSeries();



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 12, 2014 7:36 AM
    Moderator
  • Thanks for your help, but then Ch0 is empty.

    sdy

    Tuesday, August 12, 2014 3:06 PM
  • Hi,

    >>but then Ch0 is empty.<<

    I suspect you may also use the oSeriesCollection[0] for the ChartObject[1] as followed. So it will delete all the series of chart Ch0. 

    ChartObject[1] = book.Charts.Add();
    ChartObject[1].ChartType = XlChartType.xlLine;
    ChartObject[1].Name = "Ch1";
    oSeriesCollection[1] = ChartObject[1].SeriesCollection();
    foreach (Series series in oSeriesCollection[0])
    {
        series.Delete();
    }

    Here is the whole sample for your reference.

    public void MakeCharts()
    {
        Excel.Chart[] ChartObject = new Excel.Chart[2];
    
        ChartObject[0] = book.Charts.Add();
        ChartObject[0].ChartType = Excel.XlChartType.xlLine;
        ChartObject[0].Name = "Ch0";
    
        foreach (Excel.Series series in ChartObject[0].SeriesCollection())
        {
            series.Delete();
        }
        ChartObject[0].SeriesCollection().NewSeries();
        ChartObject[0].FullSeriesCollection(1).Values = "=Sheet1!$A$1:$A$4";
        ChartObject[0].SeriesCollection().NewSeries();
        ChartObject[0].FullSeriesCollection(2).Values = "=Sheet1!$B$1:$B$4";
    
        ChartObject[1] = book.Charts.Add();
        ChartObject[1].ChartType = Excel.XlChartType.xlLine;
        ChartObject[1].Name = "Ch1";
    
        foreach (Excel.Series series in ChartObject[1].SeriesCollection())
        {
            series.Delete();
        }
        ChartObject[1].SeriesCollection().NewSeries();
        ChartObject[1].FullSeriesCollection(1).Values = "=Sheet1!$C$1:$C$4";
        ChartObject[1].SeriesCollection().NewSeries();
        ChartObject[1].FullSeriesCollection(2).Values = "=Sheet1!$D$1:$D$4";
    
    }



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by sdeyoreo Sunday, August 17, 2014 3:24 PM
    Thursday, August 14, 2014 7:23 AM
    Moderator
  • Thanks for your help, but I'm using Office 2010, so I don't have FullSEriesCollection.

    But, after much trial and error, I got it working. This is how I'm doing it:

     
            //******************************
            public void MakeCharts()
            {

                // loop thru number of charts
                for (int i = 0; i <2; i++)
                {
                    // add a new worksheet chart
                    Excel.Chart chartPage = book.Charts.Add();
                    chartPage.ChartType = Excel.XlChartType.xlLine;

                    // create a new series collection attached to chartpage
                    Excel.SeriesCollection[] oSeriesCollection = new Excel.SeriesCollection[1];
                    oSeriesCollection[0] = chartPage.SeriesCollection();

                    // create some new series
                   Excel.Series[] oSeries = new Excel.Series[2];

                    // create a Range object
                    String[] chartRange = new string[2];

                    // loop thru each source and get the source's range
                    // for(i=0;i<2;i++)...
                    {
                        // ... get the different ranges
                        if (i == 0)
                        {
                            chartRange[0] = "=Test1_il!$A$1:$A$401";
                            chartRange[1] = "=Test1_il!$B$1:$B$401";
                        }
                        else
                        {
                            foreach (Excel.Series series in oSeriesCollection[0])
                            {
                                series.Delete();
                            }

                            chartRange[0] = "=Test1_il!$A$1:$A$401";
                            chartRange[1] = "=Test2ilwb!$A$1:$A$401";
                         }

                        // make new series
                        oSeries[0] = oSeriesCollection[0].NewSeries();
                        oSeries[1] = oSeriesCollection[0].NewSeries();

                        // assign ranges
                        oSeries[0].Values = chartRange[0];
                        oSeries[1].Values = chartRange[1];

                    }
                }
            }
            //******************************


    sdy

    Friday, August 15, 2014 9:56 PM
  • Oh, can you direct me to a book or other source to learn much more about using EXCEL Charts in C#? I write alot of programs for electronic test equipment that generated data sheets in Ecel. I'm now adding charts to the programs and I'd like to know more.

    Thanks again.


    sdy

    Friday, August 15, 2014 10:37 PM
  • Hi again. All cleaned up, this is what works. I'm THRILLED!!!!

            public void MakeCharts(frmTestInfo TestInfo)
            {

                // loop thru number of charts
                for (int ChartNum = 0; ChartNum < TestInfo.NumberOfCharts; ChartNum++)
                {
                    // add a new worksheet chart
                    Excel.Chart chartPage = book.Charts.Add();
                    chartPage.ChartType = Excel.XlChartType.xlLine;

                    // create a new series collection attached to chartpage
                    Excel.SeriesCollection[] oSeriesCollection = new Excel.SeriesCollection[1];
                    oSeriesCollection[0] = chartPage.SeriesCollection();

                    // create some new series
                    Excel.Series[] oSeries = new Excel.Series[TestInfo.NumberOfSources[ChartNum]];

                    // clear out old stuff in series
                    foreach (Excel.Series series in oSeriesCollection[0])
                    {
                        series.Delete();
                    }

                    // create a Range object
                    String[] chartRange = new string[TestInfo.NumberOfSources[ChartNum]];

                    // loop thru each source and get the source's range
                    for (int SourceNum = 0; SourceNum < TestInfo.NumberOfSources[ChartNum]; SourceNum++)
                    {
                        // ... get the diffent ranges
                        chartRange[SourceNum] = TestInfo.ChartSourceSheet[ChartNum, SourceNum] + TestInfo.ChartSourceRange[ChartNum, SourceNum];
                        oSeries[SourceNum] = oSeriesCollection[0].NewSeries();
                        oSeries[SourceNum].Values = chartRange[SourceNum];
                    }


                }
            }
            //******************************


    sdy

    Saturday, August 16, 2014 12:23 AM