Features of the customizable, template based Excel export

Getting started: Exporting using customized Excel templates

The customized Excel exports are available on the "Export To Office" menu on tracker pages and also on Document-View page. After the "Export To Office" dialog opens you can select an "Excel export" option and use customized Excel exports from there.

You have two options, either:

• Upload an Excel template file from the local file-system, or
• Choose an existing Excel template from a special directory available in the Document Management

By clicking on the "Upload Office Template" link you can choose the template .xls or .xlsx file from the local file system, and once it is uploaded that file will be used as template. Alternatively you can drag-and-drop files to this link from file-systems if you are using a modern browser (mostly exception is the Internet Explorer versions).

Managing Excel templates

Once your Excel template is complete you can finally upload that to the Document Management, so other users can easily find and use them.

The place/directory where these templates are stored is accessible by clicking on the "Manage custom templates" link on this dialog. Any .xls or .xlsx files which are uploaded to this directory will appear in the drop-down list next to the "Use template" text, and can be easily selected from there.

Writing Excel templates

What are the Excel templates?

Excel templates are just ordinary and simple .xls or .xlsx files which contain certain scripts and special tags written into the cells of the template file. These scripts can control what content is exported and how it will look like.

During exporting of a template the scripts are evaluated for the input codeBeamer entities (the bugs/issues/tasks/requirements from the tracker) and the result is put into the result Excel document. Those parts of the original template are preserved - like charts a kept- as is which does not contain these special scripts.

The Excel export is using a simple yet powerful open-source templating engine JETT. Many of more complex details are documented on JETT's pages, so it is advised to get familiar with those pages too.

Simple properties export

To create a simple export template you can start with a new clean Excel sheet, and put scripts into cells of the template. For example the following Excel sheet will export the items (bugs, requirements etc)'s Status and Description to Excel:

Status	Description
${items.status.name}	${items.description}

What is happening here is that during the export the CodeBeamer will search the template Excel document and finds the the scripts by looking at the cell's content. If a script like ${items.description} is found then this script is evaluated for each exported items (TrackerItemDtos) and the result of this evaluation is put into the export document.

If the script is using the "items" variable the script that means that the script is executed once for every exported item, and there is automatically a new row created in the result document.

Special "items" variable: Implicit collection processing

When using the "items" variable in a script that automatically recognized, and that single row which contains the ${items.description} is evaluated for every issues. The result then will be added as one new row below each other.

This two screenshot shows the template and the result:

Exporting issue properties

You can access the issue's properties using a simple expression language which uses the common $ and "dot" notation which is used in many scripting languages. For example the ${items.description} is used to access the item.getDescription() java "getter".

The export engine uses the JEXL expressions for scripts.

There are more information available here about these scripts: JEXL syntax and some JEXL examples are availble too.

Accessing custom or customized fields by their labels

Some fields like the "name", "id" and "description" are always available in all trackers, so it is very easy to their values by simply using property access like ${item.description}.

Custom fields are however a more difficult to access. Let's have a look at this tracker definition:

There is a "Module" custom field defined in this tracker, which is stored in the "customField(0)" property (visible if you turn on "Show property name" checkbox above).

To get the "Module value you would have to use the ${item.getCustomField(0)} expression in Excel templates, which is quite inconvenient. Also that would limit the reusability of your template in case the "Module" fields is stored in different physical properties in different trackers.

So for this reason it is better to access such fields by using their label as expression. So use this expression:

${fields.getByLabel(items,"Module")}

This will find the field with label "Module" on item, and return the field's value. Much better for template reuse!

Choice Option Fields

Choice option fields can be accessed through getChoiceList(<index>) method.

These are collections, therefore their values are retrieved by querying collection items. For single option choice fields the item with 0 index has to be retrieved.

For example getting the value of a choice field with property name choiceList[1] would be:

   ${items.getChoiceList(1).get(0).getName()}

