B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Erel

Administrator
Staff member
Licensed User
jPOI creates Excel workbooks (xls or xlsx files). It doesn't support any other format.
 

Mashiane

Expert
Licensed User
There is only poi 3.17 on the apache download links. version 1.10 of this lib supports poi 3.16, please advise.. and thanks.
 

keirS

Well-Known Member
Licensed User
Could this library be updated to the latest version of POI (4.0)? I am trying to use features which are not supported in 3.16. Alternatively is it possible to have the source code for the library so I can rebuild it myself?
 

Erel

Administrator
Staff member
Licensed User
If the new version is backwards compatible then you can just change the dependency lines in the bottom of the XML file.
 

keirS

Well-Known Member
Licensed User
If the new version is backwards compatible then you can just change the dependency lines in the bottom of the XML file.
It doesn't seem to be backwards compatible. I downloaded POI 4.0 with all the dependencies Changed the dependency lines to.
B4X:
<dependsOn>poi-4.0.0</dependsOn>
 <dependsOn>poi-ooxml-4.0.0</dependsOn>
 <dependsOn>poi-ooxml-schemas-4.0.0</dependsOn>
 <dependsOn>xmlbeans-3.0.1</dependsOn>
 <dependsOn>commons-collections4-4.2</dependsOn> 
<dependsOn>commons-codec-1.10</dependsOn> 
<dependsOn>commons-compress-1.18</dependsOn>
 <dependsOn>curvesapi-1.04</dependsOn>
When setting a cell type:
B4X:
ExcelCell.CellType = ExcelCell.TYPE_STRING
I get the following error.

B4X:
Waiting for debugger to connect...
Program started.
java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
    at anywheresoftware.b4j.objects.PoiCellWrapper.setCellType(PoiCellWrapper.java:44)
    at b4j.example.exceltotableview._buildview(exceltotableview.java:192)
    at b4j.example.main._appstart(main.java:133)
    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 anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:628)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
    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 anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
java.lang.RuntimeException: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:119)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
    at anywheresoftware.b4j.objects.PoiCellWrapper.setCellType(PoiCellWrapper.java:44)
    at b4j.example.exceltotableview._buildview(exceltotableview.java:192)
    at b4j.example.main._appstart(main.java:133)
    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 anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:628)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
    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 anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    ... 12 more
 

keirS

Well-Known Member
Licensed User
V1.20 released. It is based on POI v4.0.0.
Thanks for that Erel. It appears commons-math3 is also required for re-evaluating formulas. I am not quite sure why but Maven didn't show me this in the dependency list. I downloaded commons-math3-3.6.1.jar and added the dependency to the XML file and this solves the crash.

B4X:
Waiting for debugger to connect...
Program started.
(XSSFCell) -62.5
-62.5
(XSSFCell) 8996.0
8996.0
(XSSFCell) 
(XSSFCell) 51.5
51.5
(XSSFCell) 
(XSSFCell) 47.5
47.5
(XSSFCell) 
(XSSFCell) SUM(B1:I1)
SUM(B1:I1)
Error occurred on line: 196
java.lang.reflect.InvocationTargetException
    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 anywheresoftware.b4j.object.JavaObject.RunMethod(JavaObject.java:131)
    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 anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:628)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
    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 anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:61)
    at b4j.example.exceltotableview._poigetformattedcellvalue(exceltotableview.java:497)
    at b4j.example.exceltotableview._buildview(exceltotableview.java:335)
    at b4j.example.main._appstart(main.java:133)
    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 anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:628)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
    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 anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NoClassDefFoundError: org/apache/commons/math3/linear/RealMatrix
    at org.apache.poi.ss.formula.functions.MatrixFunction.<clinit>(MatrixFunction.java:250)
    at org.apache.poi.ss.formula.eval.FunctionEval.produceFunctions(FunctionEval.java:147)
    at org.apache.poi.ss.formula.eval.FunctionEval.<clinit>(FunctionEval.java:59)
    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:534)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:54)
    at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCell(BaseFormulaEvaluator.java:177)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:991)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:949)
    ... 46 more
Caused by: java.lang.ClassNotFoundException: org.apache.commons.math3.linear.RealMatrix
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 57 more
(XSSFCell)
 

Laurent95

Active Member
Licensed User
Friends,
How can we set the columns widths ?
Hello,
You must use the property SetColumnWidth

Edit, with an example in B4J :
B4X:
       'Create workbook
       Dim wb As PoiWorkbook
       '(ndp: Voir par la suite si on ajoute au meme classeur les feuilles mois par mois)
       wb.InitializeNew(True) 'create new xlsx workbook (False for xls, True for xlsx)
       Dim sheet1 As PoiSheet
       .../...

       'Fills all cells with each field name of SQL request and adjust their width depends the name long
       Dim xz As Int
       For xz = 0 To tblViewPdf.ColumnsCount - 1
           titleRow.CreateCellString(xz, tblViewPdf.GetColumnHeader(xz))
           sheet1.SetColumnWidth(xz, (256 * (tblViewPdf.GetColumnHeader(xz).Length*1.5)))
       Next
Sorry need to retireve in my codes for the example.
Have fun
 
Last edited:
Top