nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Apache POI with Jira Scriptrunner

Mehhss

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

Accepted answers

Ram Kumar Aravindakshan _Adaptavist_

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

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 ?

All comments

Ram Kumar Aravindakshan _Adaptavist_

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

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 ?

PD Sheehan

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

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 ?

Ram Kumar Aravindakshan _Adaptavist_

Hi @Mehhss

As @PD 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

PD Sheehan

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

Mehhss

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 ?

PD Sheehan

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).

Mehhss

Workbook workbook = new XSSFWorkbook(fileInputStream)
as

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 ?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

scriptrunner

jira