Accessing fields by label is also possible:

  ${fields.getByLabel(items,"<field label>").get(0).name}

Please see attached an example template for exporting user added fields. This example is for a standard Bugs tracker with the following added fields:

The template file contains two sheets. One for accessing the fields by property names, and a second one for getting the fields by labels.

Multichoice Fields

If a field is configured for multiple choice options (see screenshot below), and can have more values, then its items have to be iterated through as described in section Explicit processing of collections and content filtering

For example:

<jt:if test="${item.getChoiceList(1) != null}"><jt:forEach items="${item.getChoiceList(1)}" var="choice"> ${choice.name}</jt:forEach></jt:if>

Note the difference in syntax when referring to collection items opposed to complete collections ( e.g. item.getChoiceList(1) vs. items.getChoiceList(1) ).

Please see attached an example template for exporting multichoice fields.

The caveat of this script above is that it will put each choice value to a new line. If you want the choice values into a single cell and separated by a "," character this small groovy script with our groovy tag can do that. This script will print out the "Category" field's values to a cell.

<cb:groovy template="false" silent="false">cats = fields.getByLabel(item,"Category"); result = []; cats.each({result.add(it.name)}); return result.join(",");</cb:groovy>

Available variables in scripts (scope)

Following objects are accesssible in the scripts:

name	class	explanation
engine	JETTExcelTemplateRenderer	The template renderer engine
applicationContext	ApplicationContext	The spring's ApplicationContext, useful to get access to DAOs or Manager objects using applicationContext.getBean(...) calls
user	UserDto	The current user executing the export
request	HttpServletRequest	The current HTTP request
now	Date	The current date-time
cbBaseUrl	String	The url pointing to CodeBeamer server, so links can be generated using this
numberFormat	NumberFormat	The number formatter
textFormatter	TextFormatter	for rendering text and localized messages
items	List<TrackerItemDto>	The items (bug/issue/requiremens) being exported
cell	Cell	The POI cell being processed
sheet	Sheet	The POI sheet being processed
workbook	Workbook	The POI workbook being processed
dates	com.intland.codebeamer.text.excel.Dates	Various date constants for easy date filtering, for more Date constants see Excel Templates

For more information about the java objects and their properties please check the javadoc shipped with your codeBeamer version or check the latest javadoc online

Explicit processing of collections and content filtering

For many simple cases it is easy to create export rows using the "items" variable and implicit processing as seen before. However in many cases you may want to filter the output data and only export just a part of the issues in a tracker. For example if you want to export only some issues which are created "today or yesterday" then you should

• Process all issues using an jt:forEach iterator tag explicity
• And filter the exported issues using an jt:if tag

This is what is put to the template:

Id	Name	...more..data...
<jt:forEach items="${items}" var="item"><jt:if test="${item.submittedAt.after(yesterday)}">${item.id}	${item.name}	...	</jt:if></jt:forEach>

Here the jt:forEach tag will iterate over all issues in the "items" collection (which is the exported issues) and then repeat all cells between the start and end of the jt:forEach tag. The current issue will be exposed as "item" variable, so the cells inside the jt:forEach should refer to issue properties using that.

Then the jt:if tag is used to filter which item is added using a simple logic expression. The item.submittedAt contains the submit date of the issue, and the "yesterday" is a date constant always refers to the start (midnight) of yesterday. As expected the elements inside the jt:if tag will appear only if the condition is true.

Tags available

There are many more tags and more tag options available than the jt:forEach or jt:if tag in the previous example. It is important to note that the tags must be closed i.e. if a <jt:forEach> tag is opened there must be a matching </jt:forEach> tag appearing at the end of the block.

Some some examples:

• jt:hiperlink can turn a cell to hyperlink
• jt:comment can add comments to cells
• jt:style can add some color or other appearance to cells
• jt:formula can create a formula inside a cell.

For the complete list of available tags you can check the JETT tag documentation

Advanced script tags and Groovy/Velocity scripts

