B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

DMW · Mar 5, 2017

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).

Tayfur · Mar 6, 2017

Erel said:
Yes.

Yes (with a simple JavaObject call).

thank you @Erel for reply
i cant found sample code

inakigarm · Mar 6, 2017

Tayfur said:
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)

Erel · Mar 7, 2017

Tayfur said:
thank you @Erel for reply
i cant found sample cod

Start a new thread in the questions forum and I'll post the code.

StarinschiAndrei · Jul 24, 2017

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

Erel · Jul 24, 2017

jPOI creates Excel workbooks (xls or xlsx files). It doesn't support any other format.

StarinschiAndrei · Jul 24, 2017

Than

Erel said:
jPOI creates Excel workbooks (xls or xlsx files). It doesn't support any other format.

Thank you.

Mashiane · Sep 21, 2017

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.

Erel · Sep 22, 2017

All the files required are available in the first post.

keirS · Oct 19, 2018

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 · Oct 19, 2018

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

keirS · Oct 19, 2018

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

Erel · Oct 21, 2018

V1.20 released. It is based on POI v4.0.0.

keirS · Oct 21, 2018

Erel said:
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)

Erel · Oct 21, 2018

V1.21 released with this dependency.

Philip Prins · Apr 10, 2019

Hello

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

Regards
Philip

Erel · Apr 10, 2019

Better to use B4XTable: https://www.b4x.com/android/forum/threads/b4xtable-jpoi-excel-workbook.104720/

rbghongade · Dec 6, 2019

Friends,
How can we set the columns widths ?

Laurent95 · Dec 6, 2019

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

B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Similar threads

DMW

Member

Tayfur

Well-Known Member

inakigarm

Well-Known Member

Erel

Administrator

StarinschiAndrei

Active Member

Erel

Administrator

StarinschiAndrei

Active Member

Mashiane

Expert

Erel

Administrator

keirS

Well-Known Member

Erel

Administrator

keirS

Well-Known Member

Erel

Administrator

keirS

Well-Known Member

Erel

Administrator

Philip Prins

Active Member

Erel

Administrator

rbghongade

Active Member

Laurent95

Active Member