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

Question

Can I extract chart data from an Office 2007 spreadsheet (xlsx / OpenXML) using Apache POI? I managed to read in the spreadsheet and even get the part related to the diagram, but I'm not sure how I can get any information from this part, for example. Type of chart, chart data, etc.

XSSFWorkbook xwb = new XSSFWorkbook("charts_lines.xlsx"); XSSFSheet sheet = xwb.getSheetAt(0); 

I can also iterate over the parts of the package to extract parts of the chart, but I don’t see how I then continue to get any information about the chart?

Please note: I am not interested in creating diagrams using POI, just read as much information about diagrams as possible ... I also do not save xlsx. I just want to extract line colors, labels, data, chart type (pie, line, bar, etc.).

Answer 1

There is currently no high-level view, so you need to go down to the xmlbeans level and work with low-level CT * objects.

The chart tables have an XSSFChartSheet that will give you a CTChartsheet object that has some information.

For XSSFChart and XSSFChartSheet (Plain and Chart Sheets), you will need to view the drawings to get charts. Each sheet with diagrams on it should have one drawing, and diagrams are attached to the drawing, and not to the sheet itself.

As with r1090442 (therefore POI 3.8 or later), there is an XSSFDrawing method to provide you with all of the XSSFChart objects (which are wrappers around the / charts / chart # .xml part). If you are really a really old version of POI, use CTDrawing to get the details of the chart, take the / charts / chart # .xml part that matches, and then xmlbeans will provide you with CT objects for it. In any case, this will allow you to get headers, types, data ranges, etc.

This is a bit strange, so please consider sending a patch to the POI if you get something good for working with CTChart objects!

Answer 2

you can read chart data in XML format using XSSFDrawing

like

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

print the whole chart as XMl , and also using

 drawing.getCharts(); 

you can add an iterator to it to view the chart

Answer 3

I don’t know the exact answer to your question, but the OpenXML SDK 2.0 comes with the DocumentReflector.exe tool, which will show you exactly how the chart is determined (including all the relationships between the SpreadsheetML and DrawingML packages). For more information about this tool, see this article .