B4A Library [Class] TableView - Supports tables of any size

An improved version of this class is available here: http://www.b4x.com/forum/additional...icial-updates/30649-class-flexible-table.html


The Table class allows you to show tables of any sizes. The views (labels) are reused to avoid creating many views.

With the help of StringUtils the table can be loaded and saved to a CSV file. It shouldn't be difficult to show SQL tables using DBUtils.ExecuteMemoryTable.

SS-2012-07-04_10.38.01.png


Follow the attached example to see how the class is used. To add it to your own project you need to add the class module to your project and add a reference to StringUtils library.

RemoveRow code is available here: http://www.b4x.com/forum/showpost.php?p=146795&postcount=147

V1.10 uploaded. New method: SetColumnsWidths. Allows you to manually set the width of each column
V1.11 uploaded. Fixes a bug with grey stripes appearing.
 

Attachments

  • Table.zip
    14.9 KB · Views: 7,922
Last edited:

Mahares

Expert
Licensed User
Longtime User
Thank you Erel very much:
Here is the code for using a SQLite table instead of CSV file. See below code snippet:
OBSERVATIONS:
1. Table opens fast. Nice feature.
2. It would be complete if horizontal scrolling is possible here.
3. It would be a complete if columns width can be adjusted. Make my day with the last 2 wishes.

B4X:
Cursor1=SQL1.ExecQuery(txt)     
Dim Col_Name(4) As String
For i=0 To Cursor1.ColumnCount-1
      Col_Name(i)=Cursor1.GetColumnName(i)
Next
Table2.SetHeader(Array As String(Col_Name(0), Col_Name(1), Col_Name(2),Col_Name(3)))
Cursor1.Position=0
For i=0 To Cursor1.RowCount-1
      Cursor1.Position=i
      Table2.AddRow(Array As String(Cursor1.GetString("Field1 "), Cursor1.GetString("Field2"), Cursor1.GetString("Field3"),Cursor1.GetString("Field4")  ))
Next
 

klaus

Expert
Licensed User
Longtime User
Attached you find a modified version of the Table class with horizontal scrolling with the ScrollView2D object and columns with different widths.

Best regards.

EDIT: 2012.07.15
Updated the project with Erels version V1.11 fixing the grey line problem.

EDIT: 2012.12.07
Added project V1_13 with highlighting of the selected cell.
The colors can be changed in the Table module at lines 79, 80, 81 , 87.
 

Attachments

  • TableExampleV1_1_11_2D.zip
    15.1 KB · Views: 878
  • TableExampleV1_1_13_2D.zip
    15.2 KB · Views: 955
Last edited:

Mahares

Expert
Licensed User
Longtime User
@Klaus: C'est magnifique. I adapted your sample to importing data from a SQLite table. I used temporarily 8 fields (columns), but plan to use at leat 30 to 50 columns with thousands of rows as I experiment with it. It works very well so far: Here are few observations to think about:
1. Please see stray gray vertical bars on your sample. See atatched screenshot. On my example, I used 2 tables with different data. Both screens, top and bottom show a wide gray vertical bar 2 cm in width.
2. Is there a way to adjust the width of the columns as the content of the cell changes.
3. My top table has unique records. The bottom table has one common field (column) with the first table but has multiple records. In other words, a one-to-many relationship between the 2 tables. I would like to: When I click on a row of the top (unique records) table, the cursor scrolls to the first correcponding record of the 2nd table. Is this a possibility in this scenario.
Again, thank you so much. You are blessed with ample ingenuity.


I think I have the answer to Item 3 by using Table1 cell click event, but I can still could use some help with items 1 and 2:
Sub Table1_CellClick (Col As Int, Row As Int)
Dim MyF As String
Table2.ClearAll
MyF=Table1.GetValue(0, Row) 'Always return the contents of cell 0 and the given row
txt = "SELECT F1, F2, F3, F4, F5, F6, F7, F8 FROM MyTable WHERE F1 " _
& "LIKE '" & MyF & "'"
Cursor1=SQL1.ExecQuery(txt)

