B4J Tutorial B4XTable + jPoi + Excel Workbook

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

The almost complete 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(3) As 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.

 

[GMan]

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

 

[Erel]

No. It is completely different than this example: https://www.b4x.com/android/forum/t...t-excel-xls-and-xlsx-workbooks.57392/#content
Example from the library thread shows how to create a new Excel workbook. It doesn't include any UI.

Example here, reads an existing workbook and shows the data in a B4XTable.

 

[GMan]

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

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(1, 0, 4, 0)
    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(4, 256 * 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, 5, 5, 7, 8)
    sheet1.SetImage(imageIndex, 5, 9, 7, 12)
    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

 

[Erel]

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

 

[GMan]

Puuh, am I glad.
Began thinking that the bits & bytes in my Skull are dancing HipHop