Recipes

In this page

User interface

Hiding the "Export" menus on Jira Software boards

If you never export directly from the Jira Software boards, you may want to hide the menu drop-down buttons placed there by the app.

To do that, go to Administration → Announcement banner, add the following snippet to the Announcement text area, then hit Save.

<style>
.jxls-agile-board-header .jxls-export-dropdown-trigger,
.jpdf-agile-board-header .jpdf-export-dropdown-trigger {
	display: none;
}
#announcement-banner {
	padding: 0px;
}
</style>

In case there's already some announcement text is set (i.e. you are actually using announcements), then remove this part from the code above:

#announcement-banner {
	padding: 0px;
}

Creating quick links for frequently used Excel exports

Users often times prefer to have a list of ready-made export links that generate the export with a single click, instead of manually running a saved filter and then exporting the results from the "Export" drop-down menu. Quick links can save lots of time and tedious navigation.

To create a quick link:

1. Execute the saved filter.
2. Open the "Export" drop-down, right-click the menu item representing the export type you wanted to use, and copy the link to the clipboard.
3. Now insert the link from the clipboard into a "Text" type Jira gadget (tutorial), into a Confluence page (tutorial), into a website, CMS or any other tool that allows sharing and categorising URL hyperlinks (bookmarks).

See this example:

<a href="http://localhost:8080/rest/com.midori.jira.plugin.betterexcel/1.0/xls/xls-view/4/render?tempMax=100000&context=issue_navigator&filterId=10901" target="_blank">B4B Project - Q4 Sales Report (Excel)</a><br>
<a href="http://localhost:8080/rest/com.midori.jira.plugin.betterexcel/1.0/xls/xls-view/4/render?tempMax=100000&context=issue_navigator&filterId=10900" target="_blank">B4B Project - Open tickets from last week (Excel)</a><br>
<a href="http://localhost:8080/rest/com.midori.jira.plugin.betterexcel/1.0/xls/xls-view/4/render?tempMax=100000&context=issue_navigator&filterId=10902" target="_blank">WebStore Project - SLA breach report (Excel)</a><br>

Yes, it is really that simple. Please note that these hyperlinks are secure, meaning that even if you post them to some external system, clicking them will direct your browser to Jira, which will require you to properly login if you aren't yet.

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 the standard sorter tool (by VelocityTools).
• 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 the standard sorter tool

(since app version 8.1.0)

You can sort the issues with the standard sorter tool.

Instead of just iterating over $issues, sort the collection in Groovy:

def sortedIssues = sorter.sort(issues, "key:asc"))

...and iterate over the sorted collection in the Excel template.

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 the standard sorter tool (by VelocityTools).
• 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="${fieldHelper.getCommentsForUser(issue, user)}" 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 the standard sorter tool

(since app version 8.1.0)

You can sort the comments with the standard sorter tool.

Instead of just iterating over the comments, sort the collection in Groovy:

def sortedComments = sorter.sort(fieldHelper.getCommentsForUser(issue, user), "created:desc"))

...and iterate over the sorted collection in the Excel template.

This example sorts the comments as "newest first". 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="${fieldHelper.getComments(issue, user)}" var="comment">

   to:

   <jt:forEach items="${commentSorter.descending(fieldHelper.getComments(issue, user))}" 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="${fieldHelper.getCommentsForUser(issue, user)}" var="comment">${comment.authorFullName}

...and replace it with something like:

<jt:forEach items="${fieldHelper.getCommentsForUser(issue, user)}" 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="${worklogManager.getByIssue(issue)}" 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!

Examples:

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

<!-- by worker name, then by date per worker -->
<jt:forEach item="${worklogs}" var="worklog" orderBy="full_name;work_date">...</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)}" orderBy="work_date asc" var="worklog">${worklog.work_date}

Using the standard sorter tool

(since app version 8.1.0)

Velocity provides a universal tool intuitively called SortTool to sort collections of issues, comments, worklogs and other type of data. It is accessible by the name sorter in Groovy scripts.

Sorting issues

Use the $sorter tool with the properties of the Issue class.

Examples:

// sort by a property
sorter.sort(issues, "name")

// sort by a nested property
sorter.sort(issues, "creator.displayName")

// sort in descending order (ascending is the default)
sorter.sort(issues, "startDate:desc")

// sort by multiple properties
sorter.sort(issues, ["startDate", "summary"])

// all these combined
sorter.sort(issues, ["creator.displayName:asc", "summary:desc"])

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!

Assuming that you want to modify the issue-navigator.xlsx template:

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:

1. Create the script named jql-search-tool.groovy:
   • Jira 8 compatible version:

     import com.atlassian.jira.component.ComponentAccessor
     import com.atlassian.jira.issue.search.SearchRequestManager
     import com.atlassian.jira.mail.TemplateIssue
     import com.atlassian.jira.web.bean.PagerFilter
     import com.atlassian.jira.bc.issue.search.SearchService
     import org.apache.log4j.Logger
     
     jqlSearch = new JqlSearchTool(user: user)
     
     class JqlSearchTool {
     	def log = Logger.getLogger(this.getClass())
     
     	private user
     
     	/**
     	 * Returns the issues found by executing the passed JQL
     	 * (or null in case of failure).
     	 */
     	def searchByJql(def jql) {
     		def clazz = ComponentAccessor.class.classLoader.loadClass("com.atlassian.jira.jql.parser.JqlQueryParser")
     		def jqlQueryParser = ComponentAccessor.getComponentOfType(clazz)
     
     		def query = jqlQueryParser.parseQuery(jql)
     		if(query == null) {
     			log.debug("<{$query.queryString}> could not be parsed")
     			return null
     		}
     		log.debug("<{$query.queryString}> is parsed")
     
     		return search(query)
     	}
     
     	/**
     	 * Returns the issues found by executing the saved filter with the passed ID
     	 * (or null in case of failure).
     	 */
     	def searchBySavedFilter(def savedFilterId) {
     		def searchRequest = ComponentAccessor.getComponentOfType(SearchRequestManager.class).getSearchRequestById(user, savedFilterId)
     		if(searchRequest == null) {
     			log.debug("Filter #${savedFilterId} not found")
     			return null
     		}
     		log.debug("Filter #${savedFilterId} found: \"${searchRequest.name}\"")
     
     		return search(searchRequest.query)
     	}
     
     	private search(def query) {
     		def searchResults = ComponentAccessor.getComponentOfType(SearchService.class).search(user, query, PagerFilter.getUnlimitedFilter())
     		if(searchResults == null) {
     			return null
     		}
     		log.debug("<{$query.queryString}> found ${searchResults.total} issues")
     
     		return searchResults.results.collect { new TemplateIssue(it, ComponentAccessor.fieldLayoutManager, ComponentAccessor.rendererManager, ComponentAccessor.customFieldManager, null, null) }
     	}
     }
     

   • Jira 6 and Jira 7 compatible version:

     import com.atlassian.jira.component.ComponentAccessor
     import com.atlassian.jira.issue.search.SearchRequestManager
     import com.atlassian.jira.mail.TemplateIssue
     import com.atlassian.jira.web.bean.PagerFilter
     import com.atlassian.jira.issue.search.SearchProvider
     import org.apache.log4j.Logger
     
     jqlSearch = new JqlSearchTool(user: user)
     
     class JqlSearchTool {
     	def log = Logger.getLogger(this.getClass())
     
     	private user
     
     	/**
     	 * Returns the issues found by executing the passed JQL
     	 * (or null in case of failure).
     	 */
     	def searchByJql(def jql) {
     		def clazz = ComponentAccessor.class.classLoader.loadClass("com.atlassian.jira.jql.parser.JqlQueryParser")
     		def jqlQueryParser = ComponentAccessor.getComponentOfType(clazz)
     
     		def query = jqlQueryParser.parseQuery(jql)
     		if(query == null) {
     			log.debug("<{$query.queryString}> could not be parsed")
     			return null
     		}
     		log.debug("<{$query.queryString}> is parsed")
     
     		return search(query)
     	}
     
     	/**
     	 * Returns the issues found by executing the saved filter with the passed ID
     	 * (or null in case of failure).
     	 */
     	def searchBySavedFilter(def savedFilterId) {
     		def searchRequest = ComponentAccessor.getComponentOfType(SearchRequestManager.class).getSearchRequestById(user, savedFilterId)
     		if(searchRequest == null) {
     			log.debug("Filter #${savedFilterId} not found")
     			return null
     		}
     		log.debug("Filter #${savedFilterId} found: \"${searchRequest.name}\"")
     
     		return search(searchRequest.query)
     	}
     
     	private search(def query) {
     		def searchResults = ComponentAccessor.getComponentOfType(SearchProvider.class).search(query, user, PagerFilter.getUnlimitedFilter())
     		if(searchResults == null) {
     			return null
     		}
     		log.debug("<{$query.queryString}> found ${searchResults.total} issues")
     
     		return searchResults.issues.collect { new TemplateIssue(it, ComponentAccessor.fieldLayoutManager, ComponentAccessor.rendererManager, ComponentAccessor.customFieldManager, null, null) }
     	}
     }
     

