B4J Code Snippet [XLUtils] [LOA] Reading and writing Excel data with ListOfArrays

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:

'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:

'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:

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:

 

[zhangbao]

The latest version downloaded is still 2.09

 

[Erel]

Fixed.