Apache POI with Jira Scriptrunner

Mehhss January 8, 2024

We use Jira Data Centre and I wanted to use Apache POI from my scriptrunner to parse excel data. So where do I have to place the pom.xml (where it has the dependencies mentioned for apache poi) ? Any help is much appreciated

3 answers

2 accepted

Suggest an answer

Log in or Sign up to answer
0 votes
Answer accepted
Mehhss January 28, 2024

Hi @Ram Kumar Aravindakshan _Adaptavist_ , I have placed the libraries on the jira installation path and I was able to import the apache poi classes from the Scriptrunner on Jira.

But I keep getting errors with the line 

Workbook  workbook = new XSSFWorkbook(fileInputStream)
as 

java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException

java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet

Unable to load class org.apache.poi.xssf.usermodel.XSSFWorkbook due to missing dependency org/apache/commons/collections4/ListValuedMap

java-lang-classnotfoundexception-org-apache-commons-io-output-unsynchronizedbyt

java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap

So what are other libraries I need to install along with poi and poi-ooxml ?

0 votes
Answer accepted
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 12, 2024

Hi @Mehhss

I am going to assume that you are using the scriptrunner-sample project along with the Atlassian SDK to build it.

To include the Apache POI, you will need to go into the jira/subfolder and edit the pom.xml file in it.

In the pom.xml file, you will need to include the Apache POI dependency, i.e. 

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>

into the dependency section of the pom.xml file.

It should look something like this:-

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

<modelVersion>4.0.0</modelVersion>
<packaging>atlassian-plugin</packaging>

<parent>
<groupId>com.adaptavist.pom</groupId>
<artifactId>scriptrunner-jira-standard</artifactId>
<version>62</version>
<relativePath/>
</parent>

<groupId>com.onresolve.scriptrunner.assets</groupId>
<artifactId>sr-sample-plugin-jira</artifactId>
<version>1.1-SNAPSHOT</version>

<organization>
<name>Example Company</name>
<url>http://www.example.com/</url>
</organization>

<name>Jira Sample Plugin for ScriptRunner</name>
<description>ScriptRunner for Jira script plugin</description>

<properties>
<!-- Set your JIRA version here -->
<jira.version>9.4.12</jira.version>
<!-- Set logging levels for development here -->
<logging.levels>com.example;DEBUG,com.onresolve;DEBUG</logging.levels>
</properties>

<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>com.atlassian.maven.plugins</groupId>
<artifactId>jira-maven-plugin</artifactId>
<configuration>
<jvmArgs>-Xms512m -Xmx1g -XX:MaxPermSize=1g -XX:-UseGCOverheadLimit -server</jvmArgs>
<applications>
<!-- Uncomment these if you wish to include them -->

<!-- Include Jira Software features -->
<!--
<application>
<applicationKey>jira-software</applicationKey>
<version>${jira.software.version}</version>
</application>
-->

<!-- Include Jira Service Desk features -->
<!--
<application>
<applicationKey>jira-servicedesk</applicationKey>
<version>${jira.servicedesk.version}</version>
</application>
-->
</applications>
</configuration>
</plugin>
</plugins>
</build>

<repositories>
<!-- This is required to find the parent pom and ScriptRunner dependencies -->
<repository>
<id>adaptavist-external</id>
<url>https://nexus.adaptavist.com/content/repositories/external</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
<releases>
<enabled>true</enabled>
<checksumPolicy>fail</checksumPolicy>
</releases>
</repository>
</repositories>
</project>

I hope this helps to answer your question. :-)

Thank you and Kind regards,

Ram

Mehhss January 14, 2024

Hi Ram thanks for your help, we have our jira data centre instance hosted in AWS and I could see the pom.xml under these folders:
/opt/atlassian/jira-software/9.4.5/atlassian-jira/WEB-INF/classes/META-INF/maven/com.atlassian.jira/jira-core/pom.xml


/opt/atlassian/jira-software/9.4.5/atlassian-jira/META-INF/maven/com.atlassian.jira/jira-webapp-dist/pom.xml


/opt/atlassian/jira-software/9.4.5/atlassian-jira/META-INF/maven/com.atlassian.jira/atlassian-jira-webapp/pom.xml

 

So to which pom.xml I should add the apache poi dependencies ?

Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 14, 2024

Hi @Mehhss

As @Peter-Dave Sheehan mentioned, you should not play with the POM files in Jira's WEB-INF/classes path. 

As I mentioned in my previous comment, if you are using the Atlassian SDK along with the scriptrunner-sample project, then the approach I have shown you in my previous comment works fine.

However, if you are not using the scriptrunner-sample project, the safer approach would be to just:

1. Shutdown your Jira instance

