Basics

The DTM (and all) excel exports are written in a markdown language called Java Excel Template Translator (JETT).

Fundamentals

The most fundamental keys to understanding and the most important takeaway from the DTM export file are that:

1. The groovy script collects and defines the data.
2. The contents of the Excel file govern how the data is displayed.

Therefore, when exporting, the groovy script "runs first" and the data it collects and defines is then placed in the resulting file by the templating language.

As such, it doesn't actually matter what cell the script is placed into (unless you're return-ing data to the cell). For example, currently the script return-s an empty string viz. return ""—the last line—leading to an empty cell. Theoretically, you could return "hello world" and populate the cell thusly.

Codebeamer "primitives"

There is a list of pre-defined variables ("primitives") from Codebeamer's export context that are given by Codebeamer for free. The ones that we make use of in the groovy script are:

• items
  • List of tracker items in context
• fields
  • Field accessor for custom fields
• issueReferences
  • Access to incoming/outgoing references
• user
  • The currently authenticated user who initiated the export

Tips & Tricks

1. Use the Swagger API

The swagger api is located at https://<CODEBEAMER_BASE_URL>/cb/v3/swagger/editor.spr. If you're ever stuck about variable names or properties, you can find them there.

2. Red Herrings

While editing the groovy script, if it contains syntax error or unhandled exception, it will abort—leading to the variables being undefined when the template begins render. The following error will be displayed during export:

Unexpected error:Null value or expected variable missing in expression "${unMapSize.get(item.id)}". inside tag "span" (net.sf.jett.tag.SpanTag), at Sheet1!B9 (originally at Sheet1!B9) inside tag "forEach" (net.sf.jett.tag.ForEachTag), at Sheet1!B8 (originally at Sheet1!B8)

If the variable appears to be defined, the issue is most likely that a groovy syntax error was made.

3. Use Editing Tools

• Edit the .groovy file in a text editor and add it to the excel template spreadsheet. Avoid editing directly inside the spreadsheet. The recommended editor for editing groovy is Visual Studio Code with the code-groovy language support extension. Opening the file in Visual Studio Code will provide basic syntax highlighting to help read the code.

• Edit JETT in an HTML or XML file extension for syntax highlighting and formatting.

  • Note: line-breaks will break the template and should not be put directly into it. Only use them when reading and understanding it inside an editor. All line breaks will have to be removed when the final JETT expressions are placed in the cells of the excel file.

4. Learn Groovy

There are a plethora of online resources that teach the basics of groovy. Notable idiomatic groovy techniques used in the export script are:

• Closures

• Map literals

• Built-in iterator methods

• Elvis operator

• Null-safe method and variable access

  Here is the official documentation on learning groovy. Since there is currently no mock codebeamer environment that defines the codebeamer utilities, trial and error is necessary for evaluating scripts.

Exercise

1. Use the codebeamer swagger api to get a new field from a design input item.
2. Add that new property to the diItem data structure (this is clearly demarcated in the file) inside groovy.
3. Reference the new property you added inside the template.
4. Export and display the property inside the exported file.

Guided Implementation

Define The Data

1. Create a new TreeMap where all of the other global declarations called unFolderMap. This will keep track of the parents in the tracker tree for grouping.

   unFolderMap = new TreeMap()

2. Pass this folder map to the design input handler function handleDesignInput.

   def addedRows = handleDesignInput(
     userNeedItem,
     userNeedDownItem,
     diChildren,
     userNeedChildren,
     doMap,
     diMapSize,
     verMap,
     unFolderMap, // adding folder map here
     [lineNumber]
   )

3. Update the function definition to accept the folder as a parameter

   def handleDesignInput = { userNeedItem, userNeedDownItem, diChildren, userNeedChildren, doMap, diMapSize, verMap, unFolderMap /* added parameter */, lineNumberRef ->

4. Add the folder name to the unFolderMap for top-level JETT access.

   unFolderMap[userNeedItem.id] = userNeedDownItem?.parent?.name ?: "Uncategorized"

Display The Data

Inside the DTM-Start.xlsx file:

1. Add the updated groovy script to cell L8. (press f2 when the cell is highlighted, Ctrl+a to select all the text, paste the contents of the groovy script)

2. Move all the JETT cells down one row to row 9

3. Move the outer-most <jt:forEach> tag to cell B8

   <jt:forEach items="${items}" var="item">

4. Nest a <jt:span> tag that references the data and expands right 10 columns to fill the width of the template document.

   <jt:span factor="10" value="${unFolderMap.get(item.id)}" expandRight="true" />

5. Stylize as necessary.

6. Export

If everything was done correctly, for every User Need, its parent directory should be displayed on export.

Code Walkthrough and Questions

• Video recording with live Q&A