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: 2,608
  • jPOI.zip
    17.3 KB · Views: 1,614
Last edited:

Laurent95

Active Member
Licensed User
Hello,

Thanks for this library who is already very useful, but i stay to think to futures possibilities.
There are many other properties in the Java project in comparison of those who are accessible on Excel. ie page setup, named cells, etc. And it seems the library can access to many other object like OLE2 Word, PowerPoint etc.
This library could be improved on properties or on documents format in time ?

Thanks for reply if someone have an idea on this.

Regards.
 

peacemaker

Expert
Licensed User
I hope, in any future the possibility to save .DOC also will be wrapped ? For start maybe some main classes for .DOC creation - text with fontsize+fontcolor and a picture insertion.
 

Stefano Bordini

Member
Licensed User
Hi everybody,i try to read xlsx file with this code

B4X:
Dim wb As PoiWorkbook
    wb.InitializeExisting("/home/pi/billi/","Billi.xlsx","")

this code in windows work but when i try in raspberry the program don't work he's block in this action,before this action i read sqlite db and this operation work correctly.
Sometime after 1 minues the program exit without messages
and sometime with this error
Waiting for debugger to connect...
Program started.
java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:3210)
at java.util.Arrays.copyOf(Arrays.java:3181)
at java.util.ArrayList.grow(ArrayList.java:261)
at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:235)
at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:227)
at java.util.ArrayList.add(ArrayList.java:458)
at org.apache.xmlbeans.impl.values.NamespaceContext$NamespaceContextStack.push(NamespaceContext.java:81)
at org.apache.xmlbeans.impl.values.NamespaceContext.push(NamespaceContext.java:106)
at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated(XmlObjectBase.java:1318)
at org.apache.xmlbeans.impl.values.XmlObjectBase.getStringValue(XmlObjectBase.java:1529)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getR(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFCell.<init>(XSSFCell.java:106)
at org.apache.poi.xssf.usermodel.XSSFRow.<init>(XSSFRow.java:68)
at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFShee
t.java:238)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:201)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:188)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:378)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:166)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:241)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:175)
at anywheresoftware.b4j.objects.PoiWorkbookWrapper.InitializeExisting(PoiWorkbookWrapper.java:53)
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:497)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:656)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:232)
at anywheresoftware.b4a.shell.She
ll.raiseEvent(Shell.java:159)
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:497)
 
Last edited:

Erel

Administrator
Staff member
Licensed User

hibrid0

Active Member
Licensed User
Hi, I want a method to make more bigger the row something like SetColumnWidth but for row.

How can I do it?
I want to put an imagen on the exact field.
 

StarinschiAndrei

Active Member
Licensed User
my released app crash when i try to handle excel file. The computer where my app runs has office 2016. How can i create a crash log?
 
Status
Not open for further replies.
Top