B4J Tutorial B4XTable + jPoi + Excel Workbook

Discussion in 'B4J Tutorials' started by Erel, Apr 10, 2019.

  1. Erel

    Erel Administrator Staff Member Licensed User

    [​IMG]

    It is very simple to read data from an Excel workbook and show it with B4XTable.

    The almost complete code:
    Code:
    B4XTable1.AddColumn("Country", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn(
    "Region", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn(
    "Population", B4XTable1.COLUMN_TYPE_NUMBERS)
    Dim poi As PoiWorkbook
    poi.InitializeExisting(
    File.DirAssets, "countries of the world.xls""")
    Dim sheet As PoiSheet = poi.GetSheet(0)
    Dim data As List
    data.Initialize
    For Each row As PoiRow In sheet.Rows
       
    If row.RowNumber > 5 Then
           
    Dim DataRow(3As Object
           DataRow(
    0) = row.GetCell(0).ValueString 'country
           DataRow(1) = row.GetCell(1).ValueString.Trim 'region
           DataRow(2) = row.GetCell(2).ValueNumeric 'population
           data.Add(DataRow)
       
    End If
    Next
    B4XTable1.SetData(data)
    poi.Close
    Project is attached.
     

    Attached Files:

    Last edited: Apr 10, 2019
  2. GMan

    GMan Well-Known Member Licensed User

    Just the same example as in the other POI/EXCEL - tutorial ?
     
  3. Erel

    Erel Administrator Staff Member Licensed User

  4. GMan

    GMan Well-Known Member Licensed User

    have i the same issue at last time with the same names for different examples ?
    Here the content of the attached sample project above:

    Code:
    Sub Process_Globals
        
    Private fx As JFX
        
    Private MainForm As Form
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        MainForm = Form1
        MainForm.SetFormStyle(
    "UNIFIED")
        MainForm.Show
        CreateWorkbook
        fx.ShowExternalDocument(
    File.GetUri(File.DirApp, "1.xlsx"))
        
    ExitApplication
    End Sub

    Private Sub CreateWorkbook
        
    Dim wb As PoiWorkbook
        wb.InitializeNew(
    True'create new xlsx workbook
        Dim sheet1 As PoiSheet = wb.AddSheet("Sheet 1"0)
        sheet1.AddMergedRegion(
    1040)
        
    Dim row As PoiRow = sheet1.CreateRow(0)
        
    Dim cell As PoiCell = row.CreateCellString(1"Merged Title")
        
    Dim cs As PoiCellStyle
        cs.Initialize(wb)
        cs.HorizontalAlignment = cs.HORIZONTAL_CENTER
        cell.CellStyle = cs
        
    Dim titleStyle As PoiCellStyle
        titleStyle.Initialize(wb)
        titleStyle.BorderBottom = titleStyle.BORDER_THIN
        titleStyle.ForegroundColor = fx.Colors.LightGray
        titleStyle.HorizontalAlignment = titleStyle.HORIZONTAL_CENTER
        
    Dim font As PoiFont
        
    font.Initialize(wb)
        
    font.Bold = True
        titleStyle.SetFont(
    font)
        
    Dim titleRow As PoiRow = sheet1.CreateRow(1'second row
        titleRow.CreateCellString(1"Name")
        titleRow.CreateCellString(
    2"Price")
        titleRow.CreateCellString(
    3"Amount")
        titleRow.CreateCellString(
    4"Total (formula)")
        sheet1.SetColumnWidth(
    4256 * 20)
        SetStyleToRowCells(titleRow, titleStyle)
        
    Dim priceStyle As PoiCellStyle
        priceStyle.Initialize(wb)
        priceStyle.SetDataFormat(wb, 
    "$0.00")
        
    For i = 0 To 25
            
    Dim row As PoiRow = sheet1.CreateRow(i + 2)
            row.CreateCellString(
    1"Item " & i)
            row.CreateCellNumeric(
    2, i)
            row.CreateCellNumeric(
    3, i + 1)
            row.CreateCellFormula(
    4$"C${i + 3} * D${i + 3}"$)
            row.GetCell(
    2).CellStyle = priceStyle
            row.GetCell(
    4).CellStyle = priceStyle
        
    Next
        
    'add image to the workbook
        Dim imageIndex As Int = wb.AddImage(File.DirAssets, "smiley.png")
        
    'put the image on the sheet
        sheet1.SetImage(imageIndex, 5578)
        sheet1.SetImage(imageIndex, 
    59712)
        wb.Save(
    File.DirApp, "1.xlsx")
        wb.Close
    End Sub

    Private Sub SetStyleToRowCells(row As PoiRow, style As PoiCellStyle)
        
    For Each cell As PoiCell In row.Cells
            cell.CellStyle = style
        
    Next
    End Sub
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    You are correct. The wrong zip file was previously uploaded. It is fixed.
     
  6. GMan

    GMan Well-Known Member Licensed User

    Puuh, am I glad.
    Began thinking that the bits & bytes in my Skull are dancing HipHop :D
     
    Erel likes this.
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