B4A Library [Class] Flexible Table

incendio · May 30, 2014

Hi guys,

Want to share a modified ver of LoadSQLiteDB.
This sub will display data in numeric value to numeric with digit sign. For ex, if cell value is 5000, it will display 5,000. Data in SQL database is left unchanged, but, data in table changed !

Here are the codes

B4X:

Public Sub LoadSQLiteDB2(SQLite As SQL, Query As String, FieldList As List,AutomaticWidths As Boolean)
    Dim Curs As Cursor
    Curs = SQLite.ExecQuery(Query)
  
    cAutomaticWidths = AutomaticWidths
    NumberOfColumns = Curs.ColumnCount
    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) = Curs.GetColumnName(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
            For row = 0 To Curs.RowCount - 1
                Curs.Position = row
                str = Curs.GetString2(col)
                If str <> Null 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 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
                If(FieldList.IndexOf(Curs.GetColumnName(col)) > -1) Then
                    If(str <> "") Then
                        Private Num As Int
                        str = str.Replace(",", "")
                        Num = str
                        str = NumberFormat2(Num,0,0,0,True)
                    End If
                End If
                R(col) = str
            Else
                R(col) = ""
            End If
        Next
        AddRow(R)
    Next
  
    Curs.Close
End Sub

FieldList is a list of header columns whose value will be formatted.

LucaMs · May 30, 2014

Good idea.

But you should also show how and were you fill the FieldList and, above all, consider that the decimal separator is not always a comma but a dot.

incendio · May 30, 2014

LucaMs said:
Good idea.

But you should also fill FieldList and, above all, consider that the decimal separator is not always a comma but a dot.

FieldList is fill before call this sub, something like these

B4X:

Private FieldList As List
    FieldList.Initialize
    FieldList.AddAll(Array As String("COST PRICE","SALES PRICE","BB QTY","CALC"))
    Tbl.LoadSQLiteDB2(Main.SqlLt, Q, FieldList,True)

Decimal separator, could be added as a parameter or class property. Feel free to modify it

LucaMs · May 30, 2014

This gave me another idea: you could pass an array of "types" for formatting (font type and size, color, upper/lower/capital case...)

incendio · May 30, 2014

Or pass an array of highlighted cells with a special color/font.

LucaMs · May 30, 2014

much work for the great Klaus

klaus · May 30, 2014

incendio said:
Want to ask, what is the function of no of columns variable in sub
Table.Initialize(Me, "Tbl", 1, Gravity.CENTER_HORIZONTAL, True) ?

I always set to 1, no matter how many columns returns from sql statement. No error raised and it seem a bit faster when loading (probably this is just personal op)

The NumberOfColumns parameter sets the column number.
As you are using LoadSQLiteDB the NumberOfColumns is ignored and set in the routine to the database column number.
The table class can be used to display any data not only coming from a datatbase where the column number must be given.

klaus · May 30, 2014

incendio said:
Want to share a modified ver of LoadSQLiteDB.
This sub will display data in numeric value to numeric with digit sign. For ex, if cell value is 5000, it will display 5,000. Data in SQL database is left unchanged, but, data in table changed.

Thank you for sharing this.
But I am afraid that the routine is too specifec to include it in the class.

klaus · May 30, 2014

LucaMs said:
This gave me another idea: you could pass an array of "types" for formatting (font type and size, color, upper/lower/capital case...)

incendio said:
Or pass an array of highlighted cells with a special color/font.

There are many ideas for this class.
But I am afraid that it will become too big with too many options.

Mahares · May 30, 2014

@klaus: What would make this class a complete class is the ability to have one or two left side fixed columns while scrolling horizontally. This way you can associate the data with the identifying left fixed (frozen) column.

LucaMs · May 30, 2014

Mahares said:
@klaus: What would make this class a complete class is the ability to have one or two left side fixed columns while scrolling horizontally. This way you can associate the data with the identifying left fixed (frozen) column.

I am afraid that it will become too big with too many options.

(how do you "paste" @Name?)

incendio · May 30, 2014

Mahares said:
@klaus: What would make this class a complete class is the ability to have one or two left side fixed columns while scrolling horizontally. This way you can associate the data with the identifying left fixed (frozen) column.

Looking for this too.

incendio · Jun 1, 2014

Hi guys,

I use this class to load data from sql. Total number of data is about 18500 rows and 6 columns. I took almost 90 secs, and crashed when column header clicked.

I need to refresh table when data inserted/updated/deleted and 90 secs seem to long. I don't need sorting capabilities when column header clicked, is there a way to speed up loading process?

LucaMs · Jun 1, 2014

Can you filter the records?

Or you should "paging" them, load groups of records

incendio · Jun 1, 2014

Probably, I could filter it, assuming, there are 1800 rows after filtered, and assuming again that time is linear, it will take about 9 secs to refresh the table.

Imagine, for every input, users need to wait for 9 secs to see data has been inserted into the table, I think, this is still to long.

LucaMs · Jun 2, 2014

incendio said:
Probably, I could filter it, assuming, there are 1800 rows after filtered, and assuming again that time is linear, it will take about 9 secs to refresh the table.

Imagine, for every input, users need to wait for 9 secs to see data has been inserted into the table, I think, this is still to long.

Unless it is possible to optimize the queries (other than what I have already written) ... you can always suggest users to write everything by hand

klaus · Jun 2, 2014

How do you call LoadSQLiteDB(SQL1, Query, True) ?
or
LoadSQLiteDB(SQL1, Query, False) ?
The boolean parameter indicates automatic column widths.
The width calculation could take time with many rows.
If you have set it to True, try to set it to False to see the difference.
Unfortunately, you need to set the column width your self in the code.

LucaMs · Jun 2, 2014

http://stackoverflow.com/questions/14468586/how-paging-with-sqlite-with-millions-of-records

incendio · Jun 2, 2014

klaus said:
How do you call LoadSQLiteDB(SQL1, Query, True) ?
or
LoadSQLiteDB(SQL1, Query, False) ?
The boolean parameter indicates automatic column widths.
The width calculation could take time with many rows.
If you have set it to True, try to set it to False to see the difference.
Unfortunately, you need to set the column width your self in the code.

Loading +/- 18500 rows with LoadSQLiteDB(SQL1, Query, False) took about 30 secs.

I am looking the source code in this class. It is beyond my skill right now.
How do you draw borders and row lines? Is this using built in class/sub or you draw it manually?

Is that possible to display data to users, just a few number rows that fits screen size, and display another data when users scroll it?

If screen can only fit 10 rows, table only display 10 data to users, and when users scroll, another 10 data show again to users.

incendio · Jun 2, 2014

LucaMs said:
http://stackoverflow.com/questions/14468586/how-paging-with-sqlite-with-millions-of-records

I just run a raw test, loading sqlite data to ListView, only 1 column, the longest column, without filtering, and it took instantly to display the data.

B4A Library [Class] Flexible Table

Attachments

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Expert

Well-Known Member

Well-Known Member

Similar Threads