While simple property acesss and JEXL expressions are sufficient in most cases, sometimes you may need some more powerful script or template for building the export data.

For these cases the Excel export contains few tags that can execute a Groovy or Velocity scripts.

These tags are available:

Tag Name	Description
cb:script	Executes a template using the default script language -Velocity- and puts result to cell
cb:groovy	Evaluates a Groovy template, and puts result to cell
cb:velocity	Executes a velocity template and puts result to cell

For example this tag:

<cb:groovy>Groovy name:${item.name}</cb:groovy>

Will set the cell to if the exported item's name is "my bug"

Groovy name:my bug

Another example for using a Regexp from Groovy to extract some data:

<cb:groovy template="false" silent="false"> email = item.submitter.email; domainRegexp = /@(.*?)\./; matcher= (email =~ domainRegexp); return matcher[0][1];</cb:groovy>

This will extract domain name from the email address of the issues. For example if the submitter is "zluspai@intland.com" the script will put the "intland" text to the cell.

Limitations of scripts:

• The cb:script tags will use the complete content of the 1st cell as script, even that part which is outside of the <cb:script>...</cb:script> tags!
• These tags will ignore and remove all the remaining cells even that the tag spans multiple cells

The cb:groovy tag has some special feature that it can run in two different modes: as template or as script. The "template" mode means that the script is evaluated as a Groovy Simple Template, which has following main characteristics:

• The template is producing a string output (not a date or number)
• Bean properties are accessed using the ${bean.property} notation
• The new variables declared inside the template are "local", which means they are lost when the template evaluation is completed.

An example of this:

<cb:groovy>Hello ${world}</cb:groovy>

On the other hand, the Groovy template can run in "script" mode. That mean that the script-text is evaluated as some Groovy code. For example:

<cb:groovy template="false" silent="false">item.submittedAt</cb:groovy>

This differs from the template mode as:

• The variables and bean properties are accessed using the simple dot ("bean.property") notation, no ${...} text is needed around
• Any variables declared in the script will be available later as beans in the scope. For example this:

     <cb:groovy template="false">email = item.submitter.email</cb:groovy>
   

  will create an "email" bean in the scope which later can be used as ${email} elsewhere in the Excel sheet.
• By default the script won't produce any output, i.e. it won't write anything to cell. However if the silent="false" property is set then return value of the script will be inserted to the table cell. This means that it can return Date objects or numbers too, like in this example the cell will contain a Date data.

Auto-sizing columns

The cb:autosize advanced tag will auto-size the columns to fit their content automatically. Just put the starting <cb:autosize> tag to the 1st column and the closing </cb:autosize> tag to the last column and the result Excel sheet will be nicely sized for those columns.

Advanced functions and expressions

Besides the accessing bean's properties the expressions can also access static java methods directly. Just use the fully qualified class name as prefix, for example this calls a static method an String object:

${java.lang.String.format('Hello %s', 'world')}

The output is the obvious "Hello world".

The other advanced function is that you can access the static methods on Apache Commons' StringUtils directly using a "StringUtils:" prefix:

${StringUtils:substringAfter(item.name, "-")}

Filtering by dates

For easier filtering issues by dates the render variables contain the following date constants automatically. These are mostly self explanatory, the "begin" means the 1st second of the mentioned date/range, the "end..." means the "last..." second of that:

today
tomorrow
yesterday
thisWeek
thisMonth
lastMonth
thisYear
thisQuarter
previousQuarter

beginOfToday
endOfToday
beginOfTomorrow
endOfTomorrow
beginOfYesterDay
endOfYesterDay
beginOfThisWeek
endOfThisWeek
beginOfNextWeek
endOfNextWeek
beginOfLastWeek
endOfLastWeek
beginOfThisMonth
endOfThisMonth
beginOfNextMonth
endOfNextMonth
beginOfLastMonth
endOfLastMonth
beginOfThisYear
endOfThisYear
beginOfNextYear
endOfNextYear
beginOfLastYear
endOfLastYear