'Here I put the table2 header

'Here I put the code for cycle through the records in SQLite table and insert & add to table2 rows
Cursor1.Position=0
For i=0 To Cursor1.RowCount-1
Cursor1.Position=i
Table2.AddRow(Array As String(Cursor1.GetString(F1), Cursor1.GetString(F2), .....etc.))
Next
End Sub
 

Attachments

  • Table2DKlaus.png
    Table2DKlaus.png
    91.2 KB · Views: 773
Last edited:

markh2011

Member
Licensed User
Longtime User
How do i bring the data in from a remote mysqldb.
I am using the following to query the db:
Dim req As HttpRequest ' requesting net data
req.InitializeGet("http://thexspots.webege.com/netdb.php?action=getdev&userid="&auser)
httpC.Execute(req, 1)

I am currently putting the result into a listview but i now want to use this class. The following code is adapted from the "vision college" data on the forum and i dont have much idea how to adapt/get it to work . I assume that i will need to read the following result back to the table.
The problem is that i dont understand how to do it and need an example of the code required if possible

File.WriteString(File.DirInternalCache, "nettalk.txt",result)
reader.Initialize(File.OpenInput(File.DirInternalCache, "nettalk.txt"))

The following is the code i currently use for the listview;
Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
Dim result As String
result = Response.GetString("UTF8")
Dim Endstring As Int
Endstring = result.IndexOf("<!")
result = result.SubString2(0, Endstring)
result = result.Replace("_"," ")
File.WriteString(File.DirInternalCache, "netdevice.txt",result)
reader.Initialize(File.OpenInput(File.DirInternalCache, "netdevice.txt"))
Dim devid As String
devid = reader.ReadLine
edtImei.Text = devid
edtUserId.text = userID
Do While devid <> Null
Dim aphnum As String
aphnum = reader.ReadLine
Dim auid As String
auid = reader.ReadLine
lvdevices.SingleLineLayout.Label.TextSize = 13
lvdevices.SingleLineLayout.ItemHeight = 35
lvdevices.SingleLineLayout.Label.TextColor = Colors.Black
lvdevices.AddSingleLine( devid & ": "& aphnum)
lvdevices.SetSelection(0)
devid = reader.ReadLine
Loop
reader.Close
ProgressDialogHide
End Sub
 

klaus

Expert
Licensed User
Longtime User
Hi Mahares,
1) It seems that in Sub ShowRow you didn't set this line correctly.
I suspect this because all columns in the table have the same width, but the headers are right.
B4X:
SV.Panel.AddView(lbls(i), TotalColumnWidth(i), row * RowHeight, ColumnWidth_1(i), RowHeight_1)
2) It is possible. The code below calculates the max text width for a given column:
B4X:
Sub Globals
    Dim cvs As Canvas
    Dim bmp As Bitmap
End Sub
B4X:
Sub Activity_Create(FirstTime As Boolean)
    bmp.InitializeMutable(1, 1)
    cvs.Initialize2(bmp)
B4X:
Sub CalculateMaxTextWidth(HeaderName As String, ColumName As String) As Int
    Dim Curs As Cursor
    Dim i, MaxNumberOfChars As Int
    ' reads the max number of characters in the column
    MaxNumberOfChars = SQL1.ExecQuerySingleResult("SELECT max(length(" & ColumName & ")) FROM " & DBTableName)
    ' gets all rows with the max number of characters
    Curs = SQL1.ExecQuery("SELECT " & ColumName & " FROM " & DBTableName & " WHERE length(" & ColumName & ") = " & MaxNumberOfChars)
    Curs.Position = 0
    Dim TextMaxWidth As Int
    ' measures the text width in pixels of the header name
    TextMaxWidth = cvs.MeasureStringWidth(HeaderName, Typeface.DEFAULT, FontSize)
    For i = 0 To Curs.RowCount - 1
        Curs.Position = i
        ' gets the max text width of the text
        TextMaxWidth = Max(TextMaxWidth, cvs.MeasureStringWidth(Curs.GetString(ColumName), Typeface.DEFAULT, FontSize))
    Next
    'returns the max text width + 10dip as margin
    Return TextMaxWidth + 10dip
