private IEnumerable <T> SetChartSeries <T>(OpenXmlCompositeElement chart, int newSeriesCount, bool addNewIfRequired) where T : OpenXmlCompositeElement
{
var seriesList = chart.Elements <T>();
int currentSeriesCount = seriesList.Count();
if (currentSeriesCount > newSeriesCount)
{
//chart on template has more series than in the data, remove last x series
int seriesToRemove = currentSeriesCount - newSeriesCount;
for (int i = 0; i < seriesToRemove; i++)
{
chart.RemoveChild <T>(seriesList.Last());
}
seriesList = chart.Elements <T>();
}
else
if (addNewIfRequired && currentSeriesCount < newSeriesCount)
{
//chart on the template has fewer series so we need to add some by clonning the last one
for (int i = 0; i < newSeriesCount - currentSeriesCount; i++)
{
var lastSeries = chart.Elements <T>().Last();
var seriesClone = (T)lastSeries.CloneNode(true);
var lastSeriesIndex = lastSeries.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Index>();
var seriesCloneIndex = seriesClone.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Index>();
seriesCloneIndex.Val = new UInt32Value(lastSeriesIndex.Val.Value + 1);
var lastSeriesOrder = lastSeries.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Order>();
var seriesCloneOrder = seriesClone.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Order>();
seriesCloneOrder.Val = new UInt32Value(lastSeriesOrder.Val.Value + 1);
chart.InsertAfter(seriesClone, lastSeries);
}
seriesList = chart.Elements <T>();
}
return(chart.Elements <T>());
}
internal static T GetOrCreateAfter <T>(this OpenXmlCompositeElement element, OpenXmlElement after) where T : OpenXmlElement, new()
{
if (!element.Has <T>())
{
element.InsertAfter(new T(), after);
}
return(element.Elements <T>().First());
}
// SetProperties (to enforce XSD Schema compliance)
#region SetProperties
/// <summary>
/// Insert a style element inside a RunProperties, taking care of the correct sequence order as defined in the ECMA Standard.
/// </summary>
/// <param name="containerProperties">A RunProperties or ParagraphProperties wherein the tag will be inserted.</param>
/// <param name="tag">The style to apply to the run.</param>
protected void SetProperties(OpenXmlCompositeElement containerProperties, OpenXmlElement tag)
{
// This implementation is largely inspired by DocumentFormat.OpenXml.OpenXmlCompositeElement.SetElement which is internal.
int tagOrder = GetTagOrder(tag);
OpenXmlElement firstChild = containerProperties.FirstChild;
OpenXmlElement openXmlElement = null;
Type type = tag.GetType();
while (firstChild != null)
{
bool isKnownElement = (!(firstChild is OpenXmlUnknownElement) && !(firstChild is OpenXmlMiscNode));
if (isKnownElement)
{
int num = GetTagOrder(firstChild);
if (num != tagOrder)
{
if (num > tagOrder)
{
break;
}
openXmlElement = firstChild;
}
#if FEATURE_REFLECTION
else if (!type.IsInstanceOfType(tag))
#else
else if (!type.GetTypeInfo().IsAssignableFrom(tag.GetType().GetTypeInfo()))
#endif
{
openXmlElement = firstChild;
}
else
{
openXmlElement = firstChild.PreviousSibling();
containerProperties.RemoveChild <OpenXmlElement>(firstChild);
break;
}
}
firstChild = firstChild.NextSibling();
}
if (tag != null)
{
containerProperties.InsertAfter(tag, openXmlElement);
}
}
// todo (a.dobrynin, 05.08.2020): replace this method with targetWorksheet.AddChild(child) when DocumentFormat.OpenXml 2.12.0 is released
// https://github.com/OfficeDev/Open-XML-SDK/pull/774
/// <summary>
/// Adds node in the correct location according to the schema
/// http://msdn.microsoft.com/en-us/library/office/cc880096(v=office.15).aspx
/// https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.worksheet?view=openxml-2.8.1#definition
/// https://github.com/OfficeDev/Open-XML-SDK/blob/058ec42001ca97850fd82cc16e3b234c155a6e7e/src/DocumentFormat.OpenXml/GeneratedCode/schemas_microsoft_com_office_excel_2006_main.g.cs#L90
/// </summary>
public static T AddChildToCorrectLocation <T>(this OpenXmlCompositeElement parent, T child) where T : OpenXmlElement
{
if (!(parent is Worksheet))
{
throw new InvalidOperationException("Schema-oriented insertion is supported only for Worksheets");
}
var precedingElementTypes = openXmlWorksheetNodesOrder.TakeWhile(x => x != typeof(T));
var closestPrecedingSibling = precedingElementTypes.Reverse()
.Select(precedingElementType => parent.Elements().LastOrDefault(x => x.GetType() == precedingElementType))
.FirstOrDefault(existingElement => existingElement != null);
return(closestPrecedingSibling != null
? parent.InsertAfter(child, closestPrecedingSibling)
: parent.InsertAt(child, 0));
}
private void FillSeriesDataPoints(OpenXmlCompositeElement seriesItem, Column dataColumn)
{
var seriesChartShapeProperties = seriesItem.FirstElement <ChartShapeProperties>();
for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++)
{
if (dataColumn.Data[rowNo] != null)
{
DataPoint dp = seriesItem.Elements <DataPoint>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == rowNo);
if (dp == null)
{
var dataPoint = new DataPoint();
DocumentFormat.OpenXml.Drawing.Charts.Index index = new DocumentFormat.OpenXml.Drawing.Charts.Index()
{
Val = new UInt32Value((uint)rowNo)
};
InvertIfNegative invertIfNegative = new InvertIfNegative()
{
Val = false
};
Bubble3D bubble3D = new Bubble3D()
{
Val = false
};
ChartShapeProperties chartShapeProperties = seriesChartShapeProperties == null ? new ChartShapeProperties() : (ChartShapeProperties)seriesChartShapeProperties.CloneNode(true);
dataPoint.Append(index);
dataPoint.Append(invertIfNegative);
dataPoint.Append(bubble3D);
dataPoint.Append(chartShapeProperties);
DataPoint lastDp = seriesItem.Elements <DataPoint>().LastOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value < rowNo);
if (lastDp != null)
{
seriesItem.InsertAfter(dataPoint, lastDp);
}
else
{
seriesItem.Append(dataPoint);
}
}
}
}
}
/// <summary>
/// Insert a style element inside a RunProperties, taking care of the correct sequence order as defined in the ECMA Standard.
/// </summary>
/// <param name="containerProperties">A RunProperties or ParagraphProperties wherein the tag will be inserted.</param>
/// <param name="tag">The style to apply to the run.</param>
protected void SetProperties(OpenXmlCompositeElement containerProperties, OpenXmlElement tag)
{
// This implementation is largely inspired by DocumentFormat.OpenXml.OpenXmlCompositeElement.SetElement which is internal.
int tagOrder = GetTagOrder(tag);
OpenXmlElement firstChild = containerProperties.FirstChild;
OpenXmlElement openXmlElement = null;
Type type = tag.GetType();
while (firstChild != null)
{
bool isKnownElement = (!(firstChild is OpenXmlUnknownElement) && !(firstChild is OpenXmlMiscNode));
if (isKnownElement)
{
int num = GetTagOrder(firstChild);
if (num != tagOrder)
{
if (num > tagOrder) break;
openXmlElement = firstChild;
}
else if (!type.IsInstanceOfType(tag))
{
openXmlElement = firstChild;
}
else
{
openXmlElement = firstChild.PreviousSibling();
containerProperties.RemoveChild<OpenXmlElement>(firstChild);
break;
}
}
firstChild = firstChild.NextSibling();
}
if (tag != null)
containerProperties.InsertAfter(tag, openXmlElement);
}
/// <summary>
/// Sets the color of the series using a solidcolor brush
/// If a null brush is supplied any color is removed so the color will be automatic
/// </summary>
/// <param name="line">The line.</param>
/// <param name="brush">The brush.</param>
public static void UpdateLineBrush(this OpenXmlCompositeElement line, Brush brush)
{
if (line == null)
{
return;
}
// If we have a BarChart, we really want tp update the SolidFill (not the Outline.SolidFill)
BarChartSeries barChartSeries = line as BarChartSeries;
if (barChartSeries != null)
{
// For BarCharts, we update the SolidFill
barChartSeries.UpdateSeriesColour((SolidColorBrush)brush);
}
else
{
// Update the Outline.SolidFill + set the SolidFill to the same colour
var chartShapeProperties = line.Descendants <ChartShapeProperties>().FirstOrDefault();
if (brush == null && !(brush is SolidColorBrush))
{
if (chartShapeProperties != null)
{
line.RemoveChild <ChartShapeProperties>(chartShapeProperties);
}
return;
}
// the series title, this is the name of the column header
var seriesText = line.Descendants <SeriesText>().FirstOrDefault();
if (chartShapeProperties == null)
{
chartShapeProperties = new ChartShapeProperties();
// if there's a series text then insert afterwards
if (seriesText == null)
{
line.InsertAt <ChartShapeProperties>(chartShapeProperties, 0);
}
else
{
line.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText);
}
}
var outline = chartShapeProperties.Descendants <Outline>().FirstOrDefault();
if (outline == null)
{
outline = new Outline();
chartShapeProperties.InsertAt(outline, 0);
}
var outlineSolidFill = outline.Descendants <SolidFill>().FirstOrDefault();
if (outlineSolidFill == null)
{
outlineSolidFill = new SolidFill();
outline.Append(outlineSolidFill);
}
// Update the fill with the supplied brush colour
outlineSolidFill.UpdateSolidFill((SolidColorBrush)brush);
// Clones the OutlineSolidFill as the SolidFill of the series...
var solidFill = chartShapeProperties.GetFirstChild <SolidFill>();
if (solidFill != null)
{
chartShapeProperties.RemoveChild(solidFill);
}
chartShapeProperties.InsertAt(outlineSolidFill.CloneNode(true), 0);
}
}
