B4J Question [SOLVED] Error on upgrade to jPOI v5.01

bdunkleysmith · May 27, 2021

After upgrade to jPOI v5.01 on installation of B4J v9.00 BETA I am encountering an error on an existing project.

Despite jPOI now being an internal library, I assumed I still needed to copy the dependencies into the additional libraries folder and have done so.

The relevant code begins:

B4X:

Sub SaveTable
    Dim wb As PoiWorkbook
    wb.InitializeNew(True) 'create new xlsx workbook
    Dim sheet1 As PoiSheet = wb.AddSheet("Users by Company", 0)

and the error thrown is:

main._savetable (java line: 734)
java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook$Factory
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:452)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:255)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:249)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:237)
at anywheresoftware.b4j.objects.PoiWorkbookWrapper.InitializeNew(PoiWorkbookWrapper.java:60)
at b4j.example.main._savetable(main.java:734)
at b4j.example.main._btnsavetable_click(main.java:209)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:108)
at anywheresoftware.b4a.BA$1.run(BA.java:233)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:583)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)
... 20 more

Changing

B4X:

wb.InitializeNew(True)

to

B4X:

wb.InitializeNew(False)

changes the error thrown to:

main._savetable (java line: 752)
java.lang.NumberFormatException: For input string: "0xccccccff"
at java.base/jdk.internal.math.FloatingDecimal.parseHexString(FloatingDecimal.java:2082)
at java.base/jdk.internal.math.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1870)
at java.base/jdk.internal.math.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
at java.base/java.lang.Double.parseDouble(Double.java:543)
at anywheresoftware.b4a.BA.ObjectToNumber(BA.java:412)
at b4j.example.main._savetable(main.java:752)
at b4j.example.main._btnsavetable_click(main.java:209)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:108)
at anywheresoftware.b4a.BA$1.run(BA.java:233)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)

I can continue to use jPOI v1.21 by putting the "old" jPOI.jar and jPOI.xml in the internal library folder as per @Erel's post, but I'd prefer to avoid that given I don't believe I'm using any features impacted by the limited backwards compatibility.

Any suggestions on what I've missed in implementing this update?

Erel · May 27, 2021

Have you downloaded poi5_dependencies: https://www.b4x.com/android/forum/threads/xlutils-jpoi-5-read-and-write-ms-excel-workbooks.129969/ ?

bdunkleysmith · May 27, 2021

Yes, as I mentioned in the original post, "I assumed I still needed to copy the dependencies into the additional libraries folder and have done so."

My additional libraries folder is set to:

and contains:

I'm not sure if it is relevant, but I noted that while the zip contained the above jars, the dependencies listed in jPOI.xml did not include poi-ooxml-lite-5.0.0.jar, but just:

<version>5.01</version>
<dependsOn>poi-5.0.0</dependsOn>
<dependsOn>poi-ooxml-5.0.0</dependsOn>
<dependsOn>xmlbeans-4.0.0</dependsOn>
<dependsOn>commons-collections4-4.4</dependsOn>
<dependsOn>commons-compress-1.20</dependsOn>
<dependsOn>commons-math3-3.6.1</dependsOn>
<dependsOn>commons-codec-1.15</dependsOn>
<dependsOn>SparseBitSet-1.2</dependsOn>

Erel · May 27, 2021

You are correct. Add a reference to XLUtils.
It will add the missing dependency and the required packager settings.

bdunkleysmith · May 27, 2021

Thanks @Erel. That fixed the first error and I now realise the second error was due to the change in the way colors are passed, referred to in this post.

I had:

B4X:

HeaderStyle.ForegroundColor = fx.Colors.LightGray

which I changed to:

B4X:

HeaderStyle.ForegroundColor = XL.COLOR_GREY_25_PERCENT

but it doesn't shade the row light grey, but black. So I'll investigate that further and start a new thread if I need to.

Erel · May 27, 2021

It should be:

B4X:

HeaderStyle.ForegroundColor = XUI.Color_LightGray

The XL Color constants are indexed colors. They are used when creating styles with XLUtils (there are technical reasons for using the indexed colors in that case).

bdunkleysmith · May 27, 2021

Thanks @Erel again for your great support. I've followed your general suggestion and in the midst of updating the code to use XLUtils.

B4J Question [SOLVED] Error on upgrade to jPOI v5.01

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Similar Threads

B4J Question [SOLVED] Error on upgrade to jPOI v5.01

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Erel

B4X founder

bdunkleysmith

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency