Reading a spreadsheet

I want to import the data for a three-dimensional parameter p(i,j,k) that is stored in in k excel sheets but GAMS does not let me use dollar control statements in loops. Is there any way to do that using loops or other flow control statements like 'for' or 'while'?

Let’s make some data as follows:

We can read this as follows:

$set xls  d:\tmp\test2.xlsx $set gdx  s.gdx set   i /i1*i3/   j /j1*j5/   k 'sheet names' /Sheet1*Sheet3/ ; parameter   s(i,j)  'single sheet'   a(i,j,k)  'all data' ; file f /task.txt/; loop(k,   putclose f,'par=s rng=',k.tl:0,'!a1 rdim=1 cdim=1'/;   execute 'gdxxrw i=%xls% o=%gdx%  @task.txt trace=2';   execute_loaddc '%gdx%',s;   a(i,j,k) = s(i,j); ); display a;

The output will look like:

----     23 PARAMETER a  all data            Sheet1      Sheet2      Sheet3 i1.j1       1.000       2.000       3.000 i1.j2       1.000       2.000       3.000 i1.j3       1.000       2.000       3.000 i1.j4       1.000       2.000       3.000 i1.j5       1.000       2.000       3.000 i2.j1       1.000       2.000       3.000 i2.j2       1.000       2.000       3.000 i2.j3       1.000       2.000       3.000 i2.j4       1.000       2.000       3.000 i2.j5       1.000       2.000       3.000 i3.j1       1.000       2.000       3.000 i3.j2       1.000       2.000       3.000 i3.j3       1.000       2.000       3.000 i3.j4       1.000       2.000       3.000 i3.j5       1.000       2.000       3.000

It is quite slow however as we do a call to gdxxrw for each sheet (we usually prefer to read all data into a single GDX file using just one invocation of gdxxrw).