Excel Templates

Excel Reporting

Exporting Using Customized Excel Templates

The customized Excel exports are available in the Export To Office menu on tracker pages, and also on Document View page. After the Export To Office dialog opens, users can select an Excel export option.

• Choose an existing Excel template from a special directory available in the Document Management (uploaded previously).

Managing Excel Templates

Once the Excel template is complete, it can be uploaded to the Document Management.

The directory where these templates are stored is accessible by clicking on the Manage custom templates link on this dialog. The .xls or .xlsx files uploaded to this directory can be selected form the Use template drop-down list.

Writing Excel Templates

What are the Excel Templates?

Excel templates are .xls or .xlsx files which contain certain scripts and special tags. These scripts manage how the content is exported.

During exporting 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 which do not contain special scripts are preserved (for example, charts).

The Excel export uses the JETT open-source templating engine.

Simple Properties Export

To create an export template, start with a new clean Excel sheet and put the relevant scripts into cells of the template.

For example, the following Excel sheet exports the status and description of an item (bug, requirement, and so on) to Excel:

During the export, codebeamer checks the scripts added to the template Excel document. If a script, for example ${items.description} is found, this script is evaluated for each exported items (TrackerItemDtos), and the result of the evaluation is put into the export document.

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

Special "Items" Variable: Implicit Collection Processing

Using the "items" variable in a script that automatically recognizes it, that single row containing the ${items.description} is evaluated for every issues. The result is added in new rows below each other.

The template:

The result:

Exporting Issue Properties

The properties of an issue can be accessed using a simple expression language which uses the common $ and "dot" notation 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. For more information and examples, see: JEXL syntax, JEXL examples.

Accessing Custom or Customized Fields by Their Labels

Certain fields, for example name, id and description , are always available in all trackers. To access the properties of such fields, use the ${items.description} .

Custom fields are, however, more difficult to access.

There is a Module custom field defined in this tracker, which is stored in the customField(0) property. This is visible if the Show property name checkbox is selected.

To get the Module value, the ${items.getCustomField(0)} expression should be used in Excel templates. This method limits the reusability of your template in case the Module fields are stored in different physical properties in different trackers.

For this reason, it is better to access such fields by using their label as expression:

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

This finds the field with label Module on item, and return the field's value.

Accessing and Formatting Custom Date Fields

Custom field values can be accessed by using the following property access:

${items.getCustomField(0)}

In case the date cell formatting is to be applied from a custom template, the date type custom fields should be accessed by using the following property access:

${items.getCustomFieldDate(0)}

Alternatively, these fields can be accessed by their labels. For further information, see section Accessing custom or customized fields by their labels.

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.

To get, for example, the value of a choice field with property name choiceList[1] , use the following:

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

Accessing fields by label is also possible:

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

See the attached 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 for getting the fields by their labels.

Multichoice Fields

If a field is configured for multiple choice options (see below), and can have more values, 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>

Difference in syntax to be used when referring to, for example,

• collection items: item.getChoiceList(1)
• complete collections: items.getChoiceList(1)

See: example template for exporting multichoice fields

The caveat of the above script is that it puts each choice value to a new line. The following groovy script with our groovy tag puts the choice values into a single cell, separated by a "," character. This script prints out the values of the Category field 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

The following objects are accessible 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
ALL other from Word export		You can use ALL objects from Word export

For more information about the java objects and their properties, check the javadoc shipped with your codebeamer version You must login to see this link. Register now, if you have no user account yet..

Explicit Processing of Collections and Content Filtering

In many cases, the "items" variable can be used to create export rows, and to implicit processing as seen before.

However, you may want to filter the output data and export only a part of the issues of a tracker. For example, to export only some issues which were created today or yesterday:

• Process all issues using an jt:forEach iterator tag explicitly, and
• Filter the exported issues using an jt:if tag

The content of the template:

The jt:forEach tag iterates over all issues in the "items" collection (which is the exported issues), and repeats all cells between the start and end of the jt:forEach tag.

The current issue is exposed as "item" variable, therefore, the cells inside the jt:forEach should refer to the issue properties using that.

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, the "yesterday" date constant always refers to the start (midnight) of yesterday. As expected, the elements inside the jt:if tag 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.

Some examples:

• jt:hyperlink - turns a cell to hyperlink
• jt:comment - adds comments to cells
• jt:style - adds some color or other appearance to cells
• jt:formula - creates a formula inside a cell.

For the complete list of available tags, see the JETT tag documentation.

JT:Formula Tag

The jt:formula tag is used to create cells with Excel formulas inside. The engine evaluates the expression and places the Excel formula in the cell.

Examples:

• Formula to convert a string to an integer in the cell: <jt:formula text="VALUE(${items.getCustomField(1)})"/> . In this case, the cell where the number field is placed in the template should also be in number format.
• Formula to convert long values to date: <jt:formula text="${items.getCustomField(0)}/86400000"/> . Set the cell format in the template to the desired time format.

Advanced Script Tags and Groovy/Velocity Scripts

While simple property access and JEXL expressions are sufficient in most cases, sometimes you may need more powerful scripts or templates to build the export data.

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

The following tags are available:

Example 1

The following tag:

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

Sets the cell if the exported item's name is "my bug"

Groovy name:my bug

Example 2

Using a Regexp from Groovy to extract data:

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

It extracts the domain names from the email addresses of the issues. For example if the submitter is "zluspai@intland.com", the scrip puts the "intland" text to the cell.

The cb:groovy tag can run as a template or as a script.

As a template, the script is evaluated as a Groovy Simple Template which has following characteristics:

• The template is producing a string output, not a date or number.
• Bean properties are accessed by 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.

For example:

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