2. Execute it in your template:

   <mt:execute script="jql-search-tool.groovy"/>

Searching with JQL queries

After executing the script explained in the previous section, 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

After executing the script explained in the previous section, 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. (You could, of course, solve this particular use case easier in a local Jira using IssueManager, but we use this to demonstrate making a simple REST API call.)

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("http://jira.acme.com/rest/api/2/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 local Jira instance only, prefer using Jira's internal Java API 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 (or the loop-back interface of your server). 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.

Connecting to databases to run SQL queries

Retrieving data from databases, including both the Jira database and external databases, is possible with some Groovy scripting:

1. Create the script named database-tool.groovy:

   import com.atlassian.jira.component.ComponentAccessor
   import groovy.sql.Sql
   import org.apache.log4j.Logger
   
   database = new DatabaseTool()
   
   class DatabaseTool {
   	def log = Logger.getLogger(this.getClass())
   
   	def executeSql(def jdbcDriverClassName, def url, def user, def password, def sqlQuery) {
   		def result
   
   		def sql
   		def conn
   
   		try {
   			// assumes that the JDBC driver is available on the classpath
   			def jdbcDriverClazz = ComponentAccessor.class.classLoader.loadClass(jdbcDriverClassName)
   			log.debug("JDBC driver class: " + jdbcDriverClazz.canonicalName)
   
   			def jdbcDriver = jdbcDriverClazz.newInstance()
   			log.debug("JDBC driver: " + jdbcDriver)
   
   			def props = new Properties()
   			props.put("user", user)
   			props.put("password", password)
   
   			conn = jdbcDriver.connect(url, props)
   			sql = Sql.newInstance(conn)
   			result = sql.rows(sqlQuery)
   			log.debug("Results found: " + result.size())
   		} catch (Exception ex) {
   			log.error("Failed to execute SQL", ex)
   		} finally {
   			sql.close()
   			conn.close()
   		}
   
   		return result
   	}
   }

2. Execute it in your template:

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

3. Call the database.executeSql method, iterate over the result, and access database column values via properties with the same name. For example, query the Jira user accounts directly from the database:

   | <jt:forEach items="${database.executeSql('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost:3306/jiradb', 'root', '', 'SELECT * FROM cwd_user')}" var="row">${row.display_name} | ${row.user_name}</jt:forEach> |
   

Please read the official Groovy documentation on working with relational databases for more details.

Exporting additional Tempo Timesheets worklog details

Tempo Timesheets worklog information are collected using the Tempo Timesheets Servlet API. Please quickly read through the details of the XML format returned by Tempo Timesheets to understand the theory behind the following recipes.

Tempo Timesheets billed hours

To export the billed hours, change this line of the tempo-tool.groovy:

def m = [ hours: w.hours.toDouble(), work_date: Date.parse("yyyy-MM-dd",  w.work_date as String), username: w.username, billing_key: w.billing_key, work_description: w.work_description, full_name: w.user_details.full_name, billing_name: w.Billing.@name, activity_name: w.activity_name ]

...to:

def m = [ hours: w.hours.toDouble(), billed_hours: w.billed_hours.toDouble(), work_date: Date.parse("yyyy-MM-dd",  w.work_date as String), username: w.username, billing_key: w.billing_key, work_description: w.work_description, full_name: w.user_details.full_name, billing_name: w.Billing.@name, activity_name: w.activity_name ]

(Note: the property billed_hours was added.)

Having this change, use this expression in your templates:

${worklog.billed_hours}

Tempo Timesheets custom worklog attributes

To export the custom worklog attributes, change this line of the tempo-tool.groovy:

def m = [ hours: w.hours.toDouble(), work_date: Date.parse("yyyy-MM-dd",  w.work_date as String), username: w.username, billing_key: w.billing_key, work_description: w.work_description, full_name: w.user_details.full_name, billing_name: w.Billing.@name, activity_name: w.activity_name ]

...to:

def m = [ hours: w.hours.toDouble(), work_date: Date.parse("yyyy-MM-dd",  w.work_date as String), username: w.username, billing_key: w.billing_key, work_description: w.work_description, full_name: w.user_details.full_name, billing_attributes: w.billing_attributes, billing_name: w.Billing.@name, activity_name: w.activity_name ]

(Note: the property billing_attributes was added.)

Having this change, use this expression in your templates:

${worklog.billing_attributes}

Please note that the worklog attributes are returned as a single comma-separated string, like: "Country=Germany,Region=EMEA,Foobar=123". Trivially, it should be split at the comma-character to get the individual attribute name-value pairs, and then split at the equals sign character to separate names and values.

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. 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. "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 contains the word "all" → "all fields" mode.
2. Otherwise → "current fields" mode.

Customizing Excel views for all 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" 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 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!

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 (since app version 8.1.0)
issue-navigator-with-tempo-worklogs.xlsx	issue-navigator-with-repeating-tempo-worklogs.xlsx (since app version 2.4.0)
issue-navigator-with-worklogs.xlsx	issue-navigator-with-repeating-worklogs.xlsx (since app version 2.4.0)

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.

Exporting in a different language without switching locales

Excel files are exported using the language selected in the Jira user's profile who initiated the export. Sometimes, although you are using English, want to create exports in German without switching your language to German for the time of export. This is also doable.

Localized texts are produced by the bean called $i18n which is normally initialized to use the language of the current user. You can, however, replace the default instance on the fly by instantiating a new object with the same name, but using a specific locale!

In the following example we change the locale to German:

1. Create the one-line Groovy script named locale-tool.groovy:

   // awkward constructor invocation to avoid classloading problems
   i18n = i18n.getClass().forName('com.atlassian.jira.web.bean.I18nBean').getDeclaredConstructor(String.class).newInstance("de_DE")
   

2. Execute it in your template:

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

3. From this point, all calls on $i18n will produce German texts.

Tip: see the commonly used locales in the Jira manual.

Productivity tips

How to work fast without uploading Excel templates and Groovy scripts again and again? (devmode)

When you work on a more complicated Excel template or script, you may find it inconvenient that you need to upload the modified files to Jira again and again. The app supports a special type of working mode called the "developer mode" (or "devmode" in short), that helps avoiding this.

How to activate the developer mode? By specifying a single JVM parameter, you can override the app's resource loading logic.

Parameter name	Example value	Notes
jxls.dev.resourceDir	/path/to/my/templates	Excel templates and Groovy script files will be loaded from this file system directory (instead of using the files uploaded to Jira).

Note: when we say "file system", it means the file system of the Jira server, obviously. You cannot use devmode to run Jira on some external server and edit the template files on your local computer, unless you mount your local file system to server's file system.

How will you work in devmode?

1. Set the parameter above as written here. (Note: if you are running Jira as a Windows service and editing the JVM parameters in the Registry, you have to edit the already existing "Options" parameter.)
2. Open the Excel template you are working on in Microsoft Excel.
   Every time you change something, just save the file, make a new export, and you changes are immediately visible!
3. Open the Groovy script you are working on in a text editor or IDE.
   Save the file, make a new export, and your changes are visible.

Devmode will make you super-productive until you stop Jira and even after restarts, as long as the parameter settings are in place!

If you finished with the template, stop Jira, start that normally without the devmode parameter, and upload the latest versions of the template and script files. For heavy template development or scripting work, we suggest using a Jira developer instance, and "releasing" the completed templates by uploading them to the production Jira.

That's the theory, see this in action:



Building Jira Excel templates in "devmode" for Better Excel Exporter

If you want to confirm that you successfully turned on the devmode, just look at your Jira log. Since app version 2.4.0, an entry like this appears in the log at each Excel export:

2017-02-27 16:07:11,085 http-bio-8080-exec-9 WARN admin 967x368x1 6horcn 192.168.0.12 /rest/com.midori.jira.plugin.betterexcel/1.0/xls/xls-view/18/render [betterexcel.service.support.DevModeHelper] Better Excel Exporter is running in DevMode (see http://bit.ly/2l4dEFI)

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.