Hi there,

I have trouble reading a 2-dimensional array as an input in Excel.

I have tried to search from sources provided by FICO (getting started folioexcel.mos and Xpress_moselodbc doc) but with no success; examples are only given in 1-dimensional array.

As an example, following is a 2-dimensional input:

1 2 3 4

-----------------------------------------------

Amber | 11 12 13 14

Billy | 21 22 23 24

Cathy | 31 32 33 34

Danny | 41 42 43 44

How would I then read it as an input matrix Z: array(range, range) of integer?

Thanks in advance.

There are 2 options for treating this case:

1- dense data format (reading only the data entries, no indices)

2- rectangular data format (first n-1 columns of the selected area contain indices, the values for the last index are written across the columns)

The 2 cases are discussed in the whitepaper moselodbc.pdf that you are referring to (see section 6.2 "Dense vs sparse data format" and section 6.2.1 "Multidimensional tables in rectangular format"). The correspinding example files are contained in the subdirectory examples/mosel/Whitepapers/MoselODBC of the standard Xpress distribution and they can also be found at http://examples.xpress.fico.com/example.pl#mosel_data

1: In this case, all indices must be ranges, and be known before reading in the data values (this could be done by reading just the contents of the relevant row or column from the spreadsheet before reading in the data values; for simplicity's sake I have just fixed them here). I am assuming that the header line of your example data is contained in the first row of your sheet, and the entry "Amber" is in the cell A2:

declarations

R1 = 1..4

R2 = 1..4

Z: array(R1,R2)

end-declarations

initializations from "mmsheet.excel:myfile.xls"

Z as "noindex;[Sheet1$B2:E5]"

end-initializations

2: In this case, the last index must be a range and be known upfront. The spreadsheet I/O drivers of Mosel have a special option "partndx" to handle this format:

declarations

S: set of string

R2 = 1..4

Z: array(S,R2)

end-declarations

initializations from "mmsheet.excel:myfile.xls"

Z as "partndx;[Sheet1$A2:E5]"

end-initializations