User Tools

Site Tools


Writing data to a spreadsheet and keeping control over the area used

We want to write a parameter to a sheet, 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, which was introduced in distribution 22.8

*create the data we want to write
set i /i1*i20/
    j /j1*j5/;
parameter A(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;
      cend(c+Card(j),r+Card(i)) = cstart(c,r)
file pv /pv.txt/;
put pv;
loop(cstart(c,r), put, );
put ':';
loop(cend(c,r), put,;
* note:  xlstalk is new for the 22.8 release (save/close the file and open file)
execute 'xlstalk.exe -s pv.xls';
execute 'gdxxrw pv.gdx o=pv.xls par=A rng=@pv.txt cdim=1 rdim=1';
execute 'xlstalk.exe -o pv.xls'
interfaces/writing_data_to_a_spreadsheet_and_keeping_control_over_the_area_used.txt · Last modified: 2008/07/26 05:13 by support