none
How can I insert a series into a chart? RRS feed

  • Question

  • Hi all;

    This is in a COM Excel AddIn. 

    Let's say I have a chart that has 3 line series that use the left and top axis. And 3 bar(column) series that use the right and bottom axis. They each also have some additional formatting that is set on each sub-collection.

    Now my AddIn wants to add 1 more line series and 1 more bar series so I now have 4 of each.

    I want to keep them grouped. But I also want to duplicate existing series [3] and [6] (API is 1-based) for the two new series.

    How can I do that?

    thanks - dave


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


    • Edited by DavidThi808 Saturday, December 8, 2018 6:35 PM
    Saturday, December 8, 2018 6:29 PM

Answers

  • Assign the relevant axis to the new series, for example sr.AxisGroup = 1 or 2. Then referring to the series which in effect you want to clone, copy all relevant properties to the new series. Repeat for the second series. It might be best not to copy schemecolors, just RGBs.

    The position your x-axes top, bottom or intermediate is not relevant here, it's a property of the respective y-axis.

    In your OP you mentioned 'grouping' and 'sub-collection' which got me a bit confused. Tried  to second-guess if perhaps you meant ChartGroups and ChartGroup.SeriesCollection. A ChartGroup includes all series of a given category of chart types on a given axis.

    I couldn't figure the relevance of "API is 1 based for the two series" in your OP and again here so maybe I've missed something.


    Monday, December 10, 2018 10:25 PM
    Moderator
  • 1, Is there value in our placing all series from a single graph in a ChartGroup? We were just adding the new series to the end and figuring Excel would sort it all out.

    You can’t explicitly place a series into a specified chartgroup. Indeed Excel "sorts it out" automatically depending on the series’ chart-type and axisgroup properties. (I’ve corrected and clarified what I said in my previous post about similar chartype series in a chartgorup could be on different axes)

    2a, We did this because we couldn't find a way to insert a series into a SeriesCollection, you can only add to the end.

    When you first add a new series it’s added as the last series in its given chartgroup, typically the "end" but not necessarily. But then you can move it to chosen axis and change the order it appears on the axis (plotorder) and in the legend, and in turn the order it exists in the main seriescollecction and its chartgroup seriescollection.

    2b, Ok, I misread the AxisGroup documentation (I thought it meant it was for 3D charts only). But how does this work - it can be 1 or 2 but can't you have 4 different sets of axis? Left/bottom, left/top, right/bottom, & right/top?

    An AxisGroup is a pair of x & y axes, xlPrimary and xlSecondary, so only two sets of axes with up to 4 axes displayed. Each axis can be positioned as required (at least with most chart-types). When you apply an axisgroup to a series you apply the pair of axes, wherever they happen to be positioned.
    • Marked as answer by DavidThi808 Wednesday, December 12, 2018 12:43 PM
    Tuesday, December 11, 2018 10:36 AM
    Moderator
  • 1. In your 'combination' chart (multiple chart types) best always apply new series' charttype and axisgroup properties, even if by default they get added correctly. You might want to change the plot order, say to position the new series just after the series it's a copy of.

    2. Yes, the seriesCollection order updates with change of plotorder 

    3 Just apply the plotorder to the new series, others will update accordingly. If the series has a higher order, say #5, to move it between existing 3 & 4 apply plotorder = 4 and what was #4 will become #5. But to move #1 between 3 & 4 change its plot order to 3, and what was #3 will become #2.

    4 I don't follow, for typical charts there can be up to 2 x 2 = 4-axes, and each respective vertical (for an X) or horizontal (for a Y) location can be configured.

    5 Manually, in the UI the x-axis position is 'apparently' a property of its y-axis sibling, and vice-versa. Select an axis, then look for Horizontal (or Vertical) Crosses at... (the UI for this differs in successive xl versions)

    With code, ax.Crosses = Automatic, or .CrossesAt = a valid sibling axis value

    6 Normally the second Y axis gets added automatically as required, but to show/hide for any axis use the chart's HasAxis(arg1, arg2) property

    • Marked as answer by DavidThi808 Wednesday, December 12, 2018 12:44 PM
    Tuesday, December 11, 2018 3:29 PM
    Moderator
  • Short answer, yes that's right, can't have 3 axis groups. Or even a specified axisGroup with 2 category axes which is what your question describes.

    More specifically, in 'typical' non 3d charts you can refer to the 2x2=4 axes like this

    xlAxisType: xlCategory or xlValue (1 or 2)
    xlAxisGroup: xlPrimary or xlSecondary (1 or 2)
    chart.Axes(Type, AxisGroup)

    In passing, if you need say 3 category axes you could make a dummy Line series with a suitable Y value for each point to place it where required, formatted to look like an axis, perhaps with datalabels to display the pseudo axis values.


    Wednesday, December 12, 2018 2:10 PM
    Moderator

