poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yegor Kozlov <yegor.koz...@dinom.ru>
Subject Re: Solution to support Graph in XSSFWorkbook.cloneSheet method
Date Wed, 09 Jan 2013 07:12:19 GMT
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 <
gdegentile@finbox-solutions.com> 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: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message