beginOfQuarter
endOfQuarter

beginOfPreviousQuarter
endOfPreviousQuarter

You can also reference to certain dates using a text description of that and convert it to real Date using the dates.get("...expression...") function. For example followings are valid:

//same as beginOfQuarter:
dates.get("Start of this quarter")
// same as endOfQuarter
dates.get("End of this quarter")

To get a specific date use this method, which returns 1st of September in 2014:

dates.get(2014, 9, 1)

To get a relative date from today use past() function,for example to get the day 180 days ago:

dates.past(180)

To create a date using the current user's default date or datetime format use:

// parses date or datetime
dates.parse("Sep 01 2014")

// parses only date, ignores time
dates.parseDate("Sep 01 2014")

// parses a date with time
dates.parseDateTime("Sep 01 12:34 2014")

Example: rendering data in multi-row

Creating an export where an issue appears in multiple row instead of a single row is easy, just use a jt:forEach tag which spans multiple rows. All those rows will be repeated and the expressions inside are evaluated.

The template is:

And the output is:

Note: if you want a more compact look: to remove the blank line between block the jt:forEach should be put to just before the "Name" text in the same cell.

Hyperlinks back to codeBeamer

If you want to render a hyperlink that points back to codeBeamer you should use this:

<jt:hyperlink address="${cbBaseUrl}${item.urlLink}" value="${item.id}"></jt:hyperlink>

As result the item's "id" will become a hyperlink which can be clicked on inside Excel, and it will open the same issue in a new browser window.

The hyperlink tag does not support implicit iteration: it wont work with the items variable. So this won't work:

<jt:hyperlink address="${cbBaseUrl}${items.urlLink}" value="${items.id}"></jt:hyperlink>

Instead of you have to do an forEach iteration like this in your Excel:

ID	Name
<jt:forEach items="${items}" var="it"><jt:hyperlink address="${cbBaseUrl}${it.urlLink}" value="${it.id}"/>	${it.name}	</jt:forEach>

Executing SQL queries

Even if it is advised to query data using the DAO and Manager objects, but sometimes one just can not avoid having a nice little SQL in templates.

By using the "jdbc" object you can execute SQL queries as this example shows:

User	Email
<jt:forEach items="${jdbc.execQuery('SELECT name, email FROM users')}" var="user">${user.name}	${user.email}</jt:forEach>

This little fragment will export users' names and emails from codeBeamer to Excel.

Adding Charts

You can also include charts to the Excel templates such way that charts will automatically pick up the exported data and will nicely present that.

So let's create charts using following steps !

Export source data for charts

First of all you should prepare the data which will be visualised in the chart. That is performed the usual way by referring properties of items, or using tags or any similar way.

There is one requirement for the data export however: in our example we are defining a pivot chart using named ranges to feed the charts with data. This means that the export data must have a header line too as highlighted in the following screenshot.

Put sample data into the Excel template for charts

As soon as the data is prepared we can start defining the charts. Yet before we could define the chart there must be some sample data available in the Excel sheet already. Such sample data contains a subset of the real export data, and required because Excel does not allow defining a chart without providing a some data to it.

The best practice is that you do an export now with the scripts you've defined in the previous step. Then pick a part of the export data and copy into the Excel template.

Important: the sample data is not needed in the final Excel export and therefore should be removed from the final result. In order to remove that you should wrap the sample data in a <jt:if test='false'> condition, like this:

|<jt:if test="false">|...sameple data|</jt:if>

A screenshot as illustration:

Create named ranges on sample data

After the sample data is included you should define a named range which is then used in chart(s) to find their source data. The named range is a certain rectangular area of Excel cells which contain the source data, which is identified by their names.

So for defining a named range go to:

• Formulas tab and Name Manager
• In the dialog click New and enter name for range
• In the "Refers to" field below select the named range in the sample data (by clicking on the little chart on the right of the input box).

