private static void CopyChartObjects(ChartPart oldChart, ChartPart newChart)
 {
     foreach (XElement dataReference in newChart.GetXDocument().Descendants(C.externalData))
     {
         string relId = dataReference.Attribute(R.id).Value;
         try
         {
             EmbeddedPackagePart oldPart = (EmbeddedPackagePart)oldChart.GetPartById(relId);
             EmbeddedPackagePart newPart = newChart.AddEmbeddedPackagePart(oldPart.ContentType);
             using (Stream oldObject = oldPart.GetStream(FileMode.Open, FileAccess.Read))
             using (Stream newObject = newPart.GetStream(FileMode.Create, FileAccess.ReadWrite))
             {
                 int byteCount;
                 byte[] buffer = new byte[65536];
                 while ((byteCount = oldObject.Read(buffer, 0, 65536)) != 0)
                     newObject.Write(buffer, 0, byteCount);
             }
             dataReference.Attribute(R.id).Value = newChart.GetIdOfPart(newPart);
         }
         catch (ArgumentOutOfRangeException)
         {
             ExternalRelationship oldRelationship = oldChart.GetExternalRelationship(relId);
             Guid g = Guid.NewGuid();
             string newRid = "R" + g.ToString().Replace("-", "");
             var oldRel = oldChart.ExternalRelationships.FirstOrDefault(h => h.Id == relId);
             if (oldRel == null)
                 throw new DocumentBuilderInternalException("Internal Error 0007");
             newChart.AddExternalRelationship(oldRel.RelationshipType, oldRel.Uri, newRid);
             dataReference.Attribute(R.id).Value = newRid;
         }
     }
 }
Пример #2
0
        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();
                        }
                    }
                }
            }
        }
Пример #3
0
 private static void CopyChartObjects(ChartPart oldChart, ChartPart newChart)
 {
     foreach (XElement dataReference in newChart.GetXDocument().Descendants(ns_c + "externalData"))
     {
         string relId = dataReference.Attribute(ns_r + "id").Value;
         EmbeddedPackagePart oldPart = (EmbeddedPackagePart)oldChart.GetPartById(relId);
         EmbeddedPackagePart newPart = newChart.AddEmbeddedPackagePart(oldPart.ContentType);
         using (Stream oldObject = oldPart.GetStream(FileMode.Open, FileAccess.Read))
         using (Stream newObject = newPart.GetStream(FileMode.Create, FileAccess.ReadWrite))
         {
             int byteCount;
             byte[] buffer = new byte[65536];
             while ((byteCount = oldObject.Read(buffer, 0, 65536)) != 0)
                 newObject.Write(buffer, 0, byteCount);
         }
         dataReference.Attribute(ns_r + "id").Value = newChart.GetIdOfPart(newPart);
     }
 }
