Working with lists

Data in spreadsheets or databases is stored in the form of ranges or tables and so far we have always used Mosel arrays as the corresponding structure within our models. Yet there are other possibilities. In this section we shall see how to work with Mosel lists in correspondence to 1-dimensional tables/ranges in the data source. The next section shows how to work with the Mosel data structure 'record'.

Assume we are given a spreadsheet listdata.xls with two 1-dimensional ranges, List1 and List2 and an integer A:

The following Mosel model listinout.mos reads in the two ranges as lists and also the integer A, makes some modifications to each list and writes them out into predefined output ranges in the spreadsheet.

model "List handling (Excel)"
 uses "mmsheet"

 declarations
  R: range
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations

 initializations from "mmsheet.excel:listdata.xls"
  LI as "List1"
  A
  LS as "List2"
 end-initializations

! Display the lists
 writeln("LI: ", LI)
 writeln("A: ", A, ", LS: ", LS)

! Reverse the list LI
 reverse(LI)

! Append some text to every entry of LS
 LS2:= sum(l in LS) [l+" "+A]

! Display the modified lists
 writeln("LI: ", LI)
 writeln("LS2: ", LS2)

 initializations to "mmsheet.excel:listdata.xls"
  LI as "List1Out"
  LS2 as "List2Out"
 end-initializations

end-model 

Please note that we may choose as input ranges a column or a row of a spreadsheet. Similarly, when using the excel driver to access the spreadsheet the output area of a list may also be a column or a row. List data in databases is always represented as a field of a database table.

The same model implemented with SQL commands looks as follows.

 setparam("SQLverbose",true)
 SQLconnect("listdata.sqlite")
 SQLexecute("select * from List1", LI)
 A:= SQLreadinteger("select * from A")
 SQLexecute("select * from List2", LS)

...

 SQLexecute("delete from List1Out")     ! Cleaning up previous results: works
 SQLexecute("delete from List2Out")     ! only for databases, cannot be used
                                        ! with spreadsheets (instead, delete
                                        ! previous solutions directly in the
                                        ! spreadsheet file)
 SQLexecute("insert into List1Out values (?)", LI)
 SQLexecute("insert into List2Out values (?)", LS2)
 SQLdisconnect 

The modules mmodbc, mmoci and mmsheet do not accept composed structures involving lists like 'array of list' (such constructs are permissible when working with text files in Mosel format).