Exporting work items to with their parent-child hierarchy

If you want to do an Excel export from items which are parent/child of each other so forming a hierarchy.

So you have this work item hierarchy:

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

The template is using a groovy script which figures out the the depth of an exported item. The script can be found in the Excel sheet, and I'm adding it here too:

<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>