// 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));
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chart">The chart.</param>
/// <param name="sheetName">Name of the sheet.</param>
protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName)
{
if (chart != null)
{
chart.RemoveAllChildren <BarChartSeries>();
var barChartSeries = chart.InsertAt(new BarChartSeries(), 3);
barChartSeries.Index = new Index {
Val = 1
};
barChartSeries.Order = new Order {
Val = 1
};
barChartSeries.SeriesText = new SeriesText(new NumericValue {
Text = chartData.xColumnName
});
var categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());
var values = barChartSeries.AppendChild(new Values());
var stringReference = categoryAxisData.AppendChild(new StringReference());
var numberReference = values.AppendChild(new NumberReference());
var stringCache = stringReference.AppendChild(new StringCache());
var numberingCache = numberReference.AppendChild(new NumberingCache());
uint rowIndex = 0;
foreach (var xToY in chartData.xValToYValMap)
{
var stringPoint = stringCache.AppendChild(new StringPoint {
Index = rowIndex
});
stringPoint.AppendChild(new NumericValue {
Text = xToY.Key
});
var numericPoint = numberingCache.AppendChild(new NumericPoint {
Index = rowIndex
});
numericPoint.AppendChild(new NumericValue {
Text = xToY.Value.ToString()
});
rowIndex++;
}
}
}
/// <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);
}
}