/// <summary> /// Updates a line/bar series, which is generally cloned from an existing series, so that elements are prepared for insertion into an existing chart. /// </summary> /// <param name="series">The series that we are updating (must be a <see cref="BarChartSeries" /> or a <see cref="LineChartSeries" /><br /> /// i.e. a series that consists of a Category to identify the series, and a series value.</param> /// <param name="newSeriesIndex">The 0-based index/order to be set for the new series in the charts's Series collection</param> /// <param name="categoryHeadingRange">The category heading range.</param> /// <param name="axisDataRange">The axis data range.</param> /// <param name="seriesDataRange">The series data range.</param> /// <exception cref="ArgumentNullException">series</exception> /// <exception cref="InvalidOperationException">Only valid for series of type ScatterChartSeries</exception> public static void UpdateXYValueChartSeries(this OpenXmlCompositeElement series, uint newSeriesIndex, CompositeRangeReference categoryHeadingRange, CompositeRangeReference axisDataRange, CompositeRangeReference seriesDataRange) { if (series == null) { throw new ArgumentNullException("series"); } if (!(series is ScatterChartSeries)) { throw new InvalidOperationException("Only valid for series of type ScatterChartSeries"); } var index = series.Descendants <Index>().FirstOrDefault(); if (index != null) { index.Val = (UInt32Value)newSeriesIndex; } var order = series.Descendants <Order>().FirstOrDefault(); if (order != null) { order.Val = (UInt32Value)newSeriesIndex; } // the series title, this is the name of the column header var seriesText = series.Descendants <SeriesText>().FirstOrDefault(); if (seriesText != null) { seriesText.StringReference = new StringReference(); seriesText.StringReference.Formula = new Formula(); seriesText.StringReference.Formula.Text = categoryHeadingRange.Reference; } // set the formula for the category axis, currently always the 1st column of data var xvalues = series.Descendants <XValues>().FirstOrDefault(); if (xvalues != null) { xvalues.StringReference = new StringReference(); xvalues.StringReference.Formula = new Formula(); xvalues.StringReference.Formula.Text = axisDataRange.Reference; } var yvalues = series.Descendants <YValues>().FirstOrDefault(); if (yvalues != null) { yvalues.NumberReference = new NumberReference(); yvalues.NumberReference.Formula = new Formula(); yvalues.NumberReference.Formula.Text = seriesDataRange.Reference; } }
/// <summary> /// Updates the category value chart series. /// </summary> /// <param name="series">The series.</param> /// <param name="newSeriesIndex">New index of the series.</param> /// <param name="categoryHeadingRange">The category heading range.</param> /// <param name="axisDataRange">The axis data range.</param> /// <param name="seriesDataRange">The series data range.</param> /// <exception cref="ArgumentNullException">series</exception> /// <exception cref="InvalidOperationException">Only valid for series of type BarChartSeries, LineChartSeries, AreaChartSeries & PieChartSeries</exception> public static void UpdateCategoryValueChartSeries(this OpenXmlCompositeElement series, uint newSeriesIndex, CompositeRangeReference categoryHeadingRange, CompositeRangeReference axisDataRange, CompositeRangeReference seriesDataRange) { if (series == null) { throw new ArgumentNullException("series"); } if (!(series is BarChartSeries) && !(series is LineChartSeries) && !(series is AreaChartSeries) && !(series is PieChartSeries)) { throw new InvalidOperationException("Only valid for series of type BarChartSeries, LineChartSeries, AreaChartSeries & PieChartSeries"); } // Updates the supplied series Index and Order var index = series.Descendants <Index>().FirstOrDefault(); if (index != null) { index.Val = (UInt32Value)newSeriesIndex; } var order = series.Descendants <Order>().FirstOrDefault(); if (order != null) { order.Val = (UInt32Value)newSeriesIndex; } // Set the formula on the SeriesText ('Legend Entries(Series)' in Excel). // This is set to reference the column header in Excel and determines the Category Heading var seriesText = series.Descendants <SeriesText>().FirstOrDefault(); if (seriesText != null) { seriesText.StringReference = new StringReference(); seriesText.StringReference.Formula = new Formula(); seriesText.StringReference.Formula.Text = categoryHeadingRange.Reference; } // Set the formula on the Category Axis var categoryAxisData = series.Descendants <CategoryAxisData>().FirstOrDefault(); if (categoryAxisData != null) { categoryAxisData.StringReference = new StringReference(); categoryAxisData.StringReference.Formula = new Formula(); categoryAxisData.StringReference.Formula.Text = axisDataRange.Reference; } // Set the Formula on the data var values = series.Descendants <Values>().FirstOrDefault(); if (values != null) { values.NumberReference = new NumberReference(); values.NumberReference.Formula = new Formula(); values.NumberReference.Formula.Text = seriesDataRange.Reference; } }