B4A Library [Class] Flexible Table

This thread will be used by Erel, Melamoud and myself to discuss / post new releases of the Table class.

The table class is a flexible UI component that enable scrollable table like UI, with sortable columns, multiselect rows etc

the table is very efficient, maintaining labels only for visible rows

old thread with details : http://www.b4x.com/forum/additional...view-supports-tables-any-size.html#post110901

The class depend on following libraries:
- StringUtils (standard)
- SQL (standard)
- JavaObject (standard)
- ScrollView2D (additional)

List of major features.
1. scrollable table UI
2. sortable columns
3. select a row, cell or multi select rows
4. callback for selection / click a cell / row
5. callback for long click action
6. read / write to CSV file

Current version --> 3.33 Custom View
Current version --> 1.44 Class

Other complementary routines:

Load data with the Remote Database Connector.

EDIT: LucaMs has written a routine to fill a table with a Remote Database Connector query result see post 182.
The routine hasn't been added into the Class for the reasons explained in post 183.
A sample program can be found HERE.

Code:

'load data from a RDC Request
'Result = DBResult object got from a RDC request
'AutomaticWidths  True > set the column widths automaticaly
'Written by LucasMs
Public Sub LoadRDCResult(Result As DBResult, AutomaticWidths As Boolean)
    cAutomaticWidths = AutomaticWidths
    NumberOfColumns = Result.Columns.Size
    innerClearAll(NumberOfColumns)

    Dim Headers(NumberOfColumns) As String
    Dim ColumnWidths(NumberOfColumns) As Int
    Dim HeaderWidths(NumberOfColumns) As Int
    Dim DataWidths(NumberOfColumns) As Int
    Dim col, row As Int
    Dim str As String
    For col = 0 To NumberOfColumns - 1
        Headers(col) = Result.Columns.GetKeyAt(col)
        If AutomaticWidths = False Then
            ColumnWidths(col) = 130dip
            HeaderWidths(col) = 130dip
            DataWidths(col) = 130dip
        Else
            HeaderWidths(col) = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize) + 8dip + cLineWidth
            DataWidths(col) = 0

            Dim FieldValue As Object
            For row = 0 To Result.Rows.Size - 1
                Dim Record() As Object = Result.Rows.Get(row)
                FieldValue = Record(col)
                If GetType(FieldValue) = "java.lang.String" Then
                    DataWidths(col) = Max(DataWidths(col), cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize) + 8dip + cLineWidth)
                End If
            Next
            ColumnWidths(col) = Max(HeaderWidths(col), DataWidths(col))
        End If
    Next
    SetHeader(Headers)
    SetColumnsWidths(ColumnWidths)

    For Each Record() As Object In Result.Rows
        Dim R(NumberOfColumns) As String
        Dim FieldV As String
        For col = 0 To NumberOfColumns - 1
            FieldV = Record(col)
            R(col) = FieldV
        Next
        AddRow(R)
    Next
End Sub

This is another routine updated by cimperia in post #392 using a Map for the columns and a List for the rows.

'load data from a RDC Request
'A RDC request returns a DBResult object, therefore this method
'could be called as is:
'LoadRDCResult(DBResult.Columns, DBResult.Rows, True)
'AutomaticWidths  True > set the column widths automaticaly
'Written by LucasMs
Public Sub LoadRDCResult(Columns As Map, Rows As List, AutomaticWidths As Boolean)
  cAutomaticWidths = AutomaticWidths
  NumberOfColumns = Columns.Size
  innerClearAll(NumberOfColumns)

  Dim Headers(NumberOfColumns) As String
  Dim ColumnWidths(NumberOfColumns) As Int
  Dim HeaderWidths(NumberOfColumns) As Int
  Dim DataWidths(NumberOfColumns) As Int
  Dim col, row As Int
  Dim str As String
  For col = 0 To NumberOfColumns - 1
    Headers(col) = Columns.GetKeyAt(col)
    If AutomaticWidths = False Then
      ColumnWidths(col) = 130dip
      HeaderWidths(col) = 130dip
      DataWidths(col) = 130dip
    Else
      HeaderWidths(col) = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize) + 8dip + cLineWidth
      DataWidths(col) = 0

      Dim FieldValue As Object
      For row = 0 To Rows.Size - 1
        Dim Record() As Object = Rows.Get(row)
        FieldValue = Record(col)
       If GetType(FieldValue) = "java.lang.String" Then
         DataWidths(col) = Max(DataWidths(col), cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize) + 8dip + cLineWidth)
       End If
      Next
      ColumnWidths(col) = Max(HeaderWidths(col), DataWidths(col))
    End If
  Next
  SetHeader(Headers)
  SetColumnsWidths(ColumnWidths)

  For Each Record() As Object In Rows
    Dim R(NumberOfColumns) As String
    Dim FieldV As String
    For col = 0 To NumberOfColumns - 1
      FieldV = Record(col)
      R(col) = FieldV
    Next
    AddRow(R)
  Next
