Writing data to an Excel spreadsheet and keeping control over the area used

[aileen]

How do I write a parameter to an Excel sheet using GDXXRW, but keep control over the area used?

[aileen]

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 <sysdir>\inclib\win32.gms for details).

Code: Select all

*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';