2. Add the Apache POI jar file in your Jira's lib folder, for example,/var/lib/atlassian/jira/lib folder.

3. Restart your Jira instance.

After this, you can import and access the Apache POI class files in any ScriptRunner Editor.

Using a @Grab annotation is not an approach I would recommend, as this could cause other issues, specifically after the Groovy 4 version.

Thank you and Kind regards,

Ram

Mehhss January 16, 2024

Hi @Ram Kumar Aravindakshan _Adaptavist_ Thanks for your reply, so as per "Add the Apache POI jar file in your Jira's lib folder, for example,/var/lib/atlassian/jira/lib folder."

Can I place the apache jar files in here "/var/atlassian/application-data/jira " ?

there is no lib directory under the path /var/atlassian/application-data/jira, so can I create a lib directory and place the apache poi library files ?

Peter-Dave Sheehan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 16, 2024

No, you have to put it in the jira installation folder. Not the jira-home.

It might be something like /opt/atlassian/jira-software/9.4.5/lib (I'm not familiar with that installation model).

Like Mehhss likes this
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 16, 2024

Hi @Mehhss

You must put it into the lib/ folder in your Jira installation path as shown in the screenshot below:-

jira_installation_path.png

There is no such folder in the application-data/jira path.

I hope this solves your question. :-)

Thank you and Kind regards,

Ram

Mehhss January 16, 2024

hi @Ram Kumar Aravindakshan _Adaptavist_  , my JIRA data center is hosted on the AWS EC2 linux instance and my project is as below :
 jiraStructure.PNG

so I couldnt find the lib folder as shown in the image you have shared.

we have scriptrunner installed on our jira and I wanted to access excel files from the scriptrunner. I tried using @Grab annotation as said my @Peter-Dave Sheehan , it gives me below error :

java.lang.NoClassDefFoundError: Could not initialize class org.apache.poi.util.IOUtils at org.apache.poi.poifs.filesystem.FileMagic.valueOf(FileMagic.java:177) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:309) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:277) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:255) at org.apache.poi.ss.usermodel.WorkbookFactory$create.call(Unknown Source) at Script109.run(Script109.groovy:22) at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:331) at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:161) at javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:233) at javax.script.ScriptEngine$eval.call(Unknown Source) at com.onresolve.scriptrunner.runner.AbstractScriptRunner.runScriptAndGetContext(AbstractScriptRunner.groovy:189) at com.onresolve.scriptrunner.runner.AbstractScriptRunner$runScriptAndGetContext$5.callCurrent(Unknown Source) at com.onresolve.scriptrunner.runner.AbstractScriptRunner.runScriptAndGetContext(AbstractScriptRunner.groovy:308) at com.onresolve.scriptrunner.runner.AbstractScriptRunner$runScriptAndGetContext$4.callCurrent(Unknown Source) at com.onresolve.scriptrunner.runner.AbstractScriptRunner.runScript(AbstractScriptRunner.groovy:320) at com.onresolve.scriptrunner.runner.rest.common.UserScriptEndpoint$_execFromJson_closure1.doCall(UserScriptEndpoint.groovy:90) at com.onresolve.scriptrunner.runner.rest.common.UserScriptEndpoint$_execFromJson_closure1.doCall(UserScriptEndpoint.groovy) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:343)




With my case scenario, is it not possible at all to use apache poi libraries to access excel files from scriptrunner using groovy script ?

please advise

Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 17, 2024

Hi @Mehhss

From the screenshot you shared, it looks like you do not have full permission to access the Jira installation path; hence, you cannot view the lib folder.

I suggest that you reach out to your Administrator to grant you temporary access to the <JIRA_HOME>/lib folder to add the Apache POE jar file.

Please clarify if you can access the Jira instance via the terminal. If yes, can you access the Jira installation path, and go to the lib folder, as I shared on the screenshot in my previous comment?

Thank you and Kind regards,
Ram

Mehhss January 17, 2024

Hi @Ram Kumar Aravindakshan _Adaptavist_  & @Peter-Dave Sheehan  I’m a jira administrator in my team, the screenshot I have shared about my project structure is the terraform structure to handle the jira instance hosted in the aws ec2 instance. 


Yes I can access jira instance via the terminal ( via ec2 session manager on aws) , can you please guide me where or how I can find the jira home or jira installation path ? 

Is it under /opt or under / var ??? 

The Scriptrunner version we use is 8.7.0 and the jira version we use is 9.4.5
kind regards and thank you

 Mehala

Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 18, 2024

Hi @Mehhss

Whether the Jira instance is installed in the /opt or /var folder depends on your configuration.

If you intend to locate it, you can do a basic search:-

1. Go to either the /opt or /var folder

