B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Status
Not open for further replies.
Edit:
This is the old version. The new version which has some small non-backwards compatible changes is available here: https://www.b4x.com/android/forum/threads/129969
Copy jPOI.jar / xml to the internal libraries folder.

jPOI is based on Apache POI project: http://poi.apache.org/
Currently it only supports Microsoft Excel related APIs.

It is an alternative library to jExcel: https://www.b4x.com/android/forum/threads/jexcel-library.35004/
Advantages of jPOI:
  • Supports both xls and xlsx (Excel 2007+ format). jExcel only supports the old format.
  • More powerful.
  • Apache POI is an active project.
  • Supports password protected workbooks.
  • A bit simpler to use.
The disadvantage of jPOI is that the library is much larger (about 10mb).

How to use

The first step is to create a PoiWorkbook. You can either create a new workbook (InitializeNew) or read the data of an existing workbook (InitializeExisting).
Now you can add sheets to the workbook, or access existing sheets.
A sheet represented by PoiSheet holds a list of rows (PoiRow).
Each row holds a list of cells (PoiCell).
Note that if you call PoiSheet.GetRow with the index of an empty row it will return an uninitialized row.
The same is true for PoiRow.GetCell.

PoiSheet.Rows will return a list with all the non-empty rows.
PoiRow.Cells will return a list with all the non-empty cells.

These properties are useful for a iterating over the rows and cells with For Each blocks:
B4X:
For Each row As PoiRow In Sheet.Rows
For Each cell As PoiCell In row.Cells
  Log(cell.Value)
Next
Next

Cells (and rows) can be styled with PoiCellStyle objects. These objects should be reused when possible. Meaning that if multiple cells should have the same style then they should all use the same object.

The rows, sheets and cell indices all start from 0.

Reading the cells values is done with the various PoiCell.Value properties. If the cell type is known then use the Value property that returns the correct type (for example ValueNumeric). Otherwise use the general Value property which checks the cell type and returns the value.

Saving the workbook is done with Workbook.Save. When you are done with the workbook you should call Workbook.Close.

Note that if the workbook is opened in Excel then the program will fail to open it.

The attached program creates a simple table with some styling, formulas and formats. It also adds an image.

SS-2015-08-19_13.17.07.png


The library depends on additional jar files. You should download them from this link:
www.b4x.com/b4j/files/jPoi_AdditionalJars.zip
Copy the jar files to the additional libraries folder.

Don't forget to download the attached library.

Updates

V1.21 - Adds a missing dependency.
V1.20 - based on Apache POI v4.0.0. Make sure to update the additional jars as well.
Note that this is a major upgrade. Calls with JavaObject might need some updates. Start a new thread if you encounter any issue.

V1.10 - based on Apache POI v3.16. Make sure to update the additional jars as well.
 

Attachments

  • PoiExcelExample.zip
    4.4 KB · Views: 3,268
  • jPOI.zip
    17.3 KB · Views: 2,248
Last edited:

keirS

Well-Known Member
Licensed User
Longtime 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?
 

keirS

Well-Known Member
Licensed User
Longtime 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
Longtime 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)
 

Philip Prins

Active Member
Licensed User
Longtime User
Hello

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

Regards
Philip
 

Laurent95

Active Member
Licensed User
Longtime 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:
Status
Not open for further replies.
Top