Hi Yegor, ok, I will create a new bug in bugzilla and attach the solution to it (also with a JUnit test case).
I just need time knowing that I am working on it on my free time. regards, Guillaume Envoyé de mon iPhone Le 9 janv. 2013 à 08:12, Yegor Kozlov <[email protected]> a écrit : > Can you create a new bug in POI Bugzilla and attach the patch to it? This > is the POI way of submitting patches. > Also, can you write some unit tests that demonsrate that your fix works? > What does the current code do wrong when cloning sheets with charts? Can it > be expressed in terms of JUnit asserts? If the test requires a sample > .xlsx file then upload it too. > > Yegor > > On Wed, Jan 9, 2013 at 1:42 AM, Guillaume de GENTILE < > [email protected]> wrote: > >> Hi all, >> >> I have amended the POI api to support Graph while cloning sheet and also >> while renaming sheet. >> With current release it is not possible to rename a sheet which contains >> some graphs, and it is also not possible to copy graphs while cloning a >> sheet. >> >> Below the solution I have implemented (note that I am not experienced with >> the POI api, so it might be possible to perform the same operation in a >> more official way). >> >> I hope it will be usefull... >> >> I have amended the XSSFWorkbook.cloneSheet method as below: >> >> /** >> * 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 >> int chartNumber = >> getPackagePart().getPackage().getPartsByContentType(XSSFRelation.CHART.getContentType()).size() >> + 1; >> XSSFChart c = (XSSFChart) >> clonedDg.createRelationship(XSSFRelation.CHART, XSSFFactory.getInstance(), >> chartNumber); >> >> // Instantiate new XmlNodeUtils >> XmlNodeUtils nodeUtils = new XmlNodeUtils(this); >> int clonedSheetNum = >> this.getSheetIndex(clonedSheet); >> >> // duplicate source CTChart >> // the new CTChart is still referencing the source >> sheet! >> CTChart ctc = (CTChart) chart.getCTChart().copy(); >> Node node = ctc.getPlotArea().getDomNode(); >> nodeUtils.updateDomDocSheetReference(node, >> sheetNum, clonedSheetNum); >> c.getCTChart().set(ctc); >> >> // duplicate source CTChartSpace >> // the new CTChartSpace is still referencing the >> source sheet! >> CTChartSpace ctcs = (CTChartSpace) >> chart.getCTChartSpace().copy(); >> node = ctcs.getDomNode(); >> nodeUtils.updateDomDocSheetReference(node, >> sheetNum, clonedSheetNum); >> c.getCTChartSpace().set(ctcs); >> >> // create new relation for the new chart >> 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; >> } >> >> >> I have also amended the method XSSFWorkbook.setSheetName as below: >> >> /** >> * Set the sheet name. >> * >> * @param sheetIndex sheet number (0 based) >> * @param sheetname the new sheet name >> * @throws IllegalArgumentException if the name is null or invalid >> * or workbook already contains a sheet with this name >> * @see #createSheet(String) >> * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String >> nameProposal) >> */ >> public void setSheetName(int sheetIndex, String sheetname) { >> validateSheetIndex(sheetIndex); >> >> // YK: Mimic Excel and silently truncate sheet names longer than >> 31 characters >> if(sheetname != null && sheetname.length() > 31) sheetname = >> sheetname.substring(0, 31); >> WorkbookUtil.validateSheetName(sheetname); >> >> if (containsSheet(sheetname, sheetIndex )) >> throw new IllegalArgumentException( "The workbook already >> contains a sheet of this name" ); >> >> XmlNodeUtils xmlUtils = new XmlNodeUtils(this); >> xmlUtils.updateRelationsSheetName(sheetIndex, sheetname); >> >> XSSFFormulaUtils utils = new XSSFFormulaUtils(this); >> utils.updateSheetName(sheetIndex, sheetname); >> >> workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname); >> } >> >> >> >> And created a new class XmlNodeUtils : >> >> package proposal.org.apache.poi.ss.util; >> import java.util.Iterator; >> import java.util.List; >> import org.apache.poi.POIXMLDocumentPart; >> import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet; >> import org.apache.poi.ss.formula.FormulaParser; >> import org.apache.poi.ss.formula.FormulaRenderer; >> import org.apache.poi.ss.formula.FormulaRenderingWorkbook; >> import org.apache.poi.ss.formula.FormulaType; >> import org.apache.poi.ss.formula.ptg.NamePtg; >> import org.apache.poi.ss.formula.ptg.NameXPtg; >> import org.apache.poi.ss.formula.ptg.Ptg; >> import org.apache.poi.xssf.usermodel.XSSFChart; >> import org.apache.poi.xssf.usermodel.XSSFDrawing; >> import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; >> import org.apache.poi.xssf.usermodel.XSSFName; >> import org.apache.poi.xssf.usermodel.XSSFWorkbook; >> import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart; >> import org.w3c.dom.Node; >> import org.w3c.dom.NodeList; >> >> /** >> * @author Guillaume de GENTILE (gentile_g at yahoo dot com) >> * >> */ >> public class XmlNodeUtils { >> private final XSSFWorkbook _wb; >> private final XSSFEvaluationWorkbook _fpwb; >> >> public XmlNodeUtils(XSSFWorkbook wb) { >> _wb = wb; >> _fpwb = XSSFEvaluationWorkbook.create(_wb); >> } >> >> >> public void updateRelationsSheetName(final int sheetIndex, final >> String sheetname) { >> String oldSheetName = _wb.getSheetName(sheetIndex); >> >> /** >> * An instance of FormulaRenderingWorkbook that returns >> */ >> FormulaRenderingWorkbook frwb = new FormulaRenderingWorkbook() { >> >> public ExternalSheet getExternalSheet(int externSheetIndex) { >> return _fpwb.getExternalSheet(externSheetIndex); >> } >> >> public String getSheetNameByExternSheet(int externSheetIndex) { >> if (externSheetIndex == sheetIndex) return sheetname; >> else return >> _fpwb.getSheetNameByExternSheet(externSheetIndex); >> } >> >> public String resolveNameXText(NameXPtg nameXPtg) { >> return _fpwb.resolveNameXText(nameXPtg); >> } >> >> public String getNameText(NamePtg namePtg) { >> return _fpwb.getNameText(namePtg); >> } >> }; >> >> // update charts >> List<POIXMLDocumentPart> rels = >> _wb.getSheetAt(sheetIndex).getRelations(); >> >> // if the sheet being cloned has a drawing then update it >> 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; >> >> Iterator<XSSFChart> it = dg.getCharts().iterator(); >> while(it.hasNext()) { >> XSSFChart chart = it.next(); >> //System.out.println("chart = " + chart); >> CTChart c = chart.getCTChart(); >> >> Node node1 = chart.getCTChart().getDomNode(); >> updateDomDocSheetReference(node1, frwb, oldSheetName); >> >> Node node2 = chart.getCTChartSpace().getDomNode(); >> updateDomDocSheetReference(node2, frwb, oldSheetName); >> >> } >> continue; >> } >> } >> } >> >> /** >> * Update sheet name in all formulas and named ranges. >> * <p/> >> * <p> >> * The idea is to parse every formula and render it back to string >> * with the updated sheet name. >> * </p> >> * >> * @param rootNode root node of the XML document >> * @param sourceSheetIndex the source sheet index >> * @param targetSheetIndex the target sheet index >> */ >> public void updateDomDocSheetReference(Node rootNode, final int >> sourceSheetIndex, final int targetSheetIndex) { >> final String name = _wb.getSheetName(targetSheetIndex); >> /** >> * An instance of FormulaRenderingWorkbook that returns >> */ >> FormulaRenderingWorkbook frwb = new FormulaRenderingWorkbook() { >> >> public ExternalSheet getExternalSheet(int externSheetIndex) { >> return _fpwb.getExternalSheet(externSheetIndex); >> } >> >> public String getSheetNameByExternSheet(int externSheetIndex) { >> if (externSheetIndex == sourceSheetIndex) return name; >> else return >> _fpwb.getSheetNameByExternSheet(externSheetIndex); >> } >> >> public String resolveNameXText(NameXPtg nameXPtg) { >> return _fpwb.resolveNameXText(nameXPtg); >> } >> >> public String getNameText(NamePtg namePtg) { >> return _fpwb.getNameText(namePtg); >> } >> }; >> >> String oldName = _wb.getSheetName(sourceSheetIndex); >> updateDomDocSheetReference(rootNode, frwb, oldName); >> } >> >> private void updateDomDocSheetReference(Node rootNode, >> FormulaRenderingWorkbook frwb, String oldName) { >> String value = rootNode.getNodeValue(); >> //System.out.println(" " + rootNode.getNodeName() + " -> " + >> rootNode.getNodeValue()); >> if(value!=null) { >> if(value.contains(oldName)) { >> XSSFName name1 = _wb.createName(); >> name1.setRefersToFormula(value); >> updateName(name1, frwb); >> rootNode.setNodeValue(name1.getRefersToFormula()); >> _wb.removeName(name1.getNameName()); >> } >> } >> NodeList nl = rootNode.getChildNodes(); >> for (int i = 0; i < nl.getLength(); i++) { >> updateDomDocSheetReference(nl.item(i), frwb, oldName); >> } >> } >> >> /** >> * Parse formula in the named range and re-assemble it back using the >> specified FormulaRenderingWorkbook. >> * >> * @param name the name to update >> * @param frwb the formula rendering workbook that returns new sheet >> name >> */ >> private void updateName(XSSFName name, FormulaRenderingWorkbook frwb) { >> String formula = name.getRefersToFormula(); >> if (formula != null) { >> int sheetIndex = name.getSheetIndex(); >> Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, >> FormulaType.NAMEDRANGE, sheetIndex); >> String updatedFormula = FormulaRenderer.toFormulaString(frwb, >> ptgs); >> if (!formula.equals(updatedFormula)) >> name.setRefersToFormula(updatedFormula); >> } >> } >> >> public void printNode(Node rootNode, String spacer) { >> System.out.println(spacer + rootNode.getNodeName() + " -> " + >> rootNode.getNodeValue()); >> NodeList nl = rootNode.getChildNodes(); >> for (int i = 0; i < nl.getLength(); i++) >> printNode(nl.item(i), spacer + " "); >> } >> } >> >> >> >> >> Regards, >> Guillaume >> >> >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
