In this page

The Excel file rendering process

Better Excel Exporter generates Excel spreadsheets by merging Excel template spreadsheets with Jira data.

You create a regular spreadsheet in Excel, containing special placeholder texts (like ${issue.summary}), it is called the template, and upload it to Jira. Then, every time you want to make an export using that template, Jira will merge the current data into the template, by replacing the placeholder texts with actual values. For example, ${issue.summary} will be replaced with the text "Null pointer exception when changing the description". This process is called rendering.

This diagram shows a conceptual drawing about the rendering process:

The rendering process has the following inputs:

  1. The issues that are to be exported.
    Although the issues are the core of the data, it is really just the starting point. Through the issues you also get access to further information in the Jira data model, like projects, people, workflows, versions, sprints.
  2. The template spreadsheet.
    It defines the content and the format of the output, and also controls the rendering process (e.g. by evaluating conditions, running scripts).
  3. Additional resources like scripts, for example.

The rendering context

The rendering context is the set of variables and tools you can use in your template spreadsheet and in your Groovy scripts (optional, see this later). Every item in the context is identified by a unique name, just like a variable in a programming language. For example, the issues to be exported are available under the name $issues.

Velocity objects for templates

This table contains the domain objects available in the context:

Domain Object Description
$ctx The Velocity context itself.
$user User instance representing the currently signed-in user, i.e. the user who initiated the export.
$i18n I18nHelper instance that allows accessing internationalized Jira texts (like field names) while rendering.
$xlsView Represents the "export type" which was selected in the Export menu to initiate the document generation.
You can access its public properties by the expressions ${xlsView.name}, ${xlsView.description} and ${xlsView.templateName}.
$currentDate Date of the document generation.
$title String title for the document.
See this section for details.
$issues The Collection that stores the Issue objects to export. It contains only one item if the app was invoked from the Issue Details screen (single issue export), or multiple item when the app was invoked from Issue Navigator (multiple issue export).
You can iterate through this collection using the <jt:forEach> tag or using the implicit iteration technique. Make sure you read this section for details.
$searchRequest SearchRequest that is available only if the document is generated from a "search": browsing the issues with the Issue Navigator, executing filters, running free text searches, etc.
Not available for single issue exports.

You are, of course, not limited to using these objects only. You can access tons of other information by navigating from these starting points in the object graph.

You can, for example, retrieve the number of versions in an issue's project with the following JEXL expression:

${issue.projectObject.versions.size()}
Velocity tools for templates

First, you can use the same tools that are available for Jira email templates also in Excel templates. These offer basic functionality for formatting, escaping and other common use cases.

You can additionally use:

Component / Tool Description
$changeHistoryManager ChangeHistoryManager returns the issue field value changes by various criteria.
Use this to export issue updates, workflow transition histories or metrics calculated from those.

Groovy example:
def changeHistories = changeHistoryManager.getChangeHistoriesForUser(issue, user)
$columnLayoutManager ColumnLayoutManager gives access to various Issue Navigator column layouts: a saved filter's or search's own column layout, the current user's own column layout, and the system default column layout.
This manager is particularly useful if you want to export an issue list, but reusing the column layout definition the user configured in Issue Navigator.
$commentManager CommentManager is used to retrieve the issue comments.

Groovy example:
def comments = commentManager.getCommentsForUser(issue, user)
$componentAccessor ComponentAccessor can be used to get references to $projectManager, $userManager, $permissionManager and other Jira core components, that are not directly available in the Velocity context.
You will use this when developing templates and scripts that require components not listed in this table.

Groovy example:
def projectManager = componentAccessor.projectManager
$componentManager ComponentManager is the deprecated (legacy) way to get references to Jira core components.
You should normally use $componentAccessor (see the previous item), and revert to using this only in Jira versions prior to 5.2.
Completely removed in app version 3.5.0.
$customFieldManager CustomFieldManager supports working with custom fields.

Groovy example:
def customFields = customFieldManager.getCustomFieldObjects(issue)
$fieldScreenRendererFactory FieldScreenRendererFactory allows you to obtain field screen renderers, to check which custom fields are added to what screens and tabs.
$fieldVisibilityManager FieldVisibilityManager returns whether a custom field is visible for an issue.

