Yes, It is possible to read any type of chart using Apache POI. But Before reading any chart information you need to know what XML string you are receiving because this could be different based on the chart type i.e. pie, line, bar, scatter or mixed (a combination of two or more) charts, etc. Therefore you approach will be different for different type of chart.
For a simple bar chart like this:
![enter image description here]()
Your XML will look something like this:
<xml-fragment ...>
<c:title>
<c:tx>
<c:rich>
...
<a:p>
...
<a:r>
...
<a:t>Employee Salary</a:t>
</a:r>
</a:p>
</c:rich>
</c:tx>
...
</c:title>
...
<c:plotArea>
...
<c:barChart>
...
<c:ser>
...
<c:cat>
<c:strRef>
...
<c:strCache>
<c:ptCount val="5"/>
<c:pt idx="0">
<c:v>Tom</c:v>
</c:pt>
<c:pt idx="1">
<c:v>John</c:v>
</c:pt>
<c:pt idx="2">
<c:v>Harry</c:v>
</c:pt>
<c:pt idx="3">
<c:v>Sam</c:v>
</c:pt>
<c:pt idx="4">
<c:v>Richa</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:cat>
<c:val>
<c:numRef>
...
<c:numCache>
<c:formatCode>"$"#,##0</c:formatCode>
<c:ptCount val="5"/>
<c:pt idx="0">
<c:v>1000</c:v>
</c:pt>
<c:pt idx="1">
<c:v>700</c:v>
</c:pt>
<c:pt idx="2">
<c:v>300</c:v>
</c:pt>
<c:pt idx="3">
<c:v>900</c:v>
</c:pt>
<c:pt idx="4">
<c:v>800</c:v>
</c:pt>
</c:numCache>
</c:numRef>
</c:val>
...
</c:ser>
...
</c:barChart>
...
</c:plotArea>
...
</xml-fragment>
Now based on the above XML String we can use CT* classes and its various methods to traverse through the whole XML using Apache POI. Let's see how to read Chart Title, Labels(Employee names) and Series(Employee salaries) using POI:
Workbook workbook = new XSSFWorkbook(new File(PATH));
Sheet sheet = workbook.getSheet("GraphSheet");
XSSFSheet xsheet = (XSSFSheet) sheet;
XSSFDrawing drawing = xsheet.getDrawingPatriarch();
if (drawing != null) {
List<XSSFChart> charts = drawing.getCharts();
for (int chartIndex = 0; charts != null && chartIndex < (charts.size()); chartIndex++) {
XSSFChart chart = charts.get(chartIndex);
CTChart chart2 = chart.getCTChart();
CTPlotArea plot = chart2.getPlotArea();
System.out.println("Chart Title :" + chart2.getTitle().getTx().getRich().getPArray(0).getRArray(0).getT());
CTBarSer[] ctScaSerList = plot.getBarChartArray(0).getSerArray();
for (CTBarSer ctLineSer : ctScaSerList) {
CTStrVal[] ctStrVals = ctLineSer.getCat().getStrRef().getStrCache().getPtArray();
for (int i = 0; i < ctStrVals.length; i++) {
System.out.print(ctStrVals[i].getV() + ",");
}
System.out.println();
CTNumVal[] ctXNumVal = ctLineSer.getVal().getNumRef().getNumCache().getPtArray();
for (int i = 0; i < ctXNumVal.length; i++) {
System.out.print(ctXNumVal[i].getV() + ",");
}
}
}
}
Console:
Chart Title :Employee Salary
Tom,John,Harry,Sam,Richa,
1000,700,300,900,800,
Note: Here, the idea is to first read the XML String(because could be different based on your graph type) and then traverse the whole XML accordingly.
