Recipes

In this page

Filtering the issues passed to the Excel template

JQL, the query language used by Jira, is extremely flexible and allows implementing complex searches. You should just set up a saved filter in Jira, run that and export the result set.

In those cases when JQL filtering is not sufficient or you really need to filter the issues once again, you can do that in the template.

How? Each template contains a main loop that iterates over the issues like this:

<jt:forEach items="${issues}" var="issue">...</jt:forEach>

You should rewrite that in the following way to evaluate an extra condition and to export only if that condition evaluates to true:

<!-- only export the issues in the 'FOO' project and ignore others -->
<jt:forEach items="${issues}" var="issue" where="${issue.key.contains('FOO')}">...</jt:forEach>

Sorting the issues and metadata passed to the Excel template

This section is dedicated to sorting the most important entities in the Jira data model. For sorting in general, also see the sorting data section.

Sorting issues

In most of the cases, you can flexibly sort your issues in JQL using the ORDER BY clause. Afterwards, if you just iterate over the $issues collection, it will access the issues in the order produced by the JQL.

In those cases when JQL sorting is not sufficient or you really need to sort the issues using custom logic, you can:

• Sort using the orderBy attribute of the <jt:forEach> tag.
• Sort using a custom issue sorter tool (written in Groovy).

Using the "orderBy" attribute of the <jt:forEach> tag

You can sort the issues before iterating over those in the Excel templates. Just use the orderBy attribute:

<jt:forEach item="${issues}" var="issue" orderBy="key asc">...</jt:forEach>

This example sorts the issues by issue key ascending. See more examples in the sorting data section.

Using a custom issue sorter tool

You can sort the issues by writing a custom sorter tool in Groovy (easier than it may sound!):

1. Create a sorter class in Groovy that implements your ordering and save it as issue-sorter-tool.groovy:

   issueSorter = new IssueSorterTool()
   
   public class IssueSorterTool {
   	public sort(issues) {
   		return issues.sort { a, b -> a.summary <=> b.summary } // sort by summary
   	}
   }
   

2. Execute it in your template:

   <mt:execute script="issue-sorter-tool.groovy"/>

3. Pass the incoming collection $issues (a Velocity context parameter) to the Groovy code and iterate over the re-sorted collection like this:

   <jt:forEach items="${issueSorter.sort(issues)}" var="issue">

You can implement any kind of flexible sorting logic based on this example.

Sorting comments as "newest first"

Issue comments are exported in "newest last" order by default.

To sort the comments in "newest first" order (the reverse order), you can:

• Sort using the orderBy attribute of the <jt:forEach> tag.
• Sort using a custom comment sorter tool (written in Groovy).

Using the "orderBy" attribute of the <jt:forEach> tag

You can sort the comments before iterating over those in the Excel templates. Just use the orderBy attribute:

<jt:forEach items="${commentManager.getCommentsRaw()}" var="comment" orderBy="created desc">...</jt:forEach>

This example sorts the comments by creation date, descending. See more examples in the sorting data section.

Using a custom comment sorter tool

You can sort the comments by writing a custom sorter tool in Groovy (easier than it may sound!):

1. Write a short sorter class in Groovy that implements your ordering and save it as comment-sorter-tool.groovy:

   commentSorter = new CommentSorterTool()
   
   public class CommentSorterTool {
   	public descending(comments) {
   		return comments.sort { a, b -> b.created <=> a.created } // sort in reverse order
   	}
   }
   

2. Execute it in your template:

   <mt:execute script="comment-sorter-tool.groovy"/>

3. Change the expression:

   <jt:forEach items="${issue.getCommentsRaw()}" var="comment">

   to:

   <jt:forEach items="${commentSorter.descending(issue.getCommentsRaw())}" var="comment">

You can implement any kind of flexible sorting logic based on this example.

Sorting data (in general)

There are multiple ways to sort collections. Choose the best way based on "where" you want to sort (in Excel templates or in Groovy scripts?) and how complex the sorting criteria is.

Using the "orderBy" attribute of the <jt:forEach> tag

You can sort any collection before iterating over its items in the Excel templates. You can do this, because the <jt:forEach tag supports the orderBy attribute which accepts properties and directions.

Sorting issues

Use the orderBy attribute with the properties of the Issue class.

Examples:

<!-- sort by a property -->
<jt:forEach item="${issues}" var="issue" orderBy="key">...</jt:forEach>

<!-- sort by a nested property -->
<jt:forEach item="${issues}" var="issue" orderBy="creator.displayName">...</jt:forEach>

