How to read data in a excel file from Jira(Data Center) Instance Using scriptrunner

[Mehhss]

I want to read the excel data from Scriptrunner

[PD Sheehan]

I recently had a similar requirement.

I was able to adapt code I found here: https://github.com/dtanner/groovy-excel-reader to extract content from an excel file.

[Mehhss]

Thanks for the reference link. but It does require Apache POI library ? if yes, where do I place the pom.xml(the dependencies for apache poi will be mentioned in here)

[PD Sheehan]

The beauty of groovy ...

Just add the following to your script and/or Groovy class, and the necessary components will be downloaded automatically:

import org.apache.poi.ss.usermodel.*

@Grab('org.apache.poi:poi:5.2.5')
@Grab('org.apache.poi:poi-ooxml:5.2.5')

If you're curious, the libraries will be saved in the user profile for the user that your Jira instance runs as. For example, on Linux, running jira as user "jira":

/home/jira/.groovy/grapes/org.apache.poi

[Mehhss]

I get the below compilation error while trying to use

import org.apache.poi.ss.usermodel.*

@Grab('org.apache.poi:poi:5.2.5')
@Grab('org.apache.poi:poi-ooxml:5.2.5')

The script could not be compiled: <pre>org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: General error during conversion: Error grabbing Grapes -- [unresolved dependency: org.apache.poi#poi-ooxml;5.2.5: not found, unresolved dependency: org.apache.poi#poi;5.2.5: not found, unresolved dependency: org.apache.poi#poi-ooxml;3.9: not found, unresolved dependency: org.apache.poi#poi;3.9: not found] java.lang.RuntimeException: Error grabbing Grapes -- [unresolved dependency: org.apache.poi#poi-ooxml;5.2.5: not found, unresolved dependency: org.apache.poi#poi;5.2.5: not found, unresolved dependency: org.apache.poi#poi-ooxml;3.9: not found, unresolved dependency: org.apache.poi#poi;3.9: not found] at groovy.grape.GrapeIvy.getDependencies(GrapeIvy.groovy:479) at groovy.grape.GrapeIvy.resolve(GrapeIvy.groovy:640) at groovy.grape.GrapeIvy.resolve(GrapeIvy.groovy:616) at groovy.grape.GrapeIvy.grab(GrapeIvy.groovy:280) at groovy.grape.Grape$1.run(Grape.java:172) at groovy.grape.Grape$1.run(Grape.java:158) at java.security.AccessController.doPrivileged(Native Method) at groovy.grape.Grape.grab(Grape.java:158) at groovy.grape.GrabAnnotationTransformation.visit(GrabAnnotationTransformation.java:380) at org.codehaus.groovy.transform.ASTTransformationVisitor.lambda$addPhaseOperationsForGlobalTransforms$5(ASTTransformationVisitor.java:376) at org.codehaus.groovy.control.CompilationUnit$ISourceUnitOperation.doPhaseOperation(CompilationUnit.java:896) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:692) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:666) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:373) at groovy.lang.GroovyClassLoader.lambda$parseClass$2(GroovyClassLoader.java:316) at org.codehaus.groovy.runtime.memoize.StampedCommonCache.compute(StampedCommonCache.java:163) at org.codehaus.groovy.runtime.memoize.StampedCommonCache.getAndPut(StampedCommonCache.java:154) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:314) at groovy.util.GroovyScriptEngine$ScriptClassLoader.doParseClass(GroovyScriptEngine.java:231) at groovy.util.GroovyScriptEngine$ScriptClassLoader.parseClass(GroovyScriptEngine.java:218) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:258) at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.getScriptClass(GroovyScriptEngineImpl.java:350) at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.compile(GroovyScriptEngineImpl.java:190) at javax.script.Compilable$compile.call(Unknown Source) at com.onresolve.scriptrunner.model.validation.ScriptConfigValidatorImpl.isValid(ScriptConfigValidatorImpl.groovy:63) at

[PD Sheehan]

Try to move the import below the grabs

[Kristian Walker _Adaptavist_]

I can confirm ScriptRunner for Jira Cloud only allows importing the packages listed here and does not support importing external packages.

Regards,

Kristian

[PD Sheehan]

@Kristian Walker _Adaptavist_ while this question is tagged with "cloud" I was going by the title that indicates DataCenter.

Maybe he's exporting the excel from DC and hoping to read some of the data from a Cloud instance, in which case, you are correct that this won't work.

[Mehhss]

Sorry for the wrong tagging, we are using Jira Data Centre, hosted in AWS cloud platform, we have the scriptrunner, and I am trying to access an excel file data from scriptrunner (jira data centre). Writing the imports below  

@Grab('org.apache.poi:poi:5.2.5')
@Grab('org.apache.poi:poi-ooxml:5.2.5')

also didnt work. Is there a way I can use apache poi to access excel file data from jira scriptrunner?

 

[PD Sheehan]

This works for me in the console, in an environment where POI was never used before.

import java.nio.file.Files
@Grab('org.apache.poi:poi:5.2.5')
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Row


def sourceFile = new File('/path/to/file.xlsx')
def workbook = WorkbookFactory.create(sourceFile)
def sheet = workbook.getSheetAt(0)
log.info sheet.getRow(0).getCell(0)

//returns the top-right cell (A1) in the first sheet

Could internet traffic to download the grapes be blocked by a firewall rule on the server?

[Kristian Walker _Adaptavist_]

Hi Mehala,

Thank you for your question.

I can confirm that it is possible to read in data from external systems or Jira Issues using ScriptRunner for Jira Cloud if the third-party system you want to read in the data from exposes the data via a Rest API.

This means to read in the file from Jira Cloud then you would need to store the file on an issue as an attachment and then call the Get Attachment Content API  in order to read this inside a script.

I hope this information helps.

Regards,

Kristian

[Ben van den Berg]

Thanks, @Kristian Walker _Adaptavist_; this was most helpful and put me on the right track!