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

Mahares · Jul 9, 2012

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 · Jul 9, 2012

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.

Mahares · Jul 9, 2012

@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

markh2011 · Jul 10, 2012

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

Erel · Jul 10, 2012

This line adds the data to the list:

B4X:

lvdevices.AddSingleLine( devid & ": "& aphnum)

You should instead use Table.AddRow and add the data to the table.

klaus · Jul 10, 2012

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.

Erel · Jul 10, 2012

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.

klaus · Jul 10, 2012

Thank's Erel, I was surely more concerned about the principle rather than the memory management.
The code in post#26 is changed.

Thank's again and best regards.

Mahares · Jul 11, 2012

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 · Jul 11, 2012

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 · Jul 11, 2012

@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 · Jul 11, 2012

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

Erel · Jul 11, 2012

I've uploaded a new version (1.10). This version adds a new method: SetColumnsWidths, which allows you to manually set the width of each column.

markh2011 · Jul 11, 2012

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

Erel · Jul 11, 2012

Please use [ code ] tags (without spaces) when posting code.

The problem is probably not in the Table code. Try to manually add some data and you will see that it gets added.

Mahares · Jul 11, 2012

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.

salmander · Jul 11, 2012

Hi,

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

Erel · Jul 12, 2012

Yes. Search the Table class for: "As Label" and change it to "As CheckBox".

klaus · Jul 12, 2012

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

Best regards.

young61 · Jul 13, 2012

Gray Bars

I'm getting gray bars within the bottom table.

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

Best regards.

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

Attachments

Expert

Expert

Attachments

Expert

Attachments

Member

B4X founder

Expert

B4X founder

Expert

Expert

Member

Expert

Expert

Attachments

B4X founder

Member

B4X founder

Expert

Active Member

B4X founder

Expert

Attachments

Member