B4J Question XLUtil - working with Ranges on multiple sheets

mfstuart

Active Member
Licensed User
Longtime User
I have a spreadsheet with 21 sheets in it.
I need to read each sheet. Each sheet has 6 columns of data. Each sheets data can have different number of rows, some have 19 rows, some 20 and some 21 rows.
To read each sheets data, I would assume creating a Sheet Range on each sheet.

Excel allows you to name each sheets range the same name - I did it and saved the spreadsheet with no error. I'm naming each sheets range the same, as: "Data".

I need to read each sheets "Data" range and put that data into a SQL table.

Q: how do I define the B4J code to read a range?

I see in Erel's example the code is as:
B4X:
Dim result As XLReaderResult = XL.Reader.ReadRange(File.DirAssets, "Countries of the world.xlsx", "Countries") 'named range
How does it know which sheet that range is on, especially if I name the ranges the same on all sheets, which Excel allows?

I have this code, but it returns nothing:
B4X:
Dim filename As String = txtFilename.Text
Dim folder As String = File.GetFileParent(filename)
Dim name As String = File.GetName(filename)

Dim sheetname as String = "My_Sheet"     'The XLUtil library does not allow spaces in sheet names!
Dim result As XLReaderResult = XL.Reader.ReadRange(folder, name, sheetname & "!Data")    '<<==== returns nothing

I know what you might say, and that is to name each range differently.

But my issue with this is Excel allows the same name for a range on each sheet. So if some other B4J developer does the same, they are going to have this issue.
So I think it needs to be addressed in the XLUtils library.

Thanx,
Mark Stuart
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
You cannot directly get a non-unique named range. You can use this code:
B4X:
Private Sub GetSheetRange(Workbook As XLWorkbookWriter, SheetName As String, RangeName As String) As XLRange
    For Each range As XLRange In xl.GetWorkbookRangeNames(Workbook.PoiWorkbook)
        If range.Sheet.Name = SheetName And range.Name = RangeName Then Return range    
    Next
    Return Null
End Sub

Example:
B4X:
Dim workbook As XLWorkbookWriter = xl.CreateWriterFromTemplate("C:\Users\H\Downloads\1.xlsx", "")
Dim res As XLReaderResult = xl.Reader.RangeToResult(GetSheetRange(workbook, "Sheet2", "test2"))
res.LogResult(False)
 
Upvote 0

mfstuart

Active Member
Licensed User
Longtime User
This is what I'd like to do. And that is to:
1) open an Excel workbook
2) get a named sheet
3) get a named range from the sheet
4) read that range of data (data is in Columnar and Row format)
5) load a B4XTable with the range data
6) Press a button to INSERT table data into a SQL table
Done.

Dim TableData as List
TableData.Initialize
Dim folder as String = "C:\MarksSheets"
Dim name as String = "1.xlsx"
Dim workbook as XLWorkbookWriter = XL.CreateWriteFromTemplate(folder, name)
Dim sheet as XLSheet = workbook.GetSheet("MySheet")
Dim range as XLRange = sheet.GetRange("MyRange")

TableData.SetData(range.GetData)

Set B4XTable.ColumnHeadings(???) 'from 1st row in range data
B4XTable.SetData(TableData)

I've tried a few things and it isn't working out as I planned.
What would you suggest?

Thanx in advance,
Mark Stuart
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…