B4A Library [Class] Flexible Table

klaus · Jun 30, 2013

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)
- B4XCollections (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.35 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:

B4X:

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

B4X:

'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.04.21 Version 3.35
Replaced the original sorting functions by new ones kindly provided by forum member RB Smissaert.
Amended first scroll scrolling back to 0
Amended Header with transparent color

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:

Kanne · Apr 30, 2020

OK, my tables are very small - about 100 rows / 10 cols. Setting colors for the headers should do it then.

krlos2004 · May 2, 2020

this component is fantastic, very good
klaus

Kanne · May 3, 2020

Hi Klaus,
me again
Would it be possible to have a callback function instead of managing the colors for each cell ?
If I could use a callback function at the end of "ShowRow" I could do whatever Iwant with the shown cells, e.g.

B4X:

If Row Mod 2 = 0 Then lbls(2).Color = Colors.Red

You would only have to pass the row and labels() and (not really necessary) values().
But I don't know if it is possible to call a callback function which is in another module and how to pass it to your table.bas.

klaus · May 3, 2020

Sorry, but I don't understand where you would insert this line !?
What would you achieve with this?

Kanne · May 4, 2020

I'm sorry - I have to describe a little more detailed:
If there would be a callback-function at the end of 'ShowRow' in table.bas the user could be able to colorize every cell like he wants without additional overhead.
example:

Private Sub ShowRow(Row As Int)
...
' new last statement in 'ShowRow' (table.bas) with dynamic call
' if Callback_Show <> "" then execute(Callback_Show, Row, lbls, Values) ' should be dynamic calling Callback_Show with the params - (I don't know how callback functions are executed in B4A)
CallbackTest(Row, lbls, Values) ' Test with a fix sub in table.bas as example
End Sub

Sub CallbackTest(Row As Int, lbls() As Label, Values() As String) '> => must be a SUB in own project / activity and set via a property to table !
If Row >= 0 And IsNumber(Values(3)) Then
If Values(3) < 0 Then lbls(3).color = Colors.red
If Values(3) > 0 Then lbls(3).color = Colors.green
End If
End Sub

The callback function must be a dynamic call to a sub in my own project ~~- and is different for every table; so we also need a new property in the call 'Callback_Show'.~~

Kanne · May 4, 2020

This would do it :

table.bas:

B4X:

Private Sub ShowRow(Row As Int)
...
    If SubExists(cCallBack, cEventName & "_ShowRow") Then
        CallSub3(cCallBack, cEventName & "_ShowRow" ,Row, lbls)
    End If
End Sub

my code:

B4X:

Sub Table1_ShowRow(Row As Int, lbls() As Label)
    Log("CallBack von Row " & Row)
    If Row >= 0 And IsNumber(lbls(3).text) Then
        If lbls(3).text < 0 Then lbls(3).color = Colors.red
        If lbls(3).text > 0 Then lbls(3).color = Colors.green
    End If
End Sub

artsoft · May 5, 2020

Hi Folks!

I have a question regarding the hiding of columns within the table custom view.

My code:

Load table data via SQLite and trying to hide some columns:

...
    Table1.NumberOfFixedColumns = 1

    ' ------------------------------------------------------------------------------

    Table1.LoadSQLiteDB_Font (DBank.SQL1, "SELECT name, descr, id, fg_color, bg_color FROM formtypes ORDER BY name", True, font_normal_g)

    Table1.hideCol(2)
    Table1.hideCol(3)
    Table1.hideCol(4)

    ' ------------------------------------------------------------------------------

    If Table1.NumberOfRows > 0 Then
        For i = 0 To (Table1.NumberOfRows-1)
            Table1.SetRowColorN(i, Table1.GetValue(4, i))
        Next
    End If

    ' ------------------------------------------------------------------------------

    Table1.HeaderHeight = (Table1.Height / 6)
    Table1.SetHeader(Array As String("Name", "Description", "", "", ""))

    ' ------------------------------------------------------------------------------

    Table1.RefreshTable

    ' ------------------------------------------------------------------------------

    Table1.hideCol(2)
    Table1.hideCol(3)
    Table1.hideCol(4)
...

At the end, I can see the columns 2, 3, and 4 with 1 pixel width.

What is wrong?

Thanks in advance for your help.

Regards ARTsoft

artsoft · May 5, 2020

Additional note:

I changed the SQLiteLoader because there was only an automatic column width calculation with the DEFAULT font.

Now LoadSQLiteDB_Font works with my own TTF-font in a correct way.

Regards
ARTsoft

Kanne · May 5, 2020

I'm also using TTF and after filling I call .SetAutomaticWidths - no problems in my project:

B4X:

Sub InitTable
    Table1.Initialize(Me,"Table1")
    Table1.AddToActivity(Activity, pnlTable.top,pnlTable.left, pnlTable.Width, pnlTable.Height)
    Table1.InitializeTable(1,Gravity.CENTER_HORIZONTAL, False)
    Table1.CellAlignment = Bit.Or(Gravity.CENTER_HORIZONTAL, Gravity.CENTER_VERTICAL)
    Table1.SetHeaderTypeFaces(Array As Typeface(myFont))
    Table1.SetTypeFaces(Array As Typeface(myFont))
...
End Sub

Sub FillTable
    Table1.SetHeaderTypeFaces(Array As Typeface(myFont))
    Table1.SetTypeFaces(Array As Typeface(myFont))
    sQuery.Initialize
    sQuery.append(" Select ID_Spieltag as Spieltag, Datum, Gruppentext as Gruppe, Spielerzahl as Spieler, Runden, Pflichtsolo as PS, komplett as OK, abgeschlossen as Fix ")
    ...
    sQuery.append("  from Spieltag order by ID_Spieltag desc")
    Table1.LoadSQLiteDB(SQL1, sQuery.ToString, True)
    Table1.SetAutomaticWidths
End Sub

artsoft · May 5, 2020

Thx a lot, Kanne!

But, working with TTF fonts is currently not the problem. My problem is (or better was now!) that I could see an empty column after hiding it. So, I check the original code and I could see that the developer was working with a width of min. 2dip! Now I changed it to 0dip with empty header texts.

And this works now!

_____________________________________________________

But I have now a new problem...Grrrr! ==>

In my code (you see it above) there is a special command to set the background colors of each row. This color comes from DB select ("bg_color" field). Currently I am only checking the table update:

I have currently a button on my activity which deletes the DB table completely and creates new records in table with random colors and names. After clicking the button, I call my table update method.

The names (and description) on the table custom view will be updated and all (hidden) data. But NOT the background colors, although I set them newly with new data from DB table.

By the way: I already use this command :

B4X:

  Table1.RefreshTable

When I leave the activity and call again this activity, then all colors are ok. VERY STRANGE!!!
It would be great if anybody could help here.

Regards
ARTsoft

Kanne · May 5, 2020

I think it's because of the label-cache used by table.bas.
With my new extenstion with the callback you are able to change the background of every cell
Hoping that Klaus will include it to table.bas - at the moment I've extended it by myself because I needed it for my project:

artsoft · May 5, 2020

Hi!

Yes, I also guess that this is caused by the internal cache mechanism.
Did you already make public your new extension - within this thread?

Best regards

Kanne · May 5, 2020

@artsoft :
it does not make sense to change the source for getting what you want - you have to sync all changes in the future by doing that.
It's better to report a bug or ask for help - I'm also using hidden cols and don't have this problem.
Also I'm working with 3 activities using tables - I never had to call RefreshTable.
And I don't know why you are hiding the cols 2 times - hiding cols shoulf be last statement, because some statements include an unhide (e.g.
SetAutomaticWidths will unhide all cols)

Kanne · May 5, 2020

yes- see post 1005 & 1006

klaus · May 5, 2020

Hey guys.
Please be patient, I will have a look at all this, hopefully tomorrow.

Kanne · May 5, 2020

Hi Klaus,
I found a small bug:
having a table with fixed col at some rows at the beginning the left border is missing.
It disappears when sorting the table or scrolling to the end and then back to the same row

solved:
it happened when using SetAutomaticWidths after NumberOfFixedColumns. When changing the order the problem is gone.

So far as I noticed you have to keep an order when formating the table, eg. SetAutomaticWidths also unhides cols:

B4X:

    .LoadSQLiteDB
    .SetHeaderTextColors
    .SetHeader
    .SetAutomaticWidths
    .NumberOfFixedColumns
    .hideCol

artsoft · May 5, 2020

@Kanne:
Yes, you are right. Making changes locally serves only on my side currently as a test - but doesnt help anybody here.
I hide some columns because I need them in the table data - but not on GUI. In example: the ID (as primary key of the table records) or the colors for the rows colors. Yes, the command RefreshTable has currently no effect in my case. So I removed it again.

@klaus:
Thanks a lot. Standing by while waiting on some feedback from you.

Best regards

artsoft · May 5, 2020

Update:

After removing the TableRefresh command in my update method, the first visible rows have no backend color ... I can only see the background color of the table.

With this command the background colors are displayed.
But only in a correct way after loading the activity ... but not with a simple color update while working in this activity.

I can also see a background color update in the table (in case of not visible background row colors), when I leave the activity with command: Activity.Finish
So I can see (for some milliseconds) the correct background colors before the activity is closed.

By the way:
ZEROSELECTION is set to ON currently in my table.

Test scenario:
If I click now on the first row, then I can see the selection background color. ....... So far, so good!
If I click again on this row in order to unselect the row, then I can see the correct color - set by my update row color code (see above).

So, I guess all these problems are update or cache problems.
Thanks in advance for all helps here.

Best regards

Kanne · May 5, 2020

in my project I'm only using tables by code - not with the designer.
So I just made a small testprogram and could update the row colors by either using SetRowColorN and RefreshTable or using RefreshTable with callback function Table1_ShowRow (tableDes is table from designer, Table1 from code - can't see any differences by this test)

B4X:

    Dim iFarben() As Int = Array As Int(Colors.Red, Colors.Blue, Colors.Green, Colors.Magenta, Colors.Cyan, Colors.Gray, Colors.LightGray, Colors.Yellow)
    Dim iColor1 As Int
    Dim iColor2 As Int

B4X:

Sub Table1_ShowRow(Row As Int, lbls() As Label)
    If (Row) Mod 2 = 0 Then
        For i = 0 To lbls.Length - 1
            lbls(i).Color = iColor1
        Next
    Else
        For i = 0 To lbls.Length - 1
            lbls(i).Color = iColor2
        Next
    End If
    'Table1.RefreshTable
End Sub


Sub Button1_Click
    iColor1 = iFarben(Rnd(0,iFarben.Length))
    iColor2 = iFarben(Rnd(0,iFarben.Length))
    
    For i = 0 To tableDes.NumberOfRows - 1
        If (i) Mod 2 = 0 Then
            tableDes.SetRowColorN(i,iColor1)
        Else
            tableDes.SetRowColorN(i,iColor2)
        End If
    Next
    tableDes.RefreshTable
    Table1.RefreshTable
End Sub

artsoft · May 5, 2020

Hm... Very strange!

I am working always with the Designer.
It seems that there is a difference while initializing the custom view.

Still waiting hopefully for any solution provided by Klaus probably tomorrow...

Thx Kanne

B4A Library [Class] Flexible Table

Attachments

Member

Member

Member

Expert

Member

Attachments

Member

Active Member

Active Member

Member

Active Member

Member

Attachments

Active Member

Member

Member

Expert

Member

Attachments

Active Member

Active Member

Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency