Uploaded image for project: 'SR for Jira - Development'
  1. SR for Jira - Development
  2. SRJIRA-2382

Midori Better Excel Docs Example

    Details

    • Type: Task
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.3.0
    • Component/s: None
    • Labels:
      None
    • Sprint:
      Sprint 37 - Ends Nov 28
    • Critical Points:
      0

      Description

      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"))
      }

        Attachments

          Activity

            People

            Assignee:
            jdavis Jenna Davis
            Reporter:
            nriley Neal Riley
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: