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
