none
Q5: Creating a combined chart in code RRS feed

  • Question

  • Hi all;

    I'm trying to create a chart in my AddIn (C#, COM - not VSTO). This chart has several series that may be of different types (i.e. column and line). And I have the data for each individual series held separately, even though several series may have the same X axis data points. 

    When setting the data for a series, is it best if I call Series.Name, Series.XValues, and Series.Values to give it the series title and data? Or is another set of calls better?

    thanks - dave

    Question broken out as requested by Bruce Dai. Bruce, if the above is not sufficient, please tell me what more you need for clarification.


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, October 25, 2018 12:22 PM

Answers

  • Hi Dave,

    >>if I have a range of values in a worksheet that I want to assign to a series, what is the best way to do that?

    Please refer the following code:

     Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection();
          Excel.Range xValRange = excelSheet.Range[Util.GetColumnNameByIndex(initColumn - 2) + initRow, Util.GetColumnNameByIndex(initColumn - 2) + sheetRowCount];
          for (int i = 0; i < releases.Count; i++)
          {
            string columnName = Util.GetColumnNameByIndex(initColumn);
            Excel.Series oSeries = oSeriesCollection.NewSeries();
            oSeries.Values = excelSheet.Range[columnName + initRow, columnName + sheetRowCount];
            oSeries.XValues = xValRange;
            oSeries.Name = releases[i].RELEASENAME;
          }

    For more information, please see the links as below:

    Dynamically generated Excel chart in C# with multiple series

    Series.values in C# for excel chart range?

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by DavidThi808 Monday, October 29, 2018 11:57 AM
    Monday, October 29, 2018 6:57 AM
  • Hi;

    I did as you suggested above except for the legend title I did:

    oSeries.Name = "=$b$1";

    And for Values & XValues I gave a string of the range (=$B$2:$B$20) rather than the range.

    Is providing a Range object vs providing a formula of the range any different? Or does Excel convert the range to a formula when it is passed that way?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    • Marked as answer by DavidThi808 Monday, October 29, 2018 12:01 PM
    Monday, October 29, 2018 12:01 PM

All replies

  • Hi Dave,

    The Chart.Series collection contains all data series (Series objects) in the Chart control. Each series contains a collection of DataPoint objects (the Series.Points collection property). 

    If you want to set the data for a series, please refer the following link:

    Series and Data Points in Chart Controls

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 26, 2018 3:03 AM
  • Hi Lina;

    I think what the link you referenced is suggesting is set the values using DataPoints.Add, AddXY, & AddY but it doesn't show how to do that.

    More important, I'm trying to set the data as a range in a worksheet, not a set of literal numbers assigned one at a time. What is the correct way to set this for a range, setting it per series?

    So to be more explicit in my question, if I have a range of values in a worksheet that I want to assign to a series, what is the best way to do that?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Friday, October 26, 2018 2:48 PM
  • Hi Dave,

    >>if I have a range of values in a worksheet that I want to assign to a series, what is the best way to do that?

    Please refer the following code:

     Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection();
          Excel.Range xValRange = excelSheet.Range[Util.GetColumnNameByIndex(initColumn - 2) + initRow, Util.GetColumnNameByIndex(initColumn - 2) + sheetRowCount];
          for (int i = 0; i < releases.Count; i++)
          {
            string columnName = Util.GetColumnNameByIndex(initColumn);
            Excel.Series oSeries = oSeriesCollection.NewSeries();
            oSeries.Values = excelSheet.Range[columnName + initRow, columnName + sheetRowCount];
            oSeries.XValues = xValRange;
            oSeries.Name = releases[i].RELEASENAME;
          }

    For more information, please see the links as below:

    Dynamically generated Excel chart in C# with multiple series

    Series.values in C# for excel chart range?

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by DavidThi808 Monday, October 29, 2018 11:57 AM
    Monday, October 29, 2018 6:57 AM
  • Hi;

    I did as you suggested above except for the legend title I did:

    oSeries.Name = "=$b$1";

    And for Values & XValues I gave a string of the range (=$B$2:$B$20) rather than the range.

    Is providing a Range object vs providing a formula of the range any different? Or does Excel convert the range to a formula when it is passed that way?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    • Marked as answer by DavidThi808 Monday, October 29, 2018 12:01 PM
    Monday, October 29, 2018 12:01 PM