$issueLinkManager IssueLinkManager can return the inward / outward issue links (for example, "duplicated by" and "duplicates").

Groovy example:
def linkCollection = issueLinkManager.getLinkCollection(issue, user)
$issueViewUtil IssueViewUtil is legacy collection of utility methods.
All functionality offered by this is now available via "smarter" objects in the Velocity context.
$jiraDurationUtils JiraDurationUtils export nice time duration strings in long- ("2 days 5 hours") or compact format ("2d 5h").
$tableLayoutFactory TableLayoutFactory helps to construct custom column layouts.
This is useful if you want to have a dynamically changing column layout in your Excel spreadsheets, identical with the one you currently use see in the Issue Navigator.
$worklogManager WorklogManager gives access to the "logged work" records.
You will use it if you need to process or export work log information.

Groovy example:
def worklogs = worklogManager.getByIssue(issue)
$workRatio WorkRatio calculates Jira's special "work ratio" metric.

$stringutils StringUtils offers a lot of useful utility methods for working with strings.

JEXL expression example:
${stringutils.countMatches(issue.summary, ".")}

Expressions for templates

These are the best resources to find the template language expressions for your own templates:

  1. The Expressions Reference Manual gives you categorized expressions for all the frequent needs, that you can just copy to your own templates.
  2. The code of the default templates shipped with the app are also worth a deeper look. We offer several templates built for real-life use cases, so make sure to check the default templates that export the same data or work similar to your own templates.
  3. To better understand the data model and the possibilities, study the Jira domain model documentation. You will learn which class offers what properties and what methods, and their general responsibilities.

Exporting issues

Iterating over issues

The very first thing you will want to do is iterating over the issues that are to be exported. You can iterate over the collection available via $issues in two rather different ways:

  • Implicit iteration: this technique is more concise, more readable, but less flexible, therefore it is recommended only for simple cases.
  • Explicit iteration using the <jt:forEach> tag: recommended for most real-world use cases.
Implicit iteration

This is super-simple. Let's see a concrete example by creating a template spreadsheet with two rows:

| Key           | Summary           | Assignee                           | Status                       |
| ${issues.key} | ${issues.summary} | ${issues.assigneeUser.displayName} | ${issues.statusObject.name} |

As a good practice, let's freeze the first row and add some styling so that it looks like this in Excel:

Important: for the implicit iteration, you must use the plural form $issues (i.e. the precise name of the collection) in the expressions!

How this works: the renderer will recognize the collection name, iterate over its items and render each issue into a separate row by duplicating the second row. When evaluating the expressions, the collection name will be "substituted" with the current item.

So it will result in this output:

As you can see, aside from the precisely using the plural form (collection name) in each expression, there is no special tag used or other explicit indication that an iteration should take place. That is why it is called an implicit iteration.

Although implicit iteration is more limited than the other technique (detailed in the next section), its behaviour can be configured to some extent. Please read this article about the configuration options.

Explicit iteration

Although implicit processing is easier to use, iterating over the issues using the <jt:forEach> tag is a better choice for non-trivial cases.

Some examples when explicit iteration better fits the bill:

  • filtering collection items
  • re-ordering collection items
  • grouping collection items
  • nested iterations (iterating over sub-collections of collections)
  • iterating over multiple collections simultaneously

If you are unsure which technique to choose, just use explicit iterations with the <jt:forEach> tag. With that, the previous example would look like this:

| Key                                                    | Summary          | Assignee                           | Status                                  |
| <jt:forEach items="${issues}" var="issue">${issue.key} | ${issue.summary} | ${issues.assigneeUser.displayName} | ${issue.statusObject.name}</jt:forEach> |

Important: you must pass the collection object to the items attribute and must specify another name in var, under which the current item will be available. As a convention, var is the singular form of the collection's name. For example, while you iterate over the collection $issues (plural), you typically want to access the current item as $issue (singular). (This is not a hard rule, you can use any name.)

How this works: the tag will duplicate the second row for each item, as expected. Within the iteration's body, the current item will be accessible as $issue (the variable name specified in var). Therefore all expressions must use the singular form $issue so that the renderer can evaluate each issue in its own row.