2. Enter the search parameter below on your Cloud terminal, i.e.

find -name '*.jar' -print | grep jira | grep lib/ | grep -v WEB-INF | grep -v application-data

This should list all the jar files in your jira/lib folder something like this:-

./atlassian/jira/lib/ecj-4.20.jar
./atlassian/jira/lib/catalina-storeconfig.jar
./atlassian/jira/lib/jonas_timer-1.4.3.jar
./atlassian/jira/lib/websocket-api.jar
./atlassian/jira/lib/jsp-api.jar
./atlassian/jira/lib/jul-to-slf4j-1.7.30.jar
./atlassian/jira/lib/carol-1.5.2.jar
./atlassian/jira/lib/tomcat-i18n-pt-BR.jar
./atlassian/jira/lib/log4j-api-2.17.2.jar
./atlassian/jira/lib/catalina-ant.jar
./atlassian/jira/lib/catalina.jar
./atlassian/jira/lib/jotm-iiop_stubs-1.4.3.jar
./atlassian/jira/lib/log4j-core-2.17.2.jar
./atlassian/jiralib/hsqldb-1.8.0.10.jar
./atlassian/jira/lib/catalina-ssi.jar
./atlassian/jira/lib/tomcat-i18n-zh-CN.jar
./atlassian/jira/lib/ots-jts-1.0.jar
./atlassian/jira/lib/tomcat-coyote.jar
./atlassian/jiralib/mssql-jdbc-9.2.1.jre8.jar
./atlassian/jira/lib/tomcat-dbcp.jar
./atlassian/jira/lib/tomcat-i18n-cs.jar
./atlassian/jira/lib/tomcat-api.jar
./atlassian/jira/lib/tomcat-jni.jar
./atlassian/jira/lib/catalina-ha.jar
./atlassian/jira/lib/jasper.jar
./atlassian/jira/lib/tomcat-i18n-es.jar
./atlassian/jira/lib/tomcat-jdbc.jar
./atlassian/jira/lib/xapool-1.3.1.jar
./atlassian/jira/lib/jta-1.0.1B.jar
./atlassian/jira/lib/tomcat-i18n-ja.jar
./atlassian/jira/lib/tomcat-websocket.jar
./atlassian/jira/lib/jasper-el.jar
./atlassian/jira/lib/jcl-over-slf4j-1.7.30.jar
./atlassian/jira/lib/jaspic-api.jar
./atlassian/jira/lib/el-api.jar
./atlassian/jira/lib/tomcat-i18n-ru.jar
./atlassian/jira/lib/tomcat-util-scan.jar
./atlassian/jira/lib/objectweb-datasource-1.4.3.jar
./atlassian/jira/lib/servlet-api.jar
./atlassian/jira/lib/jotm-1.4.3.jar
./atlassian/jira/lib/log4j-slf4j-impl-2.17.2.jar
./atlassian/jira/lib/jotm-jrmp_stubs-1.4.3.jar
./atlassian/jira/lib/slf4j-api-1.7.30.jar
./atlassian/jira/lib/tomcat-i18n-ko.jar
./atlassian/jira/lib/tomcat-i18n-fr.jar
./atlassian/jira/lib/carol-properties-1.5.2.jar
./atlassian/jira/lib/catalina-tribes.jar
./atlassian/jira/lib/postgresql-42.2.27.jar
./atlassian/jira/lib/log4j-1.2-api-2.17.2.jar
./atlassian/jira/lib/annotations-api.jar
./atlassian/jira/lib/tomcat-extensions-0.1.0.jar
./atlassian/jira/lib/tomcat-i18n-de.jar
./atlassian/jira/lib/tomcat-util.jar
./atlassian/jira/jre/lib/jrt-fs.jar

If you can see the jar files like above, that means you have located the lib/ directory in your Jira installation folder. You will then need to download the Apache POI jar and add it to that folder.

Once it has been added to the jira/lib folder, you must restart your nodes.

Thank you and Kind regards,

Ram

Peter-Dave Sheehan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 18, 2024

Typically, <JIRA HOME> is in /var. That is where logs, db configs, plugins, cache etc reside.  This folder doesn't change when you upgrade Jira. Configurations stored there survive upgrades.

<JIRA INSTALLATION> is usually in /opt, this is where the executables live. Any modifications you make to those files will have to be re-applied manually with each new installation/upgrade. Most configuration changes done here should be taken care of by your installer.

https://confluence.atlassian.com/adminjiraserver/important-directories-and-files-938847744.html

The lib folder you are looking for is in the <JIRA INSTALLATION> folder and if you add a POI jar there, you will need to add it again after each install/upgrade.

I use a different deployment method, but in my case, I have 2 lib folders:

