Apache POI: clone tables containing charts
According to numerous sources, like the Limitations section on the official page , perhaps the only good way to work with Excel maps from POI is to use an Excel file with an existing chart as a template and change the original cells used by the chart. And it works great.
The problem is that we need to have not only one, but several (and we do not know how many at compile time) worksheets with the same graph, but with different (dynamically generated) data. Using cloneSheet (sheetNumber) is a way to duplicate the template worksheet. But if works fine only as long as the graphs are not cloned on the page.
When I try to clone a chart sheet I get:
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. The sheet data is cloned, but not the charts (they are not in the cloned sheet).
Has anyone succeeded in cloning a chart sheet? Or maybe someone has another idea how to solve the problem we have, i.e. Making excel charts for dynamic number of sheets with POI?
source to share
I ended up with a rather complex workaround:
- I am generating a large number of sheets (for example 1000) with a chart template using an Excel macro
- I enter data in as many sheets as I need with Apache POI (and manipulate named ranges that are used by charts)
- I rename as many sheets as I need with Apache POI so that the names I want (and therefore I am limited to HSSF because XSSF does not update references to name ranges in Excel after renaming the sheet :-()
- I am deleting the rest of the sheets using Apache POI
It was a major effort to implement this conceptually simple and common use case (such as exporting to Excel with charts), but at least it is possible and works reasonably well.
source to share