<!-- sort in descending order (ascending is the default) -->
<jt:forEach item="${issues}" var="issue" orderBy="key desc">...</jt:forEach>

<!-- sort by multiple properties -->
<jt:forEach item="${issues}" var="issue" orderBy="creator.displayName;key">...</jt:forEach>

<!-- all these combined -->
<jt:forEach item="${issues}" var="issue" orderBy="creator.displayName:asc;key:desc">...</jt:forEach>

Sorting comments

Use the orderBy attribute with the properties of the Comment class.

Examples:

<!-- by creation date, oldest first -->
<jt:forEach item="${comments}" var="comment" orderBy="created asc">...</jt:forEach>

<!-- by creation date, newest first -->
<jt:forEach item="${comments}" var="comment" orderBy="created desc">...</jt:forEach>

<!-- by update date, newest first -->
<jt:forEach item="${comments}" var="comment" orderBy="updated desc">...</jt:forEach>

To apply this to the issue-navigator-with-comments.xlsx template, look for this cell:

<jt:forEach items="${commentManager.getCommentsRaw()}" var="comment">${comment.authorFullName}

...and replace it with something like:

<jt:forEach items="${commentManager.getCommentsRaw()}" var="comment" orderBy="created desc">${comment.authorFullName}

Sorting built-in worklogs

Use the orderBy attribute with the properties of the Worklog class.

Examples:

<!-- by start date, oldest first -->
<jt:forEach item="${worklogs}" var="worklog" orderBy="startDate">...</jt:forEach>

<!-- by author name, then by start date per worklog, newest first -->
<jt:forEach item="${worklogs}" var="worklog" orderBy="authorObject.displayName;startDate desc">...</jt:forEach>

To apply this to the issue-navigator-with-worklogs.xlsx template, look for this line to modify sorting:

<jt:forEach items="${issue.getWorklogsRaw()}" var="worklog"><jt:if test="${worklog.authorObject != null}" then="${worklog.authorObject.displayName}" else="Unknown"/>

...and replace it with something like:

<jt:forEach items="${issue.getWorklogsRaw()}" var="worklog" orderBy="startDate asc"><jt:if test="${worklog.authorObject != null}" then="${worklog.authorObject.displayName}" else="Unknown"/>

Sorting Tempo Timesheets worklogs

Use the orderBy attribute with the properties defined in tempo-tool.groovy. You can even extend those if you need more!

Example:

<!-- by start date, oldest first -->
<jt:forEach item="${worklogs}" var="worklog" orderBy="startDate">...</jt:forEach>

To apply this to the issue-navigator-with-tempo-worklogs.xlsx template, look for this line to modify sorting:

<jt:forEach items="${tempo.getWorklogs(issue.key)}" var="worklog">${worklog.startDate}

Using a custom sorter tool

The idea is simple: you can use Groovy to solve any types of problems, sorting also included. Just implement the sorting logic in Groovy, execute the script, pass the collection to the sorter, return the sorted collection, and use that in your Excel template!

See this section for a full working example.

Generating hyperlinks for issues

Although the most typical examples are the issue key or summary cells, you can generate hyperlinks into any Excel cell using the example below. This allows users intuitively click issue keys or summaries to jump to the Jira page of the corresponding issue.

In Issue Navigator

The default behavior is that the issue key and summary columns will be exported as hyperlinks. To export those as static texts with no links, set these configuration variables to false in field-helper.groovy:

// field-helper-tool.groovy

def issueKeyAsLink = true
def summaryAsLink = true

In custom templates

In custom templates, you can use the hyperlink tag like this:

<jt:hyperlink type="url" address="${requestContext.canonicalBaseUrl}/browse/${issue.key}" value="${issue.key}"/>
<jt:hyperlink type="url" address="${requestContext.canonicalBaseUrl}/browse/${issue.key}" value="${issue.summary}"/>

Sub-tasks

An issue can return its sub-tasks using the following getter:

Collection<Issue> getSubTaskObjects()

Exporting sub-tasks

If you want to iterate over sub-tasks and export those, the iteration looks like this:

<jt:forEach items="${issues}" var="issue"><jt:forEach items="${issue.subTaskObjects}" var="subTask">${subTask.key}</jt:forEach></jt:forEach>

Exporting sub-tasks exactly the same way as top-level issues

If you want to export sub-tasks in the same way (same fields, same formatting, etc.) as the top-level issues that were passed to the export, you can use a simple trick: prepare a list by merging the top-level issues and their sub-tasks, and then iterate over the merged list!

