How do I write a parameter to an Excel sheet using GDXXRW, but keep control over the area used?
When one uses the top left cell for the range, cells to the right and down are cleared. If one specifies a range, it may be too large or too small. The code below creates a range that is sized exactly. The example also demonstrated the usage of xlstalk via $libInclude win32 (see the header of the file in \inclib\win32.gms for details).
*create the data we want to write
set i /i1*i20/
j /j1*j5/;
parameter A(i,j);
loop((i,j),
A(i,j) = 10 * Ord(i) + Ord(j);
);
execute_unload 'pv.gdx', A;
* make set R and C as large as you need
sets r / 1*10000 /, c / a*zz /, cstart(c,r), cend(c,r);
*clear in case the code is reused
option clear=cstart, clear=cend;
* below we assume you want to use cell D3 as top left corner
* and calculate the lower right corner and write both
* values to a parameter file for gdxxrw
cstart('D','3') = yes;
loop(cstart(c,r),
cend(c+Card(j),r+Card(i)) = cstart(c,r)
);
file pv /pv.txt/;
put pv;
loop(cstart(c,r), put c.tl:0, r.tl:0 );
put ':';
loop(cend(c,r), put c.tl:0, r.tl:0);
putclose;
* note: see the header of the file in <sysdir>\inclib\win32.gms for details
$libInclude win32 xlstalk saveAndclose pv.xls';
execute 'gdxxrw pv.gdx o=pv.xls par=A rng=@pv.txt cdim=1 rdim=1';