private static void UpdateEmbeddedWorkbook(ChartPart chartPart, ChartData chartData) { XDocument cpXDoc = chartPart.GetXDocument(); XElement root = cpXDoc.Root; var firstSeries = root.Descendants(C.ser).FirstOrDefault(); if (firstSeries == null) { return; } var firstFormula = (string)firstSeries.Descendants(C.f).FirstOrDefault(); if (firstFormula == null) { return; } var sheet = firstFormula.Split('!')[0]; var embeddedSpreadsheetRid = (string)root.Descendants(C.externalData).Attributes(R.id).FirstOrDefault(); if (embeddedSpreadsheetRid == null) { return; } var embeddedSpreadsheet = chartPart.GetPartById(embeddedSpreadsheetRid); if (embeddedSpreadsheet != null) { using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(embeddedSpreadsheet.GetStream(), true)) { var workbookPart = sDoc.WorkbookPart; var wbRoot = workbookPart.GetXDocument().Root; var sheetRid = (string)wbRoot .Elements(S.sheets) .Elements(S.sheet) .Where(s => (string)s.Attribute("name") == sheet) .Attributes(R.id) .FirstOrDefault(); if (sheetRid != null) { var sheetPart = workbookPart.GetPartById(sheetRid); var xdSheet = sheetPart.GetXDocument(); var sheetData = xdSheet.Descendants(S.sheetData).FirstOrDefault(); var stylePart = workbookPart.WorkbookStylesPart; var xdStyle = stylePart.GetXDocument(); int categoryStyleId = 0; if (chartData.CategoryFormatCode != 0) { categoryStyleId = AddDxfToDxfs(xdSheet, xdStyle, chartData.CategoryFormatCode); } stylePart.PutXDocument(); var firstRow = new XElement(S.row, new XAttribute("r", "1"), new XAttribute("spans", string.Format("1:{0}", chartData.SeriesNames.Length + 1)), new [] { new XElement(S.c, new XAttribute("r", "A1"), new XAttribute("t", "str"), new XElement(S.v, new XAttribute(XNamespace.Xml + "space", "preserve"), " ")) } .Concat( chartData.SeriesNames .Select((sn, i) => new XElement(S.c, new XAttribute("r", RowColToString(0, i + 1)), new XAttribute("t", "str"), new XElement(S.v, sn))))); var otherRows = chartData .CategoryNames .Select((cn, r) => { var row = new XElement(S.row, new XAttribute("r", r + 2), new XAttribute("spans", string.Format("1:{0}", chartData.SeriesNames.Length + 1)), new[] { new XElement(S.c, new XAttribute("r", RowColToString(r + 1, 0)), categoryStyleId != 0 ? new XAttribute("s", categoryStyleId) : null, chartData.CategoryDataType == ChartDataType.String ? new XAttribute("t", "str") : null, new XElement(S.v, cn)) }.Concat( Enumerable.Range(0, chartData.Values.Length) .Select((c, ci) => { var cell = new XElement(S.c, new XAttribute("r", RowColToString(r + 1, ci + 1)), new XElement(S.v, chartData.Values[ci][r])); return(cell); }))); return(row); }); var allRows = new[] { firstRow }.Concat(otherRows); var newSheetData = new XElement(S.sheetData, allRows); sheetData.ReplaceWith(newSheetData); sheetPart.PutXDocument(); var tablePartRid = (string)xdSheet .Root .Elements(S.tableParts) .Elements(S.tablePart) .Attributes(R.id) .FirstOrDefault(); if (tablePartRid != null) { var partTable = sheetPart.GetPartById(tablePartRid); var xdTablePart = partTable.GetXDocument(); var xaRef = xdTablePart.Root.Attribute("ref"); xaRef.Value = string.Format("A1:{0}", RowColToString(chartData.CategoryNames.Length - 1, chartData.SeriesNames.Length)); var xeNewTableColumns = new XElement(S.tableColumns, new XAttribute("count", chartData.SeriesNames.Count() + 1), new[] { new XElement(S.tableColumn, new XAttribute("id", 1), new XAttribute("name", " ")) }.Concat( chartData.SeriesNames.Select((cn, ci) => new XElement(S.tableColumn, new XAttribute("id", ci + 2), new XAttribute("name", cn))))); var xeExistingTableColumns = xdTablePart.Root.Element(S.tableColumns); if (xeExistingTableColumns != null) { xeExistingTableColumns.ReplaceWith(xeNewTableColumns); } partTable.PutXDocument(); } } } } }
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++; } } } }