End Sub
FontSize is a variable holding the font size.
I tested the code in one of my programs but with a very very small database.
But I dont know how much time it would need in a hudge databse to get these values.

3) This should work with the JumpToRow function.

I modified a bit the program in post #22, the Column and Row line width handling was not the best.

Best regards.
 
Last edited:

Erel

B4X founder
Staff member
Licensed User
Longtime User
Just a small tip:
B4X:
Dim c As Canvas
c.Initialize(Activity)
Initializing the canvas with the activity object will create a bitmap at the size of the activity. If someone will call it multiple times then it will cause an out of memory error.
A better solution is to initialize it with one of the labels (or even better with a 1 pixel mutable bitmap) and store the canvas as a global object instead of calling initialize again.
 

Mahares

Expert
Licensed User
Longtime User
Good day Klaus:

I modified a bit the program in post #22, the Column and Row line width handling was not the best.

I ran your project exactly as you have it in post #22 on a phone device and a 7 inch tablet running ICS, both show the gray line image I included in my post #23. It shows up as soon as you start scrolling. I think that is due to the fact you had different column widths for all 5 columns. If you make them all with the same width, then the problem goes away, but having all the same size may not be practical. Please verify it on your device.
Salutations
 

markh2011

Member
Licensed User
Longtime User
Hi Erel,
Sorry to be a pain / dumbo...
I have tried to follow your suggestion but i get an error.
"Error description: Cannot cast type: {Type=String,Rank=0} to: {Type=String,Rank=1}
Occurred on line: 69
Table1.AddRow(devid & aphnum)
Word: aphnum

I also tried to use the load tablefromcsv but it only retuned 1 column with each device record on 1 line then the phone number on the next line.

This is the revised code:
Sub Activity_Create(FirstTime As Boolean)
httpC.Initialize("httpC")
Activity.LoadLayout("main")
Dim auser As String
auser = edtUserId.Text
Dim req As HttpRequest ' requesting net data
req.InitializeGet("http://thexspots.webege.com/netdb.php?action=getdev2&userid=1")
httpC.Execute(req, 1)
ProgressDialogShow("Loading your devices...")
Table1.Initialize(Me, "Table1", 2)
Table1.SetHeader(Array As String("DevId", "PhNum"))
Table1.AddToActivity(Activity, 0, 0dip, 100%x, 50%y)
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
Dim result As String
result = Response.GetString("UTF8")
result = result.Replace("_"," ")
File.WriteString(File.DirInternalCache, "netdevice.txt",result)
File.WriteString(File.Dirassets, "netdevice.txt",result)
reader.Initialize(File.OpenInput(File.DirInternalCache, "netdevice.txt"))
Dim devid As String
devid = reader.ReadLine
Do While devid <> " "
Dim aphnum As String
aphnum = reader.ReadLine
Table1.AddRow(devid & aphnum)
Loop
reader.Close
ProgressDialogHide
End Sub

The load from csv that partially worked is:

'Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
' Dim result As String
' result = Response.GetString("UTF8")
'' Dim Endstring As Int
'' Endstring = result.IndexOf("<!")
'' result = result.SubString2(0, Endstring)
' result = result.Replace("_"," ")
' File.WriteString(File.DirInternalCache, "netdevice.txt",result)
' reader.Initialize(File.OpenInput(File.DirInternalCache, "netdevice.txt"))
' Table1.LoadTableFromCSV(File.DirInternalCache, "netdevice.txt", True)
' reader.Close
''End Sub
 

klaus

Expert
Licensed User
Longtime User
@markh2011

