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
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
java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap
So what are other libraries I need to install along with poi and poi-ooxml ?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Mehhss
You must put it into the lib/ folder in your Jira installation path as shown in the screenshot below:-
There is no such folder in the application-data/jira path.
I hope this solves your question. :-)
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi @Ram Kumar Aravindakshan _Adaptavist_ , my JIRA data center is hosted on the AWS EC2 linux instance and my project is as below :
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
/var path for your reference
under the plugins folder, under each directory & sub directories, we have many jar files
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:-
Below is how I have added it to my Jira Installation Path's lib folder, i.e.,
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:-
I hope this helps to solve your issue. :-)
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
However when I try to use poi on scriptrunner it gives me the below error:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:-
For the ListValueMap class, it is located in the lib/commons-collection jar file, as shown in the screenshot below:-
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
None of those pom files should be modified even if you have access to them. That might break Jira.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.