Android Question Excel and date selector

[Colin Evans]

Hi, just having a look at B4A which at first glance looks amazing but being a complete novice I'm following examples and tutorials to try and learn as I go along.

My first project would be to create a phone app for myself that has a calendar on screen that when a date is clicked information from an excel spreadsheet pops up relevant to the date selected.

I have played with the excellent excel example and got it load my spreadsheet but bviously it loads the full spreadsheet onto the screen, whereas I want only the relevant bits linked to the date.

I assume this is possible but unfortunately I can't find and examples or guidance anywhere that will help a pure novice like myself,

Attached is a sample of my spreadsheet in the hope that someone will have a few minutes to create a sample that I can understand

Basically the screen would consist of a calendar

then fields labelled with the corresponding value next to them

Date: Day
1st Lock High Water
Last Lock Height
Lock Close

In the hope that someone can help
Many thanks
COlin

Don't seem to be able to upload the xls file so below is the format

Date Day 1st Lock HW Height Last Lock Lock Close
01/05/2016 Sun 04:35:00 06:30:00 7.70 08:00:00 08:20:00
02/05/2016 Mon 05:45:00 07:45:00 7.90 09:20:00 09:40:00
03/05/2016 Tue 06:45:00 08:55:00 8.50 10:40:00 11:00:00
04/05/2016 Wed 07:35:00 09:55:00 9.10 11:50:00 12:10:00
05/05/2016 Thur 08:20:00 10:50:00 9.60 12:50:00 13:10:00
06/05/2016 Fri 09:05:00 11:40:00 10.00 13:45:00 14:05:00
07/05/2016 Sat 09:55:00 12:25:00 10.10 14:35:00 14:55:00

 

[Erel]

You can use ReadableSheet.GetCellValue to get the value of the cells.

 

[DonManfred]

    Dim workbook1 As ReadableWorkbook
    Dim timesSheet As ReadableSheet
    workbook1.Initialize(File.DirAssets, "locktimes.xls")
    timesSheet = workbook1.GetSheet(0)
   
    'DateTime.DateFormat = "MM.dd.yyyy"
    Dim datetosearch As Long = DateTime.DateParse("01/05/2016")
    Log($"Date to search: ${DateTime.Date(datetosearch)}"$)
    If timesSheet.RowsCount > 0 Then
        For row = 0 To timesSheet.RowsCount -1
            Log($"Checking row: ${row}"$)
            If timesSheet.GetCellValue(0, row) = DateTime.Date(datetosearch) Then
                'day    1st Lock    HW    Height    Last Lock    Lock Close
                Dim day As String = timesSheet.GetCellValue(1, row)
                Dim firstlock As String = timesSheet.GetCellValue(2, row)
                Dim hw As String = timesSheet.GetCellValue(3, row)
                Dim height As String = timesSheet.GetCellValue(4, row)
                Dim last As String = timesSheet.GetCellValue(5, row)
                Dim lock1 As String = timesSheet.GetCellValue(6, row)
                Dim lock2 As String = timesSheet.GetCellValue(7, row)
                Dim close As String = timesSheet.GetCellValue(8, row)
                'For col = 1 To timesSheet.ColumnsCount -1
                '    Log(timesSheet.GetCellValue(col, row))
                'Next               
            End If
        Next
    End If

 

[Colin Evans]

Hi, thanks for taking the time to reply and offering what looks like a solution, as I have said I am a novice when it comes to programming never mind B4A. I have programmed in VB6 but usually use the design screen i.e. paint the text fields, buttons, lists, calendar / date picker etc and then program the bits afterwards.

So your offering is very good but how would I get to the point where it would be actioned ? Not really making myself clear I don't think.

Basically I need to know how to have a screen with a Date picker or calendar option that that I can select a specific date which then actions your code and selects the relevant row and then fills the text on the screen, I'd also need to know where to put the spreadsheet as shown
workbook1.Initialize(File.DirAssets, "locktimes.xls"

If you have any examples I could learn from I'd greatly appreciate it

Many thanks

Colin

 

[Erel]

You can use this picker: [Class] AnotherDatePicker - A simple "web style" date picker

Do whatever you need to do from the Close event.

 

[Colin Evans]

    Dim workbook1 As ReadableWorkbook
    Dim timesSheet As ReadableSheet
    workbook1.Initialize(File.DirAssets, "locktimes.xls")
    timesSheet = workbook1.GetSheet(0)
  
    'DateTime.DateFormat = "MM.dd.yyyy"
    Dim datetosearch As Long = DateTime.DateParse("01/05/2016")
    Log($"Date to search: ${DateTime.Date(datetosearch)}"$)
    If timesSheet.RowsCount > 0 Then
        For row = 0 To timesSheet.RowsCount -1
            Log($"Checking row: ${row}"$)
            If timesSheet.GetCellValue(0, row) = DateTime.Date(datetosearch) Then
                'day    1st Lock    HW    Height    Last Lock    Lock Close
                Dim day As String = timesSheet.GetCellValue(1, row)
                Dim firstlock As String = timesSheet.GetCellValue(2, row)
                Dim hw As String = timesSheet.GetCellValue(3, row)
                Dim height As String = timesSheet.GetCellValue(4, row)
                Dim last As String = timesSheet.GetCellValue(5, row)
                Dim lock1 As String = timesSheet.GetCellValue(6, row)
                Dim lock2 As String = timesSheet.GetCellValue(7, row)
                Dim close As String = timesSheet.GetCellValue(8, row)
                'For col = 1 To timesSheet.ColumnsCount -1
                '    Log(timesSheet.GetCellValue(col, row))
                'Next              
            End If
        Next
    End If

Many thanks, I seem to be getting there, changed the code slightly to accommodate the month or day length as the spreadsheet holds 05 as May not 5, but its working now with help from yourself and Erel