IMPORTANT: your named range should also contain the static header cell above the range. As in this screenshot it should contain the "Module" text too!

Define charts using named ranges

Now as the named ranges is defined on the sample data we can create a chart using that. Let's insert an Pivot chart and Pivot table:

• Create a new "Charts" sheet
• Insert a Pivot Chart and Pivot Table using the Insert -> PivotChart menu
• The pivot chart dialog appears, select the range which contains the data by using the "Select a table or range" dialog
  
  
• Pick the named-range which was defined earlier that contains the data for the chart
  
  
• Save the dialog

Now the Pivot table and chart is added, yet it is empty. To select the data it will display use the Pivot-Table sidebar:

• Select one of fields appearing, here the "Module"
• The pivot table now is showing some data (Modules), yet the pivot-chart is still empty. To fill the values of the pivot chart drag-and-drop one of the fields to the "Values" section on the sidebar. In this example dragging the "Module" field will do:

Finally an important step: we should tell the exporter to ignore the charts and the data in the pivot tables, even if they contain some scripts.

For that add a <jt:null> tag around the chart so that part won't be touched. An example:

|<jt:null>|...chart...|
|...|...chart...|
|...|...chart...|</jt:null>

Now the Pivot Table and Pivot Chart is ready, both is showing the sample data and referencing the sample data being exported.

Re-bind named ranges to the real data being exported

The last step missing is that we need to redefine the named range during the Export to point to the real data. This is done by adding a special <jt:name> tag which defines the new named range.

For example the "module" named-range will be generated from the scripts in the $H1$1:$H$2 range, so that is rebound by using this tag:

<jt:name name="module" formula="$[$H$1:$H$2]" />

Exporting charts and and example template

Finally once the template with charts is ready the export should:

• Fill the Excel sheet with real data
• The charts automatically find their source data using named ranges

You can find the attached Template-with-charts.xlsx as useful example for defining exports with charts. That demonstrates:

• How to filter the input data
• How to alter data during export
• Defining charts

Tips and tricks

Avoid Excel cell limits: cut long content

Excel has certain limitations of the content, the most important one is that a cell can not contain more than 32767 characters. This is likely causing problems when your template exports description field too, which may contain longer text than this limit.

In order to fix this the description should be abbreviated during export. Just use this expression:

${StringUtils:abbreviate(items.description, 1000)}

If this limit is exceeded Excel will report an "Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)" error when opening the result Excel file, so this is is an indication of such problem.

Using Regular Expressions

Regular expressions (Regexp) is a powerful tool, so you may want to use in Excel templates. An example is: you want to extract the domain name from the emails of the user who has reported issues. Like following users/emails has reported issues:

zluspai@javaforge.com
zoltan.luspai@intland.com

And want to get the domain name in a cell like "javaforge" or "intland" should be extracted, so a report can be made which tells how many bug reports there grouped by the companies. So the result should be:

javaforge
intland

So then you can use Regexp and the next groovy script, which extract the "domain" part:

<cb:groovy template="false" silent="false"> email = item.submitter.email; domainRegexp = /@(.*?)\./; matcher= (email =~ domainRegexp); return matcher[0][1];</cb:groovy>

This works fine, yet a bit complex. An alternative is to use a "Regexp:findFirst()" custom function which can extract the first match of a Regexp. This produces the same result:

${Regexp:findFirst(item.submitter.email, "@(.*?)\.", "$1")}

The Regexp:findFirst() function will accept these parameters:

• The 1st parameter is the input String to extract value from
• 2nd parameter is the Regexp to find in the input string
• 3rd parameter is optional, and contains the expression which tells what to extract. The "$0" text will extract the complete match, the "$1" will extract the 1st "group" from regexp, and so on.

Additionally the "Regexp:findAll()" function is available which works similar to the "findFirst" method, yet scans for all matches, and puts the results to a List (of Strings).