B4J Question XLUtil - working with Ranges on multiple sheets

mfstuart · Oct 6, 2021

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

EnriqueGonzalez · Oct 6, 2021

xlUtils has a the method: xl.GetWorkbookRangeNames

may be you can see how are all this ranges named.

Erel · Oct 6, 2021

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)

mfstuart · Oct 6, 2021

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

Erel · Oct 7, 2021

1. The question in the first post was answered. You should mark post #3 as the solution.
2. Please start a new thread for any other question and make sure to use code tags.

B4J Question XLUtil - working with Ranges on multiple sheets

mfstuart

Active Member

EnriqueGonzalez

Well-Known Member

Erel

B4X founder

mfstuart

Active Member

Erel

B4X founder

Similar Threads

B4J Question XLUtil - working with Ranges on multiple sheets

mfstuart

Active Member

EnriqueGonzalez

Well-Known Member

Erel

B4X founder

mfstuart

Active Member

Erel

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency