B4A Library [Class] Flexible Table

Erel · Jun 30, 2013

Thank you for sharing this. Might be worth adding a screenshot...

Mashiane · Jul 21, 2013

LoadTableFromDataBase & LoadTableFromList

Wow, I must say I found this to be very useful. I wanted to use this with a database and just query the database to generate the tables.
I am able to export the resulting query details to a list and also load the list to the table class with these two methods. However I have a small issue. The table does display the number of rows at the bottom but is however shows a blank table with no details. I then tried to save the list to a csv.csv file and checked this and found that everything is fine however the LoadTableFromCSV also displays a blank table though the number of rows display correctly at the end of the table view class.

Here is the code...

' load the table from a database directly
Public Sub LoadTableFromDatabase(SQL As SQL, Query As String, StringArgs() As String, Limit As Int)
Dim cur As Cursor
Dim colName As String
Dim colValue As String
If StringArgs <> Null Then
cur = SQL.ExecQuery2(Query, StringArgs)
Else
cur = SQL.ExecQuery(Query)
End If
Log("LoadTableFromDatabase: " & Query)
If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
' all records will be saved to a list including the header
Dim nL As List
nL.Initialize
Dim sb As StringBuilder
' build the header
sb.Initialize
For i = 0 To cur.ColumnCount - 1
colName = cur.GetColumnName(i)
sb.Append(colName).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
' build the records from rows
For row = 0 To cur.RowCount - 1
sb.Initialize
cur.Position = row
For i = 0 To cur.ColumnCount - 1
colValue = cur.GetString2(i)
If colValue = Null Then colValue = ""
sb.Append(colValue).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
Next
cur.Close
' save list to file (ERROR Check, comment the next two lines out on error fix and uncomment LoadTableFromList)
File.WriteList(File.DirRootExternal, "csv.csv", nL) ' the file creates property
LoadTableFromCSV(File.DirRootExternal, "csv.csv", True) ' this displays the number of rows but a blank table
'LoadTableFromList(nL, True) ' this displays the number of rows but a blank table
End Sub

' load table contents to table from a list
Public Sub LoadTableFromList(nList As List, HeadersExist As Boolean)
ClearAll
' the list is empty
If nList.Size-1 < 0 Then Return
Dim h() As String
If HeadersExist Then
Dim headers() As String
' read the first line of the list, it has headers separated by a comma
Dim strH As String = nList.Get(0)
headers = Regex.Split(",", strH)
Dim h(headers.length) As String
For i = 0 To headers.length - 1
h(i) = headers(i)
Next
Else
Dim firstRow() As String
Dim fRow As String
fRow = nList.get(0)
firstRow = Regex.Split(",", fRow)
Dim h(firstRow.Length) As String
For i = 0 To firstRow.Length - 1
h(i) = "Col" & (i + 1)
Next
End If
innerClearAll(h.Length)
ColumnWidth = SV.Width / NumberOfColumns
SetHeader(h)
For i = 0 To nList.Size - 1
Dim row() As String
Dim tRow As String = nlist.Get(i)
row = Regex.Split(",", tRow)
AddRow(row)
Next
End Sub

klaus · Jul 21, 2013

Here you have a routine to fill a table with a SQLite query:

B4X:

'load data from a SQLite database
'SQLite = SQL object
'Query = SQLite query
'AutomaticWidths  True > set the column widths automaticaly
Public Sub LoadSQLiteDB(SQLite As SQL, Query As String, AutomaticWidths As Boolean)
    Dim Curs As Cursor
    Curs = SQLite.ExecQuery(Query)

    NumberOfColumns = Curs.ColumnCount
    innerClearAll(NumberOfColumns)

    Dim Headers(NumberOfColumns) As String
    Dim Widths(NumberOfColumns) As Int
    Dim col, row, n As Int
    Dim str As String
    For col = 0 To NumberOfColumns - 1
        Headers(col) = Curs.GetColumnName(col)
        If AutomaticWidths = False Then
            Widths(col) = 130dip
        Else
            n = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize)
            For row = 0 To Curs.RowCount - 1
                Curs.Position = row
                str = Curs.GetString2(col)
                If str <> Null Then
                    n = Max(n, cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize))
                End If
            Next
            Widths(col) = n + 8dip
        End If
    Next
    SetHeader(Headers)
    SetColumnsWidths(Widths)

    For row = 0 To Curs.RowCount - 1
        Dim r(NumberOfColumns), str As String
        For col = 0 To NumberOfColumns - 1
            Curs.Position = row
            str = Curs.GetString2(col)
            If str <> Null Then
                r(col) = str
            Else
                r(col) = ""
            End If
        Next
        AddRow(r)
    Next

    Curs.Close
End Sub

Attached an example with the new Table class.

Best regards.

EDIT:
Removed the zip file, the latest version is in the first post.

Mashiane · Jul 21, 2013

Cool, thanks Klaus, let me download and explore!

Espinosa4 · Sep 10, 2013

Hi,
Can I change the font color after addtoactivity?

