User Tools

Site Tools


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

We want to 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, 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 has been introduced in 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'
IMPRESSUM / LEGAL NOTICEPRIVACY POLICY interfaces/writing_data_to_a_spreadsheet_and_keeping_control_over_the_area_used.txt · Last modified: 2020/05/26 11:11 by Frederik Fiand