none
Read Excel Charts using OpenXML RRS feed

  • Question

  • I need to get chart name and the chart type of two charts where is being in the same excel sheet. 

    chart name could be taken from DrawingPart -> WorksheetDrawing -> twoCellAnchor -> GraphicFrame -> NoneVisualGraphicFrameProperties -> NoneVisualDrawingProperties -> Name

    From where can I take chart type?

    Is it 

    DrawingPart -> ChartPart -> ChartSpace  -> Chart -> PlotArea ?

    Then How can I map particular chart name to chart type ?

    There are two drawingPart and ChartPart within a same WorkbookPart for two different charts. Is there any relational link between chart name and chart type?

    Monday, June 15, 2015 12:03 PM

Answers

  • Finally figured out what the c:chart element is in the TwoCellAnchor: it's a ChartReference (and not a Chart) object. I knew it was a reference, but the element name was throwing me off <sigh> This lets you "look up" the chart using GetPartById:
                    Array aTwoCellAnchors = TwoCellAnchors.ToArray();
                    //Array cps = dp.ChartParts.ToArray();
                    for (int cCount=0; cCount < TwoCellAnchors.Count();cCount++)
                    {
                        A.Spreadsheet.TwoCellAnchor TwoCelAnchor = (A.Spreadsheet.TwoCellAnchor)aTwoCellAnchors.GetValue(cCount);
                        if (TwoCelAnchor.InnerXml.Contains("<c:chart"))
                        {
                        txt +="\r\n" + TwoCelAnchor.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name;
                        DocumentFormat.OpenXml.Drawing.Charts.ChartReference chartRef = (DocumentFormat.OpenXml.Drawing.Charts.ChartReference)TwoCelAnchor.Descendants<A.GraphicData>().FirstOrDefault().ElementAt(0);
                        ChartPart cp =(ChartPart) dp.GetPartById(chartRef.Id.Value); 
                        //ChartPart cp = (ChartPart) cps.GetValue(cCount);
                        A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants<A.Charts.Chart>().FirstOrDefault();
                        txt += "\r\n" + c.PlotArea.ChildElements[1].LocalName.ToString();
                        }
                    }
    


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 18, 2015 6:09 PM
    Moderator

