/// <summary>
/// LineChart has a different structure for outline.
/// </summary>
public override ChartShapeProperties SetChartShapeProperties(OpenXmlCompositeElement chartSeries)
{
ChartShapeProperties chartShapeProperties = new ChartShapeProperties();
Outline outline = new Outline()
{
Width = 28575, CapType = LineCapValues.Round
};
outline.Append(new SolidFill());
outline.Append(new Round());
chartShapeProperties.Append(new SolidFill());
chartShapeProperties.Append(outline);
chartShapeProperties.Append(new EffectList());
Marker marker = new Marker();
marker.Append(new Symbol()
{
Val = MarkerStyleValues.None
});
chartSeries.Append(chartShapeProperties);
chartSeries.Append(marker);
chartSeries.Append(new Smooth()
{
Val = false
});
return(chartShapeProperties);
}
public Excel.Row GetRow(OpenXmlCompositeElement sheetData, int r, bool check, Excel.Row cloning)
{
Excel.Row rez = null;
if (check)
{
foreach (OpenXmlCompositeElement row in sheetData)
{
rez = row as Excel.Row;
if (rez != null && rez.RowIndex != null && rez.RowIndex.Value == r)
{
break;
}
}
}
if (rez == null || rez.RowIndex.Value != r)
{
if (cloning != null)
{
Excel.Row parent = null;
if (!check)
{
foreach (OpenXmlCompositeElement row in sheetData)
{
rez = row as Excel.Row;
if (rez != null && rez.RowIndex != null && rez.RowIndex.Value == r)
{
parent = rez;
break;
}
}
}
rez = CloneRow(cloning, r);
if (parent != null)
{
parent.RowIndex = parent.RowIndex.Value + 1;
sheetData.InsertBefore <Excel.Row>(rez, parent);
}
else
{
sheetData.Append(rez);
}
}
else
{
rez = new Excel.Row()
{
RowIndex = (uint)r
};
sheetData.Append(rez);
}
}
return(rez);
}
/// <summary>
/// Set display, width, color and fill of borders and data (line, bar etc.) in chart.
/// </summary>
public ChartShapeProperties SetChartShapeProperties(OpenXmlCompositeElement chartSeries, bool visible = true, uint colorPoints = 0)
{
ChartShapeProperties chartShapeProperties1 = new ChartShapeProperties();
Outline outline1 = new Outline()
{
Width = 28575, CapType = LineCapValues.Round
};
Round round1 = new Round();
outline1.Append(new NoFill());
outline1.Append(round1);
EffectList effectList1 = new EffectList();
if (!visible)
{
chartShapeProperties1.Append(new NoFill());
}
chartShapeProperties1.Append(outline1);
chartShapeProperties1.Append(effectList1);
Marker marker1 = new Marker();
Symbol symbol1 = new Symbol()
{
Val = MarkerStyleValues.None
};
marker1.Append(symbol1);
Smooth smooth1 = new Smooth()
{
Val = false
};
chartSeries.Append(chartShapeProperties1);
chartSeries.Append(marker1);
chartSeries.Append(smooth1);
for (uint i = 0; i < colorPoints; i++)
{
chartSeries.Append(colorChartLines(i));
}
return(chartShapeProperties1);
}
internal static void ApplyDefaultBorder <T>(OpenXmlCompositeElement element)
where T : BorderType, new()
{
T border = new T()
{
Val = BorderValues.Single, Color = "auto", Size = (UInt32Value)4U, Space = (UInt32Value)0U
};
element.Append(border);
}
private static void ApplyBorder <T>(string cssStyle, OpenXmlCompositeElement element)
where T : BorderType, new()
{
GetBorderProperties(cssStyle, out BorderValues borderValue, out string color, out uint width);
T border = GetBorderType <T>(borderValue, color, width);
if (border != null)
{
element.Append(border);
}
}
/// <summary>
/// Set display, width, color and fill of borders and data (line, bar etc.) in chart.
/// </summary>
public virtual ChartShapeProperties SetChartShapeProperties(OpenXmlCompositeElement chartSeries)
{
ChartShapeProperties chartShapeProperties = new ChartShapeProperties();
Outline outline = new Outline()
{
Width = 28575, CapType = LineCapValues.Round
};
outline.Append(new NoFill());
outline.Append(new Round());
chartShapeProperties.Append(outline);
chartShapeProperties.Append(new EffectList());
chartSeries.Append(chartShapeProperties);
return(chartShapeProperties);
}
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);
}
}
}
}
}
private static Text SetTextElement(OpenXmlCompositeElement elem, string name, string text)
{
var textElems = elem.Descendants <Text>();
textElems.Skip(1).ToList().ForEach(e => e.Remove());
var textElem = textElems.FirstOrDefault();
if (textElem == null && elem.ChildElements.Count > 0)
{
throw new InvalidOperationException($"[{name}] No text element: {string.Join(", ", elem.ChildElements.Select(o => o.GetType()))}");
}
if (textElem == null)
{
textElem = new Text();
elem.Append(
new Paragraph(
new Run(
//new RunProperties() { Languages = new Languages() { Val = "el-GR" } },
textElem)));
}
textElem.Text = text;
return(textElem);
}
private void ReplaceRuns(OpenXmlCompositeElement par, INodeProvider nodeProvider)
{
FixNakedText(par, nodeProvider);
string text = par.ChildElements.Where(a => nodeProvider.IsRun(a)).ToString(r => nodeProvider.GetText(r), "");
var matches = TemplateUtils.KeywordsRegex.Matches(text).Cast <Match>().ToList();
if (matches.Any())
{
List <ElementInfo> infos = GetElementInfos(par.ChildElements, nodeProvider);
par.RemoveAllChildren();
var stack = new Stack <ElementInfo>(infos.AsEnumerable().Reverse());
foreach (var m in matches)
{
var interval = new Interval <int>(m.Index, m.Index + m.Length);
// [Before][Start][Ignore][Ignore][End]...[Remaining]
// [ Match ]
ElementInfo start = stack.Pop(); //Start
while (start.Interval.Max <= interval.Min) //Before
{
par.Append(start.Element);
start = stack.Pop();
}
var startRun = (OpenXmlCompositeElement)nodeProvider.CastRun(start.Element);
if (start.Interval.Min < interval.Min)
{
var firstRunPart = nodeProvider.NewRun(
(OpenXmlCompositeElement?)nodeProvider.GetRunProperties(startRun)?.CloneNode(true),
start.Text !.Substring(0, m.Index - start.Interval.Min),
SpaceProcessingModeValues.Preserve
);
par.Append(firstRunPart);
}
par.Append(new MatchNode(nodeProvider, m)
{
RunProperties = (OpenXmlCompositeElement?)nodeProvider.GetRunProperties(startRun)?.CloneNode(true)
});
ElementInfo end = start;
while (end.Interval.Max < interval.Max) //Ignore
{
end = stack.Pop();
}
if (interval.Max < end.Interval.Max) //End
{
var endRun = (OpenXmlCompositeElement)end.Element;
var textPart = end.Text !.Substring(interval.Max - end.Interval.Min);
var endRunPart = nodeProvider.NewRun(
nodeProvider.GetRunProperties(startRun)?.Let(r => (OpenXmlCompositeElement)r.CloneNode(true)),
textPart,
SpaceProcessingModeValues.Preserve
);
stack.Push(new ElementInfo(endRunPart, textPart)
{
Interval = new Interval <int>(interval.Max, end.Interval.Max)
});
}
}
while (!stack.IsEmpty()) //Remaining
{
var pop = stack.Pop();
par.Append(pop.Element);
}
}
}
internal static void ApplyBorders(OpenXmlCompositeElement element,
string borderStyle,
string leftBorderStyle,
string topBorderStyle,
string rightBorderStyle,
string bottomBorderStyle,
bool useDefaultBorder)
{
BorderValues borderValue = BorderValues.None;
string color = string.Empty;
UInt32 width = 0;
bool hasBorder = false;
if (!string.IsNullOrEmpty(borderStyle))
{
hasBorder = true;
GetBorderProperties(borderStyle, out borderValue, out color, out width);
}
if (!string.IsNullOrEmpty(topBorderStyle))
{
ApplyBorder <TopBorder>(topBorderStyle, element);
}
else if (hasBorder)
{
TopBorder topBorder = GetBorderType <TopBorder>(borderValue, color, width);
if (topBorder != null)
{
element.Append(topBorder);
}
}
else if (useDefaultBorder)
{
ApplyDefaultBorder <TopBorder>(element);
}
if (!string.IsNullOrEmpty(leftBorderStyle))
{
ApplyBorder <LeftBorder>(leftBorderStyle, element);
}
else if (hasBorder)
{
LeftBorder leftBorder = GetBorderType <LeftBorder>(borderValue, color, width);
if (leftBorder != null)
{
element.Append(leftBorder);
}
}
else if (useDefaultBorder)
{
ApplyDefaultBorder <LeftBorder>(element);
}
if (!string.IsNullOrEmpty(bottomBorderStyle))
{
ApplyBorder <BottomBorder>(bottomBorderStyle, element);
}
else if (hasBorder)
{
BottomBorder bottomBorder = GetBorderType <BottomBorder>(borderValue, color, width);
if (bottomBorder != null)
{
element.Append(bottomBorder);
}
}
else if (useDefaultBorder)
{
ApplyDefaultBorder <BottomBorder>(element);
}
if (!string.IsNullOrEmpty(rightBorderStyle))
{
ApplyBorder <RightBorder>(rightBorderStyle, element);
}
else if (hasBorder)
{
RightBorder rightBorder = GetBorderType <RightBorder>(borderValue, color, width);
if (rightBorder != null)
{
element.Append(rightBorder);
}
}
else if (useDefaultBorder)
{
ApplyDefaultBorder <RightBorder>(element);
}
}
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
// Set chart title
chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title));
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
uint chartSeriesCounter = 0;
OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea);
foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series))
{
// Set chart and series depending on type.
OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart);
// Every method from chartPropertySetter can be overriden to customize chart export.
ChartPropertySetter.SetChartShapeProperties(chartSeries);
ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList());
chartSeriesCounter++;
}
chart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
chart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ChartPropertySetter.SetLineCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ChartPropertySetter.SetValueAxis(plotArea);
ChartPropertySetter.SetLegend(chartContainer);
ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location);
}
private static void TransferChildElements(CustomXmlElement from, OpenXmlCompositeElement to)
{
OpenXmlElement[] elements = from.ChildElements.ToArray();
foreach (var e in elements)
{
e.Remove();
}
to.Append(elements);
}
/// <summary>
/// Create and insert data to Axis
/// </summary>
public virtual void SetChartAxis(OpenXmlCompositeElement chartSeries, List <ChartModel> data)
{
dataCount = data.Count;
if (dataCount > 0 && !isArgumentDate)
{
DateTime parsedDate;
isArgumentDate = DateTime.TryParse(data[0].Argument, out parsedDate);
}
uint i = 0;
// X axis
StringLiteral stringLiteral = new StringLiteral();
stringLiteral.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
NumberLiteral numberLiteralX = new NumberLiteral();
numberLiteralX.Append(new FormatCode("mmm dd"));
numberLiteralX.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
// Y axis
NumberLiteral numberLiteralY = new NumberLiteral();
numberLiteralY.Append(new FormatCode(ChartProperties.AxisYFormatCode));
numberLiteralY.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
yAxisValue = data.Max(x => x.Value) > yAxisValue?data.Max(x => x.Value) : yAxisValue;
// Set values to X and Y axis.
foreach (var chartModel in data)
{
if (isArgumentDate)
{
numberLiteralX.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(CalculateExcelDate(chartModel.Argument)));
}
else
{
stringLiteral.Append(new StringPoint()
{
Index = new UInt32Value(i), NumericValue = new NumericValue()
{
Text = chartModel.Argument
}
});
}
numberLiteralY.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(ChartProperties.AxisYFormatCategory == "Time" ? ((double)chartModel.Value / 86400).ToString(System.Globalization.CultureInfo.InvariantCulture) : chartModel.Value.ToString(System.Globalization.CultureInfo.InvariantCulture)));
i++;
}
if (isArgumentDate)
{
chartSeries.Append(new CategoryAxisData()
{
NumberLiteral = numberLiteralX
});
}
else
{
chartSeries.Append(new CategoryAxisData()
{
StringLiteral = stringLiteral
});
}
chartSeries.Append(new Values()
{
NumberLiteral = numberLiteralY
});
}