Java 使用 Apache POI 创建 Excel 图表

Create excel chart using Apache POI

提问by javdev

I need to create excel sheet from my Java code which contains charts like Bar chart, Line Chart etc using the Apache POI library. Is it possible? I am not able to find any useful code example for the same.

我需要使用 Apache POI 库从包含条形图、折线图等图表的 Java 代码创建 Excel 表格。是否可以？我找不到任何有用的代码示例。

Is there any other alternative for this apart from POI library for Java?

除了用于 Java 的 POI 库之外，还有其他选择吗？

采纳答案by liya

You can only use Excel template to preserve chart with POI. It mean you create an Excel template file with chart in it and link the chart's datasource to one definedName,then you can load the template file from POI and using code to change the definedName. Current POI does not support to create Chart from scratch.

您只能使用 Excel 模板来保存带有 POI 的图表。这意味着您创建一个带有图表的 Excel 模板文件并将图表的数据源链接到一个定义的名称，然后您可以从 POI 加载模板文件并使用代码更改定义的名称。当前 POI 不支持从头开始创建图表。

回答by DJClayworth

This will be extremely complicated to do from scratch, as you will have to figure out what needs to go in the Excel file to create the charts. I would go a different route.

从头开始执行此操作将非常复杂，因为您必须弄清楚 Excel 文件中需要放入哪些内容才能创建图表。我会走不同的路线。

Create an Excel file that includes a macro that creates the Bar Chart etc for some data (using Excel in the usual way). Then use Apache POI to create a file with the data you want and start Excel from Java, executing the macro that creates the charts.

创建一个 Excel 文件，其中包含为某些数据创建条形图等的宏（以通常的方式使用 Excel）。然后使用 Apache POI 创建包含所需数据的文件，并从 Java 启动 Excel，执行创建图表的宏。

回答by Adrian A.

Is there any other alternative for this apart from POI library for Java?

除了用于 Java 的 POI 库之外，还有其他选择吗？

There are a few other libraries too, but I'm not sure if the can write such a thing like a chart.

还有一些其他库，但我不确定它们是否可以编写图表之类的东西。

If you can use the newer Office versions (the ones that use the XML based format files), than you could use a different approach:

如果您可以使用较新的 Office 版本（使用基于 XML 的格式文件的版本），那么您可以使用不同的方法：

• create a an Excel file with a newer version of Office, containing the charts you need, and some dummy data.
• unzip that excel file and extract the XML files from inside. Of most interest will be the files xl\worksheets\sheet1.xml xl\worksheets\sheet2.xml or xl\worksheets\sheet3.xml (depending on what sheets were used)
• Take a look at the file format (it's more complicated than using POI), but it shouldn't be that hard to identify the "dummy" data you entered before.
• Use Velocity or FreeMarker to transform that xml file into a template (by replacing your dummy data with variables and macros what would produce the same result)
• Write a small program (just a few lines) that takes you real data, merges with the template, and packs everything back in a zip, but puts the *.xlsx extension.

• 使用较新版本的 Office 创建一个 Excel 文件，其中包含您需要的图表和一些虚拟数据。
• 解压缩该 excel 文件并从内部提取 XML 文件。最感兴趣的是文件 xl\worksheets\sheet1.xml xl\worksheets\sheet2.xml 或 xl\worksheets\sheet3.xml（取决于使用的工作表）
• 看看文件格式（它比使用 POI 更复杂），但识别您之前输入的“虚拟”数据应该不难。
• 使用 Velocity 或 FreeMarker 将该 xml 文件转换为模板（通过用变量和宏替换您的虚拟数据会产生相同的结果）
• 编写一个小程序（只有几行），它为您提供真实数据，与模板合并，并将所有内容重新打包到 zip 中，但将扩展名放在 *.xlsx 中。

I know that the above steps look a little complicated, but if you don't have too complicated Excel files, it should be easier than it looks.

我知道上面的步骤看起来有点复杂，但是如果你没有太复杂的Excel文件，应该比看起来容易。

回答by Karthic Raghupathi

The thread looks old and I do not know if you have already figured out a way or are still looking for one.

该线程看起来很旧，我不知道您是否已经找到了一种方法或仍在寻找一种方法。

But here is what I would do. There is a free library called JFreeChart. You can use that to generate either a JPG or PNG file which you can then insert into excel file when you create it with Apache POI.

但这是我要做的。有一个名为JFreeChart的免费库。您可以使用它来生成 JPG 或 PNG 文件，然后您可以在使用 Apache POI 创建它时将其插入到 excel 文件中。

But the disadvantage with this method is that the data in the graph will not change dynamically when you change the data in the spreadsheet as is the case with Excel. Now that is something I cannot live with.

但这种方法的缺点是，当您更改电子表格中的数据时，图表中的数据不会像 Excel 那样动态更改。现在这是我无法忍受的。

So I'm going to do some research now. I'm pretty sure that since the question has been asked there must be an addition of a feature in Apache POI or another elegant way of doing things. If I find any I will be sure to post my findings here.

所以我现在要做一些研究。我很确定，既然已经提出了这个问题，那么 Apache POI 或其他优雅的做事方式中肯定会增加一个功能。如果我找到任何我一定会在这里张贴我的发现。

---- UPDATE ----

- - 更新 - -

In my research lasting for about an hour, I could only find one suitable library called SmartXLS (please google it, I could not post the link because i'm a newbie and the spam prevention mechanism kicked in)that is remotely close to what I would use. You can generate both the excel and the chart via a program. The website is very simple and I could not find any licensing information so I'm assuming it is free for personal and commercial use. I was able to download the library without any problem. I have not used it yet. Give it a shot and let us know how it works out.

在我持续大约一个小时的研究中，我只能找到一个名为SmartXLS 的合适库（请用谷歌搜索它，我无法发布链接，因为我是一个新手，而且垃圾邮件预防机制已经启动），它与我所使用的库非常接近会用。您可以通过程序生成excel和图表。该网站非常简单，我找不到任何许可信息，因此我假设它可免费用于个人和商业用途。我能够毫无问题地下载库。我还没有使用它。试一试，让我们知道它是如何工作的。

回答by prule

In poi-3.8 support for charts seems to be coming.

在 poi-3.8 中，对图表的支持似乎即将到来。

See this discussion.

请参阅此讨论。

And in particular, the example.

尤其是这个例子。

回答by Sumant

Here is the working example.

这是工作示例。

• I have data in JSON, and .xlsm template file with macro. - no chart, data or range name needed in excel

• Clones the template sheet every time it need to create a sheet with chart, places a required chart type at z1 and change default line chart which was inserted to appropriate chart type. Removes chart type from z1 to avoid multiple execution.

• 我有 JSON 格式的数据和带有宏的 .xlsm 模板文件。- Excel 中不需要图表、数据或范围名称

• 每次需要创建带有图表的工作表时克隆模板工作表，将所需的图表类型放置在 z1 并将插入的默认折线图更改为适当的图表类型。从 z1 中删除图表类型以避免多次执行。

Hope it helps.

希望能帮助到你。