All replies

  • Hi Desika

    If you look at the Drawing XML you should see Graphic->GraphicData->Chart and the chart element has the attribute r:id with a value something like rId1.

    From there, go to the _rels folder and open the Rels file for the Drawing file. There, you'll find the r:id value and the information in that tells you which Chart xml file is associated with the Drawing.

    The chart type is in the Chart XML file:

    chart->plotArea->[type] for example bar3DChart

    In the Open XML SDK object model, it would go something like this:

                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
                {
                    WorkbookPart bkPart = doc.WorkbookPart;
                    DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = bkPart.Workbook;
                    DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
                    
                    DocumentFormat.OpenXml.Packaging.DrawingsPart dp =
                        (DocumentFormat.OpenXml.Packaging.DrawingsPart)wsPart.DrawingsPart;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing dWs = dp.WorksheetDrawing;
                    txt = dWs.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name;
                    DocumentFormat.OpenXml.Packaging.ChartPart cp = dp.ChartParts.FirstOrDefault();
                    A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants<A.Charts.Chart>().FirstOrDefault();
                    txt += "\r\n" + c.PlotArea.ChildElements[1].LocalName.ToString();
                    this.txtMessages.Text = txt;
                }
    


    Cindy Meister, VSTO/Word MVP, my blog


    Monday, June 15, 2015 7:53 PM
    Moderator
  • Hi Cindy,

    Above mentioned solution would be nice as far as there is only one chart in the sheet.
    When there are more than one charts there will be two elements for "dWs" and two elements for dp.ChartParts . Then how can I map particular dWs element with the dp.ChartParts element?  If we try to map with elements' index order, again there will be a problem when there is some other drawing like a SmartArt in the same excel sheet. If it is so, There will be three elements for dWs and two elements for dp.ChartParts.
    Tuesday, June 16, 2015 4:54 AM
  • Hi Desika

    It always helps to inspect the underlying XML... If you look at the drawing.xml file for the worksheet you'll see that the charts are contained within a single drawing, each begins with a twoCellAnchor element.

    So for multiple Drawings use an IEnumerable object set to the Descendants of type TwoCellAnchor, then loop them to inspect each Chart. Rather than using ForEach, convert the IEnumerable to an Array or something of that nature with an index so that you can loop the Chart parts at the same time, for example:

                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
                {
                    WorkbookPart bkPart = doc.WorkbookPart;
                    DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = bkPart.Workbook;
                    DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
                    
                    DocumentFormat.OpenXml.Packaging.DrawingsPart dp =
                        (DocumentFormat.OpenXml.Packaging.DrawingsPart)wsPart.DrawingsPart;
                    A.Spreadsheet.WorksheetDrawing dWs = dp.WorksheetDrawing;
                    IEnumerable<A.Spreadsheet.TwoCellAnchor> TwoCellAnchors = dWs.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor>();
                    Array aTwoCellAnchors = TwoCellAnchors.ToArray();
                    Array cps = dp.ChartParts.ToArray();
                    for (int cCount=0; cCount < TwoCellAnchors.Count();cCount++)
                    {
                        A.Spreadsheet.TwoCellAnchor TwoCelAnchor = (A.Spreadsheet.TwoCellAnchor)aTwoCellAnchors.GetValue(cCount);
                        txt +="\r\n" + TwoCelAnchor.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name;
                        ChartPart cp = (ChartPart) cps.GetValue(cCount);
                        A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants<A.Charts.Chart>().FirstOrDefault();
                        txt += "\r\n" + c.PlotArea.ChildElements[1].LocalName.ToString();
                        
                    }
                            this.txtMessages.Text = txt;
                }



    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 16, 2015 8:22 PM
    Moderator
  • Hi Cindy.

    You just try this code with an excel sheet which is having two charts and one SmartArt. Then TwoCellAnchors.Count() will be three, but cps.Count() will be two. Then you will come up with an error for following line when the cCount = 3

     ChartPart cp = (ChartPart) cps.GetValue(cCount);

    If you suggest me to use dp.ChartParts.Count() for the loop, I have a doubt weather the first TwoCellAnchors are for chart objects. I couldn't find any documentations to check how they are organizing.

    And also I need to some more things to know.. Where can I see the data series names and X axis  values? For example series 3, series4..etc and 1, 2, 3, 4, 5 (x axis values)  in following example?


    Thanks!
    -Desika-




    • Edited by DesikaH Wednesday, June 17, 2015 11:19 AM
    Wednesday, June 17, 2015 4:50 AM
  • A little imagination takes you a long way...

    Simply test for the presence of a c:chart element, for example

    if(TwoCelAnchor.InnerXml.Contains("<c:chart"))


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by DesikaH Thursday, June 18, 2015 3:54 AM
    • Unmarked as answer by DesikaH Thursday, June 18, 2015 6:48 AM
    Wednesday, June 17, 2015 7:43 PM
    Moderator
  • <<And also I need to some more things to know.. >>

    Please start a new message for each new topic. Otherwise, people who could help you won't see your questions!


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, June 17, 2015 7:44 PM
    Moderator

  • Thanks a lot Cindy. It worked.
    InnerXml.Contains(<what ever>)  is a good hint for my further developments as well :)


    Thursday, June 18, 2015 3:54 AM

  • Even though the elements' counts are same unfortunately those elements are not mapping properly by array index.
    When I tested with two charts, I realized it went wrong. :(
    Any more suggestions?

    Thursday, June 18, 2015 6:52 AM
  • Finally figured out what the c:chart element is in the TwoCellAnchor: it's a ChartReference (and not a Chart) object. I knew it was a reference, but the element name was throwing me off <sigh> This lets you "look up" the chart using GetPartById:
                    Array aTwoCellAnchors = TwoCellAnchors.ToArray();
                    //Array cps = dp.ChartParts.ToArray();
                    for (int cCount=0; cCount < TwoCellAnchors.Count();cCount++)
                    {
                        A.Spreadsheet.TwoCellAnchor TwoCelAnchor = (A.Spreadsheet.TwoCellAnchor)aTwoCellAnchors.GetValue(cCount);
                        if (TwoCelAnchor.InnerXml.Contains("<c:chart"))
                        {
                        txt +="\r\n" + TwoCelAnchor.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name;
                        DocumentFormat.OpenXml.Drawing.Charts.ChartReference chartRef = (DocumentFormat.OpenXml.Drawing.Charts.ChartReference)TwoCelAnchor.Descendants<A.GraphicData>().FirstOrDefault().ElementAt(0);
                        ChartPart cp =(ChartPart) dp.GetPartById(chartRef.Id.Value); 
                        //ChartPart cp = (ChartPart) cps.GetValue(cCount);
                        A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants<A.Charts.Chart>().FirstOrDefault();
                        txt += "\r\n" + c.PlotArea.ChildElements[1].LocalName.ToString();
                        }
                    }
    


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 18, 2015 6:09 PM
    Moderator

  • Thanks a lot for your great support Cindy!
    Wednesday, June 24, 2015 12:26 PM
  • Hello can any one show the demo how to implement the shapes in Charts 

    how to add user shapes in Charts 

    I am trying to implement this user shapes how add to charts  using C# in action in Open xml  is it possible ? 


    Ranjith@Learner @Microsoft products

    Sunday, May 22, 2016 10:00 AM