Excel template file format
The template file can contain any Excel content as well as certain placeholders. The exported file is identical to the template, except that the placeholders are replaced with the values of fields in the Jira issue result set. When the export process encounters a row containing expressions of the form [[name]] in one or more columns, where “name” is the name of a Jiraissue field, it replaces that row with the values of the named fields of the first issue in the result set. It then inserts new rows after this row, one row for each of the remaining issues in the result set.
Examples of templates
Typically, a template file contains a header row with names for each of the Jira issue fields to be exported in columns. The next row contains placeholders for those fields in corresponding columns. Above and below these two rows the template can contain anything. For example, it could contain an explanation of the information being presented in the file.
Here is a simple example of an Excel template (basic_template.xlsx):
Requirement ID | Status | Summary |
[[key]] | [[status]] | [[summary]] |
If there are three issues in the result set, the exported file contains four rows, the first as seen above, and three after that for the issues with columns containing the values of the fields key, status and summary.
More than one placeholder may appear in a column and literal text can be included for purposes such as formatting. In the following example, instead of having the key and summary appear in different columns, as above, we put the values into a column together, separated by a colon and space:
Requirement | Status |
[[key]]: [[summary]] | [[status]] |
Additionally, the special placeholders [[$header]] and [[$requirementsPathSplitted]]may be used. The first appears in the header row and the second appears in the same column of the next row. Here is an example of such a template (template_requirementspathsplitted.xlsx):
Requirement ID | Status | Summary | [[$header]] |
[[key]] | [[status]] | [[summary]] | [[$requirementsPathSplitted]] |
When the export process encounters rows containing these expressions it splits the folder path into its elements and inserts columns for each element. The header for the first element of the path is the name of the project, and the headers of the remaining elements are “Level 1”, “Level 2” and so on. The following rows contains the values of the elements of the path for each issue in the corresponding column.
Here are examples of Excel templates using the new context items and properties:
Tree:
View file name tree_template.xlsx Status colour Green title v4.18 Coverage:
View file name coverage_template.xlsx Status colour Green title v4.18 Baseline:
View file name baseline_template.xlsx Status colour Green title v4.19
Context
items and propertiesThe following fields may appear as placeholders. Properties are only available in version 4.18.0 upward.
A custom field can be referred to either by its name (e.g. Classification) or by its internal Jira ID in the format customfield_<ID> (e.g. customfield_10221).
System Fields
Property
Value
key
.label : Retrieve field name of Jira
.url : Add hyperlink to Jira issue or Confluence page
.link : Add hyperlink to issue or page in R4J
Status colour Green title baseline Status colour Green title V4.19
Issue key, Folder name, Confluence page
issueType
.label : Retrieve field name of Jira
Issue type, "Folder", "Confluence Page"
summary
.label : Retrieve field name of Jira
Issue summary, folder name, Confluence page title
description
.label : Retrieve field name of Jira
.wiki : Render content, images (as links to Jira), tables not rendered (add as markup), attachments as links to Jira
Issue description, folder description, Confluence page content
creator
.label : Retrieve field name of Jira
.displayname: Render login name as display name. Not applicable for folder.
Issue creator, null for folder, Confluence page creator - login name
created
label : Retrieve field name of Jira
Issue created, null for folder, Confluence page created date. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
updated
.label : Retrieve field name of Jira
Issue updated, null for folder, Confluence page last modification date. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
linkedIssues
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Shows the link and the issue separated by colon
name
| Status | ||||
|---|---|---|---|---|
|
| Status | ||||
|---|---|---|---|---|
|
<default>: .row : Top-down direction
.row : Top-down direction
.col: Sidewards direction
Baseline name
fields
| Status | ||||
|---|---|---|---|---|
|
| Status | ||||
|---|---|---|---|---|
|
<default>: .row : Top-down direction
.row : Top-down direction
.col: Sidewards direction
.value : Lists the field value from Jira for all tree field context items and baseline fields
.row : Top-down direction
.col: Sidewards direction
Baseline item fields stored with issue
path
| Status | ||||
|---|---|---|---|---|
|
| Status | ||||
|---|---|---|---|---|
|
<default>: .row : Top-down direction
.row : Top-down direction
.col: Sidewards direction
Baseline item path
Other System Fields
Property
Value
affectedVersions
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Affected versions
assignee
.label : Retrieve field name of Jira
.displayname: Render login name as display name
Assignee
attachments
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Attachment names
comments
.label : Retrieve field name of Jira
.wiki : Render content, images (as links to Jira), tables not rendered (add as markup), attachments as links to Jira
.multiline : Multi line inside cell for multivalued fields
.note : Export Jira comment as note
Comments
components
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Component names
dueDate
.label : Retrieve field name of Jira
Due date. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
environment
.label : Retrieve field name of Jira
.wiki : Render content, images (as links to Jira), tables not rendered (add as markup), attachments as links to Jira
Environment
fixVersions
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Fix versions
labels
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Labels
logWork
.label : Retrieve field name of Jira
Log work (in seconds)
originalEstimate
.label : Retrieve field name of Jira
Original estimate (estimated time in seconds)
storyPoints
.label : Retrieve field name of Jira
Story points
parentLink
.label : Retrieve field name of Jira
Parent link
priority
.label : Retrieve field name of Jira
Priority
project
.label : Retrieve field name of Jira
Project
remainingEstimate
.label : Retrieve field name of Jira
Remaining estimate (estimated time in seconds)
reporter
.label : Retrieve field name of Jira
.displayname: Render login name as display name
Login name of issue reporter
resolution
.label : Retrieve field name of Jira
Resolution
resolved
.label : Retrieve field name of Jira
Resolved. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
securityLevel
.label : Retrieve field name of Jira
Security level
status
.label : Retrieve field name of Jira
Status
subTasks
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Sub-tasks
targetEnd
.label : Retrieve field name of Jira
Target end. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
targetStart
.label : Retrieve field name of Jira
Target start. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
team
.label : Retrieve field name of Jira
Team
timeSpent
.label : Retrieve field name of Jira
Time spent
Custom Field Types
Property
Value
Checkboxes
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Checkboxes custom fie type
Date Picker
.label : Retrieve field name of Jira
Date picker custom field type. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
Date Time Picker
.label : Retrieve field name of Jira
Date time picker custom field type. Store as Excel number (e.g. 44972.4603125), show as Excel "Date" format, e.g. "2/15/2023". Take time zone from user profile.
Group Picker (multiple groups)
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Group Picker (multiple groups) custom field type
Group Picker (single group)
.label : Retrieve field name of Jira
Group Picker (single group) custom field type
Labels
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields.
Labels custom field type
Number Field
.label : Retrieve field name of Jira
Number Field custom field type
Parent Link
.label : Retrieve field name of Jira
Parent link custom field type
Project Picker (single project)
.label : Retrieve field name of Jira
Project picker custom field type
R4J Issue Revision
.label : Retrieve field name of Jira
R4J issue revision custom field type
R4J Suspect
.label : Retrieve field name of Jira
R4J suspect custom field type
Radio Buttons
.label : Retrieve field name of Jira
Radio buttons custom field type
Select List (cascading)
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Select list (cascading) custom field type
Select List (multiple choices)
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Select list (multiple choices) custom field type
Select List (single choice)
.label : Retrieve field name of Jira
Select list (single choice) custom field type
Target end
.label : Retrieve field name of Jira
Target end custom field type
Target start
.label : Retrieve field name of Jira
Target start custom field type
Team
.label : Retrieve field name of Jira
Team custom field type
Text Field (multi-line)
.label : Retrieve field name of Jira
multiline : Multi line inside cell for multivalued fields
Text field (multi-line) custom field type
Text Field (read only)
.label : Retrieve field name of Jira
Text field (read only) custom field type
Text Field (single line)
.label : Retrieve field name of Jira
Text field (single line) custom field type
URL Field
.label : Retrieve field name of Jira
URL field custom field type (rendered as hyperlink)
User Picker (multiple users)
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
.displayname: Render login name as display name
User picker (multiple users) custom field type
User Picker (single user)
.label : Retrieve field name of Jira
.displayname: Render login name as display name
User picker (single user) custom field type
Version Picker (multiple versions)
.label : Retrieve field name of Jira
.multiline : Multi line inside cell for multivalued fields
Version picker (multiple versions) custom field type
Version Picker (single version)
.label : Retrieve field name of Jira
Version picker (single version) custom field type
Other Custom Field Types
Property
Value
Epic Color
.label : Retrieve field name of Jira
Epic color custom field type
Epic Link
.label : Retrieve field name of Jira
Epic link custom field type
Epic Name
.label : Retrieve field name of Jira
Epic name custom field type
Epic Status
.label : Retrieve field name of Jira
Epic status custom field type
Sprint
.label : Retrieve field name of Jira
Items and properties
See documentation here.
Limitations
Only single sheet Excel templates are supported.
Exporting attachments is not supported.
Export is only available from the R4J Tree View and Coverage View, not from the standard Jira search results in the issue navigator. (This means you cannot determine the exported result set using JQL or saved queries.)