Apache的POI：包含图表工作表克隆 [英] Apache POI: cloning worksheets containing charts

问题描述

据许多来源，官方页面例如限制部分，可能是唯一的好办法从POI Excel图表的工作是使用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.

问题是，我们需要有不只有一个，但多（我们不知道在编译时有多少）与同一个图表，但是不同的（动态生成的）数据的工作表。使用 cloneSheet（工作表NUMBER）是duplify模板工作表的方法。但如果细只有等到图表的作品都在表被克隆。

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

更新：

我切换到XSSF，现在至少没有运行时异常。表数据被克隆而不是图表（他们是不是在克隆表present）。

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

有没有人成功克隆与图（S）工作表？或者，也许有人已经其他的想法如何解决，我们有一个问题，即发电擅长与POI工作表的动态的图表？

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?

推荐答案

我结束了相当复杂的解决方法：

I ended up with quite complex workaround:

1. 我通过生成Excel宏
大数目与模板图表工作表（例如1000）

2. 我输入的数据为多张，因为我需要与Apache POI（以及由图表使用的命名范围操作）


3. 我改名为许多张，因为我与Apache POI需要，我需要（因此我仅限于HSSF，因为XSSF不重命名表后:-(更新Excel命名范围引用）
   
4. 我删除表的其余部分与Apache POI

1. I generate big number of sheets (for example 1000) with template chart by Excel macro
2. I enter data to as many sheets as I need with Apache POI ( and manipulate with named ranges that are used by charts)
3. 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 :-( )
4. I delete the rest of sheets with Apache POI

这是一个很大的努力来实现这个concepticaly简单而常见的情况（即导出到Excel使用图表），但至少这是可能的，它的工作相当不错。

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

这篇关于Apache的POI：包含图表工作表克隆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！