B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

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:

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.

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.

 

[giga]

Trying Erels PoiExcelExample When I try to run it it compiles to a white screen and Microsoft office pops up and the example disappears. See attached.

Any ideas appreciated.

Little more information:
Operating system is Windows 10. If I uninstall MS Office I get the message in the next post:

 

[giga]

 

[rboeck]

Erels example creates a workbook and then it want to show it; you should have a program installed, which is capable of xlsx files. I think it shoudl also possible with libre office and open office.

 

[giga]

Erels example creates a workbook and then it want to show it; you should have a program installed, which is capable of xlsx files. I think it shoudl also possible with libre office and open office.

Thanks for the reply.
I am testing on a system that has ONLY .xlsx files on it and would like to open them through B4J.
No excel type program is installed, So with this library based on what you are saying is you still need an Excel type program installed in addition to the library. ie. Microsoft Excel, Libre etc...

 

[imbault]

No, for creation and modification of xls/xlsx files, this lib does the job.

But if you want to open a xls/xlsx file, you need a client (Excel, open office...)

In Erel exemple, that line of code tries to open the generated file:

    fx.ShowExternalDocument(File.GetUri(File.DirApp, "1.xlsx"))

 

[giga]

No, for creation and modification of xls/xlsx files, this lib does the job.

But if you want to open a xls/xlsx file, you need a client (Excel, open office...)

In Erel exemple, that line of code tries to open the generated file:

    fx.ShowExternalDocument(File.GetUri(File.DirApp, "1.xlsx"))

Thanks imbault,

I removed my comments, I believe I understand.
To open an existing xls/xlsx I would use the Excel program installed.
but to modify it I could us the library.

I guess I was expecting the lib to be able to open/view the .xlsx file first so I could see what I could change instead of using MS Excel etc...

 

[stevel05]

I haven't tried it but the line:

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

Says that you can read an existing workbook. You would presumably have to display it in a table.

 

[giga]

Thanks for the tip @stevel05. I will look into existing workbook (InitializeExisting).

 

[giga]

Is there an equivalent to "Sheet.GetAllAsync" like jexcel for this library?

Thanks in advance.

 

[Erel]

You can use InitializeExistingAsync to load the workbook in the background. The Ready event will be raised when the workbook is ready.

 

[giga]

No, for creation and modification of xls/xlsx files, this lib does the job.

But if you want to open a xls/xlsx file, you need a client (Excel, open office...)

In Erel exemple, that line of code tries to open the generated file:

    fx.ShowExternalDocument(File.GetUri(File.DirApp, "1.xlsx"))

Thanks, I should have caught "ShowExternalDocument" sooner.

 

[giga]

You can use InitializeExistingAsync to load the workbook in the background. The Ready event will be raised when the workbook is ready.

I am using "Sheet.GetAllAsync" for .xls file showing in the tableSelected Item.
However
How do I use InitializeExistingAsync to show in the tableSelected Item from a list.
Similar to the jexcel example.

Thanks for any advice.

 

[Erel]

There isn't an equivalent method in jPOI. However it should be simple to read all the cell values. Initialize the workbook (asynchronously or synchronously).

Reading a sheet:

Dim alldata As List
alldata.Initialize
For Each r As PoiRow In sheet1.Rows
   Dim rowcells As List
   rowcells.Initialize
   For Each c As PoiCell In r.Cells
     rowcells.Add(c.Value)
   Next
   alldata.Add(rowcells)
Next

 

[Ishmael]

From the screen shots, it seems you dont have microsoft office or xls and xlsx file reader install. Get that done and your application will run

 

[Mostez]

Hi Erel,
Is it possible to create this excel file on Raspberry Pi then download it to PC, is it also possible to append existing excel file. What I really want to do is make inventory using USB barcode scanner connected to Raspberry Pi, then append scanned codes to prepared excel sheet for this purpose, i.e. append codes under "item-code" column

 

[Erel]

Hi Erel,

Please don't limit your questions to a single member.

This library is compatible with the RPi. Yes, you can append data to an existing workbook.

 

[Mostez]

Thanks for your help

 

[StarinschiAndrei]

Hello, can anybody tell me haw can i initialize poisheet or what is wrong in my code? This is the error : java.lang.RuntimeException: Object should first be initialized (PoiSheet).
Thank you
This is my code:

Sub Process_Globals
    Private fx As JFX
    Private wb As PoiWorkbook
    Private sheet As PoiSheet
    Private sheetName As String
End Sub
Sub BtnSelect_MouseClicked (EventData As MouseEvent)
Dim fc As FileChooser
fc.Initialize
fc.InitialFileName="ExcelFile.xlsx"
fc.SetExtensionFilter("Excel Files",Array As String("*.xlsx"))
fullPath = fc.ShowOpen(frmImport)
fileName=File.GetName(fullPath)
lblPath.Text=fileName
filePath=File.GetFileParent(fullPath)
wb.InitializeExisting(filePath,fileName,"")
For i=0 To wb.GetSheetNames.Length -1
    cmbSheet.Items.Add(wb.GetSheetNames(i))
Next

End Sub
Sub btnStart_MouseClicked (EventData As MouseEvent)
    Dim alldata As List  
    alldata.Initialize
    sheet.Name=cmbSheet.Value
    If cmbSheet.Value<>"" Then
        For Each r As PoiRow In sheet.Rows
            Dim rowcells As List
            rowcells.Initialize
            For Each c As PoiCell In r.Cells
            rowcells.Add(c.Value)
            Next
        alldata.Add(rowcells)
        Log(rowcells) 
        Next
    End If
End Sub

 

[Erel]

See the example in the first post. It shows you how to add a new sheet.