Exporting Hierarchy of Work Items to Excel using Custom Templates
If you want to perform an Excel export from items that are parents or children of each other, form a hierarchy.
For example,
Then you want to get this result:
Codebeamer 7.10 or later
In Codebeamer 7.10 this is lot simpler then it was earlier, because of some helper classes were added which makes this task easier. This template contains all necessary code:excelWithHierarchy710.xlsx
The script pieces from this template:
The call to the ExcelHierarchyCellDecorator will add the necessary Excel indentation to the cell being exported:
<cb:groovy template="false" silent="false">
com.intland.codebeamer.text.excel.ExcelHierarchyCellDecorator.indentCell(cell, hierarchyProvider, item);
return item.name;
</cb:groovy>
The call in the C column: ${hierarchyProvider.getLevel(item)} returns the item's level/depth in the parent-child hierarchy.
The call in the D column: ${hierarchyProvider.getPath(item)} will print out the item's path in this hierarchy like 1.3.5.
Earlier Codebeamer versions
If you want to do an Excel export from items which are parent/child of each other so forming a hierarchy then you can use the attached Excel template as an example:excelWithHierarchy.xlsx
 | There is wan caveat: before Codebeamer 7.10 this export template cannot properly set the indentation using Excel's native indentation, and this indentation cannot be imported back to Codebeamer. To fix this you will need either Codebeamer 7.10, or must download and put ooxml-schemas.jar to the classpath manually |
The template is using a groovy script which figures out the depth of an exported item. The script provided below can be found in the Excel sheet:
<cb:groovy template="false" silent="false">
// the command contains exported items and their hierarchy as a map:
// this is a SortedMap of ReleaseId,TrackerItemDto
releaseIdToReq = exportCommand.getRequirements();
//find the ReleaseId contains the depth
releaseId = null;
releaseIdToReq.each({
if (it.value == item) { releaseId = it.key };
});
// the depth/level of the item can be get from the releaseId
depth = 0;
if (releaseId != null) {
depth = releaseId.getNumbers().size();
}
name = item.name;
try {
// indent the name with as many spaces as many the depth is
// caching style in StyleRegistry to avoid too many styles created
styleRegistry = com.intland.codebeamer.text.excel.StyleRegistry.getInstance(workbook);
styleName = "indented" + depth ;
indentedStyle = styleRegistry.get(styleName);
if (indentedStyle == null) {
workbook = cell.sheet.workbook;
indentedStyle = workbook.createCellStyle();
indentedStyle.cloneStyleFrom(cell.getCellStyle());
styleRegistry.put(styleName, indentedStyle);
// set indent on style
indentedStyle.indention = depth - 1;
}
cell.setCellStyle(indentedStyle);
} catch (Throwable th) {
//log.warn("You will need ooxml-schemas.jar on classpath to make the indentation correct", th);
// just indent with spaces
if (depth > 1) {
1.upto(depth) { name = " " + name; }
}
}
return name;
</cb:groovy>