Chart is not refreshing after updating Embedded excel part in open xml sdk 2.0

Question

I am trying to update embedded excel part of chart in power point by some other excel file, it is updating the embedded excel file but the chart.

one thing which I noticed is, once I come back from "Edit Data" option i can able to see the update in the chart. Please help me how to refresh the chart in open xml

I am using the following code,

 PresentationDocument myDestDeck = PresentationDocument.Open(@"Presentation1.pptx", true);

        PresentationPart PresPart = myDestDeck.PresentationPart;
        SlidePart slidePart = PresPart.SlideParts.FirstOrDefault();
        ChartPart chartPart1 = slidePart.ChartParts.FirstOrDefault();

        EmbeddedPackagePart embeddedPackagePart1 = chartPart1.EmbeddedPackagePart;
        embeddedPackagePart1.FeedData(new FileStream(@"Output12.xlsx", FileMode.Open, FileAccess.ReadWrite));

        PresPart.Presentation.Save();
        myDestDeck.Close();

Please help

Thanks in advance,

Answer 1

I have only worked with Open XML in .docx context, but I suspect you are having the same problem I had when I wanted to update some embedded chars in the document.

Assuming the same approach I took is valid for Power Point, a chart has two datapoints in the zip-package that contains all the files. First you have the data-entry in excel files (which in a word document would be located in /word/embeddings) Besides that you have a data-cache that contains (for some reason) a cache of the data showed in your file in xml format. (located in /word/charts) For your new data to be effective at once when your file is opened, you need to update thoose files as well.

I solved this by first grabbing the chart parts

using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(name, true))
            {
                var mainPart = wordDoc.MainDocumentPart; //indlæs hovedpart af dokumentet
                DocumentFormat.OpenXml.Packaging.ChartPart[] charts = mainPart.ChartParts.ToArray(); //hent grafer
            }

You need to dig into the DocumentFormat.OpenXml.Drawing at the chart you want to edit Then you isolate the DocumentFormat.OpenXml.Drawing.Charts.ChartReference From there you can get the Charts ID which you into can look up in your Rels file to get the path to the chart xml you need to edit.

I didn't write that part of the code in the project, but I am at liberty to share the mess, so here you go. This is one ugly way of diggin from a BookmarkStart to the ChartReference.

[note: RelsRID is a dictionary with the ID as key and Filename as value.]

private String grab(BookmarkStart bookmarkStart)
    {
        //isolate chart
        #region grab
        var rids = RelsRID;
        DocumentFormat.OpenXml.Wordprocessing.Drawing elem = null;
        DocumentFormat.OpenXml.Drawing.Charts.ChartReference gd = null;
        try
        {
            elem = bookmarkStart.NextSibling().Elements<Drawing>().ElementAt(0); //første forsøg på at finde vores graf
        }
        catch (Exception)
        { //forsøg nummer 2
            OpenXmlElement testE = bookmarkStart.NextSibling();
            while (testE.LocalName.ToLower() != "drawing")
            {
                testE = testE.NextSibling();
                for (int i = 0; i < testE.Elements().Count(); i++)
                    if (testE.ElementAt(i).LocalName.ToLower() == "drawing") testE = testE.ElementAt(i);
            }
            elem = (DocumentFormat.OpenXml.Wordprocessing.Drawing)testE;
        }
        try
        { //first try at grabbing graph data
            gd = (DocumentFormat.OpenXml.Drawing.Charts.ChartReference)elem.Inline.Graphic.GraphicData.ElementAt(0);
        }
        catch (Exception)
        { //second possible route
            gd = (DocumentFormat.OpenXml.Drawing.Charts.ChartReference)elem.Anchor.Elements<Graphic>().First().Elements<GraphicData>().First().ElementAt(0);
        }
        var id = gd.Id;
        String matchname = "/word/" + rids[id.ToString()]; //create filepath
        #endregion
        return matchname;
    }

How you handle getting the filepath of the chart xml is up to you. I don't really approve of this way, but it should give you an idea of how to approach it.

When you got the path sorted out, you can do something like this (using the charts we extracted in the first code segment

public void EditGraph(BookmarkStart bookmarkStart, DocumentFormat.OpenXml.Packaging.ChartPart[] charts)
    {
        String check = grab(bookmarkStart);
        ChartPart chart = null;

        for (int i = 0; i < charts.Count(); i++) //loop th
        {
            chart = charts[i];
            if (check.ToLower().Equals(chart.Uri.ToString().ToLower()))
                break;
        }
        //chart now contains the chart-cache you are looking to edit.
    }

You can now choose how you want to edit the data it contains. I choose to pull out the plot xml from the chart, remove it from the chart, edit the xml I pulled out and put it back in. But I'm certain you can do it directly with open-xml as well. You get the plot elements like this

var plots = chart.ChartSpace.Elements<DocumentFormat.OpenXml.Drawing.Charts.Chart>();

I wish I knew a more simple way to explain it, but this is proberbly the best I can do.

Finally I never really did find a good way to extract the Rels from a document, so I ended up just jumping into the zip-file and pull it out.

I construct the rels-dictionary used previously like this

private Dictionary<String, String> RelsRIDToFile()
    {
    String rels;
            using (MemoryStream memory = new MemoryStream())
            {
                using (ZipFile zip = ZipFile.Read("wordfile.docx"))
                {
                    ZipEntry e = zip["word/_rels/document.xml.rels"];
                    e.Extract(memory);
                }
                using (StreamReader reader = new StreamReader(memory))
                {
                    memory.Seek(0, SeekOrigin.Begin);
                    rels = reader.ReadToEnd();
                }
            }
        XmlDataDocument xml = new XmlDataDocument();
        xml.LoadXml(rels);
        XmlNodeList xmlnode;
        xmlnode = xml.GetElementsByTagName("Relationship");
        Dictionary<String, String> result = new Dictionary<string, string>();
        for (int i = 0; i < xmlnode.Count; i++)
        {
            var node = xmlnode[i];
            var atts = node.Attributes;
            String id = "";
            String target = "";
            for (int ii = 0; ii < atts.Count; ii++)
            {
                var att = atts[ii];
                if (att.Name.ToLower() == "id") id = att.Value;
                if (att.Name.ToLower() == "target") target = att.Value;
            }
            result[id] = target;
        }
        return result;
    }

Good luck, and let me know if I can clarify something.