As a script, the script-text is evaluated as some Groovy code:

• 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. The below, for example, creates an email bean in the scope which can later be used as ${email} elsewhere in the Excel document:
  

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

• By default the script does not produce any output, it does not write anything to cell. However, if the silent="false" property is set, the return value of the script is inserted to the table cell. This means that it can return Date objects or numbers.

For example:

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

Auto-Sizing Columns

The cb:autosize advanced tag auto-sizes the columns to fit their content automatically. By placing the starting <cb:autosize> tag to the first columnm, as well as the closing </cb:autosize> tag to the last column, and the result Excel sheet is sized for those columns.

Advanced Functions and Expressions

Besides the accessing bean's properties, the expressions can also access static java methods directly. Use the fully qualified class name as prefix. For example, the following expression calls a static method, a 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 filtering issues by dates, the render variables contain the following date constants automatically. These are mostly self explanatory, the "begin" means the first 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

Certain dates can also be referenced by using a text description, and convert it to real Date using the dates.get("...expression...") function. For example:

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

To create an export where an issue appears in multiple rows, use a jt:forEach tag which spans multiple rows. All those rows are repeated and the expressions inside are evaluated.

The template is:

And the output is:

To remove the blank line, the jt:forEach tag should be put before the "Name" text in the same cell.

Hyperlinks Back to codebeamer

To render a hyperlink that points back to codebeamer, use the following:

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

As result, the item's "id" becomes a hyperlink which can be clicked on inside the Excel. It opens the same item in a new browser window.The hyperlink tag does not support implicit iteration, and does not work with the items variable.

Therefore, the following would not work:

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

A forEach iteration needs to be done in your Excel:

Executing SQL Queries

Even if it is advised to query data using the DAO and Manager objects, sometimes it cannot be avoided to have SQL in the template.

By using the "jdbc" object, you can execute SQL queries. For example:

This exports users' names and emails from codebeamer to Excel.

Adding Charts

Charts can also be included in the Excel templates. These automatically pick up and display the exported data.

Charts can be created by performing the steps described in the following subsections.

Export Source Data for Charts

Prepare the data to be visualized in the chart. That is performed the usual way by referring to properties of items, or, by using tags, for instance.

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 on the following screenshot.

Put Sample Data Into the Excel Template for Charts

1. As soon as the data is prepared, the charts can be defined. 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 adding some data to it.
2. Perform an export with the scripts you have defined in the previous step. Then, pick a part of the export data and copy into the Excel template.

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

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

Create Named Ranges on Sample Data

After the sample data is included, you should define a named range which is used in charts to find their source data. The named range is a certain rectangular area of Excel cells containing the source data, which is identified by their names.

To define a named range, go to:

1. Formulas tab ► Name Manager
2. In the dialog, click New and enter name for range
3. In the Refers to field, select the named range in the sample data by clicking on the little chart on the right of the input box.

The 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, a chart can be created based on that. Insert a Pivot Chart and Pivot Table:

1. Create a new "Charts" sheet
2. Insert a Pivot Chart and Pivot Table using the Insert ► PivotChart menu
   
   
   
3. The pivot chart dialog appears. Select the range which contains the data by using the Select a table or range dialog.
   
4. Pick the named range defined earlier that contains the data for the chart.
   
5. Save the dialog. The Pivot table and chart are added. The Pivot-Table sidebar is displayed to select the data.
6. Select one of fields appearing, here the "Module"
   
   
   
7. The pivot table shows some data (Modules), however, 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 of the sidebar.
8. Instruct the exporter to ignore the charts and the data in the pivot tables, even if they contain some scripts. Add a <jt:null> tag around the chart, and that part will not be touched.
   For example:
   

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

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

Re-Bind Named Ranges to the Real Data Being Exported

The named range should be defined 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 is generated from the scripts in the $H1$1:$H$2 range, therefore, that is rebound by using this tag:

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

Exporting Charts and Example Template

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.

See the Template-with-charts.xlsx as an example to define exports with charts. It demonstrates:

• How to filter the input data.
• How to alter data during export.
• How to define charts.

Tips and Tricks & FAQs

Avoid Excel Cell Limits: Cut Long Content

Excel has certain limitations of the content. The most important one is that a cell cannot contain more than 32767 characters. This is can cause problems when your template exports the description field too, which may exceed his limit.

In order to fix this, the description should be abbreviated during export by using the following expression:

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

If this limit is exceeded, Excel displays a "Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)" error when opening the result Excel file.

The Description and Other Wiki Fields, Content is Exported as Wiki Source

When you have a description field or other wiki type field and export this to Excel, then the Excel export contains the wiki source instead of the same output as shown inside codebeamer.

The text-formatting capabilities of Excel is very limited, it cannot display a full HTML content as it would be needed for properly exporting the wiki content as HTML. Therefore, when exporting a wiki-formatted content (for example, the description of a bug) that is simply exported as its wiki source, which is essentially a plain text.

This wiki formatted text also works when importing back from the Excel document (in a roundtrip or when just importing data from Excel).

Using Regular Expressions

Regular expressions (Regexp) is a powerful tool, and can be used in Excel templates.

Example: You would like to extract the domain name from the emails of the user who has reported issues.

The following users/emails has reported issues:

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

The domain names like "javaforge" or "intland" should be extracted and written into cells. For this, a report showing the number of bug reported by companies could be made. The result should be:

javaforge
intland

Then, you can use Regexp and the following groovy script which extracts the "domain" part:

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

An alternative way 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 accepts these parameters:

• The first parameter is the input String to extract value from
• The second parameter is the Regexp to find in the input string
• The third parameter is optional, and contains the expression which tells what to extract. The "$0" text will extract the complete match, the "$1" extracts the first "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).