如何使用Apache POI从Excel电子表格中获取图表信息？

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.

是否可以使用Apache POI从Office 2007（xlsx / OpenXML）电子表格中提取图表信息？我已经设法在电子表格中阅读，甚至获得了引用图表的部分，但不确定如何从该部分检索任何信息，例如图表类型，图表数据等

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.)

注意，我对使用POI创建图表不感兴趣，只读取尽可能多的图表信息...我也没有保存xlsx。我只想提取线条颜色，标签，数据，图表类型（饼图，线条，条形图等）

3 个解决方案

#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.

目前没有高级别的表示，因此您需要下载到xmlbeans级别并使用低级CT *对象。

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

对于图表，有XSSFChartSheet，它将为您提供一个CTChartsheet对象，它有一些信息。

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.

对于XSSFChart和XSSFChartSheet（常规表和图表），您需要通过图纸来获取图表。每张带有图表的图表都应该有一个图纸，图表会从图纸而不是图纸本身链接。

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.

从r1090442开始（所以POI 3.8或更新版本），XSSFDrawing上有一个方法可以为您提供所有XSSFChart对象（它们是/charts/chart#.xml部分的包装器）。如果您使用的是非常旧版本的POI，请使用CTDrawing获取图表的详细信息，获取对应的/charts/chart#.xml部分，然后让xmlbeans为其提供CT对象。这两种方式都可以让你获得标题，类型，数据范围等。

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!

虽然它有点繁琐，所以如果你得到一些用于处理CTChart对象的好东西，请考虑发送补丁到POI！

#2

you can read chart data as XML using XSSFDrawing

您可以使用XSSFDrawing将图表数据读取为XML

喜欢

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

will print whole chart as XMl and also using

将整个图表打印为XMl并使用

drawing.getCharts();

you can add Iterator to it to browse chart

你可以添加Iterator来浏览图表

#3

I don't know the exact answer to your question, but the OpenXML SDK 2.0 comes with a DocumentReflector.exe tool that will show you exactly how the chart is defined (including all relationships between the SpreadsheetML and the DrawingML packages). There is some more info on this tool in this article.

我不知道您的问题的确切答案，但OpenXML SDK 2.0附带了一个DocumentReflector.exe工具，它将准确显示图表的定义方式（包括SpreadsheetML和DrawingML包之间的所有关系）。本文中有关于此工具的更多信息。

#1