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, sorting the issues passed to the Excel template

Filtering issues

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 issues

In most of the cases, you can flexibly sort 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 a custom issue sorter tool (written in Groovy).

Using a custom issue sorter tool

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"

Comments for an issue are shown in ascending order by default, i.e. the newest one is displayed last.

To sort the comments in reverse order, you can:

• Sort using a custom comment sorter tool (written in Groovy).

Using a custom comment sorter tool

1. Write a short sorter class in Groovy that implements your ordering and save it as comment-sorter.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.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

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

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 creating pivot tables, pivot charts, other reports and for data transfer in case the target system expects the data this way. 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

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