For Country = 1 to 210 'don't worry, we're going to quit before we get this high
Dim FirstCityColumn As Int = 2 + (Country - 1) * 5 'start at column 2, groups of 5
If Spreadsheet.GetText("R12C" & FirstCityColumn).Trim.Length = 0 then 'city names on eg row 12? to allow for sheet headings, summaries, corporate logos, whatever
Exit 'for
End If
Dim NumCountries As Int = Country
Log("There should be five cities in columns " & FirstCityColumn & " to " & (FirstCityColumn + 4))
Next
Log("Found " & NumCountries & " countries with (hopefully) 5 cities each")
@emexes:
But the problem is the implementation. Excel spreadsheets use letters as column names. Your code is correct in terms of the solution. But how do you apply it to the addressing of an Excel spreadsheet with letters?
'converts eg Row 5 Col 7 to G5
Sub SpreadsheetCellAddress(Row As Int, Col As Int) As String
Dim Letters(5) As Int 'that oughta do it, A..ZZZZZ good for > 10 million columns
Letters(0) = Col
For I = 0 to Letters.Length - 2
Do While Letters(I) > 26 'mildly inefficient but easy to see how it works
Letters(I) = Letters(I) - 26
Letters(I + 1) = Letters(I + 1) + 1
Loop
Next
Dim AllLetters As String = ""
For I = 0 to Letters.Length - 1
If Letters(I) <> 0 Then
AllLetters = Chr(64 + Letters(I)) & AllLetters
End If
Next
Return AllLetters & Row
End Sub
Yeah, I was taking a chance that your Excel API could handle RnCn-style addressing like Excel can (or could: it was a carryover from Multiplan, maybe it got dropped).
https://support.microsoft.com/en-us/office/use-a-formula-in-a-word-or-outlook-table-cbd0596e-ea8a-485e-a35d-b2cb2c4f3e27#:~:text=it is ignored.-,RnCn references,row and the second column.
Anyway, this (untested) function might be useful:
.AddressZero(col, row)
.Get
method returns an Object, which could be a Double (if the cell is a number) or a String (if the cell is a string).'returns True if looks like a row or column heading, False if not
Sub ObjectLooksLikeHeading(o As Object) As Boolean
If o Is String Then 'headings are Strings (not Doubles etc)
Dim s As String = o
If s.ToUpperCase.CompareTo(s.ToLowerCase) <> 0 Then 'with letters in them (right?...)
Return True
End If
End If
Return False
End Sub
Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("Layout1")
MainForm.Show
Dim xlu As XLUtils
xlu.Initialize
Dim xlr As XLReader
xlr.Initialize(xlu)
Dim result As XLReaderResult = xlr.ReadSheetByIndex(File.DirAssets, "cities.xlsx", 0)
'assume spreadsheet contains at least one city, with data in at least the second column (col 1)
Dim HeaderRow As Int = -1
For row = 1 To 20
If ObjectLooksLikeHeading( result.Get(xlu.AddressZero(1, row)) ) Then
HeaderRow = row
else if HeaderRow <> -1 Then
Exit 'for
End If
Next
Log("Header row is " & HeaderRow)
Dim FirstHeaderCol As Int = 1 'already known from above
Dim LastHeaderCol As Int = -1
For col = FirstHeaderCol To 255
If ObjectLooksLikeHeading( result.Get(xlu.AddressZero(col, HeaderRow)) ) Then
LastHeaderCol = col
Else
Exit 'for
End If
Next
Log("Header and (presumably) Data columns are " & FirstHeaderCol & " to " & LastHeaderCol)
Dim FirstDataRow As Int = -1
For row = HeaderRow + 1 To 255
If result.get(xlu.AddressZero(FirstHeaderCol, row)) Is Double Then
FirstDataRow = row
Exit 'for
End If
Next
If FirstDataRow = -1 Then
Log("what the heck?!?! - no data?")
Return
End If
Dim LastDataRow As Int = -1 'about to be rewritten at FirstDataRow
For row = FirstDataRow To 255
If result.get(xlu.AddressZero(FirstHeaderCol, row)) Is Double Then
LastDataRow = row
Else
Exit 'for
End If
Next
Log("Data rows are " & FirstDataRow & " to " & LastDataRow)
End Sub
In principle, there are always several entries grouped next to each other in a defined form that have to be read and evaluated together. But I never know beforehand (to stay with the example) which city is in which position (column-wise).
It's part of something on which I work from time to time, and it grew over time - grew as in 'oh that's an idea, let's add that too'. This was the initial step: get the Excel data into an SQLite db and then take it from there as data manipulation is way easier then (as I'm sure you know). My first impulse was to just extract the code to which you referred but then thought that less isn't more in this caseThank you very much!
Very complex for an example programme, it will take me a while to understand the code. But it's also not a simple topic. I have worked a lot with the jExcel library under Android, but with B4J you can also read XLSX or XLSM files thanks to jPOI and XLReader. But the differences are enormous.
How long did you work on this programme? Weeks?
For col0Based = 0 To 5
Dim Argument As String = XLResult.Get(XL.Addresszero(col0Based,0))
Final = Final & "," & Argument
Next
Log ("Final" & Final)
I think I've given your brain a problem to work on, haven't I?
I could have written that they are not cities, but car names with technical data.
The thing with the automobiles was just another example I had in mind.Although I'm intrigued - what are the five columns per vehicle (model?) ?
Every sheet contains a lot of information, machines side by side. The Excel file often changes weekly.
That's the problem nowadays, all the valuable information you need is within a spreadsheet, designed by idiots...
So it is a huge waste of time for every technician to find the information he is looking for. SQL can do that much better with the right search arguments prepared...
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?