All replies

  • Hi dave,

    You can add one or more new series to the SeriesCollection collection:

    public Microsoft.Office.Interop.Excel.Series Add (object Source, Microsoft.Office.Interop.Excel.XlRowCol Rowcol = (Microsoft.Office.Interop.Excel.XlRowCol) -4105, object SeriesLabels, object CategoryLabels, object Replace);

    For more information, please review the following links:

    SeriesCollection.Add(Object, XlRowCol, Object, Object, Object) Method

    Excel C# Com Chart Multiple Series Problem

    Hopefully it helps you.

    Best Regards,

    Yuki


    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.

    Monday, December 10, 2018 4:19 AM
    Moderator
  • Hi Yuki;

    Unfortunately what you provided I already know. My question is:

    I want to keep them grouped. But I also want to duplicate existing series [3] and [6] (API is 1-based) for the two new series.

    So to be specific to my example, how can I create a new series that has the same settings as Series[3] - line chart with axis on the left and top? And how can I create a new series that has the same settings as Series[6] - bar chart with axis on the right and bottom?

    And yes I can copy those settings across. But I want to copy anything else that might be set for that series too - so I want to duplicate Series [3] & [6].

    How can I do that?

    thanks - dave


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

    Monday, December 10, 2018 12:24 PM
  • Assign the relevant axis to the new series, for example sr.AxisGroup = 1 or 2. Then referring to the series which in effect you want to clone, copy all relevant properties to the new series. Repeat for the second series. It might be best not to copy schemecolors, just RGBs.

    The position your x-axes top, bottom or intermediate is not relevant here, it's a property of the respective y-axis.

    In your OP you mentioned 'grouping' and 'sub-collection' which got me a bit confused. Tried  to second-guess if perhaps you meant ChartGroups and ChartGroup.SeriesCollection. A ChartGroup includes all series of a given category of chart types on a given axis.

    I couldn't figure the relevance of "API is 1 based for the two series" in your OP and again here so maybe I've missed something.


    Monday, December 10, 2018 10:25 PM
    Moderator
  • Hi Peter;

    Thank you for the reply, and figuring out my incomplete descriptions.

    Let me first explain what we're doing as the context will make this a lot clearer. We have a docgen/reporting product where you design the templates in Word/Excel/PowerPoint. From the template we then merge in data to generate a report.

    For a chart, you can specify the data as 1 .. N graphs. And each graph has 1 .. X series (it can be a different number for each graph). The graph is a chart type, axis settings, etc. So you can have a graph that's the collection of series that's a bar chart and another graph that is the collection of Series that are a line chart.

    We do this because when you run against the data, you may end up with fewer or more series for a graph. Maybe you did where each series in one graph is data for a year and you're now doing 3 years instead of 2. We can't just grab the first 3 series in the existing chart as the 3rd series was set up for the bar chart.

    And to add to the complexity - the tag in the template for rendering a chart is... a chart. So our AddIn creates the chart, they set up the selects that we run against their sample data, they then set the chart properties as they want them.

    And if the number of series in each graph then never changed, life would be good. But it can change.

    And that is when, if they have more series (fewer is easy), we need to add an additional series that matches the other series set for that graph.

    So with all that I have 2 questions:

    1. Is there value in our placing all series from a single graph in a ChartGroup? We were just adding the new series to the end and figuring Excel would sort it all out. We did this because we couldn't find a way to insert a series into a SeriesCollection, you can only add to the end.
    2. Ok, I misread the AxisGroup documentation (I thought it meant it was for 3D charts only). But how does this work - it can be 1 or 2 but can't you have 4 different sets of axis? Left/bottom, left/top, right/bottom, & right/top?

    thanks - dave


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

    Monday, December 10, 2018 11:24 PM
  • 1, Is there value in our placing all series from a single graph in a ChartGroup? We were just adding the new series to the end and figuring Excel would sort it all out.

    You can’t explicitly place a series into a specified chartgroup. Indeed Excel "sorts it out" automatically depending on the series’ chart-type and axisgroup properties. (I’ve corrected and clarified what I said in my previous post about similar chartype series in a chartgorup could be on different axes)

    2a, We did this because we couldn't find a way to insert a series into a SeriesCollection, you can only add to the end.

    When you first add a new series it’s added as the last series in its given chartgroup, typically the "end" but not necessarily. But then you can move it to chosen axis and change the order it appears on the axis (plotorder) and in the legend, and in turn the order it exists in the main seriescollecction and its chartgroup seriescollection.

    2b, Ok, I misread the AxisGroup documentation (I thought it meant it was for 3D charts only). But how does this work - it can be 1 or 2 but can't you have 4 different sets of axis? Left/bottom, left/top, right/bottom, & right/top?

    An AxisGroup is a pair of x & y axes, xlPrimary and xlSecondary, so only two sets of axes with up to 4 axes displayed. Each axis can be positioned as required (at least with most chart-types). When you apply an axisgroup to a series you apply the pair of axes, wherever they happen to be positioned.
    • Marked as answer by DavidThi808 Wednesday, December 12, 2018 12:43 PM
    Tuesday, December 11, 2018 10:36 AM
    Moderator
  • Hi Peter;

    This is super helpful and I'm understanding it a lot better now. So to confirm a couple of things, and a couple of final (I hope) questions:

    1. When I add a series, I need to set its ChartType, AxisGroup, and PlotOrder. With that Excel will then sort things out for grouping as appropriate.
    2. Question: When I set the PlotOrder, does the series I created stay in the same position in the SeriesCollection? Or can it shift to the PlotOrder position?
    3. Question: If I want to put a series between the series at PlotPoint == 3 and PlotPoint == 4 (ie set PlotPoint to 3.5), do I just set it to 3 or 4? Or do I set it to 3 and for all series after it adjust their PlotPoint up by one?
    4. While there are 4 combinations of axis locations, any given chart can only have two (for 3D charts one) axis group(s)/combination(s).
    5. Question: How do I find if a given axis is l/b/r/t? I don't see any axis property that will tell me this.
    6. Question: If a chart has only 1 axis, how do I add a second axis? And how do I set the l/r and t/b for it?

    Again thank you - this has been a giant help.


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


    • Edited by DavidThi808 Tuesday, December 11, 2018 1:03 PM
    Tuesday, December 11, 2018 1:01 PM
  • 1. In your 'combination' chart (multiple chart types) best always apply new series' charttype and axisgroup properties, even if by default they get added correctly. You might want to change the plot order, say to position the new series just after the series it's a copy of.

    2. Yes, the seriesCollection order updates with change of plotorder 

    3 Just apply the plotorder to the new series, others will update accordingly. If the series has a higher order, say #5, to move it between existing 3 & 4 apply plotorder = 4 and what was #4 will become #5. But to move #1 between 3 & 4 change its plot order to 3, and what was #3 will become #2.

    4 I don't follow, for typical charts there can be up to 2 x 2 = 4-axes, and each respective vertical (for an X) or horizontal (for a Y) location can be configured.

    5 Manually, in the UI the x-axis position is 'apparently' a property of its y-axis sibling, and vice-versa. Select an axis, then look for Horizontal (or Vertical) Crosses at... (the UI for this differs in successive xl versions)

    With code, ax.Crosses = Automatic, or .CrossesAt = a valid sibling axis value

    6 Normally the second Y axis gets added automatically as required, but to show/hide for any axis use the chart's HasAxis(arg1, arg2) property

    • Marked as answer by DavidThi808 Wednesday, December 12, 2018 12:44 PM
    Tuesday, December 11, 2018 3:29 PM
    Moderator
  • Hi;

    Thank you - one last clarification:

    I don't follow, for typical charts there can be up to 2 x 2 = 4-axes, and each respective vertical (for an X) or horizontal (for a Y) location can be configured.

    Let me use a specific example. I can have several series using the left/bottom as their axis and that's one AxisGroup. Then a couple more series can use the right/bottom as their axis and that's the secondary AxisGroup. At this point, I cannot have a third set of series using the left/top as it's axis, because that would be a third AxisGroup.

    Is that correct?

    thanks - dave


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

    Wednesday, December 12, 2018 1:06 PM
  • Short answer, yes that's right, can't have 3 axis groups. Or even a specified axisGroup with 2 category axes which is what your question describes.

    More specifically, in 'typical' non 3d charts you can refer to the 2x2=4 axes like this

    xlAxisType: xlCategory or xlValue (1 or 2)
    xlAxisGroup: xlPrimary or xlSecondary (1 or 2)
    chart.Axes(Type, AxisGroup)

    In passing, if you need say 3 category axes you could make a dummy Line series with a suitable Y value for each point to place it where required, formatted to look like an axis, perhaps with datalabels to display the pseudo axis values.


    Wednesday, December 12, 2018 2:10 PM
    Moderator
  • Again thank you. This thread has been super educational for me.

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

    Wednesday, December 12, 2018 3:40 PM