B4X:

        DateTime.TimeFormat = "HH:mm"
        If CurServiciosReducidos.GetString("TToma") <> Null Then
            Hora = DateTime.TimeParse(CurServiciosReducidos.GetString("TToma"))
            If Hora >= DateTime.TimeParse("11:59") Then
                TblServiciosReducidos.FontColor = Colors.red
            End If
        End If
        TblServiciosReducidos.AddRow(Array As String(Ser,Tom,Dej,Jor,Lin,Noc,Obs,Cor))

Thank you very very much indeed for all!

klaus · Sep 10, 2013

Not in the current version.
Attached version which does it.

Best regards.

EDIT:
Removed the zip file, the latest version is in the first post.

Espinosa4 · Sep 10, 2013

klaus said:
Not in the current version.
Attached version which does it.

Best regards.

Thank you very very much klaus! Great news! Fantastic work!

Cheers
Espinosa

Espinosa4 · Sep 11, 2013

Hi klaus,

It doesn't work for my code.
Why?
The if conditions works perfectly

B4X:

    TblServiciosReducidos.ClearAll
    For i = 0 To CurServiciosReducidos.RowCount - 1
        DateTime.TimeFormat = "HH:mm"
        If CurServiciosReducidos.GetString("TToma") <> Null Then
            Hora = DateTime.TimeParse(CurServiciosReducidos.GetString("TToma"))
            If Hora > DateTime.TimeParse("11:59") Then
                TblServiciosReducidos.TextColor = Colors.red
            Else
                TblServiciosReducidos.TextColor = Colors.blue
            End If
        End If
        TblServiciosReducidos.AddRow(Array As String(Ser,Tom,Dej,Jor,Lin,Noc,Obs,Cor))
    Next

Regards

klaus · Sep 11, 2013

I misunderstood your request the first time.
What I added is changing the text color for all entries.
But you ask for changing the text of different lines, unfortunately this is not easily possible because it needs to memorise the text color for each line.

Best regards.

Espinosa4 · Sep 11, 2013

I am very sorry, my english is very poor. Sorry.
Ok, I undertand.
Thank you very much indeed for your work!

Best regards

AubreyPCR · Sep 19, 2013

As a RAD tool, your table class seemed ideal so I gave it a go, very nice, thank you.

I am not using any local DB but rather getting my data in XML format via web services. I needed a generic way of loading data into your table from an internal structure (ie data I had in memory) so I rolled my own and thought I'd share in case it might be useful to someone else.
I store my data in a list of array objects to simulate a simple table structure. I added a sub to your class to load data from such a structure based on your code.

For anyone who is interested, here is the extra sub you can add to your table class and some sample code to show its usage.

This is how I use it :-

B4X:

    Dim ColumnHeaders() As String
    Dim ProductionData as List
  
    ColumnHeaders = Array As String ("ID", "Name", "Description", "StartTime", "EndTime", "Reporter")

    ProductionData.Initialize
    LoadDataToList(ProductionData)

    tblData.LoadListData(ColumnHeaders, ProductionData)

The sub LoadDataToList is used to populate the LIST object with data from your chosen data source. In case it is not obvious here is the code used to add each record (an array object) to the table (list object).

B4X:

       ' inside your data load loop
        Dim r(6) As String
        r(0) = ProdRecordTemp.ID
        r(1) = ProdRecordTemp.Name
        r(2) = ProdRecordTemp.Description
        r(3) = ProdRecordTemp.StartTime
        r(4) = ProdRecordTemp.EndTime
        r(5) = ProdRecordTemp.Reporter
        DataList.Add(r)

This is the sub itself.
It takes two arguments, the first a string array of column headers, the second a list object containing the data. It is assumed that the dimensions of the col headers and record array match (ie that there are the same number of fields per record as there are colum headers).
I left in the "auto column width" code but you could easily modify this sub to accept an extra array of column widths, use fixed width, include the column headers and/or widths in the data list or any other combination that works for you.

B4X:

Public Sub LoadListData(ColHeaderText() As String , DataList As List)
    Dim AutomaticWidths As Boolean
    AutomaticWidths = True
    'AutomaticWidths  True > set the column widths automaticaly
  
    NumberOfColumns = ColHeaderText.Length
    innerClearAll(NumberOfColumns)
  
    Dim Headers(NumberOfColumns) As String
    Dim Widths(NumberOfColumns) As Int
    Dim col, row, n As Int
    Dim str As String
  
    For col = 0 To NumberOfColumns - 1
        Headers(col) = ColHeaderText(col)
        If AutomaticWidths = False Then
            Widths(col) = 130dip
        Else
            n = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize)
            For row = 0 To DataList.Size -1
                Dim r(NumberOfColumns) As String
                r = DataList.Get(row)
                str = r(col)
                If str <> Null Then
                    n = Max(n, cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize))
                End If
            Next
            Widths(col) = n + 8dip
        End If
    Next
  
    SetHeader(Headers)
    SetColumnsWidths(Widths)
  
    For row = 0 To DataList.Size - 1
        Dim r(NumberOfColumns) As String
        r = DataList.Get(row)
        For col = 0 To NumberOfColumns - 1
            If r(col) = Null Then
                r(col) = ""
            End If
        Next
        AddRow(r)
    Next
  
End Sub

Regards

Espinosa4 · Sep 19, 2013

Hello,

I've problems after delete a record and the table class refresh.

After delete a record using sql I recharge the table class doing before table.clearall but the deleted record is still there. Can you help me please?

Thank you

Cheers
Carlos

klaus · Sep 19, 2013

Could you post your project ?
It would be much easier to help you, otherwise we need to guess what you have done and how.

Best regards.

Espinosa4 · Sep 19, 2013

klaus said:
Could you post your project ?
It would be much easier to help you, otherwise we need to guess what you have done and how.

Best regards.

Good evening klaus,
Thanks for your fast reply.

Of course, here the code:

B4X:

Sub BtnBorrar_Click
    Dim Result As Int
    If CurTipos.RowCount = 0 Then
        Return
    End If
    Result = Msgbox2("¿Borrar el tipo " & EdtDescripcion.Text &  "?","Confirmar","Si","No","",ImgIcono)
    If Result = DialogResponse.POSITIVE Then
        SQLTipos.ExecNonQuery("DELETE FROM Tipos WHERE NumTipo = " & NumReg)
        DBLoad
    Else
        Return
    End If
End Sub

Sub DBLoad
    Dim Descrip,HorasTra,NTipo As String
    ProgressDialogShow("Un momento por favor")
    TblTipos.Initialize(Me, "TblTipos",3,0,False)
    TblTipos.CellAlignment = Gravity.LEFT
    TblTipos.TextSize = TamanyoTexto
    TblTipos.TextColor = Colors.blue
    If Activity.Width < Activity.Height Then
        TblTipos.AddToActivity(Activity, 0,20dip, 100%x, 40%y)
    Else
        TblTipos.AddToActivity(Activity, 0, 55dip, 100%x, 82%y)
    End If
    TblTipos.HeaderColor = Colors.DarkGray
    TblTipos.HeaderTextColor = Colors.White
    TblTipos.SetHeader(Array As String("Núm","Descripción", "H. Trabajadas"))
    TblTipos.SetColumnsWidths(Array As Int(15%x,55%x,30%x))
    TblTipos.ClearAll
    For i = 0 To CurTipos.RowCount - 1
        CurTipos.Position = i
        If CurTipos.GetString("NumTipo")<> Null Then
            NTipo = CurTipos.GetString("NumTipo")
        Else
            NTipo = ""
        End If
        If CurTipos.GetString("Descripcion")<> Null Then
            Descrip = CurTipos.GetString("Descripcion")
        Else
            Descrip = ""
        End If
        If CurTipos.GetString("HorasTrabajadas")<> Null Then
            HorasTra = CurTipos.GetString("HorasTrabajadas")
        Else
            HorasTra = ""
        End If
        NumReg = CurTipos.GetString("NumTipo")
        EdtDescripcion.Text = Descrip
        EdtHoras.Text = HorasTra
        TblTipos.AddRow(Array As String(NTipo,Descrip,HorasTra))
    Next
    ProgressDialogHide
End Sub

I have also problems with JumpToRow because after the dbload code I can't go directly to a specific record I need write the code into a button event or something like that.

Best Regards
Espinosa

klaus · Sep 20, 2013

If you know the row number you should use

B4X:

TblTypos.RemoveRow(Row)

Or in your routine add this code before Initialize.

B4X:

If TblTipos.IsInitialized Then
    TblTipos.RemoveView
End If
TblTipos.Initialize(Me, "TblTipos",3,0,False)

Attached the latest version 1.24
Added RemoveView to remove the whole table.
Added SetCellAlignments(Alignments() As String which allows to set a different alignment for each column.

Best regards.

EDIT:
Removed the zip file, the latest version is in the first post.

Espinosa4 · Sep 20, 2013

All works fine, thank you very very much!!!

Thank for all your great work!

Best Regards!

Mike Olmsted · Sep 22, 2013

I would like to upgrade my app to the newest table but on certain data sets I cannot select the last record in the list.
On my Galaxy S3 it only does it in landscape....on my new Nexus 7 it is almost okay in landscape, portrait almost impossible.

klaus · Sep 23, 2013

Could you post a screenshot.
On my Nexus One it works OK.
Try to replace the height of the table in this line:
Activity.AddView(pnlTable, 0dip, 0dip, 100%x , 90%y )

Best regards.

Mike Olmsted · Sep 23, 2013

Thanks for the prompt reply. I find the product amazing.

The problem only occurs when the table exceeds one page. Try to select Wages on page 2 of the table.
If I make the table fit on one page, no problem. This has been happening since ver 1.20. My version 1.13
is fine.

B4A Library [Class] Flexible Table

Attachments

B4X founder

Expert

Expert

Expert

Active Member

Expert

Active Member

Active Member

Expert

Active Member

Member

Active Member

Expert

Active Member

Expert

Active Member

Member

Attachments

Expert

Member

Attachments