1. Create the Groovy script sub-task-helper-tool.groovy:

   subTaskHelper = new SubTaskHelperTool()
   
   public class SubTaskHelperTool {
   	/**
   	 * Returns the passed issues merged with their sub-tasks.
   	 */
   	public getIssuesAndSubtasks(issues) {
   		def issuesAndSubtasks = []
   		for(issue in issues) {
   			issuesAndSubtasks.add(issue)
   			for(subTask in issue.subTaskObjects) {
   				issuesAndSubtasks.add(subTask)
   			}
   		}
   		return issuesAndSubtasks
   	}
   }
   

2. Execute it in your template:

   <mt:execute script="sub-task-helper-tool.groovy"/>

3. Call the new method, and iterate over the merged list in the template:

   <jt:forEach items="${subTaskHelper.getIssuesAndSubtasks(issues)}" var="issue">

Tip: make sure to filter out sub-tasks from the input by using a JQL like this, otherwise those will appear twice in the export:

project = FOO AND type != Sub-task

(You can, alternatively, filter out sub-tasks while creating the merged list, but doing that with JQL is simpler.)

Exporting sub-task custom fields

Custom field values can be accessed using the $fieldHelper tool. This works the same way for sub-tasks as for top-level issues.

You can export custom field values using the custom field ID:

| Custom field (by ID)                                       |
| ${fieldHelper.getFieldValue(subTask, "customfield_10100")} |

...or using the custom field name:

| Custom field (by name)                                   |
| ${fieldHelper.getFieldValueByName(subTask, "My Field")}  |

Exporting parent issues of sub-tasks

Typically you export sub-tasks while exporting their parents, but sometimes the situation may be reversed. In that case, any field of the parent issue is accessible from a sub-task through its parentObject property.

You can, for example, get the key of the parent issue with this expression:

${subTask.parentObject.key}

Searching for issues

In addition to the issues passed to the template, it is possible to execute further JQL searches and also use those issues in your template.

Searching with JQL queries

Execute a JQL query and iterate through the results:

<jt:forEach items="${jqlSearch.searchByJql('project=FOO ORDER BY summary')}" var="issue">[${issue.key}] ${issue.summary}</jt:forEach>

Searching with saved filters

Execute the saved filter with the ID=13100 and iterate through the results:

<jt:forEach items="${jqlSearch.searchBySavedFilter(13100)}" var="issue">[${issue.key}] ${issue.summary}</jt:forEach>

Connecting to REST APIs

It's pretty easy to connect to REST API based services to include information from that data source in your Excel files.

Connecting to the Jira REST API

This example demonstrates connecting to the Jira REST API using BASIC authentication and getting an issue.

1. Create the script called jira-rest-api-tool.groovy that implements the REST API invocation:

   import groovy.json.JsonSlurper
   import org.apache.commons.io.IOUtils
   
   def user = "admin"
   def password = "admin"
   def urlConnection = new URL("https://mysite.atlassian.net/rest/api/3/issue/DEMO-1").openConnection()
   urlConnection.setRequestProperty("Authorization", "Basic " + (user + ":" + password).bytes.encodeBase64().toString())
   def jsonString = IOUtils.toString(urlConnection.inputStream)
   
   issueFromApi = new JsonSlurper().parseText(jsonString)
   

2. Execute it in your template:

   <mt:execute script="jira-rest-api-tool.groovy"/>

3. Use the issueFromApi object to access the returned issue's fields in the template:

   ${issueFromApi.key}
   ${issueFromApi.fields.summary}
   ${issueFromApi.fields.status.name}
   

Connecting to external REST APIs

This is an example of calling an external REST API without authorization:

1. Create the script called external-rest-api-tool.groovy that implements the REST API invocation:

   import groovy.json.JsonSlurper
   
   def jsonSlurper = new JsonSlurper()
   dataFromApi = jsonSlurper.parseText(new URL("https://www.foo.com/rest/1/user/123").text)
   

2. Execute it in your template:

   <mt:execute script="external-rest-api-tool.groovy"/>

3. If the REST API returns a user object like this, for instance:

   { "id": 123, "name": "John Doe", "email": "john.doe@example.com" }

   ...then use the $dataFromApi object to access the returned information in the template:

   ${dataFromApi.id}
   ${dataFromApi.name}
   

More on REST authentication

Some thoughts on REST authentication:

• If you need to pull data from the running Jira instance only, prefer using our helpers and tools over the REST API. That's faster, easier and you completely eliminate the need for authentication.
• If you are worried about using BASIC authentication, it is basically fine if used over HTTPS. If that's the case in your environment, keep it simple and just use BASIC.
• You have full control over the user account used for making REST calls. This means, you can set up a dedicated, restricted Jira user for REST. For instance, create a user account named rest-client-account, remove all "write" permissions, only add "read" permissions for certain projects, and then use this account in REST calls.