What makes this technique more flexible is that within the iteration's body you can do anything you want with the current item's object (even passing to scripts). You can also freely combine the <jt:forEach> with other tags.

To learn more about the <jt:forEach>tag, read the tag documentation. We also recommend studying the default templates shipped with the app for inspiration.

Exporting issue properties

You can access the issue properties using the well-known dot notation, separating the object names with a dot character.

For example, when the Issue class has a getter like this:

IssueType getIssueTypeObject()

then you can call that by omitting the "get" prefix and using the correct lowercase first character:

${issue.issueTypeObject}

Similarly, as the IssueType class has a getter like this:

String getName()

you can easily export the issue's type name with a single expression:

${issue.issueTypeObject.name}

Some more examples:

| Issue summary:           | ${issue.summary}                  |
| Assignee's full name:    | ${issue.assigneeUser.displayName} |
| Enclosing project's key: | ${issue.projectObject.key}        |

When working with the Jira data model objects, your primary source of information will be the Jira Javadocs.

More on expressions

The expressions introduced in the previous section are written in the JEXL language. JEXL stands for Java Expression Language, and it is the expression language used by Better Excel Exporter internally.

Please consider reading the following pages for more information on the JEXL language and its syntax:

  • JEXL syntax reference (description of language elements and grammar)
  • JEXL examples (how to define arithmetic expressions, call methods, access properties and work with data structures)

Formatting field values

Jira and Excel represent values differently. In practice, this means that when you get an issue field's value as a Java object, and insert that into an Excel cell, its format in Excel won't always be optimal.

Why? Without telling this what to do, the renderer will just call the toString() method on the value object, and insert the resulted string to the cell. This works perfect for text type fields, but not for a multi-user picker type custom field, for instance.

You should use the $fieldHelper tool in the Excel templates to bridge this gap. It basically transforms the field values from the Java objects returned by Jira to the optimal value to be inserted into Excel.

In our previous example, a multi-user picker type custom field returns a list of user objects as value in Jira, but $fieldHelper transforms that to a comma-separated list of user display names. Similarly, time values (ex: Original estimate) are stored in milliseconds in Jira, but as Excel expects them as hours, this tool does the conversion.

This tool will be an invaluable asset when developing Excel templates, and it is super easy to use, just read on.

Basics of fields

For a solid understanding of how the exporter works, a few of the underlying concepts must be clarified. This information is critical if you plan to modify or extend the $fieldHelper tool, but might be useful otherwise, too.

There are three field categories used by the Better Excel Exporter app:

  • Jira system fields: Jira fields that are inherent parts of all issues, like summary, assignee
  • Jira custom fields: added to issues by the user, with configurable types, names, layout, etc.
  • Calculated fields: special fields, whose values are calculated on-the-fly during the export

Each of these field categories has three important properties that the rendering engine needs to know:

  • Value: the actual data to display in the Excel cell (can be altered later by the format property)
  • Cell type: Excel cell type, one of string, number or date, that represents the kind of the data the best
  • Format: Excel cell format code, only applicable to number and date cell types

Using the $fieldHelper tool

In Issue Navigator

The templates developed for Issue Navigator are typically using the $issueNavigator tool. That delegates all formatting to $fieldHelper. Thus you don't have to do anything extra to take advantage of $fieldHelper in these templates.

In custom templates

To use $fieldHelper in custom templates, execute the tool's script like this before anything else:

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

Don't forget that this tag must be processed before the first actual use of the tool. A good practice is to insert this to the first or the last cell in the first Excel row.

After the script was executed, the object $fieldHelper becomes available in the rendering context. It provides a dead simple API:

/**
 * Returns the system-, custom- or calculated field value for the issue.
 * Note: the returned value is strongly typed (String, Date, etc.).
 *
 * @param fieldId is either the ID of a system field (e.g. "assignee"),
 * 				ID of a custom field (e.g. "customfield_10123"),
 * 				or the ID of a calculated field (e.g. "Excel parent key").
 * @param columnLayoutItem (optional) should be passed when the value is
 *				to be obtained in an Issue Navigator context.
 */
