XLUtils v2.10 adds support for reading data as ListOfArrays, and writing data from ListOfArrays.
Note that it depends on ListOfArrays v0.94+.
Very simple to use.
Excel workbook:
Reading:
Lets play with the data:
Writing:
Output:
Note that it depends on ListOfArrays v0.94+.
Very simple to use.
Excel workbook:
Reading:
B4X:
'Read the sheet:
Dim res As XLReaderResult = xl.Reader.ReadSheetByIndex("C:\Users\H\Downloads\Countries of the world.xlsx", "", 0)
'Select a range to be read as a LOA:
Dim loa As ListOfArrays = res.GetListOfArrays(xl.CreateXLRange(xl.AddressName("B4"), res.BottomRight), True)Lets play with the data:
B4X:
'There are extra white spaces in the country names column. Need to clean...
Dim ls As LOASet = loa.CreateLOASet
Do While ls.NextRow
Dim CountryName As String = ls.GetValue("Country")
ls.SetValue("Country", CountryName.Trim)
Loop
loa.Sort("Population", False)
'print a few columns:
Log(loa.ToListOfArrays(Array("Country", "Region", "Population", "Testing Dates")).ToString(5))
'get data of single country:
Dim Zim As ListOfArrays = loa.GetRowsByValue("Country", "Zimbabwe")
Log(Zim.ToString(0))Writing:
B4X:
Dim writer As XLWorkbookWriter = xl.CreateWriterBlank 'new empty workbook
Dim sheet As XLSheetWriter = writer.CreateSheetWriterByName("Cool Table") 'add a sheet
Dim TableRange As XLRange = sheet.PutLOA(loa, xl.AddressName("B2"), Array("Testing Dates")) 'write the LOA, starting from B2
'make the table a bit prettier:
For c = TableRange.FirstAddress.Col0Based To TableRange.SecondAddress.Col0Based
sheet.AutoSizeColumn(c)
Next
Dim table As XLTable = sheet.CreateTable(TableRange, "Countries", "TableStyleMedium2") 'set it to be an Excel table.
table.ShowRowStripes = True
table.AddAutoFilter
Dim f As String = writer.SaveAs("C:\Users\H\Downloads", "Test.xlsx", False)
xl.OpenExcel(f)Output: