Recipes

In this page

User interface

Hiding the "Export" menus on Jira Agile boards

If you never export directly from the Jira Agile 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, sorting the issues passed to the Excel template

Filtering issues

In most of the cases, you don't need a secondary filter. 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 is not sufficient, or you really need to execute a second filter, 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 'DEV' project and ignore others -->
<jt:forEach items="${issues}" var="issue" where="${issue.key.contains('DEV')}">...</jt:forEach>

Sorting issues to export those in a different order

In most of the cases, you can flexibly sort issues in JQL using ORDER BY.

If you need to sort according to some more complex logic, follow this pattern:

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

   sorter = new SorterTool()
   
   public class SorterTool {
   	public sort(issues) {
   		return issues.sort { a, b -> a.summary <=> b.summary }
   	}
   }
   

2. Execute it in your template:

   <mt:execute script="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="${sorter.sort(issues)}" var="issue">

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

Sorting the comments as "newest first"

Comments for an issue are shown in ascending order by default, i.e. the most recent one is displayed at the bottom. To reverse this order:

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

   commentSorter = new CommentSorterTool()
   
   class CommentSorterTool {
   	def descending(comments) {
   		return comments.sort { a, b -> b.created <=> a.created }
   	}
   }
   

2. Execute it in your template:

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

3. Change the expression

   <jt:forEach items="${commentManager.getCommentsForUser(issue, user)}" var="comment">

   to:

   <jt:forEach items="${commentSorter.descending(commentManager.getCommentsForUser(issue, user))}" var="comment">

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

Since app version 2.2.0, the default behaviour is that the issue key and summary columns will be exported as hyperlinks. (In pre-2.2.0 versions, these were static texts.)

To return to static texts, 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 isssues, 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">

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

project = FOOBAR 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.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 { it -> 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.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 { it -> new TemplateIssue(it, ComponentAccessor.fieldLayoutManager, ComponentAccessor.rendererManager, ComponentAccessor.customFieldManager, null, null) }
     	}
     }
     

2. Execute it in your template:

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

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=FOOBAR ORDER BY summary')}" var="issue">[${issue.key}] ${issue.summary}</jt:forEach>

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

If you need to connect to REST services, that's pretty easy, too.

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)
   
   issueRest = new JsonSlurper().parseText(jsonString)
   

2. Execute it in your template:

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

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

   ${issueRest.key}
   ${issueRest.fields.summary}
   ${issueRest.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()
   dataRest = jsonSlurper.parseText(new URL("http://services.groupkt.com/country/get/iso2code/US").text)
   

2. Execute it in your template:

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

3. Use the dataRest object to access the returned information in the template:

   ${dataRest.RestResponse.result.name}
   ${dataRest.RestResponse.result.alpha2_code}
   

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-acc, 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 worklog details

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

Tempo 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 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 5 variations of the issue-navigator.xlsx template, which offer further configuration options detailed below. Remember that you can flexibly use any combination of these modes, giving solutions to every kind of worksheet needs.

All fields vs. selected fields modes

Templates can either export all fields or only the ones currently visible in Issue Navigator. Technically speaking, there is a customizable logic that decides whether the export should utilize the table layout associated with the current search (JQL, saved filter) or use a table layout that contains all fields.

The default logic is simple and intuitive: if the Excel view name case-insensitively contains the word "all", then it will export all fields. You can freely change the default logic by modifying the following method in issue-navigator.groovy:

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

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

Please note that there are default views created for each combinations: "current columns" / "all columns" modes for columns only, columns plus comments, columns plus worklogs, columns plus Tempo worklogs, columns plus change history. You can use these 2*5 views immediately after installing the app.

Repeating fields vs. non-repeating fields modes

When using the issue-navigator-with-worklogs.xlsx or the issue-navigator-with-tempo-worklogs.xlsx templates, there can be multiple rows created for each issue that has multiple worklogs. For instance, if an issue has 3 worklogs added, then the issue will be exported to 3 physical spreadsheet rows: the field values will only be exported to the topmost row, and then each worklog will appear in its own row.

In some situations, this would be useful to repeat the field values for each of those 3 rows. We call this the repeating fields mode:

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

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

Since app version 2.4.0, the repeating version of the issue-navigator-with-worklogs.xlsx is available as issue-navigator-with-repeating-worklogs.xlsx among the default templates. Similarly, the repeating version of issue-navigator-with-tempo-worklogs.xlsx is available as issue-navigator-with-repeating-tempo-worklogs.xlsx.

How to use those?

1. If you need both the default and the repeating mode, then create a new intuitively named Excel view and use the repeating template with that. For instance, add a new view name "Worklogs rep. (Current f.)" with the issue-navigator-with-repeating worklogs.xlsx template.
2. If you need the repeating mode only, then switch your existing Excel views to the repeating version of the corresponding template. For instance, switch the "Worklogs (Current fields)" and "Worklogs (All fields)" views to the issue-navigator-with-repeating worklogs.xlsx template.

Please remember that you should use the repeating mode primarily for reporting and data transfer purposes. Compared to the default 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

Loading Java classes that cannot be imported

There are times when you fail to reference a Java (Groovy) class by using an import statement, as the class is made available for apps by Jira. For example, you'd like to get the JqlQueryParser component from Jira, but the Groovy interpreter cannot import its class.

The problem is due to Jira running apps in an OSGi environment with controlled classloading. Groovy scripts run in the same environment as the app that executes those, therefore the same limits affect scripts, too.

Luckily, Groovy being a dynamic language, there is a clever trick to overcome this:

// load the class by name using the class-loader of a Jira-internal class
def clazz = ComponentAccessor.class.classLoader.loadClass("com.atlassian.jira.jql.parser.JqlQueryParser")

// don't define the type when getting the component
def jqlQueryParser = ComponentAccessor.getOSGiComponentInstanceOfType(clazz)

// we have a working query parser, yay!
def query = jqlQueryParser.parseQuery("project = CONTRACTOR and assignee = ${assignee.name}")
	

Alternating row colors (zebra stripes)

If you want to add alternating row colors to your templates, 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>
   

It is up to you to implement more complex coloring 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

Please note that Excel limits worksheet names to 31 characters!

Jira Service Desk 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. Exporting "Is met?" is just 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 = issues.get(0).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.

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

Debugging templates

Tracing variables

We use a super-simple best practice to make our templates "debuggable", both while developing it and after they are deployed to production.

This can be done simply by writing expressions temporarily into an empty cell of your template, like this:

| Key                     | Summary           | Status                      |
| ${issues.key}           | ${issues.summary} | ${issues.statusObject.name} |
|                         |                   |                             |
| Debug: ${issues.size()} |                   |                             |

Logging from scripts

See the logging from scripts section in the Scripting page.

Debugging scripts

See the debugging scripts in Jira section in the Scripting page.

Productivity

Developer mode: avoiding repeated re-uploads of Excel templates and Groovy scripts

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:



Using "devmode" to build Excel export templates fast and conveniently in Jira

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)

Questions?

Ask us any time.