如何使用 Apache POI 从 Excel 电子表格中获取图表信息? [英] How to get chart info from an Excel spreadsheet using Apache POI?

问题描述

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

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?

请注意，我对使用 POI 创建图表不感兴趣，只是阅读尽可能多的图表信息……我也不保存 xlsx.我只想提取线条颜色、标签、数据、图表类型(饼图、线条、条形等)

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

推荐答案

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

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.

对于 Chart Sheets，有 XSSFChartSheet 它会给你一个 CTChartsheet 对象，它有一些信息.

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

对于 XSSFChart 和 XSSFChartSheet(常规和图表表格)，您需要通过图纸来获取图表.每张带有图表的工作表都应该有一个绘图，并且图表是从绘图链接的，而不是工作表本身.

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.

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

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.

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

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!

这篇关于如何使用 Apache POI 从 Excel 电子表格中获取图表信息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！