Setting up Apache POI

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Jon, Steve, Ian, Dave

Segi
Ebase User
Posts: 649
Joined: Mon Dec 09, 2013 6:37 pm

Setting up Apache POI

#1

Postby Segi » Fri Aug 17, 2018 4:39 pm

I am trying to set up and work with Apache POI

I installed these 4 jar files in UfsServer\tomcat\webapps\ufs\WEB-INF\lib and restarted the server

poi-3.17.jar
poi-excelant-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar

I created a simple script which has the following code:

Code: Select all

importPackage(com.ebasetech.xi.api);
importPackage(com.ebasetech.xi.services);
importPackage(Packages.org.apache.poi);
importPackage(Packages.org.apache.poi.hssf.usermodel);
importPackage(Packages.org.apache.poi.xssf.usermodel);

var workbook = HSSFWorkbook();

var sheet = workbook.createSheet("Countries");
var row = sheet.createRow(0);
var cell0 = row.createCell(0);
cell0.setCellValue("U.S.A.");

var fos = new FileOutputStream("Countries.xls");
workbook.write(fos);
fos.close();
workbook.close();
This code works perfectly. My issue is that I want to create an xlsx Excel file, not xls. In order to do that, you need to create an XSSFWorkbook object instead of HSSFWorkbook.

If I change

Code: Select all

var workbook = HSSFWorkbook();
to

Code: Select all

var workbook = XSSFWorkbook();
Verj gives me a generic error message which is

Code: Select all

org/apache/commons/collections4/ListValuedMap
I tried referencing the full package name (which I verified exists in poi-ooxml-3.17.jar) by doing

Code: Select all

var workbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook();
but that gives me the same error

Using the full package for HSSFWorkbook works fine.

Does anyone know why this is happening ?

Edit: After searching online I found the answer that fixed this issue.

Verj comes with a JAR library called commons-collections-3.2.1.jar. I downloaded version 4.2 from https://commons.apache.org/proper/commo ... ctions.cgi and added it to VerjIO\UfsServer\tomcat\webapps\ufs\WEB-INF\lib leaving commons-collections-3.2.1.jar there as well and I am now able to create XLSX files.
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: Setting up Apache POI

#2

Postby Jon » Mon Aug 20, 2018 7:55 am

Segi,

You should remove the older jar - otherwise you will have two jar files with the same classes and it's random which one will be used, which can give some strange problems.

And watch out in the future - our upgrade instructions will direct you to re-install the old version.

Jon
0 x

Segi
Ebase User
Posts: 649
Joined: Mon Dec 09, 2013 6:37 pm

Re: Setting up Apache POI

#3

Postby Segi » Mon Aug 20, 2018 2:57 pm

Jon,

The reason that I said to keep both versions of the common-collections jar is because I originally replaced commons-collections-3.2.1.jar with commons-collections4-4.2.jar. After restarting the server this is the server log:

Code: Select all

Mon Aug 20 07:55:09:  ************************************************************
Mon Aug 20 07:55:09:  ***  Starting Ebase Xi Server version 5.3.2, Build 20180105
Mon Aug 20 07:55:09:  ************************************************************
Mon Aug 20 07:55:09: INFO Licence is valid
Mon Aug 20 07:55:13: ERROR Error detected during initialisation of Ebase webapp
Mon Aug 20 07:55:13: ERROR Error detected during initialisation of Ebase webapp
java.lang.NoClassDefFoundError: org/apache/commons/collections/map/CaseInsensitiveMap
	at com.ebasetech.common.FlexibleCaseMap.<init>(FlexibleCaseMap.java:29)
	at com.ebasetech.ufs.kernel.StandaloneUFSComponent.<init>(StandaloneUFSComponent.java:130)
	at com.ebasetech.ufs.kernel.IntegrationService.<init>(IntegrationService.java:68)
	at com.ebasetech.ufs.kernel.IntegrationService.newSystemService(IntegrationService.java:56)
	at com.ebasetech.ufs.kernel.fileRepository.jbAssemblers.JBIntegrationServiceAssembler.readJBObject(JBIntegrationServiceAssembler.java:163)
	at com.ebasetech.ufs.kernel.fileRepository.jbAssemblers.JBEntityObjectAssemblers.readJBObject(JBEntityObjectAssemblers.java:47)
	at com.ebasetech.ufs.kernel.fileRepository.EntityFileLoader.assembleEntityObject(EntityFileLoader.java:281)
	at com.ebasetech.ufs.kernel.fileRepository.EntityFileLoader.loadEntity_(EntityFileLoader.java:147)
	at com.ebasetech.ufs.kernel.fileRepository.CachedEntityFileLoader.loadEntity_(CachedEntityFileLoader.java:53)
	at com.ebasetech.ufs.kernel.EntityLoader.loadProjectEntity(EntityLoader.java:258)
	at com.ebasetech.ufs.kernel.workspace.WorkspaceFormLoader.loadProjectEntity(WorkspaceFormLoader.java:138)
	at com.ebasetech.ufs.kernel.workspace.WorkspaceFormLoader.loadProjectEntity_noSessionCheck(WorkspaceFormLoader.java:161)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.loadEntityFromAllWorkspaces(ServerRuntimeLoader.java:624)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.registerService(ServerRuntimeLoader.java:557)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.registerService_noException(ServerRuntimeLoader.java:532)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.registerSystemServices(ServerRuntimeLoader.java:490)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.readWorkspaceFiles(ServerRuntimeLoader.java:475)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.initialiseWorkspaces(ServerRuntimeLoader.java:421)
	at com.ebasetech.ufs.kernel.workspace.ServerRuntimeLoader.initialise(ServerRuntimeLoader.java:127)
	at com.ebasetech.ufs.kernel.UFSServletContextProxy.initialiseFormEngine(UFSServletContextProxy.java:261)
	at com.ebasetech.ufs.kernel.UFSServletContextProxy.init(UFSServletContextProxy.java:166)
	at com.ebasetech.ufs.kernel.UFSServletContextProxy.Initialise(UFSServletContextProxy.java:242)
	at formservlets.EbaseContextListener.contextInitialized(EbaseContextListener.java:40)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4745)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5207)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:752)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:728)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734)
	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:630)
	at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1842)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections.map.CaseInsensitiveMap
	at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1291)
	at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1119)
	... 36 more
Mon Aug 20 07:55:13: ERROR Error detected initialising the designer server component:
Mon Aug 20 07:55:13: ERROR Error detected initialising the batch server component:
Mon Aug 20 07:55:13: ERROR Error detected initialising the workflow servlet component:
Mon Aug 20 07:55:13:  FormCacheLoader: start
Mon Aug 20 07:55:13:  FormCacheLoader: unexpected error null
Mon Aug 20 07:55:13:  ******************************************************************
Mon Aug 20 07:55:13:  ***  Ebase Xi Server V5.3.2 successfully initialized in 5 seconds
Mon Aug 20 07:55:13:  ***  Using workspace C:\verjIO\Workspace
Mon Aug 20 07:55:13:  ******************************************************************
Visiting my Verj instance URL gives the error

Code: Select all

An error has occurred. Please try again later.
This only occurs when removing commons-collections-3.2.1.jar. Keeping both JARs does not produce this error.
0 x


Who is online

Users browsing this forum: Google [Bot] and 5 guests