A hint for Graph support while cloning a sheet

[Guillaume de GENTILE <gd...@finbox-solutions.com>]

Hi all,

While cloning a sheet (workbook.cloneSheet(indexSource)), the graphs 
from the source sheet are not duplicated in the target sheet and the 
resulting report is corrupted (error message while openning the Excel 
document).

I have amended the cloneSheet method to duplicate the graph from the 
source system (The amended code is in RED color).
However I am new with the pio API and don't really know how to handle 
objects (I don't get a clear picture of the code yet).
The target sheet contains a copy of the source graphs, however the 
graphs are still referencing the source sheet.
This code should be amended so the new graphs reference the new sheet.
It would be great to have this funtionality in the next release (if 
somebody knows how to do it...)

     /**
      * Create an XSSFSheet from an existing sheet in the XSSFWorkbook.
      *  The cloned sheet is a deep copy of the original.
      *
      * @return XSSFSheet representing the cloned sheet.
      * @throws IllegalArgumentException if the sheet index in invalid
      * @throws POIXMLException if there were errors when cloning
      */
     public XSSFSheet cloneSheet(int sheetNum) {
         validateSheetIndex(sheetNum);

         XSSFSheet srcSheet = sheets.get(sheetNum);
         String srcName = srcSheet.getSheetName();
         String clonedName = getUniqueSheetName(srcName);

         XSSFSheet clonedSheet = createSheet(clonedName);
         try {
             ByteArrayOutputStream out = new ByteArrayOutputStream();
             srcSheet.write(out);
             clonedSheet.read(new ByteArrayInputStream(out.toByteArray()));
         } catch (IOException e){
             throw new POIXMLException("Failed to clone sheet", e);
         }
         CTWorksheet ct = clonedSheet.getCTWorksheet();
         if(ct.isSetLegacyDrawing()) {
             logger.log(POILogger.WARN, "Cloning sheets with comments is 
not yet supported.");
             ct.unsetLegacyDrawing();
         }
         if (ct.isSetPageSetup()) {
             logger.log(POILogger.WARN, "Cloning sheets with page setup 
is not yet supported.");
             ct.unsetPageSetup();
         }

         clonedSheet.setSelected(false);

         // copy sheet's relations
         List<POIXMLDocumentPart> rels = srcSheet.getRelations();
         // if the sheet being cloned has a drawing then remember it and 
re-create tpoo
         XSSFDrawing dg = null;
         for(POIXMLDocumentPart r : rels) {
             // do not copy the drawing relationship, it will be re-created
             if(r instanceof XSSFDrawing) {
                 dg = (XSSFDrawing)r;
                 continue;
             }

             PackageRelationship rel = r.getPackageRelationship();
             clonedSheet.getPackagePart().addRelationship(
                     rel.getTargetURI(), rel.getTargetMode(), 
rel.getRelationshipType());
             clonedSheet.addRelation(rel.getId(), r);
         }

         // clone the sheet drawing alongs with its relationships
         if (dg != null) {
             if(ct.isSetDrawing()) {
                 // unset the existing reference to the drawing,
                 // so that subsequent call of 
clonedSheet.createDrawingPatriarch() will create a new one
                 ct.unsetDrawing();
             }
             XSSFDrawing clonedDg = clonedSheet.createDrawingPatriarch();
             // copy drawing contents
             clonedDg.getCTDrawing().set(dg.getCTDrawing());

             // Clone drawing relations
             List<POIXMLDocumentPart> srcRels = 
srcSheet.createDrawingPatriarch().getRelations();
             for (POIXMLDocumentPart rel : srcRels) {
                 if(rel instanceof XSSFChart) {
                     XSSFChart chart = (XSSFChart) rel;
                     try {

                         // create new chart from source Chart
                         int chartNumber = 
getPackagePart().getPackage().getPartsByContentType(XSSFRelation.CHART.getContentType()).size() 
+ 1;
                         XSSFChart c = (XSSFChart) 
clonedDg.createRelationship(XSSFRelation.CHART, 
XSSFFactory.getInstance(), chartNumber);
                         c.getCTChart().set(chart.getCTChart());
c.getCTChartSpace().set(chart.getCTChartSpace());

                         // the new chart is still referencing the 
source sheet!

                         PackageRelationship relation = 
c.getPackageRelationship();
clonedDg.getPackagePart().addRelationship(relation.getTargetURI(), 
relation.getTargetMode(),
                                 relation.getRelationshipType(), 
relation.getId());

                     } catch (Exception e) {
                         // TODO Auto-generated catch block
                         e.printStackTrace();
                     }
                 } else {
                     PackageRelationship relation = 
rel.getPackageRelationship();
                     clonedSheet
                             .createDrawingPatriarch()
                             .getPackagePart()
.addRelationship(relation.getTargetURI(), relation.getTargetMode(),
relation.getRelationshipType(), relation.getId());
                 }
             }
         }
         return clonedSheet;
     }


Thanks,
Guillaume