End Sub

Load data from a MSMariaDB database.

Another routine for loading data from a MSMariaDB database can be found in post#727.
Thanks to @Magma.

Updates:
EDIT: 2024.01.13 Version 3.33
Changed possible values for DataType
TEXT and NUMBER become T, R and I
Amended problem with column colors
Amended problems with SetHeaderColors and SetHeaderTextColors

Version 3.32
Amended Header and HeaderFirst problem in SaveCSVFromTable
Moved If (lblStatusline... from AddRow to ShowRow

Version 3.31
Added SingleLine property for the Designer
Added StatusLineHeight as a property
Added FastScrollLabelMaxChars as a property

EDIT: 2021.06.28 Version 3.30
Added a check for none numeric values for numeric sorting.

EDIT: 2021.06.28 Version 3.29
Amended problem with column colors
Version 3.28
Added NumberOfColumns in the code
Added TopRowIndex method
Version 3.27
Amended MultiSelect EDIT: 2020.09.02 Version 3.26
Amended problem with sort with remove accents
Amended problem with SetRowColorN
Added SetCellAlignmentColN method
Added SetHeaderAlignmentColN method

EDIT: 2020.08.05 Version 3.24
Amended problem with JumpToRowAndSelect not being selected.
Amended error when setting RowHeight before the table initialized

EDIT: 2020.06.19 Version 3.22
Amended error in the insertRowAt routine.

EDIT: 2020.05.25 Version 3.21
Amended bug with TextSize in fixed columns

EDIT: 2020.05.16 Version 3.20
Added fast scroll feature
Version 3.19
Improved automatic width calculation and hidden columns
Version 3.18
Added a check in RemoveRowColorN to ensure that Row is not out of bounds
Added ShowRow event
Amended automatic width calculations
Amended hidden column width problem

EDIT: 2020.04.21 Version 3.17
Amended HeaderHight problem with fixed columns

EDIT: 2020.04.21 Version 3.16
Amended two errors.

EDIT: 2020.04.14 Version 3.14
Added the methods below
- LoadSQLiteDB4(SQLite As SQL, Query As String, AutomaticWidths As Boolean)
loads SQLite data with data type checking
- LoadSQLiteDB5(SQLite As SQL, Query As String, Values() As String, AutomaticWidths As Boolean).
loads SQLite data with data type checking , similar to LoadSQLiteDB4 but for parametrized queries.
- GetColumnDataTypes As String(), returns an Array with the data type for each column.
- GetColumnDataType(Column As Int) As String, returns the data type of the fiven column.
Added the InnerTotalWidth property, read only.
Added multiple first fiexed columns
Added line colors

EDIT: 2020.03.10 Version 3.10
Amended bug reported HERE

EDIT: 2020.03.06 Version 3.09
Amended bug reported HERE.

EDIT: 2020.02.29 Version 3.08
Amended SetHeaderTypefaces method problem reprted HERE.
Added HeaderTypeface property.

EDIT: 2020.01.08 Version 3.07
Amended bug ShowStatisLine = False property bug.
Added MultiSelect property to Designer properties.
You need to open and close the Designer when you use the new version the first time to make the MultiSelect property active.

EDIT: 2019.12.28 Version 3.06
Amened some bugs

EDIT: 2019.12.25 Version 3.05
Added FirstColumnFixed property which allows to fix the first column.
Attention: You need to open and close the Designer to make the new property active.

EDIT: 2019.11.15 Version 3.04
- Added SelectedRowTextColor and SlectedCellTextColor properties
- Added ZeroSelections property, True > when a selected row is pressed it will be unselected False > it remains selected.

EDIT: 2019.11.12 Version 3.03
- Changed JumpToRowAndSelect(Row As Int, Col As Int) to JumpToRowAndSelect(Col As Int, Row As Int)
- Changed LoadSQLiteDB2 signature. Replaced the possible values from "T", "I", "R" to "TEXT", "NUMBER" for coherence with SetColumnDataTypes.
- Added internal sorting bitmaps, avoids loading the image files into the Files folder.
- Added two new properties: SortBitmapWidth and SortBitmapColor.
- Added SetCustomSortingBitmaps method, which allows to use custom bitmaps instead of the internal ones.
Attention: You need to open and close the Designer to make the new properties active.
Attention: You need to invert the parameters in JumpToRowAndSelect.

EDIT: 2019.07.04 Version 3.02
Amended error reported in post #887

EDIT: 2019.06.26 Version 3.01
Amended SingleLine property setting in the code

EDIT: 2019.04.05 Version 3.00
Amended SetColumnColors and SetTextColors
Removed Reflection library dependency

EDIT: 2018.04.11 Version 2.29
Version 2.27
set the two variables sortedCol and sortingDir to Public instaed of Private
added RemoveAccent routine for sorting with accented characters
Version 2.28
Added SetHeaderTypeFaces
Added SortRemoveAccents property
Version 2.29
Added SaveTableToCSV2 with a user defined separator character

EDIT: 2018.04.11 Version 2.26
added LoadSQLiteDB3 method using SQLExec2 instead of SQLExec
The query can include question marks which will be replaced with the values in the array.

EDIT: 2018.03.27 Version 2.25
amended minor errors
added UpdateCell method

EDIT: 2017.11.19 Version 2.22
improved JumpToRowAndSelect scrolls horizontally to the selected column
improved setHeaderHeight
added padding for status bar Label

EDIT: 2017.06.27 Version 2.19
Replaced DoEvents by Sleep(0)
Asked HERE

EDIT: 2017.06.27 Version 2.19
Replaced DoEvents by Sleep(0)
Asked HERE

EDIT: 2017.05.16 Version 2.18
Amended error reported HERE.

EDIT: 2017.03.09 Version 2.17
Amended error reported HERE.

EDIT: 2017.03.09 Version 2.15
Amended error reported here, Event signatures
#Event: CellClick(col As Int, row As Int)
#Event: CellLongClick(col As Int, row As Int)

EDIT: 2016.12.05 Version 2.14
Added NumberOfColumns and NumberOfRows as Public variables.
Amended error reported here.

EDIT: 2016.12.05 Version 2.13
Amended error reported here.
Added NumberOfColumns as a property for the Designer.

EDIT: 2016.07.30 Version 2.10
Amended error with TextAlignment and HeaderTextAlignment reported in post #606

EDIT: 2016.03.15 Version 2.00
Added CustomView support.
This version can be compiled into a library.
Changes between the previous versions and version 2.00
For a Table added in the Designer, this is new
No need to initialize nor add it onto a parent view
'For a Table added in the Designer, this is new
'No need to initialize nor add it onto a parent view

For a Table added in the code:
The Initialize routine has been splittend into two routines.
New:
Initialize (CallBack As Object, EventName As String)
InitializeTable (vNumberOfColumns As Int, cellAlignement As Int, showStatusL As Boolean)
'Example:
Table1.Initialize(Me, "Table1")
Table1.InitializeTable(5, Gravity.CENTER_HORIZONTAL, True)

Old:
Initialize(CallBack As Object, EventName As String, vNumberOfColumns As Int, cellAlignement As Int, showStatusL As Boolean)
Example:
Table1.Initialize(Me, "Table1", 5, Gravity.CENTER_HORIZONTAL, True)

EDIT: 2015.04.29 Version 1.43
As the modifications in LoadSQLiteDB don't work in all cases I went back.
LoadSQLiteDB as in version 1.40
Added LoadSQLiteDB2 where the column data types must be given.

EDIT: 2015.04.26 Version 1.42
Changed he LoadSQLiteDB routine, version 1.41 didn't work as expected.
The final solution was suggested by cimperia HERE.

EDIT: 2015.04.16 Version 1.41
Changed the LoadSQLiteDB routine according to the error reported in the SQL issue thread
and the SQLite Cursor GetString versus GetDouble thread.
The problem appears with numbers bigger than 999999.
I left version 1.40 in case of problems.
I tested it with a few databases, but I am not sure if it works in all cases.

EDIT: 2015.03.05
Amended bugs reported in posts #383 and #386
Added SetAutomaticWidths routine

EDIT: 2015.02.19
Amended the problem alignment reported in post # 378

EDIT: 2015.02.13
Amended the problem of rows not shown reported in post # 371

EDIT: 2015.01.09
Added header aligments

EDIT: 2014.08.14
Added HeaderHeight property
Amended RowColor problem reported in post #260

EDIT: 2014.08.10
Added SortColumn property asked in post #266
Added UseColumnColors ColumnColors and HeaderColors propeties

EDIT: 2014.05.10 Added RowHeight as a property

Screenshot:

 

[Espinosa4]

Good morning klaus!
The new version works fine! Many many thanks for your work and for your time!

Best regards,
Espinosa

 

[Mike Olmsted]

Okay, I am starting to be a believer but........................

table1.SetColumnsWidths(Array As Int(60dip,170dip,60dip,60dip,120dip,00dip,00dip))

doesn't work anymore, need to set column width at 2 or more.

 

[klaus]

What exactly doesn't work.
I tried setting column widths to 0 in a test program and it works.
What am I missing ?

 

[Mike Olmsted]

The problem only occurs after a table refresh ( like after an edit). Try my refresh button.

 

[klaus]

Hi Mike,
An updated version is available in the first post.

 

[elitevenkat]

Hi Klaus
I do not have b4a v.3 but 2.71 When I tried it say version conflict. Do I have to upgrade or do you have (sample) for v 2.71 for me test.

 

[Mike Olmsted]

Hi Klaus
I do not have b4a v.3 but 2.71 When I tried it say version conflict. Do I have to upgrade or do you have (sample) for v 2.71 for me test.

Just make sure you extract all......then say okay a couple times...and compile in 2.71.....

 

[elitevenkat]

okay i will try ...

 

[HARRY]

Looking for simple example.

Hi, recently I have made a serious start with Basic4Android; I have been using Basic4PPC for many years. Now I am converting some 'old' programs. In Basic4PPC I use a KML file by reading it line by line, analyzing the contents and storing each collection of data of an object in a multi column (13) table, columns having a header and many columns with column width set to 0.

I can find examples of using CSV files or data bases, but not what is necessary to define the table , the columns, etc and adding rows. I understood that the complete KML file has to be read into a list and that that list can be operated upon line by line; that works well. May be that I am thinking still too much in Basic4PPC.

Can somebody help me.

Harry

 

[Erel]

You should post the code that you are trying to run.

Table.AddRow adds a single row.

 

[HARRY]

Hallo Erel,
This is the code of the program:

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim KMLPath As String
    Dim KMLFile As String
    Dim TextLine As String
    Dim FirstLine As String
    Dim SecondLine As String
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim POITable As Table
    Dim PanelForTable As Panel
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("KMLtest")
   
    PanelForTable.Initialize("")
    Activity.AddView(PanelForTable, 20dip, 20dip, 100%x - 40dip, 100%y - 90dip)
   
    POITable.Initialize(Me, "POITable", 15, Gravity.CENTER_HORIZONTAL, True)
    POITable.CellAlignment = Bit.OR(Gravity.LEFT, Gravity.CENTER_VERTICAL)
    POITable.HeaderColor = Colors.Blue
    POITable.HeaderTextColor = Colors.Yellow
    POITable.TextColor = Colors.Blue
    POITable.TableColor = Colors.Red
    POITable.AddToActivity(PanelForTable, 0, 0, PanelForTable.Width, PanelForTable.Height)   
    POITable.SetColumnsWidths(Array As Int(0,0,0,0,0,0,0,0,0,0,0,0,0,0,100))
    KMLFile = "IGO.kml"
    KMLPath="/storage/extSdCard/iGo/content/userdata/poi/"
    ReadKML
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub ReadKML
Dim State
Dim GroupName
Dim POIF
Dim POII
Dim POIGroup
Dim POIName
Dim POIDescription
Dim POIComment
Dim POISurface
Dim POITelephone
Dim POIPhoto1
Dim POIPhoto2
Dim POIPhoto3
Dim POILongitude
Dim POILatitude
Dim POIVisits
Dim POIWWW
Dim TempLine As String
Dim ToModStr As String
Dim Pos As Int
Dim CurrentGroup As String
Dim POIList As List
Dim i As Int
Dim XMLStr As String
Dim KMLStr As String
    XMLStr="<?xml"
    KMLStr="<kml"
    File.OpenInput(KMLPath,KMLFile)
    POIList=File.ReadList(KMLPath,KMLFile)
    TempLine=POIList.Get(0)
    TextLine=TempLine.Trim
    TempLine=TextLine.SubString2(1,6)
    If TempLine <> XMLStr Then
        BadKml
    Else
        FirstLine=TextLine
    End If
    TempLine=POIList.Get(1)
    TextLine=TempLine.Trim
    If TextLine.SubString2(0,4) <> KMLStr Then
        BadKml
    Else
        SecondLine=TextLine
    End If
    For i = 2 To POIList.Size-1
        TempLine=POIList.Get(i)
        TextLine=TempLine.Trim
        If TextLine.Contains("<Folder>") Then
            State=1
        Else If TextLine.Contains("<name>") Then
            If State =1 Then               
                CurrentGroup=TextLine.SubString2(6,TextLine.Length-7)
                'GroupsTable.AddRow(CurrentGroup)
            Else If State=2 Then
                POIGroup=CurrentGroup
                POIName= TextLine.SubString2(6,TextLine.Length-7)
            End If
        Else If TextLine.Contains( "<Placemark>") Then
            State =2
            POIF=""
            POII=""
            POIGroup=""
            POIName=""
            POIDescription=""
            POIComment=""
            POIPhoto1=""
            POIPhoto2=""
            POIPhoto3=""
            POILongitude=""
            POILatitude=""
            POIVisits=""
            POISurface=""
            POITelephone=""
            POIWWW=""
           
        Else If TextLine.Contains( "<phoneNumber>")  Then
            POITelephone= TextLine.SubString2(13,TextLine.Length-14)
        Else If TextLine.Contains("<description>") Then
            POIDescription=TextLine.SubString2(22,TextLine.Length-17)
        Else If TextLine.Contains( "<Data name=" & Chr(34) & "Surface" & Chr(34) & "><value>") Then
            POISurface=TextLine.SubString2(28,TextLine.Length-15)
        Else If TextLine.Contains("<Data name=" & Chr(34) & "Comment" & Chr(34) & "><value>")  Then
            ToModStr=TextLine.SubString2(28,TextLine.Length-15)
            POIComment=ToModStr.Replace(Chr(95)& Chr(95) , Chr(13) & Chr(10))
            ToModStr=POIComment
            POIComment=ToModStr.Replace("/_", "_")
        Else If TextLine.Contains("<Data name="& Chr(34)& "Photo1" & Chr(34) & "><value>") Then
            POIPhoto1=TextLine.SubString2(27,TextLine.Length-15)
            POIF="*"
        Else If TextLine.Contains("<Data name="& Chr(34)& "Photo2" & Chr(34) & "><value>")Then
            POIPhoto2=TextLine.SubString2(27,TextLine.Length-15)
            POIF="*"
        Else If TextLine.Contains("<Data name="& Chr(34)& "Photo3" & Chr(34) & "><value>") Then
            POIPhoto3=TextLine.SubString2(27,TextLine.Length-15)
            POIF="*"
        Else If TextLine.Contains("<Data name="& Chr(34)& "Visits" & Chr(34) & "><value>") Then
            POIVisits=TextLine.SubString2(27,TextLine.Length-15)
        Else If TextLine.Contains("<Data name="& Chr(34)& "WWW" & Chr(34) & "><value>") Then
            POIWWW=TextLine.SubString2(24,TextLine.Length-15)
            POII="*"
        Else If TextLine.Contains("<coordinates>") Then
            Pos=TextLine.IndexOf2( ",",0)
            POILongitude=TextLine.SubString2(13, Pos)
            POILatitude= TextLine.SubString2(Pos +1, TextLine.Length- 15)       
        Else If TextLine.Contains("</Placemark>")    Then
            POITable.AddRow(Array As String(POIDescription,POIPhoto1,POIPhoto2,POIPhoto3,POIComment,POILongitude,POILatitude,POIVisits,POIGroup,POISurface,POITelephone,POIWWW,POIF,POII,POIName))
        Else If TextLine.Contains("</Folder>") Then
            If State=3 Then
                Exit
            Else
                State=3
            End If
        End If   
    Next
    'FileClose(KML)
    'KMLMod=False
    'GroupsTable.TableSort("Groepen ASC")
    'GroupsTable.Visible=True
    'T4Ignore=True
End Sub

Sub BadKml
    Msgbox ("Foutief begin KML bestand", "KML")
    Activity.Finish
   
End Sub

If more convenient for you I could also mail the code and the file to be loaded into the table.

Regards,

Harry

 

[Erel]

You should use an XML parser to parse the string. It will be much easier.

What is the result? The row is not added?

 

[HARRY]

Hi Erel,

The program does not reach AddRow. It halts on line 191 of the Table module in the Sub SetColumnWidths with as LastException: java.langRuntimeException: Object should first be initialized(View).

Harry

 

[Erel]

Run your program in debug mode and see which line raises this error. I'm not familiar with this implementation. Though I'm sure that we will be able to solve this error...

 

[klaus]

It would be much easier to help you if you posted your project as a zip file.

 

[HARRY]

Hallo Klaus,
Please find attached a .zip file containing the program and the file. I did some more tests: TableV1_29.b4a supplied with the table module runs well as it is. However, when I insert after Table1.AddToActivity(pnlTable, 0, 0, pnlTable.Width, pnlTable.Height) a line with
the text Table1.SetColumnsWidths(Array As Int(10dip,10dip,10dip,10dip)) the program reports the same error .

I do hope that I am not putting you and Erel at work because of a stupid error of me.

Regards,

Harry

 

[klaus]

Here you are.
You missed adding the headers !
In your ReadKML routine you Dim a lot of variables without the type specification.

But I don't understand why filling a table with 15 columns and showing only 1 ?

 

[HARRY]

Hallo Klaus,

Thanks!

I did't find that I had to add headers and as I was not interested in headers ......

I understood that String is the default type in Basic4Android.

The visible column is the one used to select an item ; then the program, in Basic4PPC 8 Forms, uses all other data of the selected row. Now I have to find another solution as the table is only accesssable in one Activity. May be the best way is to copy the table, after sorting, into an array of types in Process Globals. The table is not that large.

Regards,

Harry

 

[lhbrito]

How can I resize (or move) the table after AddToActivity?

explaining:
On my screen I will use two tables, the first by default is on top and the second at the bottom.
I want to let the user change the visibility of these tables.
(example: if the first table to be invisible, the second has to occupy the entire screen ...)

Is it possible?

Thanks!

 

[klaus]

Not in the current version, it would add need to these properties in the Class code.