B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Discussion in 'B4J Libraries & Classes' started by Erel, Aug 19, 2015.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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: http://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:
    Code:
    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.

    [​IMG]

    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.
     

    Attached Files:

    Last edited: Oct 21, 2018
    Chris2, DonManfred, Mashiane and 13 others like this.
  2. giga

    giga Well-Known Member Licensed User

    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.

    upload_2015-8-21_20-52-59.png

    Little more information:
    Operating system is Windows 10. If I uninstall MS Office I get the message in the next post:
     
    Last edited: Aug 22, 2015
  3. giga

    giga Well-Known Member Licensed User

  4. rboeck

    rboeck Well-Known Member Licensed User

    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.
     
  5. giga

    giga Well-Known Member Licensed User

    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...
     
  6. imbault

    imbault Well-Known Member Licensed User

    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:
    Code:
    fx.ShowExternalDocument(File.GetUri(File.DirApp, "1.xlsx"))
     
    DonManfred, giga and Erel like this.
  7. giga

    giga Well-Known Member Licensed User

    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...
     
    Last edited: Aug 22, 2015
  8. stevel05

    stevel05 Expert Licensed User

    I haven't tried it but the line:

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

    giga Well-Known Member Licensed User

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

    giga Well-Known Member Licensed User

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

    Thanks in advance.
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    You can use InitializeExistingAsync to load the workbook in the background. The Ready event will be raised when the workbook is ready.
     
    DonManfred and tdocs2 like this.
  12. giga

    giga Well-Known Member Licensed User

    Thanks, I should have caught "ShowExternalDocument" sooner.
     
    tdocs2 likes this.
  13. giga

    giga Well-Known Member Licensed User

    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.
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    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:
    Code:
    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
     
    Mashiane and giga like this.
  15. Ishmael

    Ishmael New Member

    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
     
  16. Mostez

    Mostez Active Member

    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
     
  17. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  18. Mostez

    Mostez Active Member

    Thanks for your help
     
  19. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    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:
    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
    upload_2016-2-14_23-58-33.png
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    See the example in the first post. It shows you how to add a new sheet.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice