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 Jira issue 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:
Coverage:
Context items and properties
The following fields may appear as placeholders. In addition, all custom fields may be used.
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
| Issue key, Folder name, Confluence page |
issueType
| Issue type, "Folder", "Confluence Page" |
summary
| Issue summary, folder name, Confluence page title |
description
| Issue description, folder description, Confluence page content |
creator
| Issue creator, null for folder, Confluence page creator - login name |
created
| 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
| Shows the link and the issue separated by colon |
Other System Fields
Property | Value |
---|---|
affectedVersions
| Affected versions |
assignee
| Assignee |
attachments
| Attachment names |
comments
| Comments |
components
| Component names |
dueDate
| 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
| Environment |
fixVersions
| Fix versions |
labels
| Labels |
logWork
| Log work (in seconds) |
originalEstimate
| Original estimate (estimated time in seconds) |
storyPoints
| Story points |
parentLink
| Parent link |
priority
| Priority |
project
| Project |
remainingEstimate
| Remaining estimate (estimated time in seconds) |
reporter
| Login name of issue reporter |
resolution
| Resolution |
resolved
| 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
| Security level |
status
| Status |
subTasks
| Sub-tasks |
targetEnd
| 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
| 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
| Team |
timeSpent
| Time spent |
Custom Field Types
Property | Value |
---|---|
Checkboxes
| Checkboxes custom fie type |
Date Picker
| 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
| 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)
| Group Picker (multiple groups) custom field type |
Group Picker (single group)
| Group Picker (single group) custom field type |
Labels
| Labels custom field type |
Number Field
| Number Field custom field type |
Parent Link
| Parent link custom field type |
Project Picker (single project)
| Project picker custom field type |
R4J Issue Revision
| R4J issue revision custom field type |
R4J Suspect
| R4J suspect custom field type |
Radio Buttons
| Radio buttons custom field type |
Select List (cascading)
| Select list (cascading) custom field type |
Select List (multiple choices)
| Select list (multiple choices) custom field type |
Select List (single choice)
| Select list (single choice) custom field type |
Target end
| Target end custom field type |
Target start
| Target start custom field type |
Team
| Team custom field type |
Text Field (multi-line)
| Text field (multi-line) custom field type |
Text Field (read only)
| Text field (read only) custom field type |
Text Field (single line)
| Text field (single line) custom field type |
URL Field
| URL field custom field type (rendered as hyperlink) |
User Picker (multiple users)
| User picker (multiple users) custom field type |
User Picker (single user)
| User picker (single user) custom field type |
Version Picker (multiple versions)
| Version picker (multiple versions) custom field type |
Version Picker (single version)
| Version picker (single version) custom field type |
Other Custom Field Types
Property | Value |
---|---|
Epic Color
| Epic color custom field type |
Epic Link
| Epic link custom field type |
Epic Name
| Epic name custom field type |
Epic Status
| Epic status custom field type |
Sprint
| Rendered as sprint name |
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.)