Description

This program illustrates writing to an MS Excel file at execution time.
At first, a set is defined and written to spreadsheet while demonstrating the
values option. Afterwards, a parameter is generated and written to spreadsheet, too.

This model is referenced in "Writing Set to Spreadsheet" and "Writing Parameter
to Spreadsheet" from the GDXXRW Documentation.

Category : GAMS Data Utilities library

Main file : GDXXRWExample11b.gms   includes :  GDXXRWExample11b.gms

$title Writing Sets and Parameter to Spreadsheet at Execution Time (GDXXRWExample11b,SEQ=023)

$onText
This program illustrates writing to an MS Excel file at execution time.
At first, a set is defined and written to spreadsheet while demonstrating the
values option. Afterwards, a parameter is generated and written to spreadsheet, too.

This model is referenced in "Writing Set to Spreadsheet" and "Writing Parameter
to Spreadsheet" from the GDXXRW Documentation.
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"

* Writing Set to spreadsheet
Set x / element1 'explanatory text'
        element2
        element3 'previous element does not have explanatory text' /;

* unload set x and write to spreadsheet using a option file to increase readability
* while demonstrating the values option
execute_unload 'writingSet.gdx' x;

$onEcho > howToWrite.txt
hText="values: noData,,,values: yn,,,values: string" rng=Sheet1!A1
set=x rng=Sheet1!A2 rDim=1 values=noData
set=x rng=Sheet1!D2 rDim=1 values=yn
set=x rng=Sheet1!G2 rDim=1 values=string
$offEcho

execute 'gdxxrw writingSet.gdx output=writingSet.xlsx @howToWrite.txt trace=0'
*-------------------------------------------------------------------------------


* Writing Parameter to spreadsheet
* Create the three dimensional parameter V and write to spreadsheet afterwards
Set
   i / i1*i4 /
   j / j1*j4 /
   k / k1*k4 /;

Parameter V(i,j,k);
V(i,j,k)$(uniform(0,1) < 0.30) = uniform(0,1);

execute_unload 'writingPar.gdx', i, j, k, V;
execute 'gdxxrw writingPar.gdx output=writingPar.xlsx par=V rng=A1 trace=0';