<JIRA INSTALLATION>/lib

<JIRA INSTALLATION>/atlassian-jira/META-INF/lib

I am not sure what's the difference between the two, but I know that I add my mysql jar file to the first.

Mehhss January 22, 2024

Thanks @Ram Kumar Aravindakshan _Adaptavist_ & @Peter-Dave Sheehan  for your kind responses. 

I tried placing the apache poi libraries under the path /opt/atlassian/jira-software/9.4.5/atlassian-jira/WEB-INF/lib/ where I have the jar files, but my jira instance isnt starting after the node restart, it is giving 502 gateway error.
/opt path for your reference
opt path.PNG

 

/var path for your reference
var path.PNG

under the plugins folder, under each directory & sub directories, we have many jar files varpath.PNG

The jar files I am trying to place are 

poi-5.2.5.jar

poi-ooxml-5.2.5.jar

poi-ooxml-schemas-4.1.2.jar

should I have to use any specific version(Jira data center version is 9.4.5, and we use java 1.8) and all the three jar files should have the same version ?

So where should I place the apache poi files?

Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 22, 2024

Hi @Mehhss

As I have already explained in my last comment, please copy the jar file to the <JIRA_INSTALLATION_PATH>/lib folder and not the /WEB-INF/lib folder.

In your case, the jar file must be added to:-

/opt/atlassian/jira-software/9.4.5/lib/

 

Below is how I have added it to my Jira Installation Path's lib folder, i.e., 

/media/ram/Linux_Disk_Space/atlassian/jira9/lib

image1.pngimage2.png

Once I added the Apache POI jar file into my <JIRA_INSTALLATION_PATH>/lib folder, I restarted my Jira instance.

Now I have no problem invoking the POI class files in my ScriptRunner Console, as shown in the screenshot below:-

image3.png

I hope this helps to solve your issue. :-)

Thank you and Kind regards,

Ram

Like Mehhss likes this
Mehhss January 23, 2024

Hi @Ram Kumar Aravindakshan _Adaptavist_ , I have finally placed the apache poi files in the <jira-installation-path>/lib folder and the instance is up now, 
poilib.PNG
However when I try to use poi on scriptrunner it gives me the below error:
scriptrunner.PNG

Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 29, 2024

Hi @Mehhss

The Apache POI Zip file will contain a couple of jar files. I suggest placing all of them in the <JIRA_INSTALLATION_PATH>/lib folder and restarting your instance.

Regarding the exception you are getting, it is because the xmlbeans-5.1.1.jar also needs to be included.

The file is located in the ooxml-lib/. You will find it when you extract the Apache POI zip file as shown in the screenshot below:-

poi_dependency.png

For the ListValueMap class, it is located in the lib/commons-collection jar file, as shown in the screenshot below:-

poi_dependency2.png

Please note that only adding the jar files I have pointed out may not solve the issue. You may need to do this several times until all the jar file dependency requirements are fulfilled.

I suggest that whenever you see an exception returned due to a missing class, run this command on your terminal to locate in which jar file the class is stored:-

for i in `find -name '*.jar' -print`; do echo $i; jar -tvf $i | grep <The CLASS NAME> ; done

for example:-

for i in `find -name '*.jar' -print`; do echo $i; jar -tvf $i | grep ListValuedMap; done

I hope this helps to solve your question. :-)

Thank you and Kind regards,

Ram 

Like Mehhss likes this
0 votes
Peter-Dave Sheehan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2024

What Ram is talking about is the pom.xml that is part of a brand new plugin that you build with the Atlassian SDK.

You can't (or at least shouldn't) modify the pom.xml for Jira itself.

If however, you are just looking to use POI as part of your scriptrunner scripts, then it's possible to dynamically include those jar files.

 

Here is a sample script that reads a file from your jira host:

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

I have been able to adapt some code I found here: https://github.com/dtanner/groovy-excel-reader with decent success.

Mehhss January 14, 2024

Hi Peter thanks for your help, we have our jira data centre instance hosted in AWS and I could see the pom.xml under these folders:
/opt/atlassian/jira-software/9.4.5/atlassian-jira/WEB-INF/classes/META-INF/maven/com.atlassian.jira/jira-core/pom.xml


/opt/atlassian/jira-software/9.4.5/atlassian-jira/META-INF/maven/com.atlassian.jira/jira-webapp-dist/pom.xml


/opt/atlassian/jira-software/9.4.5/atlassian-jira/META-INF/maven/com.atlassian.jira/atlassian-jira-webapp/pom.xml

 

So to which pom.xml I can add the apache poi dependencies ?

Peter-Dave Sheehan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 15, 2024

None of those pom files should be modified even if you have access to them. That might break Jira.

DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events