public def getFieldValue(issue, String fieldId, columnLayoutItem = null)
public def getFieldValue(issue, field, columnLayoutItem = null)

/**
 * Returns the Excel cell type for the issue field.
 * It is one of "number", "date" or "string".
 */
public def getFieldCellType(String fieldId)
public def getFieldCellType(field)

/**
 * Returns the format specifier string (ex: "dd/mmm/yyyy") for the issue field.
 * The format specifier is typically used for number- or date type fields.
 */
public def getFieldFormat(String fieldId)
public def getFieldFormat(field)

As you see, it has basically three public functions to get a field's value, a field's cell type and a field's format. Each function comes with two signatures: one receives the ID of the field as argument, while the other receives the field object itself.

Convenience methods

Although we encourage accessing fields by their identifiers (that never changes), as suggested in the previous section, there are additional convenience methods to retrieve custom field values by the custom field's name.

Getting field values by field names is supported by custom fields only, because system fields ("assignee") and calculated field ("Excel parent key") have identifiers that are identical in any Jira instance. Custom fields, in contrary, have identifiers that vary from Jira to Jira (e.g. a custom field "Story Points" can have the ID "customfield_10123" in one Jira and "customfield_10245" in another). Because these vary, templates that rely on custom fields would require you to configure the custom field IDs to the ones in your Jira, prior to using the template.

So, identifying custom fields by their names in the template can make your life easier in the following ways:

  1. These allow creating templates that work immediately without requiring you to search for and enter custom field IDs to the template. For instance, a template can assume that the Tempo managed custom field is called "Account" and get its value by the name, and you don't need to search for its numerical ID.
  2. If you assume that the same custom field is called the same in your staging and production Jira, then you can easily transfer the template between them and that will work without any configuration work.

The only caveat is that your template will break if the custom field is renamed, obviously.

So, the primary convenience method is this:

/**
 * This convenience method is only to be used if you want to get a field value by the field name.
 * The getFieldValue(issue, String fieldId, columnLayoutItem = null) method is generally a safer
 * option, because that accepts the immutable field ID (e.g. "customfield_10123"), not the name.
 * This is more convenient because it can work out of the box without configuring the custom field
 * IDs in the templates, but breaks when the custom field is renamed.
 */
public def getFieldValueByName(issue, String fieldName, columnLayoutItem = null)

We offer a more bullet-proof variant of this convenience method, that allows enumerating multiple potential custom field names! For instance, if your custom field is called "Invoice Number" in one Jira, but "Recipe No." in another, or if it will be renamed from "Bug owner" to "Owner" in the next week, you can enumerate all options, and the value will be returned from the first existing custom field.

So this can handle situations when the name is not guaranteed, but there is a small number of possible options:

/**
 * This convenience method is similar to the previous one, but tolerates custom field renamings
 * by receiving multiple name variants.
 *
 * @param fieldNameVariants is an array with multiple custom field names.
 * 				The value of the first existing custom field name will be returned.
 */
public def getFieldValueByName(issue, String[] fieldNameVariants, columnLayoutItem = null)
Usage examples

Don't forget to put the field identifier between quotation marks!

// - standard methods ---------------------------------------------------------

// for system fields
${fieldHelper.getFieldValue(issue, "assignee")}

// for custom fields (by ID)
${fieldHelper.getFieldValue(issue, "customfield_10015")}

// for calculated fields
${fieldHelper.getFieldValue(issue, "User is active")}

// - convenience methods ------------------------------------------------------

// for custom fields (by name)
${fieldHelper.getFieldValueByName(issue, "Sprint")}

// for custom fields (by multiple potential names)
${fieldHelper.getFieldValueByName(issue, [ "Time to resolution", "Time to closing" ])}
System fields

This is the list of the system field identifiers to be passed to $fieldHelper:

