How to get chart info from an Excel spreadsheet using Apache POI?

Question

Is it possible to extract chart information from an Office 2007 (xlsx / OpenXML) spreadsheet using Apache POI? I've managed to read in the spreadsheet and even get the part that refers to the chart but not sure how I can retrieve any info from this part e.g. Type of chart, chart data etc.

XSSFWorkbook xwb = new XSSFWorkbook("charts_lines.xlsx");

XSSFSheet sheet = xwb.getSheetAt(0);

I can also iterate through the package parts to retrieve the chart part, but I don't see how I then go on to retrieve any info about the chart?

Note, I'm not interested in creating charts using POI, just read as much chart info as is possible to do...I'm also not saving an xlsx. I simply wish to extract line colours, labels, data, chart type (pie, line, bar etc.)

Answer 1

There isn't a high level representation at the moment, so you'll need to drop down into the xmlbeans level and work with the low level CT* objects.

For Chart Sheets, there's XSSFChartSheet which will give you a CTChartsheet object, which has a little bit of info.

For both XSSFChart and XSSFChartSheet (regular and chart sheets), you'll need to go via the drawings to get the charts. Each sheet with charts on it should have one Drawing, and the charts get linked from the drawing, rather than the sheet itself.

As of r1090442 (so POI 3.8 or newer), there's a method on XSSFDrawing to give you all the XSSFChart objects (which are wrappers around the /charts/chart#.xml part). If you're on a really really old version of POI, use the CTDrawing to get the details of the chart, grab the /charts/chart#.xml part that corresponts, and then have xmlbeans give you the CT objects for it. Either way that'll let you get the titles, types, data ranges etc.

It is a bit fiddly though, so do please consider sending in a patch to POI if you get something good worked out for working with the CTChart objects!

Answer 2

you can read chart data as XML using XSSFDrawing

like

 XSSFDrawing drawing = ((XSSFSheet)sheet).createDrawingPatriarch();
        System.out.println(drawing.getCTDrawing().toString());

will print whole chart as XMl and also using

drawing.getCharts();

you can add Iterator to it to browse chart