In an OOXML spreadsheet document, UserShapes can be added to a chart space via chart-drawing-parts. They will appear together with the chart in the chart space if referenced correctly. I manage to do this programmatically with C# and DocumentFormat.OpenXml.
I fail however to correctly align a user object to a series in the chart (say a bar chart). I tried to align with relative anchors, but resizing the chart leads to a different postion w.r.t. the plotted series. That is mainly due to the axis labels and chart margins that remain constant no matter what size the chart is, as can be seen when resizing it in Excel application. Only removing margin and labels will allow me to keep the position w.r.t to the series by using relative positioning:
Resizing a chart with user shapes with or w/o axes labels and margin
So what is the proper way of aligning UserShapes onto the displayed chart series? Do I miss something here that does the trick?
I can, of course, get rid of axes and titles and perhaps the margin between chart and graphic frame also (still, don't know atm how), but then the chart becomes somewhat useless...
Any hint would be highly appreciated :)
I have found a solution to my issue.
You need to define a PlotArea.Layout for the chart by specifying a ManualLayout with Left, Top, Width, and Height values. Then you know the margin around the plot is in the Excel chart as you have specified.
Using a RelativeAnchorSize to which you add the shape, you can then calculate the position in the plot by adding the margin of the manual layout to the relative offset within the plot to get the position in the chart.
E.g. for x position:
xRange = maximumXAxisValue - minimumXAxisValue;
xLeft = LayoutLeft + (LayoutWidth * (UserShapeLeft - minimumXAxisValue) / xRange);
xRight = LayoutLeft + (LayoutWidth * (UserShapeRight - maximumXAxisValue) / xRange);