You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Guillaume de GENTILE <gd...@finbox-solutions.com> on 2013/01/08 22:42:38 UTC
Solution to support Graph in XSSFWorkbook.cloneSheet method
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
Re: Solution to support Graph in XSSFWorkbook.cloneSheet method
Posted by Guillaume de GENTILE <gd...@finbox-solutions.com>.
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 <ye...@dinom.ru> 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 <
> 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
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Solution to support Graph in XSSFWorkbook.cloneSheet method
Posted by Yegor Kozlov <ye...@dinom.ru>.
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
>