Search code examples
javaapache-poijett

Jett : Excel Templating in Java : cloning sheets that contain charts

I posted this on the Jett user mailing list but no response in over a month.

Here is the question

I see that on your site you have: Beans on a Per-Sheet Basis/Cloning Sheets

http://jett.sourceforge.net/transformation/sheet_specific_beans.html

If a template sheet contains an Excel chart, when Jett clones that template sheet, does it fully copy all contents on the sheet, including the chart?

Solution

  • JETT relies on Apache POI to perform all spreadsheet manipulations. To copy the template sheet, it calls Workbook's cloneSheet method. That method appears to copy everything the best it can, but it does not copy charts well at all. I tested using "sheet specific beans" with a chart on the sheet to clone in the template sheet. I tested both .xls and .xlsx templates, and I tried Apache POI 3.9, 3.10, and 3.11. I used the latest version of JETT, which as of this writing is 0.9.0.

    The results were the same regardless of the version of Apache POI used. The results differed depending on whether .xls (HSSF) or .xlsx (XSSF) was used, but either way the results were not good.

    • .xls Transformation:

    When the cloneSheet method is called, I get a RuntimeException coming from Apache POI: 

    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:73)
at org.apache.poi.hssf.model.InternalSheet.cloneSheet(InternalSheet.java:418)
at org.apache.poi.hssf.usermodel.HSSFSheet.cloneSheet(HSSFSheet.java:142)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.cloneSheet(HSSFWorkbook.java:749)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.cloneSheet(HSSFWorkbook.java:100)
at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:549)

    This exception prevents the generation of the resultant spreadsheet.

    • .xlsx Transformation:

    The call to cloneSheet succeeds, but the resultant spreadsheet gets corrupted. When I open the spreadsheet in Excel, it complains about "unreadable content", and it removes the chart objects from each cloned sheet, except for the original sheet. If the original sheet's name doesn't change, then that chart survives intact. However, if the original template sheet's name does change, then the chart's series references aren't changed and are lost.

    Unfortunately, JETT can’t do anything about this; it's an Apache POI problem. When Apache POI creates API support for charts, hopefully this would improve. Feel free to add a new bug in Apache POI's bug database.