B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Some of the questions raised lately can find their answers in the two following tutorials:
https://www.b4x.com/android/forum/threads/creating-excel-reports-with-jpoi-part-i.75568/
https://www.b4x.com/android/forum/threads/creating-excel-reports-with-jpoi-part-ii.75570/

JPOI work well with 2007/2010/2013/2016 (I have tested these versions).

 

thank you @Erel for reply
i cant found sample code

 

Look at http://stackoverflow.com/questions/14951378/delete-an-excel-sheet-using-apache-poi for Java code (need translate to Javaobject but not seems too dificult)

 

Hello, is it possible to convert/ save as (in background) the excel file in PDF ?

 

Than

Thank you.

 

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.

 

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?

 

It doesn't seem to be backwards compatible. I downloaded POI 4.0 with all the dependencies Changed the dependency lines to.

Code:

<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:

Code:

ExcelCell.CellType = ExcelCell.TYPE_STRING

I get the following error.

Code:

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

 

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.

Code:

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)

 

Hello

I am looking for a tutorial / example to read a Excel sheet and convert to Tableview/

Regards
Philip