Apache POI：克隆包含图表的工作表(Apache POI: cloning worksheets containing charts)

Apache POI：克隆包含图表的工作表(Apache POI: cloning worksheets containing charts)

根据众多消息来源，例如官方页面上的限制部分 ，可能使用POI的Excel图表的唯一好方法是使用现有图表作为模板的Excel文件并修改图表使用的源单元格。 而且效果很好。

问题是我们不仅需要一个而且需要多个（我们不知道在编译时有多少）具有相同图表但不同（动态生成）数据的工作表。 使用cloneSheet（sheetNumber）是一种复制模板工作表的方法。 但是，如果工作正常，直到图表被克隆。

当我尝试克隆带有图表的工作表时，我得到：

Exception in thread "main" java.lang.RuntimeException: The class org.apache.poi.hssf.record.chart.ChartFRTInfoRecord needs to define a clone method at org.apache.poi.hssf.record.Record.clone(Record.java:71) at org.apache.poi.hssf.model.InternalSheet.cloneSheet(InternalSheet.java:388) at org.apache.poi.hssf.usermodel.HSSFSheet.cloneSheet(HSSFSheet.java:125) at org.apache.poi.hssf.usermodel.HSSFWorkbook.cloneSheet(HSSFWorkbook.java:652)

更新：

我切换到XSSF，现在至少没有运行时异常。 工作表数据是克隆的，但不是图表（它们不存在于克隆表中）。

有没有人成功克隆带有图表的工作表？ 或者也许有人有其他想法如何解决我们遇到的问题，即为POI生成动态数量的工作表的excel图表？

According to numerous sources, for example Limitations section on official page, probably the only good way to work with excel charts from POI is using Excel file with existing chart as a template and modify source cells used by chart. And it works great.

The problem is that we need to have not only one but multiple (and we don't know how many at compile time) worksheets with the same chart but different (dynamically generated) data. Using cloneSheet(sheetNumber) is a way to duplicate a template worksheet. But if works fine only until charts are on sheet being cloned.

When I try to clone a sheet with a chart I'm getting:

Exception in thread "main" java.lang.RuntimeException: The class org.apache.poi.hssf.record.chart.ChartFRTInfoRecord needs to define a clone method at org.apache.poi.hssf.record.Record.clone(Record.java:71) at org.apache.poi.hssf.model.InternalSheet.cloneSheet(InternalSheet.java:388) at org.apache.poi.hssf.usermodel.HSSFSheet.cloneSheet(HSSFSheet.java:125) at org.apache.poi.hssf.usermodel.HSSFWorkbook.cloneSheet(HSSFWorkbook.java:652)

UPDATE:

I switched to XSSF and now at least there is no runtime exception. Sheet data is cloned but not charts (they are not present in cloned sheet).

Did anyone succeed to clone a worksheet with chart(s)? Or maybe someone has other idea how to solve a problem that we have, i.e. generating excel charts for dynamic number of worksheets with POI?

最满意答案

我最终得到了相当复杂的解决方法：

我使用Excel宏生成模板图表的大量工作表（例如1000） 我使用Apache POI将数据输入到我需要的多个工作表（并使用图表使用的命名范围进行操作） 我使用Apache POI将所需的页面重命名为我需要的名称（因此我仅限于HSSF，因为在重命名工作表后XSSF不会更新Excel中的命名范围引用:-(） 我用Apache POI删除了其余的工作表

实现这个概念上简单和常见的用例（即使用图表导出到Excel）是一项认真的努力，但至少它是可能的并且它运行得很好。

I ended up with quite complex workaround:

I generate big number of sheets (for example 1000) with template chart by Excel macro I enter data to as many sheets as I need with Apache POI ( and manipulate with named ranges that are used by charts) I rename as many sheets as I need with Apache POI to names that I need (and hence I'm limited to HSSF, because XSSF doesn't update named ranges references in Excel after renaming sheet :-( ) I delete the rest of sheets with Apache POI

It was a serious effort to implement this conceptually simple and common use case (i.e. exporting to Excel with charts) but at least it's possible and it's working quite well.