Пример #4
0
        private static void UpdateSeries(ChartPart chartPart, ChartData chartData)
        {
            UpdateEmbeddedWorkbook(chartPart, chartData);

            XDocument cpXDoc = chartPart.GetXDocument();
            XElement root = cpXDoc.Root;
            var firstSeries = root.Descendants(C.ser).FirstOrDefault();
            var numRef = firstSeries.Elements(C.val).Elements(C.numRef).FirstOrDefault();
            string sheetName = null;
            var f = (string)firstSeries.Descendants(C.f).FirstOrDefault();
            if (f != null)
                sheetName = f.Split('!')[0];

            // remove all but first series
            XName chartType = firstSeries.Parent.Name;
            firstSeries.Parent.Elements(C.ser).Skip(1).Remove();

            var newSetOfSeries = chartData.SeriesNames
                .Select((string sn, int si) =>
                {
                    XElement cat = null;

                    var oldCat = firstSeries.Elements(C.cat).FirstOrDefault();
                    if (oldCat == null)
                        throw new OpenXmlPowerToolsException("Invalid chart markup");

                    var catHasFormula = oldCat.Descendants(C.f).Any();
                    if (catHasFormula)
                    {
                        XElement newFormula = null;
                        if (sheetName != null)
                            newFormula = new XElement(C.f, string.Format("{0}!$A$2:$A${1}", sheetName, chartData.CategoryNames.Length + 1));
                        if (chartData.CategoryDataType == ChartDataType.String)
                        {
                            cat = new XElement(C.cat,
                                new XElement(C.strRef,
                                    newFormula,
                                    new XElement(C.strCache,
                                        new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                        chartData.CategoryNames.Select((string cn, int ci) =>
                                        {
                                            var newPt = new XElement(C.pt,
                                                new XAttribute("idx", ci),
                                                new XElement(C.v, chartData.CategoryNames[ci]));
                                            return newPt;
                                        }))));
                        }
                        else
                        {
                            cat = new XElement(C.cat,
                                new XElement(C.numRef,
                                    newFormula,
                                    new XElement(C.numCache,
                                        new XElement(C.formatCode, FormatCodes[chartData.CategoryFormatCode]),
                                        new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                        chartData.CategoryNames.Select((string cn, int ci) =>
                                        {
                                            var newPt = new XElement(C.pt,
                                                new XAttribute("idx", ci),
                                                new XElement(C.v, chartData.CategoryNames[ci]));
                                            return newPt;
                                        }))));
                        }
                    }
                    else
                    {
                        if (chartData.CategoryDataType == ChartDataType.String)
                        {
                            cat = new XElement(C.cat,
                                new XElement(C.strLit,
                                    new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                    chartData.CategoryNames.Select((string cn, int ci) =>
                                    {
                                        var newPt = new XElement(C.pt,
                                            new XAttribute("idx", ci),
                                            new XElement(C.v, chartData.CategoryNames[ci]));
                                        return newPt;
                                    })));
                        }
                        else
                        {
                            cat = new XElement(C.cat,
                                new XElement(C.numLit,
                                    new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                    chartData.CategoryNames.Select((string cn, int ci) =>
                                    {
                                        var newPt = new XElement(C.pt,
                                            new XAttribute("idx", ci),
                                            new XElement(C.v, chartData.CategoryNames[ci]));
                                        return newPt;
                                    })));
                        }
                    }

                    XElement newCval = null;

                    if (sheetName == null)
                    {
                        newCval = new XElement(C.val,
                                new XElement(C.numLit,
                                    new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                    chartData.CategoryNames.Select((string cn, int ci) =>
                                    {
                                        var newPt = new XElement(C.pt,
                                            new XAttribute("idx", ci),
                                            new XElement(C.v, chartData.Values[si][ci]));
                                        return newPt;
                                    })));
                    }
                    else
                    {
                        newCval = new XElement(C.val,
                                new XElement(C.numRef,
                                    sheetName != null ?
                                        new XElement(C.f, string.Format("{0}!${2}$2:${2}${1}", sheetName, chartData.CategoryNames.Length + 1, SpreadsheetMLUtil.IntToColumnId(si + 1))) : null,
                                    new XElement(C.numCache,
                                        sheetName != null ? numRef.Descendants(C.formatCode) : null,
                                        new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                        chartData.CategoryNames.Select((string cn, int ci) =>
                                        {
                                            var newPt = new XElement(C.pt,
                                                new XAttribute("idx", ci),
                                                new XElement(C.v, chartData.Values[si][ci]));
                                            return newPt;
                                        }))));
                    }

                    var serHasFormula = firstSeries.Descendants(C.f).Any();
                    XElement tx = null;
                    if (serHasFormula)
                    {
                        XElement newFormula = null;
                        if (sheetName != null)
                            newFormula = new XElement(C.f, string.Format("{0}!${1}$1", sheetName, SpreadsheetMLUtil.IntToColumnId(si + 1)));
                        tx = new XElement(C.tx,
                            new XElement(C.strRef,
                                newFormula,
                                new XElement(C.strCache,
                                    new XElement(C.ptCount, new XAttribute("val", 1)),
                                    new XElement(C.pt,
                                        new XAttribute("idx", 0),
                                        new XElement(C.v, chartData.SeriesNames[si])))));
                    }
                    else
                    {
                        tx = new XElement(C.tx,
                            new XElement(C.v, chartData.SeriesNames[si]));
                    }

                    XElement newSer = null;

                    if (chartType == C.area3DChart || chartType == C.areaChart)
                    {
                        newSer = new XElement(C.ser,
                            // common
                            new XElement(C.idx, new XAttribute("val", si)),
                            new XElement(C.order, new XAttribute("val", si)),
                            tx,
                            firstSeries.Elements(C.spPr),

                            // CT_AreaSer
                            firstSeries.Elements(C.pictureOptions),
                            firstSeries.Elements(C.dPt),
                            firstSeries.Elements(C.dLbls),
                            firstSeries.Elements(C.trendline),
                            firstSeries.Elements(C.errBars),
                            cat,
                            newCval,
                            firstSeries.Elements(C.extLst));
                    }
                    else if (chartType == C.bar3DChart || chartType == C.barChart)
                    {
                        newSer = new XElement(C.ser,
                            // common
                            new XElement(C.idx, new XAttribute("val", si)),
                            new XElement(C.order, new XAttribute("val", si)),
                            tx,
                            firstSeries.Elements(C.spPr),

                            // CT_BarSer
                            firstSeries.Elements(C.invertIfNegative),
                            firstSeries.Elements(C.pictureOptions),
                            firstSeries.Elements(C.dPt),
                            firstSeries.Elements(C.dLbls),
                            firstSeries.Elements(C.trendline),
                            firstSeries.Elements(C.errBars),
                            cat,
                            newCval,
                            firstSeries.Elements(C.shape),
                            firstSeries.Elements(C.extLst));
                    }
                    else if (chartType == C.line3DChart || chartType == C.lineChart || chartType == C.stockChart)
                    {
                        newSer = new XElement(C.ser,
                            // common
                            new XElement(C.idx, new XAttribute("val", si)),
                            new XElement(C.order, new XAttribute("val", si)),
                            tx,
                            firstSeries.Elements(C.spPr),

                            // CT_LineSer
                            firstSeries.Elements(C.marker),
                            firstSeries.Elements(C.dPt),
                            firstSeries.Elements(C.dLbls),
                            firstSeries.Elements(C.trendline),
                            firstSeries.Elements(C.errBars),
                            cat,
                            newCval,
                            firstSeries.Elements(C.smooth),
                            firstSeries.Elements(C.extLst));
                    }
                    else if (chartType == C.doughnutChart || chartType == C.ofPieChart || chartType == C.pie3DChart || chartType == C.pieChart)
                    {
                        newSer = new XElement(C.ser,
                            // common
                            new XElement(C.idx, new XAttribute("val", si)),
                            new XElement(C.order, new XAttribute("val", si)),
                            tx,
                            firstSeries.Elements(C.spPr),

                            // CT_PieSer
                            firstSeries.Elements(C.explosion),
                            firstSeries.Elements(C.dPt),
                            firstSeries.Elements(C.dLbls),
                            cat,
                            newCval,
                            firstSeries.Elements(C.extLst));
                    }
                    else if (chartType == C.surface3DChart || chartType == C.surfaceChart)
                    {
                        newSer = new XElement(C.ser,
                            // common
                            new XElement(C.idx, new XAttribute("val", si)),
                            new XElement(C.order, new XAttribute("val", si)),
                            tx,
                            firstSeries.Elements(C.spPr),

                            // CT_SurfaceSer
                            cat,
                            newCval,
                            firstSeries.Elements(C.extLst));
                    }

                    if (newSer == null)
                        throw new OpenXmlPowerToolsException("Unsupported chart type");

                    int accentNumber = (si % 6) + 1;
                    newSer = (XElement)UpdateAccentTransform(newSer, accentNumber);
                    return newSer;
                });
            firstSeries.ReplaceWith(newSetOfSeries);
            chartPart.PutXDocument();
        }