Field IdentifierDescription
aggregateprogressProgress percentage (aggregated from sub-tasks)
aggregatetimeestimateRemaining estimated time (aggregated from sub-tasks)
aggregatetimeoriginalestimateOriginal estimated time (aggregated from sub-tasks)
aggregatetimespentSpent time (aggregated from sub-tasks)
assigneeAssignee
attachmentAttachments (don't use directly)
commentComments (don't use directly)
componentsComponents
createdCreation date
creatorCreator
descriptionDescription
duedateDue date
environmentEnvironment
fixVersionsFix versions
issuekeyIssue key
issuelinksLinked issues (don't use directly)
issuetypeIssue type
labelsLabels
lastViewedLast viewed (don't use directly)
numberIssue number
priorityPriority
progressProgress percentage
projectEnclosing project
reporterReporter
resolutionResolution
resolutiondateResolution date
securitySecurity level
statusStatus
subtasksSub-tasks (don't use directly)
summarySummary
timeestimateRemaining estimated time
timeoriginalestimateOriginal estimated time
timespentSpent time
timetrackingTime tracking data (don't use directly)
thumbnailAttachment image thumbnails (don't use directly)
updatedUpdate date
versionsAffects versions
voterVoters
votesNumber of votes
watcherWatchers
watchesNumber of watchers
worklogWorklogs (don't use directly)
workratioWork ratio

Custom fields

Custom field IDs are always in the format customfield_12345. The text prefix is always customfield_, but the number after the underscore is changing.

Assuming you know the custom field's name, to find out the ID:

  1. Login to Jira as administrator.
  2. Go to AdministrationIssuesCustom Fields.
  3. Find the custom field by its name.
  4. Click the "cog" icon, move your cursor above one of the View, Configure, Edit or Translate links in the dropdown menu.
  5. The URL will end with something like ...?customFieldId=10001 or ...?id=10001. This is the number you are looking for!

Modifying the field value formats

If you want to modify the output for certain fields globally (in all templates using $fieldHelper), you are welcome to modify the code in field-helper-tool.groovy. Examples can be changing the user picker custom fields to export the users' login names instead of the users' display names, or changing the preferred date format from "dd/mmm/yyyy".

field-helper-tool.groovy is nicely written, easy to read and modify. The most important methods that you may want to modify are:

private def getSystemFieldValue(issue, fieldId)
private def getCustomFieldValue(issue, customField, columnLayoutItem)
private def getCalculatedFieldValue(issue, customField)

private def getSystemFieldCellType(fieldId)
private def getCustomFieldCellType(customField)
private def getCalculatedFieldCellType(customField)

private def getSystemFieldFormat(fieldId)
private def getCustomFieldFormat(customField)
private def getCalculatedFieldFormat(customField)

The method names exactly describe what the methods do. (Note: a system field is a field that is neither a custom field, nor a calculated field. Examples: Status, Resolution or Summary.)

Calculated fields

A calculated field in Better Excel Exporter is a field that does not actually exist in Jira, but dynamically calculated during the export.

Calculated fields are identified by unique names, as in the table below. Their value, data type (ex: text, number, date) and data format specifier (ex: "0.00") are calculated by a customizable Groovy script.

The values can be calculated in any possible way, utilizing any resource. Examples:

  1. Return the text of the latest comment of an issue.
  2. Get the value of multiple fields and combine them using some logic, that involves if-then decisions and a math formula.
  3. Connect to an external API and retrieve values from that.

Out of the box the following calculated fields are available:

Variable name Description
Excel parent key Key of the parent issue or a blank string if the current issue is not a sub-task.
Based on the issueKeyAsLink boolean configuration variable in field-helper-tool.groovy, this is rendered as a clickable link (default) or as plain text.
(since 2.4.0)
Excel comments Text, user and date of all comments.
It is a longer block of text that can be inserted into a single Excel cell. If you need only the first or last comments, and more granularity, see the next items.
Excel first comment Text, user and date of the first (earliest) comment.
It is a block of text that can be inserted into a single Excel cell. If you need more granularity or better data types, see the next 3 items.
Excel first comment text The actual text of the first comment.
Excel first comment user The user who added the first comment.
Excel first comment date The date when the first comment was added.
Excel last comment Text, user and date of the last (latest) comment.
It is a block text of that can be inserted into a single Excel cell. If you need more granularity or better data types, see the next 3 items.
Excel last comment text The actual text of the last comment.
Excel last comment user The user who added the last comment.
Excel last comment date The date when the last comment was added.

Using calculated fields

In Issue Navigator

The templates that use the column layout in Issue Navigator must be tricked a little. As the calculated fields are not actual Jira fields, they cannot be added to the columns visible in Issue Navigator, i.e. cannot be exported.

How to overcome this? Add a dummy custom field that acts as a placeholder for the calculated field, and add that to your column configuration.

Steps:

  1. Create a new Jira custom field with the name of the calculated field you wanted to export. It can be of any type, as the value returned by the script, not the value returned by the custom field, will be exported.
  2. Add that field to your Issue Navigator columns. Although the corresponding column will be displayed with all blank values, don't worry.
  3. Make an export and voila, the Excel column is filled with the calculated values!
  4. Have a chocolate.
Custom templates

If you already have a custom field with the name of a calculated field, you can simply refer to it like that:

${fieldHelper.getFieldValue(issue, "customfield_10000")}

$fieldHelper also supports calculated fields by passing their names as field ID: (since 1.1.1)

${fieldHelper.getFieldValue(issue, "Excel last comment text")}

Using this syntax, you do not need to create any dummy custom fields to use calculated fields (as you would do in the Issue Navigator).

Naming collisions

If you are not careful enough, your calculated field names might be identical ("collide") with other field names. Here is what you should expect in those situations:

  • It is completely valid to reuse the name of a system field as a calculated field name (ex: "Summary"), but it may confuse your users.
  • If you reuse the name of an existing custom field, the calculated field will override that in the output. Instead of exporting the actual value stored in the custom field, the value generated by the Groovy script will be exported.
  • If you reuse the name of an existing calculated field, the one appearing higher in the switch-case blocks in the Groovy handler methods will take precedence. That means you should always keep a consistent ordering of case sections in all handler methods. (Which is a best practice any way.)
  • You might create mutiple distinct Jira custom fields referencing the same calculated field by their name. That will not cause any problems, the calculated field may get rendered multiple times in the output. This is probably not what you want, so remove the surplus fields.

Excel spreadsheet properties

The app generates a sensible default title and default filename for each output Excel spreadsheet. You can optionally override one or both of those via the template. This offers an easy way to generate consistent document properties.

How is the title used? Well, it is completely up to the template. Most typically, the title is displayed in the top part, in the header, in some cover page, or the template may not use it at all.

How is the filename used? It will be offered as the default filename when downloading and saving the Excel spreadsheet from the browser. Therefore, properly defining the filename via the template can eliminate the need for tedious file renames.

Default title

The default title is generated according to the following logic:

  • "issue key" for single issues (e.g. "FOO-123")
  • "search request name" for saved filters with a name (e.g. "Team Alpha Pending Tickets")
  • "Excel view name (Jira title)" for ad-hoc searches without a name (e.g. "Worklog Report (ACME Jira)")

Customizing titles

To override the title for the generated Excel spreadsheet, just assign a string to the context variable title any time during the rendering process. Please note that this variable originally contains the default title, allowing you to set the final title based on the default one. (Please see the scripting tutorial for executing scripts and defining context variables.)

In Groovy:

// "title" is a standard Java String, not a GString
title = "Pending tickets - ${new Date().format('yyyy-MM-dd')}".toString()

This example will generate timestamped titles, e.g. "Pending tickets - 2016-11-20".

Default filename

The default filename is simply the title plus the ".xlsx" file extension, e.g. "FOO-123.xlsx" or "Team Alpha Pending Tickets.xlsx". Or, if you are using an ".xlsm" template, then the filename defaults to "FOO-123.xlsm".

Customizing filenames

Similarly to the title, to override the filename, just assign a string to the context variable filename. You can do this any time during the rendering process.

In Groovy:

// "filename" is a standard Java String, not a GString
filename = "pending-tickets-${new Date().format('yyyy-MM-dd')}.xlsx".toString()

This example will generated timestamped filenames based on the title, e.g. "pending-tickets-2016-11-20.xslx".

Resources

basic-issue-list-template.xlsx — a sample Excel template to generate basic task lists. Use this as starting point for your own templates.

Next step

Use formulas and functions to export values calculated from the issue properties.

Questions?

Ask us any time.