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