How can one programmatically read the graph values from a Powerpoint presentation using Apache's POI?

Question

I have a Powerpoint presentation with an graph which I want to access using Java and Apache's POI. When I edit the graph data using Powerpoint an Excel window opens with the values, I want to access these values from my Java application.

How does one access the values of the graph programmatically?

Can you not just [get the embedded resource](http://poi.apache.org/poifs/embeded.html) out, then open it with [Apache POI's HSSF spreadsheet code](http://poi.apache.org/spreadsheet/) as normal? — Gagravarr, Dec 02 '13 at 09:58
@Gagravarr Your link shows how to do it for HSLF objects, but how do we go about doing the same for XSLF? — mahacoder, Oct 28 '15 at 06:49

score 5 · Accepted Answer · answered Dec 04 '13 at 08:04

In the first part we need to navigate to an XSLFChart object:

final String filename = "resources/fptbenchmark/Powerpoint Import.pptx";
final XMLSlideShow ppt = new XMLSlideShow(new FileInputStream(filename));

final XSLFSlide slide = ppt.getSlides()[5];

The slide contains different parts (getRelations()) one of which should contain the XSLFChart:

final List<POIXMLDocumentPart> relations = slide.getRelations();
assert relations.size() == 3;

final XSLFChart xslfChart = (XSLFChart)relations.get(2);

When you examine the xslfChart variable in the debugger you will notice that the field CTChartImpl chart shows the underlying XML data, which might look like this:

<xml-fragment xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<c:autoTitleDeleted val="0"/>
<c:plotArea>
  <c:scatterChart>
    <c:ser>
      <c:tx>
        <c:strRef>
          <c:f>Sheet1!$E$8</c:f>
          <c:strCache>
            <c:ptCount val="1"/>
            <c:pt idx="0">
              <c:v>y axis caption</c:v>
            </c:pt>
          </c:strCache>
        </c:strRef>
      </c:tx>
      <c:xVal>
        <c:numRef>
          <c:f>Sheet1!$A$9:$A$28</c:f>
          <c:numCache>
            <c:formatCode>General</c:formatCode>
            <c:ptCount val="20"/>
            <c:pt idx="0">
              <c:v>1200</c:v>
            </c:pt>
            <c:pt idx="1">
              <c:v>1600</c:v>
            </c:pt>
            <c:pt idx="2">
              <c:v>2000</c:v>
            </c:pt>
...

You can naviage this tree starting with the CTChart:

CTChart ctChart = xslfChart.getCTChart();

Since there is a <c:plotArea> tag, you call the associated member function to access it:

CTPlotArea plotArea = ctChart.getPlotArea();

From there you should be able to navigate your way around

List<CTNumVal> ptList = plotArea.getScatterChartList().get(1)
    .getSerList().get(0)
    .getXVal()
    .getNumRef()
    .getNumCache()
    .getPtList();

Now you have access to the values:

ptList.get(0).getV();

References

How can one programmatically read the graph values from a Powerpoint presentation using Apache's POI?

1 Answers1

Linked