Android Question Why is this reading beyond the end of my spreadsheet?

[Tim Chapman]

Entire project is attached so you can see the spreadsheet.
Line 11 of the code below with the Log statement returns 65535 as the number of rows in the spreadsheet when there are actually 1397.
Any ideas?
Thanks!

Sub LoadTable(Dir As String, FileName As String)
    Dim workbook1 As ReadableWorkbook
    Dim TodoSheet As ReadableSheet
    workbook1.Initialize(Dir, FileName)
    TodoSheet = workbook1.GetSheet(0)
    If table1.IsInitialized Then
        Activity.RemoveAllViews 'remove the current table
    End If
    table1.Initialize(Me, "Table1", 5) 'The last value here sets the number of columns in the table that shows on the screen.
    table1.AddToActivity(Activity, 0, 0, 100%x, 100%y)
    Log("TodoSheet.RowsCount = "&TodoSheet.RowsCount)
    For row = 0 To TodoSheet.RowsCount - 1
        'Dim values(5) As String
        coll = 0
        For i = 0 To 17 'i is the column number. values.Length is from the Dim values(6) line a few lines up.
            If i = 1 Or i = 2 Or i = 5 Or i = 6 Or i = 17 Then
                coll = coll + 1
                Todos(coll,row) = TodoSheet.GetCellValue(i, row)
                If i = 1 And Todos(coll,row) = "" Then
                    LastTodo = i
                    Exit
                End If
            End If
        Next
    Next

 

[emexes]

I "successfully" reproduced the error here using your project.

Then I deleted the empty-but-yellow-formatted-so-maybe-they-still-count-as-non-blank rows from the spreadsheet (and renamed it to make the changes stick - for some reason, the files in B4A files tab seem to keep to their first version, even after doing a Sync) and then I got:

** Activity (main) Resume **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
TodoSheet.RowsCount = 1397
** Activity (main) Resume **

Mildly interesting is that I use LibreOffice Calc and it loaded the original file as being shaded yellow down to its last row 1048576 (2^20) not 65535 (2^16 - 1).

So maybe the yellow formatting range is stored as just a column letter, and that gets translated to "column letter row 1: column letter row max possible" which .RowsCount then includes when determining the highest numbered row reference. No, I don't know that for certain, I'm just guessing at reasons. ?

 

[emexes]

Any ideas?

A fix (or workaround?) might be to make your own more-accurate count by scanning upwards from the supplied .RowsCount looking for the first non-blank row ie that actually contains data, not just formatting.

 

[Tim Chapman]

Thank you! That is what I have chosen to do.