In this page

Note that there are three techniques to implement automatic processes with Better Excel Exporter for Jira. To be sure that the XLS API is the best for you, read the Automation article before this one.

The XLS API

Better Excel Exporter for Jira provides a lightweight in-process API (Application Programming Interface) for the programs that run in Jira's JVM and want to generate Excel spreadsheet from Jira data. For sake of simplicity, this interface is called the XLS API.

Technically speaking, the XLS API can be used from any programming language that can run on the JVM (in Jira's core). Nonetheless, there are two major languages that are used in 99% of the practical situations:

  1. Groovy would be Midori's primary choice of language.
    Groovy is a very powerful dynamic scripting language that allows you to reuse your Java experience, and also making you super productive when building small Jira extensions. Midori believes that instead of building those extensions in pure Java and maintaining full-blown Jira apps, writing short and concise Groovy scripts and executing them with the popular ScriptRunner app is the most easiest way to implement custom integration logic.
  2. Java should be used when building some "really big" app (with its own user interface, model and persistence, and so on), or when adding the Excel spreadsheet exporting functionality to an existing app written in Java.

Please note that all examples in this article will be written in Groovy and will rely on the ScriptRunner app. It is totally trivial to port them to Java. As always, and you are welcome to contact us with your questions.

XLS API functions

XLS API exposes the Excel spreadsheet rendering functionality by the following super-simple self-documenting methods: 

public interface XlsApi {
	/**
	 * This is a convenience method to render Excel spreadsheets from single issues.
	 * @see #getXls(String, String, List, Map) For parameters, see the API call that supports multiple issues.
	 */
	XlsRenderingResult getXls(String templateName, String title, Issue issue, Map<String, ?> additionalModel);

	/**
	 * Renders Excel spreadsheets from the passed issues using the specified template.
	 * @param templateName identifies the template to use. Example: issue-navigator.xlsx
	 * @param title the title for the resulted Excel spreadsheet.
	 * @param issues the issues to be exported in the Excel spreadsheet.
	 * @param additionalModel additional model objects that are passed to the Excel spreadsheet renderer. They can be used in the template code as regular Velocity variables. (This is purely optional and may be just an empty map.)
	 * @return the result object containing the Excel spreadsheet filename and its actual bytes.
	 */
	XlsRenderingResult getXls(String templateName, String title, List<Issue> issues, Map<String, ?> additionalModel);
}

Note: in older versions of Better Excel Exporter for Jira, the getXls() methods returned the type RenderingResult. Starting from the 5.0.0 app version, it was changed to the more expressive XlsRenderingResult.

In the future, we plan to add further API methods, to query the templates defined, to allow creating, modifying or deleting templates and so on. Please contact us any time if you need some functionality exposed through the XLS API. We are happy to help.

Using the XLS API with Groovy

Obtaining the XLS API instance in your Groovy script, and exporting Excel spreadsheets is as simple as: 

import com.atlassian.jira.component.ComponentAccessor

def xlsApiClass = this.class.forName('com.midori.jira.plugin.betterexcel.api.XlsApi', true, ComponentAccessor.pluginAccessor.classLoader)
def xlsApi = ComponentAccessor.getOSGiComponentInstanceOfType(xlsApiClass)
def xlsResult = xlsApi.getXls("issue-navigator.xlsx", "Weekly Report", issues, [:])

The xlsResult object offers the following methods: 

// Returns the filename.
String getFileName()

// Reads the stream returned by getInputStream() to a byte array, and returns that.
// This is convenient for small documents, but in applications where the document size can
// grow large, use the stream getter (see next) instead.
byte[] getBytes()

// Returns the input stream from which the bytes resulted from the rendering (i.e. the "file")
// can be read. Do not use this after calling getBytes(), since the backing InputStream
// will already be consumed.
InputStream getInputStream()
Using the IntegrationUtils library

To make it even easier, Midori provides a small open source integration library for the Better Excel Exporter for Jira. It is called IntegrationUtils, and its aim is to provide ready-made utility functions for the most frequent problems. Functions include rendering Excel spreadsheets with a single method call, running a saved Jira filter, sending an email, adding an issue comment and so on.

All the examples in this article rely on the IntegrationUtils library which is available for various Jira version. You are encouraged to look into the IntegrationUtils.groovy file to see what functions are available, and copy or modify those to your needs.

To utilize the library, execute (evaluate in Groovy's terminology) its script in the beginning of your actual script (see this in details later). It is a good idea to keep all your actual scripts and the IntegrationUtils.groovy file in the same folder, but even in that case, you need to specify the full path for evaluate(): 

evaluate(new File("/path/to/my/post-functions/IntegrationUtils.groovy"))

The execution will add a new object called integrationUtils to your execution context. To utilize library functionality, you can invoke methods on that object like: 

xlsResult = integrationUtils.getXls("issue-navigator.xlsx", "Export of ${issue.key}", issue)
integrationUtils.sendXlsInEmail("john.doe@acme.com", "Don't forget about ${issue.key}", "Hey John!\n\nThis issue must be reviewed very soon.", xlsResult)
integrationUtils.addIssueComment(issue, transientVars.context.caller, "A reminder email was sent to John.")

Using the XLS API with Java

Using the XLS API in Java is simple, as well. For this you should be familiar with the basics of Jira app development. If you could use some sample code or you have some questions, please ask our engineers any time.

How to access the API?

  1. Declare a component import in your app descriptor: 
    <!-- atlassian-plugin.xml -->

<component-import key="xls-api" interface="com.midori.jira.plugin.betterexcel.api.XlsApi"/>
  2. Inject the API object to the instance of your own class "Foo" via its constructor: 
    // Foo.java

public Foo(XlsApi xlsApi) {
	this.xlsApi = xlsApi;
}
  3. Now you can call the XLS API functions like any regular "local" method call: 
    // Foo.java

private XlsRenderingResult renderXls(List<Issue> issues) {
	// xlsResult contains the filename and the bytes of the Excel spreadsheet
	XlsRenderingResult xlsResult = xlsApi.getXls("issue-navigator.xlsx", "Test file", issues, Collections.<String, Object> emptyMap());
	return xlsResult;
}

XLS API tutorial video

In the following sections, there are complete step-by-step guides for all major use cases. If those are not 100% applicable to your situation, you are more than welcome to ask us any time.

In addition to the precise steps, we also created a tutorial video to show this in action. This particular video demonstrates the "send Excel periodically" use case with ScriptRunner:

Scripting Better Excel Exporter with ScriptRunner for Jira
Scripting Better Excel Exporter with ScriptRunner for Jira

The script examples are accessible here.

Please note that there is a similar video available for the companion product called Better PDF Exporter. That video demonstrates the "send PDF at workflow transitions" use case. That is a perfect guide to implement "send Excel at workflow transitions" even if the file formats are different!

Generating Excel spreadsheets in Jira workflows

What are Jira post functions?

As you probably know, Jira workflows are highly configurable. The ScriptRunner app extends the possibilities further, by allowing workflow post functions be written in Groovy. (Reminder: post functions are the logic executed after a workflow transition was completed.)

If you are unfamiliar with the concept of post functions, please read these:

After you understood transitions and post function, continue reading the next sections to implement post functions that integrate Excel spreadsheet exporting to your workflows.

Sending Excel spreadsheets at workflow transitions

Configuration steps:

  1. Get the Groovy file with the code of the post function: (Tip: click "Raw" and save the file from your browser.)
  2. Get the Groovy file with the code of the integration library:
  3. Copy both files to a directory that is visible for Jira, e.g. /path/to/my/post-functions.
  4. Open SendIssueXlsPostFunction.groovy in any text editor, and:
    1. Edit the line which executes the integration library script, and add the correct path to the IntegrationUtils.groovy file: 
      evaluate(new File("/path/to/my/post-functions/IntegrationUtils.groovy"))
    2. Set the name of the template file, the title for document and possibly other settings in the section starting with "// XLS configuration".
    3. Set the email subject, email body, target email address, etc. in the section starting with "// email configuration".
  5. Now open your browser, and login to Jira as Jira administrator.
  6. Go to Administration → Issues → Workflows.
  7. Click the workflow you want to modify. (If the workflow is already in use by some issue, then Jira may not allow modifying it. In that case, create a new draft and click the draft instead of the workflow in use.)
  8. Click the transition you want to add the post function to.
  9. Click the Post Functions tab, then click Add post function.
  10. Select the Script Post-Function type, then:
    1. Jira 7.x or newer: select Custom script post-function, and enter the full path of SendIssueXlsPostFunction.groovy to the Script file input box. This should be something like /path/to/my/post-functions/SendIssueXlsPostFunction.groovy. (The error markups can be safely ignored.)
    2. Jira 6.x: select the Script Post-Function type, and enter the full path of SendIssueXlsPostFunction.groovy to the Script file path input box.
  11. After submitting it, the new post function is added in the first position of the post function chain. Click Move Down several times to move it to last position (safest).
  12. If you were working on a workflow draft, don't forget to publish your changes at this point. If you forget it, your changes will not be used.
  13. Test if it works as expected, by transition issues with the modified transition, watching the log and checking if the emails are correctly sent and received. You can fine tune the settings in SendIssueXlsPostFunction.groovy without re-editing the workflow and the transition, those changes will be picked up at the next transition execution. (In other words, the scripts themselves are not cached.)

Enjoy your new workflow!

Exporting Excel spreadsheets at workflow transitions

Configuration steps:

  1. Get the Groovy file with the code of the post function: (Tip: click "Raw" and save the file from your browser.)
  2. Get the Groovy file with the code of the integration library:
  3. Copy both files to a directory that is visible for Jira, e.g. /path/to/my/post-functions.
  4. Open ExportIssueXlsPostFunction.groovy in any text editor, and:
    1. Edit the line which executes the integration library script, and add the correct path to the IntegrationUtils.groovy file: 
      evaluate(new File("/path/to/my/post-functions/IntegrationUtils.groovy"))
    2. Set the name of the template file, the title for document and possibly other settings in the section starting with "// XLS configuration".
    3. Set the path, which specifies the full filesystem path where to write the Excel spreadsheet to, in the section starting with "// file configuration".
  5. Now open your browser, and login to Jira as Jira administrator.
  6. Go to Administration → Issues → Workflows.
  7. Click the workflow you want to modify. (If the workflow is already in use by some issue, then Jira may not allow modifying it. In that case, create a new draft and click the draft instead of the workflow in use.)
  8. Click the transition you want to add the post function to.
  9. Click the Post Functions tab, then click Add post function.
  10. Select the Script Post-Function type, then:
    1. Jira 7.x or newer: select Custom script post-function, and enter the full path of ExportIssueXlsPostFunction.groovy to the Script file input box. This should be something like /path/to/my/post-functions/ExportIssueXlsPostFunction.groovy. (The error markups can be safely ignored.)
    2. Jira 6.x: select the Script Post-Function type, and enter the full path of ExportIssueXlsPostFunction.groovy to the Script file path input box.
  11. After submitting it, the new post function is added in the first position of the post function chain. Click Move Down several times to move it to last position (safest).
  12. If you were working on a workflow draft, don't forget to publish your changes at this point. If you forget it, your changes will not be used.
  13. Test if it works as expected, by transition issues with the modified transition, watching the log and checking if the file is created successfully. You can fine tune the settings in ExportIssueXlsPostFunction.groovy without re-editing the workflow and the transition, those changes will be picked up at the next transition execution. (In other words, the scripts themselves are not cached.)

Enjoy your new workflow!

Configuring post functions through transition properties

So far we suggested you to configure the post function variables (like the target email address) right in the scripts. There is an interesting configuration alternative by using transition properties.

Jira allows defining named properties for each transition. A property consists of a text key and a text value. The idea is that post function code can access those properties, and they can be used to override the script variables.

This basically means, that if you set the variable "title" to "Foo" in the Groovy post function script and also add the property "title" (same name!) with the value "Bar" to the transition that executes the post function, then the transition property takes the precedence, and title will be equal to "Bar".

OK, what's the point? The point is that you can edit the transition properties through the Jira web interface, without dealing with the Groovy files. In typical Jira instances, it is very hard to get access to the filesystem and edit the Groovy files directly. If you have Jira administration permissions, no need for this, you can easily re-configure your post functions by editing the transition properties. Also the configuration is captured at the transition, therefore the script itself can exist only as a single instance, no need for variations.

The important thing is that in the script the values must be obtained by using the integrationUtils.getWorkflowTransitionProperty() method. (All the post function scripts shipped by Midori are using this technique.) 

to = integrationUtils.getWorkflowTransitionProperty(transitionProperties, "to", companyContractorRepresentative.emailAddress, binding.variables)

The second parameter is the property key.

The third parameter is the default value (which will be used if the transition property is not defined).

The fourth parameter is used for an even more advanced feature. You can use template variables in transition property values. For example, you can set this value to a transition property called "title": 

${issue.key} export at ${new Date().format('yyyy/MM/dd')}

It will be evaluated when executing the getWorkflowTransitionProperty() method, and the result "TEST-1 export at 2017/10/15" will be assigned to "title"!

It may sound overwhelming for the first read, but please believe, it is totally straightforward to use.

Troubleshooting

If your transition fail or you don't receive the emails, the number one advice is: check your log! The error and the root cause will very likely be written there.

All Groovy scripts support logging. The logging statements are written to the standard Jira system log. By default, the logging level is set to ERROR, which means that only very severe problems will be logged. You can conveniently increase the logging level through the Jira web interface to get more details while looking for the source of the problem. The name of the logger is defined by these lines in the Groovy files: 

log = Logger.getLogger("SendIssueXlsPostFunction.groovy")

So, just increase the logging level to DEBUG for the logger SendIssueXlsPostFunction.groovy and you will see what's going on by watching the Jira log.

There is one specific error we met a couple of times, let's see that quickly. Right after installing or upgrading the app, you may see the following error message in your log every time when your script tries to access the XLS API: 

java.lang.ClassCastException: Cannot cast com.midori.jira.plugin.betterexcel.api.impl.XlsApiImpl to com.midori.jira.plugin.betterexcel.api.XlsApi

Just restart your Jira, it will go away and will not come back. (The root cause relies in the quirky details of OSGi classloading, but the Jira restart brings the classloader to a clean state.)

Generating Excel spreadsheets in Jira services

The technique below will not work with ScriptRunner 5.6.10 or newer versions as the provided services were moved to Script Jobs. For more information, see the release notes.

What are Jira services?

Jira services allow you executing routine work periodically. With the ScriptRunner app you can write custom services in Groovy.

Utilizing these, this section explains how to generate and email Excel spreadsheets Jira issues periodically. A typical example can be sending a weekly report as Excel spreadsheet email attachment in every seven days.

Sending Excel spreadsheets from a Jira filter periodically

Configuration steps:

  1. Get the Groovy file with the code of the service: (Tip: click "Raw" and save the file from your browser.)
  2. Get the Groovy file with the code of the integration library:
  3. Copy both files to a directory that is visible for Jira, e.g. /path/to/my/services.
  4. Open SendFilterXlsService.groovy in any text editor, and:
    1. Edit the line which executes the integration library script, and add the correct path to the IntegrationUtils.groovy file: 
      evaluate(new File("/path/to/my/services/IntegrationUtils.groovy"))
    2. Set the username of the user which will be used to run the filter, the ID of the filter and some other settings in the section starting with "// XLS configuration".
    3. Set the email subject, email body, target email address, etc. in the section starting with "// email configuration".
  5. Now open your browser, and login to Jira as Jira administrator.
  6. Go to Administration → System → Services.
  7. Fill the Add service form:
    1. Enter a descriptive name like "XLS Task Report Sender Service".
    2. Enter com.onresolve.jira.groovy.GroovyService as class.
    3. Set Delay to the length of the period in minutes. Set this to a relatively low value, like 1, for the testing period to send a test email every minute (Cron Expression: * * * * * ?).
      When it looks OK, set it to 60 minutes for an hourly report, or 1440 for a daily report.
    4. Submit the form.
    5. In the next screen, enter the path of the Groovy script to Input File. This should be something like /path/to/my/services/SendFilterXlsService.groovy.
  8. Test if it works as expected, watching the log and checking if the emails are correctly sent and received. You can fine tune the settings in SendFilterXlsService.groovy without re-editing the service settings, those changes will be picked up at the next service run. (In other words, the scripts themselves are not cached.)
    Don't forget to set the final Delay and to have some chocolate when you are done.

Enjoy your new service!

Sending Excel spreadsheets from an issue periodically

Sometimes you need to export just a single issue, not a full filter, periodically. This section explains how to implement that in 5 minutes.

Configuration steps:

  1. Get the Groovy file with the code of the service: (Tip: click "Raw" and save the file from your browser.)
  2. Get the Groovy file with the code of the integration library:
  3. Copy both files to a directory that is visible for Jira, e.g. /path/to/my/services.
  4. Open SendIssueXlsService.groovy in any text editor, and:
    1. Edit the line which executes the integration library script, and add the correct path to the IntegrationUtils.groovy file: 
      evaluate(new File("/path/to/my/services/IntegrationUtils.groovy"))
    2. Set the username of the user which will be used to run the filter, the key of the issue and some other settings in the section starting with "// XLS configuration".
    3. Set the email subject, email body, target email address, etc. in the section starting with "// email configuration".
  5. Now open your browser, and login to Jira as Jira administrator.
  6. Go to Administration → System → Services.
  7. Fill the Add service form:
    1. Enter a descriptive name like "XLS Task Report Sender Service".
    2. Enter com.onresolve.jira.groovy.GroovyService as class.
    3. Set Delay to the length of the period in minutes. Set this to a relatively low value, like 1, for the testing period to send a test email every minute (Cron Expression: * * * * * ?).
      When it looks OK, set it to 60 minutes for an hourly report, or 1440 for a daily report.
    4. Submit the form.
    5. In the next screen, enter the path of the Groovy script to Input File. This should be something like /path/to/my/services/SendIssueXlsService.groovy.
  8. Test if it works as expected, watching the log and checking if the emails are correctly sent and received. You can fine tune the settings in SendIssueXlsService.groovy without re-editing the service settings, those changes will be picked up at the next service run. (In other words, the scripts themselves are not cached.)
    Don't forget to set the final Delay and to have some chocolate when you are done.

Enjoy your new service!

Troubleshooting

Service script errors should be inspected in the same way as workflow transition script errors, described in the workflow troubleshooting section.

Questions?

Ask us any time.