Issue Navigator template modes

Better Excel Exporter is shipped with several variants of the issue-navigator.xlsx template. Some export only field values only, others export field values plus comments, field values plus worklogs, field values plus change history, and so on.

All the variants support the common "export modes" explained below. You can combine the exports modes with each other and with any template variant to meet every kind of spreadsheet needs.

All fields vs. non-empty vs. current fields modes

As for the fields, the following modes are available:

1. "Current fields" mode: in this mode, those fields that you select in the Issue Navigator are exported. The column order in Excel follows the column order in Issue Navigator.
   It is intuitive and very easy to use, because you can visually select and order the fields in Issue Navigator, then the Excel spreadsheet will reproduce that. It supports ad-hoc searches and saved filters, too.
2. "Non-empty fields": in this mode, every field which exists in your Jira instance and has a non-empty value for at least one exported issue is exported. The column order in Excel is alphabetical, with Issue Key and Summary fixed in the leftmost columns (customizable).
   It is useful when you don't want to select the fields one by one, but quickly export "everything that matters" to a compact spreadsheet.
3. "All fields" mode: in this mode, every field which exists in your Jira instance is exported, including also empty ones. The column order in Excel is alphabetical, with Issue Key and Summary fixed in the leftmost columns (customizable).
   It can result in super-wide spreadsheets with lots of empty cells and even with completely empty columns. It is less friendly for "human readers", but useful for data archival or for data migration to external systems. Friendly warning: use it only if you are sure that this is what you need, as it generates heavy load on Jira!

The mode is chosen automatically based on the Excel view's name:

1. If the Excel view name (case-insensitively) contains the word "non-empty" or "non-e." → "non-empty fields" mode.
2. If the Excel view name contains the word "all" → "all fields" mode.
3. Otherwise → "current fields" mode.

Customizing Excel views for all vs. non-empty vs. current modes

There are default views created for various combinations, like "current fields" and "all fields" modes for columns only, columns plus comments, columns plus worklogs, columns plus Tempo Timesheets worklogs, columns plus change history, etc. Plus, there are repeating variants created for some of these, by default. You can use all these immediately after installing the app.

Additionally, you can create a new Excel view or rename an existing one to contain the word "all", "non-empty" or "non-e." in its name, and it will activate the expected mode. It's really that easy!

Customizing modes

You can freely customize the logic that chooses the mode by modifying these simple methods in issue-navigator.groovy:

private isAllFieldsMode() {
	viewNameContains("all")
}

private isNonEmptyFieldsMode() {
	viewNameContains("non-empty") || viewNameContains("non-e.")
}

private isCurrentFieldsMode() {
	!isAllFieldsMode() && !isNonEmptyFieldsMode()
}

private viewNameContains(s) {
	(boolean) xlsView?.name?.toLowerCase()?.contains(s)
}

After the mode was chosen, you can even customize the column collector logic in this method in issue-navigator.groovy:

def getIssueTableLayout() {
	// ...
}

Based on these, you can customize how the modes work or even invent new ones!

Customizing column order

In "current fields" mode, column order in the exported Excel file is the same as the order of columns in Jira. In "all fields" and "non-empty fields" modes, columns are ordered alphabetically, but you can configure the first columns by modifying this variable in issue-navigator.groovy:

def LEFTMOST_COLUMNS = ["issuekey", "summary"]

The default is that the two leftmost columns will be Issue Key and Summary. You can add, remove, re-order system and custom field identifiers in this list.

Repeating fields vs. non-repeating fields modes

What is the "repeating fields" mode? We will explain this through the example of worklogs, but the same concept is applicable to other issue metadata, as well.

Repeating fields mode

When using the issue-navigator-with-worklogs.xlsx, if an issue that has multiple worklogs, there will be multiple rows created for the same issue. For instance, if the issue FOO-1 has 3 worklogs, then the issue will be exported to 3 spreadsheet rows in total. The field values will be exported only to the topmost row, but each worklog will be exported to its own row:

(default mode)
| Issue key | Priority | Worklog Author | Worklog Time | Time Spent (h) | Worklog comment        |
| FOO-1     | Low      | alice          |  21 Jan 2017 |           1.50 | First worklog on FOO-1 |
|           |          | bill           |  22 Jan 2017 |           2.33 | Second worklog         |
|           |          | alice          |  26 Jan 2017 |           0.25 | Third worklog          |
| FOO-2     | Critical | cecil          |  20 Jan 2017 |           4.00 | Worklog on FOO-2       |

