Midori Better Excel Docs Example

Add to this page: https://scriptrunner.adaptavist.com/latest/jira/plugins/working-with-midori.html

Content

ScriptRunner can be used with Better Excel Plugin for JIRA [link: https://marketplace.atlassian.com/plugins/com.midori.jira.plugin.betterexcel/server/overview?utm_source=adaptavist&utm_medium=scripting_addons] to create, email or locally save your JIRA issue exports or reports in native Excel format. Better Excel Plugin is deeply customizable and allows you to use the native Excel reporting features (pivot reports, pivot charts, functions, formulas) on your JIRA data.
For more technical information about Better Excel Plugin and it's API, visit its documentation here [link: http://www.midori-global.com/products/jira-better-excel-plugin/documentation/api?utm_source=adaptavist&utm_medium=scripting_addons].

Below are two simple examples of emailing and saving a JIRA Excel export. You can, however, generate even the most complex Business Intelligence reports and more by scripting, thanks to the flexibility of ScriptRunner and Better Excel Plugin.

Emailing an Excel file after a transition

This is a simple workflow postfunction example to export a transitioned issue to an Excel file and send that attached to a custom email.

[See code-snippet1-Emailing an XLSX.txt]

Running a JQL search and exporting the result to an Excel file

This example can be installed as a Groovy service to run a JQL query, generate an Excel spreadsheet from the results and save it to the filesystem.

[See code-snippet2 - Exporting the result of a JQL search to XLSX.txt]

Code Snippets

Snippet 1

import javax.activation.DataHandler
import javax.mail.internet.MimeBodyPart
import javax.mail.internet.MimeMultipart
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.mail.util.ByteArrayDataSource
import com.atlassian.mail.Email
import com.atlassian.mail.queue.SingleMailQueueItem

// XLSX configuration
def templateName = "issue-navigator.xlsx"
def title = "My sample Excel document"

// email configuration
def to = "adam@acme.com, bob@acme.com, cecil@acme.com" // can contain multiple addresses in a comma-separated list
def subject = "Post-function executed on ${issue.key}"
def body = "See the Excel document generated from \"${issue.summary}\" in the attachment!"

// render the XLSX
def user = ComponentAccessor.jiraAuthenticationContext?.user
def xlsApiClass = this.class.forName('com.midori.jira.plugin.betterexcel.api.XlsApi', true, ComponentAccessor.pluginAccessor.classLoader)
def xlsApi = ComponentAccessor.getOSGiComponentInstanceOfType(xlsApiClass)
ComponentAccessor.jiraAuthenticationContext.setLoggedInUser(user)
def xlsResult = xlsApi.getXls(templateName, title.toString(), [ issue ], [:])

// send email
to.tokenize(",").each() {
def dataSource = new ByteArrayDataSource(new ByteArrayInputStream(xlsResult.bytes), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
def attachmentPart = new MimeBodyPart(dataHandler : new DataHandler(dataSource), fileName : xlsResult.fileName)
def multipart = new MimeMultipart("mixed")
multipart.addBodyPart(attachmentPart)

def email = new Email(it.trim())
email.subject = subject
email.body = body
email.multipart = multipart
ComponentAccessor.mailQueue.addItem(new SingleMailQueueItem(email))
}

Snippet 2

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter
import org.springframework.util.FileCopyUtils

// XLSX configuration
def userName = "admin" // this user runs the search and generates the XLSX
def jql = "updated > -24h"
def templateName = "issue-navigator.xlsx"
def title = "Issues updated in the last 24 hours"

// run the search
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def query = jqlQueryParser.parseQuery(jql)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def user = ComponentAccessor.userUtil.getUser(userName)
def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())

// render the XLSX
def issues = results.issues
if(issues) {
def xlsApiClass = this.class.forName('com.midori.jira.plugin.betterexcel.api.XlsApi', true, ComponentAccessor.pluginAccessor.classLoader)
def xlsApi = ComponentAccessor.getOSGiComponentInstanceOfType(xlsApiClass)
ComponentAccessor.jiraAuthenticationContext.setLoggedInUser(user)
def xlsResult = xlsApi.getXls(templateName, title.toString(), issues, [:])

// do whatever you want to do with the XLSX
// (in this example, we just write it to a file with a timestamped filename like "example-20170526-1304.xlsx")
FileCopyUtils.copy(xlsResult.bytes, new File("c:\\tmp
example-${new Date().format('yyyyMMdd-HHmm')}.xlsx"))
}