/// <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;
}
}