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

[bdunkleysmith]

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:

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

wb.InitializeNew(True)

to

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]

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

 

[bdunkleysmith]

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]

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

 

[bdunkleysmith]

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:

HeaderStyle.ForegroundColor = fx.Colors.LightGray

which I changed to:

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]

It should be:

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]

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