Try this code:
B4X:
Do While devid <> " "
    Dim aphnum As String
    aphnum = reader.ReadLine
    dim val(1) as String
    val(0) = devid & aphnum
    Table1.AddRow(val)
Loop
or
B4X:
Do While devid <> " "
    dim val(1) as String
    val(0) = devid & reader.ReadLine
    Table1.AddRow(val)
Loop
Table1AddRow expects an array of strings even if there is only one value.

Best regards.
 

klaus

Expert
Licensed User
Longtime User
@Mahares
I don't understand why you get these grey lines.
Attached two screenshots of the program in post#22 where in the upper ScrollView the row and column lines widths are 2dips.

Can you try to change the orientation of the devices and see what happens?

Best regards.
 

Attachments

  • NexusOne.png
    NexusOne.png
    62.6 KB · Views: 464
  • Emulator.png
    Emulator.png
    26.4 KB · Views: 486

markh2011

Member
Licensed User
Longtime User
Hi Klaus,
Thanks for the reply.
If i use the first bit of code i get the header but no data.
I know that the data is correct cause i have run the query in a browser & it returns the correct results.
I have added the scrollview2d library as well but still no luck.

I have also tried with Erels latest version with your code but still cant get the results to display.

Here is the code i am using.
'Activity module
Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.
Dim httpC As HttpClient
Dim reader As TextReader
Dim userID As String

End Sub

Sub Globals
Dim Table1 As Table
End Sub



Sub Activity_Create(FirstTime As Boolean)

Dim auser As String
'auser = edtUserId.Text
Dim req As HttpRequest ' requesting net data
req.InitializeGet("http://thexspots.webege.com/netdb.php?action=getdev2&userid=1")
httpC.Execute(req, 1)
Table1.Initialize(Me, "Table1", 2)
Table1.AddToActivity(Activity, 0, 0dip, 100%x, 50%y)
Table1.SetHeader(Array As String("DevId", "PhNum"))
End Sub
Sub Activity_Resume
End Sub

Sub Activity_Pause (UserClosed As Boolean)
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
Dim result As String
result = Response.GetString("UTF8")
result = result.Replace("_"," ")
File.WriteString(File.DirInternalCache, "netdevice.txt",result)
reader.Initialize(File.OpenInput(File.DirInternalCache, "netdevice.txt"))
Dim devid As String
devid = reader.ReadLine
Do While devid <> " "
Dim val(1) As String
val(0) = devid & reader.ReadLine
Table1.AddRow(val)
Loop
reader.Close
ProgressDialogHide
End Sub
 

Mahares

Expert
Licensed User
Longtime User
1. Klaus asks:
Can you try to change the orientation of the devices and see what happens?
Mahares responds: Maybe it is an ICS issue. I changed orientation and the same gray bars appear again. Perhaps someone has tried it and let us know. There is no need to burden you with more screenshots. If we can only solve the gray bar issue, your class module would be just perfect.
2. Erel's additional SetColumnsWidths in ver 1.10 does not work with your CLASS module, since you already changed your CLASS module to reflect varying column widths. I tried to include it and made a complete mess with errors left and right. So I abandoned. Erel's sample is very good, but lacks the horizontal scroll which is essential when viewing tables.
Thanks

IMPORTANT PLEASE READ:
@Klaus: I got my hand on a device with OS2.2 and tried your sample. It is perfect There are no vertical bars. It must be OS 4 ICS that is the problem. DO you have any suggestions, because it looks bad on the ICS devices.
 
Last edited:

salmander

Active Member
Licensed User
Longtime User
Hi,

Brilliant example of class usage. A question, can I use checkboxes instead of labels in a table?
 

young61

Member
Licensed User
Longtime User
Gray Bars

I'm getting gray bars within the bottom table.

@Mahares,
Attached you find Erels' version V1.10 with variable column widths with horizontal scrolling with ScrollView2D.

Best regards.
 
Top