We call this mode "non-repeating fields", for obvious reasons.

This mode is useful when creating spreadsheets primarily for human readers, because the rows belonging to the same issue are easily recognizable.

Non-repeating fields mode

For a lot of use cases, it is useful to repeat the field values for each of those 3 rows:

(repeating fields mode: see the first two columns repeating for each worklog!)
| Issue key | Priority | Worklog Author | Worklog Time | Time Spent (h) | Worklog comment        |
| FOO-1     | Low      | alice          |  21 Jan 2017 |           1.50 | First worklog on FOO-1 |
| FOO-1     | Low      | bill           |  22 Jan 2017 |           2.33 | Second worklog         |
| FOO-1     | Low      | alice          |  26 Jan 2017 |           0.25 | Third worklog          |
| FOO-2     | Critical | cecil          |  20 Jan 2017 |           4.00 | Worklog on FOO-2       |

We call this mode "repeating fields", again for obvious reasons.

This mode is useful when you want to further analyze the exported data:

• Searching: if you search for those rows that contain "alice" as worklog author, it is easy to look up the field values of the owning issue even in very long lists.
• Filtering: if you show only those rows where the worklog author is "bill", then "repeating mode" will also display the field values in the left. Also, you can create filters that include field values and worklog attributes at the same time.
• Reporting: pivot tables require that you have the field values in the whole input range. For example, if you want to aggregate the worklog time values per issue, then the issue key FOO-1 must be present in all the rows to get the correct sum.
• And so on.

Customizing Excel views for repeating vs. non-repeating modes

It is easy to identify the non-repeating and repeating mode Exce; template pairs based on the filename:

Non-repeating Excel template	Repeating Excel template
issue-navigator-with-history.xlsx	issue-navigator-with-repeating-history.xlsx
issue-navigator-with-tempo-worklogs.xlsx	issue-navigator-with-repeating-tempo-worklogs.xlsx
issue-navigator-with-worklogs.xlsx	issue-navigator-with-repeating-worklogs.xlsx

How to use these?

1. If you need both the non-repeating and the repeating mode worklog exports, for example, then create two separate, intuitively named Excel views. For instance, "Worklogs (Current f.)" and "Worklogs (Current f. rep.)", each configured with the corresponding template.
2. If you need only one, then use only one Excel view with the template you prefer.

Important: you should use the repeating mode primarily for creating pivot tables, pivot charts, other reports and for data transfer in case the target system expects the data this way. Compared to the non-repeating mode, the repeating mode Excel files are less human-readable, require more time and more memory to generate, and larger in physical file size.

Other tips & tricks

Alternating row colors (zebra stripes)

If you want to add alternating row colors to your Excel template, just calculate the style based on the loop counter:

1. Define the two styles in the A1 cell:

   Summary<mt:class name="odd" style="fill-pattern: solid; fill-foreground-color: blue;"/><mt:class name="even" style="fill-pattern: solid; fill-foreground-color: green;"/>
   

2. Select the style depending on whether the loop counter is odd or even:

   <jt:forEach items="${issues}" var="issue" indexVar="rowIndex"><jt:style class="${rowIndex % 2 ? 'odd' : 'even'}">${issue.summary}</jt:style></jt:forEach>
   

You can implement more complex coloring logic (e.g. choosing the row color by the priority of the issue) based on this example.

Dynamic worksheet names

You can use expressions in Excel worksheet names, too.

For example, to include the number of exported issues, use this expression in the template worksheet name:

${issues.size()} bugs

Note that worksheet names are limited to 31 characters in Excel.

Jira Service Management SLA status: met vs. breached

SLA time values are exported as signed number values, out of the box. Those numbers are positive if the SLA is met or negative if the SLA is breached. Therefore, to compute the status itself, add another column to your template and use a formula with the IF() function:

| Time to Resolution                                       | Is breached?            |
| ${fieldHelper.getFieldValue(issue, "customfield_10101")} | $[IF(A2>=0,"No","Yes")] |

"A2" is the cell for the Time to Resolution SLA time value, which itself is exported using the $fieldHelper tool. Obviously, an "Is met?" value could be exported as the inverse of the IF() formula in the above example.

Further reads

Unit testing

Learn more about writing unit tests to increase the quality and reliability of your Groovy scripts.

Debugging

Learn more about debugging your Groovy scripts in the IDE or in Jira.

Logging

Learn more about writing to the Jira log from your Groovy scripts.

Troubleshooting

Learn more about finding the root cause of Excel export problems faster.

Questions?

Ask us any time.