private void FillCategoryAxisData(CategoryAxisData categoryAxisData, ChartSeriesElement newSeriesItem, Column dataColumn) { int dataCount = dataColumn.Data.Count; UInt32Value pointCount = new UInt32Value((uint)dataCount); if (categoryAxisData != null && categoryAxisData.StringReference != null) { categoryAxisData.StringReference.StringCache.RemoveAllChildren <StringPoint>(); categoryAxisData.StringReference.StringCache.PointCount.Val = pointCount; categoryAxisData.StringReference.Formula.Text = newSeriesItem.CategoryAxisDataAddress; for (int rowNo = 0; rowNo < element.Data.Rows.Count; rowNo++) { StringPoint categoryAxisDataStringPoint = new StringPoint() { Index = (UInt32Value)((uint)rowNo) }; NumericValue categoryAxisDataNumericValue = new NumericValue() { Text = element.Data.Rows[rowNo].GetHeader() }; categoryAxisDataStringPoint.Append(categoryAxisDataNumericValue); categoryAxisData.StringReference.StringCache.Append(categoryAxisDataStringPoint); } } }
private void ReplaceLineChart(List <ChartSeriesElement> newSeries, LineChart chart, ShapeElementBase element) { int newSeriesCount = element.Data.Columns.Count(c => !c.IsHidden);// newSeries.Count; var seriesList = SetChartSeries <LineChartSeries>(chart, newSeriesCount, true); int index = 0; for (int i = 0; i < newSeries.Count; i++) { ChartSeriesElement newSeriesItem = newSeries[i]; if ((element.RowIndexes.Count == 1 && element.RowIndexes[0].IsAll) || element.ColumnIndexes.Any(idx => idx == newSeriesItem.ColumnIndex) || element.RowIndexes.Any(idx => idx == newSeriesItem.ColumnIndex)) { var seriesItem = seriesList.ElementAt(index); Column dataColumn = element.Data.Column(newSeriesItem.ColumnIndex); var categoryAxisData = seriesItem.FirstElement <CategoryAxisData>(); FillCategoryAxisData(categoryAxisData, newSeriesItem, dataColumn); SetSeriesText(seriesItem, newSeriesItem, dataColumn.GetHeader()); var values = seriesItem.FirstElement <Values>(); FillNumberReference(values.NumberReference, newSeriesItem, dataColumn); //FillSeriesDataPoints(seriesItem, dataColumn); FillSeriesLabels(seriesItem, dataColumn); SetPropertiesFromLegend(seriesItem, dataColumn); var errorBars = seriesItem.FirstElement <ErrorBars>(); if (errorBars != null) { FillErrorBars(errorBars, newSeries[i].MinusErrorBar, newSeries[i].PlusErrorBar); } index++; } } }
private void FillErrorBars(ErrorBars errorBars, ChartSeriesElement minusErrorBarData, ChartSeriesElement plusErrorBarData) { var plus = errorBars.FirstElement <Plus>(); if (plus != null && plusErrorBarData != null) { FillNumberReference(plus.NumberReference, plusErrorBarData, element.Data.Column(plusErrorBarData.ColumnIndex)); } var minus = errorBars.FirstElement <Minus>(); if (minus != null && minusErrorBarData != null) { FillNumberReference(minus.NumberReference, minusErrorBarData, element.Data.Column(minusErrorBarData.ColumnIndex)); } }
private void SetSeriesText(OpenXmlCompositeElement seriesItem, ChartSeriesElement newSeriesItem, string seriesHeader) { SeriesText seriesText = seriesItem.Elements <SeriesText>().First(); seriesText.StringReference.Formula.Text = newSeriesItem.SeriesTextAddress; seriesText.StringReference.StringCache.RemoveAllChildren <StringPoint>(); StringPoint stringReferencePoint = new StringPoint() { Index = (UInt32Value)((uint)0) }; NumericValue stringReferenceNumericValue = new NumericValue() { Text = seriesHeader }; stringReferencePoint.Append(stringReferenceNumericValue); seriesText.StringReference.StringCache.Append(stringReferencePoint); }
private void FillNumberReference(NumberReference valuesNumberReference, ChartSeriesElement newSeriesItem, Column dataColumn) { int dataCount = dataColumn.Data.Count; UInt32Value pointCount = new UInt32Value((uint)dataCount); valuesNumberReference.Formula.Text = newSeriesItem.ValuesAddress; valuesNumberReference.NumberingCache.RemoveAllChildren <NumericPoint>(); valuesNumberReference.NumberingCache.PointCount.Val = pointCount; for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++) { if (dataColumn.Data[rowNo] != null) { var point = new NumericPoint() { Index = new UInt32Value((uint)rowNo) }; point.NumericValue = new NumericValue(dataColumn.Data[rowNo] == null ? "0" : string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", dataColumn.Data[rowNo])); valuesNumberReference.NumberingCache.Append(point); } } }
private void ReplaceScatterChart(List <ChartSeriesElement> newSeries, ScatterChart chart, ShapeElementBase element) { int newSeriesCount = element.Data.Columns.Count(c => !c.IsHidden);// newSeries.Count; ChartSeriesElement yValuesSeries = null; var seriesList = SetChartSeries <ScatterChartSeries>(chart, newSeriesCount, false); int index = 0; for (int i = 0; i < newSeries.Count; i++) { if (newSeries[i].YValues != null) { yValuesSeries = newSeries[i].YValues; } if (yValuesSeries == null) { throw new Exception("At least one Y series required for scatter chart"); } ChartSeriesElement newSeriesItem = newSeries.ElementAt(i); if ((element.RowIndexes.Count == 1 && element.RowIndexes[0].IsAll) || element.ColumnIndexes.Any(idx => idx == newSeriesItem.ColumnIndex) || element.RowIndexes.Any(idx => idx == newSeriesItem.ColumnIndex)) { var seriesItem = seriesList.ElementAt(index); Column dataColumn = element.Data.Column(newSeriesItem.ColumnIndex); SetSeriesText(seriesItem, newSeriesItem, dataColumn.GetHeader()); var xvalues = seriesItem.FirstElement <XValues>(); FillNumberReference(xvalues.NumberReference, newSeriesItem, dataColumn); var yvalues = seriesItem.FirstElement <YValues>(); FillNumberReference(yvalues.NumberReference, yValuesSeries, element.Data.Column(yValuesSeries.ColumnIndex)); var errorBars = seriesItem.FirstElement <ErrorBars>(); if (errorBars != null) { FillErrorBars(errorBars, newSeries[i].MinusErrorBar, newSeries[i].PlusErrorBar); } index++; } } }
public void Process(ShapeElementBase shape) { element = shape as ChartElement; fullData = element.Data.Clone(); element.Data = fullData.GetFragmentByIndexes(element.RowIndexes, element.ColumnIndexes); element.ProcessCommands(element.Data); if (element.Data == null) { return; } //get chart reference A.GraphicData graphicData = element.ChartFrame.Graphic.GraphicData; ChartReference chartReference = graphicData.FirstElement <ChartReference>(); if (chartReference == null) { return; } //various chart structure elements ChartPart chartPart = element.Slide.Slide.SlidePart.GetPartById(chartReference.Id.Value) as ChartPart; Chart chart = chartPart.ChartSpace.FirstElement <Chart>(); //get external data and update it DataElement dataToInsert = element.Data.Clone(); foreach (var item in element.ChildShapes) { var childDataElement = fullData.GetFragmentByIndexes(item.RowIndexes, item.ColumnIndexes); element.ProcessCommands(childDataElement); dataToInsert.MergeWith(childDataElement); } ExternalData externalData = chartPart.ChartSpace.FirstElement <ExternalData>(); EmbeddedPackagePart xlsPackagePart = chartPart.GetPartById(externalData.Id.Value) as EmbeddedPackagePart; Stream sourceStream = xlsPackagePart.GetStream(); Stream outputStream = new MemoryStream(); dataToInsert.TrimHiddenRowsAndColumns(); List <ChartSeriesElement> newSeries = SpreadsheetProcessor.InsertData(dataToInsert, sourceStream, outputStream); outputStream.Seek(0, SeekOrigin.Begin); xlsPackagePart.FeedData(outputStream); ChartType type = ChartType.None; Tuple <int, int> dataRange = null; var charts = chart.PlotArea.Elements().ToList(); OpenXmlElement mainChart = null; int chartIndex = 0; for (; chartIndex < charts.Count; chartIndex++) { GetChartTypeAndDataRange(ref type, ref dataRange, charts[chartIndex]); if (type != ChartType.None) { mainChart = charts[chartIndex]; chartIndex++; break; } } int seriesIndex = 0; foreach (ErrorBarCommand errorBarCommand in element.CommandsOf <ErrorBarCommand>()) { ChartSeriesElement chartSeriesMinus = newSeries.FirstOrDefault(s => s.ColumnIndex == errorBarCommand.MinusIndex && !s.ColumnIndex.IsCore); ChartSeriesElement chartSeriesPlus = null; if (errorBarCommand.PlusIndex != null) { chartSeriesPlus = newSeries.FirstOrDefault(s => s.ColumnIndex == errorBarCommand.PlusIndex && !s.ColumnIndex.IsCore); } if (seriesIndex < newSeries.Count) { newSeries[seriesIndex].MinusErrorBar = chartSeriesMinus; newSeries[seriesIndex].PlusErrorBar = chartSeriesPlus; } seriesIndex++; } foreach (ErrorBarCommand errorBarCommand in element.CommandsOf <ErrorBarCommand>()) { ChartSeriesElement chartSeriesMinus = newSeries.FirstOrDefault(s => s.ColumnIndex == errorBarCommand.MinusIndex && !s.ColumnIndex.IsCore); ChartSeriesElement chartSeriesPlus = null; if (errorBarCommand.PlusIndex != null) { chartSeriesPlus = newSeries.FirstOrDefault(s => s.ColumnIndex == errorBarCommand.PlusIndex && !s.ColumnIndex.IsCore); } if (chartSeriesMinus != null) { newSeries.Remove(chartSeriesMinus); } if (chartSeriesPlus != null) { newSeries.Remove(chartSeriesPlus); } } seriesIndex = 0; if (element.CommandsOf <YCommand>().Count > 0) { List <ChartSeriesElement> newSeriesWithoutY = new List <ChartSeriesElement>(newSeries); foreach (YCommand yCommand in element.CommandsOf <YCommand>()) { ChartSeriesElement yChartSeries = newSeries.FirstOrDefault(s => s.ColumnIndex == yCommand.Index); // && !s.ColumnIndex.IsCore if (yChartSeries != null) { newSeriesWithoutY.Remove(yChartSeries); } } foreach (YCommand yCommand in element.CommandsOf <YCommand>()) { ChartSeriesElement yChartSeries = newSeries.FirstOrDefault(s => s.ColumnIndex == yCommand.Index); // && !s.ColumnIndex.IsCore for (int i = seriesIndex; i < newSeriesWithoutY.Count; i++) { newSeriesWithoutY[i].YValues = yChartSeries; } //if (seriesIndex < newSeries.Count) //{ // newSeries[seriesIndex].YValues = yChartSeries; //} seriesIndex++; } newSeries = new List <ChartSeriesElement>(newSeriesWithoutY); } switch (type) { case ChartType.Waterfall: case ChartType.Bar: ReplaceBarChart(newSeries, mainChart as BarChart, element, element.IsWaterfall); break; case ChartType.Scatter: ReplaceScatterChart(newSeries, mainChart as ScatterChart, element); break; case ChartType.Line: ReplaceLineChart(newSeries, mainChart as LineChart, element); break; } int childShapeIndex = 0; for (; chartIndex < charts.Count; chartIndex++) { var childChart = charts[chartIndex]; if (element.ChildShapes.Count > childShapeIndex) { GetChartTypeAndDataRange(ref type, ref dataRange, childChart); if (type != ChartType.None) { ProcessChildShapes(element, element.ChildShapes[childShapeIndex], type, childChart, newSeries); childShapeIndex++; } } } }