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:

klaus · Oct 26, 2016

I'm problem when use .clearall while the table still contain data when I call .clearall

And what is the problem ?

lhbrito · Oct 26, 2016

Hello! Here is a new version with some changes that I believe could be helpful to others as well.

To facilitate the description of the changes, imagine the following scenario:

There is a SQLite database with "Products" table containing these fields: code, description, price, price_promotional, start_promotion, end_promotion.

Wish list:
- Display a table with only 3 columns: code, description and current price.
- Have something in the product description when it is on promotion period.
- User can't sort table using "current price" column.
- The price column need masked with prefix "US$"

The changes are:

- New event: OnLoadSQLite
This event is triggered before add a new line when loading the result of a query. Through this event you can modify the contents of any column before adding.

Considering the above scenario, the query would be: "select code, description, price, price_promotional, start_promotion, end_promotion from products order by description asc"

B4X:

Sub Table_OnLoadSQLite(aR() As String, Curs As Cursor)
  If Date.Now>=Curs.GetDouble("start_promotion") And Date.Now<=Curs.GetDouble("end_promotion") Then 'ignore this comparation - this is only example...
    aR(2) = Curs.GetDouble("price_promotional")
    aR(1) = aR(1)&"** PROMOTION"
  Else
    aR(2) = Curs.GetDouble("price")
  End If
End Sub

- New event: BeforeSort and AfterSort
When the table is large (eg, over 3000 lines), the sort of takes a few seconds. With these events, you can put a warning on the screen or manually control the way that the ordering is performed (can rerun the query using an "ORDER BY", as example)

Considering the above scenario, could have the following use:

B4X:

Sub Table_BeforeSort (col As Int, Ascend As Boolean) As Boolean
  If (col<>2) Then
    Panel_Processing.Visible = True
    DoEvents
    Return False 'can sort column
  Else
    Return True 'not sort this column
  End If
End Sub

Sub Table_AfterSort(col as Int, Ascend As Boolean)
  Panel_Processing.Visible = False
  DoEvents
End Sub

- New property: StatusLineColor, StatusLineTextColor, StatusLineAlignment and CaptionStausLine
To customize the visual of Status Line and customize text used in Automatic Status Line.

For the scenario, will be:

B4X:

Table.CaptionStatusLine = "product(s)"

- New subs: hideCols and unhideCols
If is necessary hide or unhide more than one column. Eg: Table.HideCols( Array as Int(2,3,4)

- SetSorted
Useful to adjust initial order status of table.

- Another code changes:
SetColumnsWidths: Correction to adjust size of column when exists hide cols (previous version show an additional space)

hideCol: Now really hide a column (not set size to '1', but set visible property to false)

unHideCol: Now is optional "newsize" (if is "0"), because the original size is preserved.

SelectionSort: optimizations, for big tables was significative.

LoadSQLiteDB2: New parameter: ResetNumberOfColumns and new datatype to column: "function"
If parameter "ResetNumberOfColumns" is true, the number columns of table will be adjust to same number of columns query result. If it is false, the result query can have more columns than coluns table. Same change made in LoadSQLiteDB.

For the scenario:

B4X:

Table.InitializeTable(3,Gravity.CENTER,False)
query = "select code, description, price, price_promotional, start_promotion, end_promotion from products order by description asc"
Table.LoadSQLiteDB2(SQL,query,False,False,Array As String("T","T","MaskPrice"))

Sub Table_MaskPrice(Value As String) As String
  If Value<>"" Then
    Value = "US$ "&Value
  End If
  Return Value
End Sub

P.S.: Sorry for any mistake in my english...

klaus · Oct 26, 2016

Thank you for sharing this.
But, I will not add these functions to the class because I don't want to make it more complicated with more specialized functions.
One comment, you used version 2.10 but the latest version is 2.12.

lhbrito · Oct 28, 2016

klaus said:
Thank you for sharing this.
But, I will not add these functions to the class because I don't want to make it more complicated with more specialized functions.
One comment, you used version 2.10 but the latest version is 2.12.

Hi Klaus! Thanks for reply. I didn't see that current version is 2.12, so I made again the changes on this version.

Forgive me for insisting: One of changes is to fix a "little bug" on hide columns. See the image original.png, I drawed in red. The "bug" is: When hide many columns, stay in screen a little space after last visible column. This 'bug' occurs because current version set column size to '1' when hide it. With my changes, the class don't make any changes on size of columns, just set visible property to 'false'.

This new version that I send is full compatible with current version, no change is required to used.

See these projects, they are based on your original example, i just put 3 checkbox to hide or unhide columns and increase the size of sql database to 3000 records (my customers has tables with more 6000 records):

original.zip : Table with version 2.12. Problems:

- hide 3 columns to see a 'extra space'
- Is not possible know correct size when unhide column (yes, is possible, but it is necessary put the column size into a variable before hide...)
- the query has a "order by" clause, but the table not starting with a 'sort symbol' in the header
- When touch on header to sort, is a bit slow and "freeze" the table (some users feel confused with this)

suggest.zip: Any changes make in project, i just put my version of Table.pas

- No more "bug" when hide columns
- The sort operation is more fast
- Is possible resolve all previous comments, but to this is necessary a bit changes in code (see newfeatures.zip).

newfeatures.zip: Same version of Table.pas that used in suggest.zip, but I changes to show the news features:

- When "open" table, it show correctly status of the sort on Header
- When touch on header to sort show a warning, is more "friendly" to user.
- When unhide a column the original size is restored automatically

Thank for your time and for this class, it's great!!!

orynkanov · Dec 19, 2016

Hello! Please add the ability to set the row colors.

klaus · Dec 19, 2016

You can set two row colors.
Table1.RowColor1 = Colors.Red 'for odd rows
Table1.RowColor2 = Colors.Blue 'for even rows
Then you have:
Table1.SelectedRowColor = Colors.Yellow
Table1.SelectedCellColor = Colors.Cyan

Setting different colors for each row will not be added, same for different cell colors, I don't want to make the class more and more complicated.
The source code is available, so every one can adapt the class to its own needs.

orynkanov · Dec 19, 2016

Hello!
I added the ability to set the color of cells and rows.
Also found an error in the code and fix it in innerClearAll procedure -

B4X:

If SV.IsInitialized = False Then SV.Initialize (0, 0, "SV")

. It seems so right.

advansis · Dec 23, 2016

Am I wrong or should it be:

#Event: CellClick (ColNum as int,RowNum as int)
#Event: CellLongClick (ColNum as int,RowNum as int)
#Event: HeaderClick (ColNum as int)
#Event: HeaderLongClick (ColNum as int)

?

klaus · Dec 23, 2016

If you are speaking of the event routines in the class, you are wrong!
These event routines are internal to the Table Class.
Outsides the Table class you can define event routines which are called from event routines in the class.
These need to be like:
Table1_CellClick(ColumIndex As Int, RowIndex As Int)

luciano deri · Jan 2, 2017

Sometimes i have a crash problem, in this thread i expose the problem. Klaus what do you think?
https://www.b4x.com/android/forum/threads/crash-nativo-in-system-lib-libinput-so.74669/#post-474779

klaus · Jan 2, 2017

Try to comment these two lines:

B4X:

visibleRows.Initialize
'    SV.VerticalScrollPosition = 0
'    DoEvents
SV.VerticalScrollPosition = 0

Commenting the two lines above works with simple test examples.
I don't remember when this was added and by whom.
ScrollToNow doesn't exist in ScrollView2D.
A SmoothScrollTo method does exist, perhaps try with this one.

rad · Jan 10, 2017

Hi all...
How to set the cursor to go (select) last row?
or how do we know the last number row of table?

Regards

klaus · Jan 10, 2017

I will add the NumberOfRows as a property in the next edition.
In the meantime you can add this routine to the Table class:

B4X:

'gets the number of row, read only
Public Sub getNumberOfRows As Int
    Return Data.Size
End Sub

And call it in the code with:
Table1.NumberOfRows

rad · Jan 10, 2017

klaus said:
I will add the NumberOfRows as a property in the next edition.
In the meantime you can add this routine to the Table class:

B4X:

'gets the number of row, read only Public Sub getNumberOfRows As Int Return Data.Size End Sub

And call it in the code with:
Table1.NumberOfRows

Thank you so much Klaus...
I have try it, and it's run like I expected...

Regards
RAD

FrenchDeveloper · Jan 18, 2017

Hello and thanks for this great view !

Am I wrong or this line is missing ?

B4X:

    NumberOfColumns = Props.Get("NumberOfColumns")

klaus · Jan 19, 2017

Of course!
Added in the next update.
If you want to add it in your class, it must be:
mNumberOfColumns = Props.Get("NumberOfColumns")

Merci.

FrenchDeveloper · Jan 19, 2017

Thank you for your quick reply !

mNumberOfColumns is neither declared nor used so I keep NumberOfColumns until your update

In case it will be usefull for anybody, I share a search function :

B4X:

' Search for a text in a column and return the row where it was found
' Return -1 if text was not found
Public Sub Search_text(Text As String, Column As Long) As Long
    Dim Row() As String, Row_index As Long
    For Row_index = 0 To Data.Size- 1
        Row = Data.Get(Row_index)
        If Row(Column) = Text Then
            Return Row_index
        End If
    Next
    Return -1
End Sub

klaus · Jan 19, 2017

Which version are you using?

FrenchDeveloper · Jan 19, 2017

klaus said:
Which version are you using?

I use V2-13 and I downloaded it yesterday to be sure to be up to date

klaus · Jan 19, 2017

Sorry, I think I already modified version 2.13 NumberOfColumns to mNumberOfColumns.

B4A Library [Class] Flexible Table

Attachments

Expert

Member

Attachments

Expert

Member

Attachments

Member

Expert

Member

Attachments

Active Member

Expert

Active Member

Expert

Member

Expert

Member

Member

Expert

Member

Expert

Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency