GDXXRWExample8.gms : Reading Special Values from Spreadsheet and writing to Spreadsheet

Description

This model illustrates reading and writing a parameter with
special values such as Eps, +Inf, -Inf, Na, Undf, booleans etc.

This model is referenced in "Reading Special Values from Spreadsheet and writing
to Spreadsheet" from the GDXXRW Documentation.


Category : GAMS Data Utilities library


Main file : GDXXRWExample8.gms   includes :  exampleData.xlsx  GDXXRWExample8.gms

$title Reading Special Values from Spreadsheet and writing to Spreadsheet (GDXXRWExample8,SEQ=019)

$onText
This model illustrates reading and writing a parameter with
special values such as Eps, +Inf, -Inf, Na, Undf, booleans etc.

This model is referenced in "Reading Special Values from Spreadsheet and writing
to Spreadsheet" from the GDXXRW Documentation.
$offText

$if %system.filesys% == UNIX $abort.noError 'This model cannot run on a non-Windows platform';
$call msappavail -Excel
$ifE errorLevel<>0 $abort.noError 'Microsoft Excel is not available!';

* Reading
Parameter vIN;

$call gdxxrw exampleData.xlsx output=specialValues.gdx NaIn=N/A squeeze=n par=vIN cDim=1 rng=specialValues!A1:S2 trace=0
$ifE errorLevel<>0 $abort Error reading special values!

$onUndf
$gdxIn specialValues.gdx
$load vIN
display vIN;
*-------------------------------------------------------------------------------


* Writing
Set v / v1*v7 /;
Parameter vOUT(v) / v1 Eps, v2 +Inf, v3 -Inf, v4 Inf, v5 Undf, v6 0.0, v7 NA /;
Scalar S / 0 /;

execute_unload 'specialValues.gdx', v, vOUT, S;

$onEcho > howToWrite.txt
* defining new strings to be used when writing special values:
EpsOut=0 pInfOut=+1E+100 mInfOut=-1E+100 UndfOut=undefined zeroOut=zero NaOut=notAvailible

* now write parameter vOUT with merge to force the column F containing set element "v6" and vOUT("v6")
set=v    rng=specialValues!A6:G6 cDim=1
par=vOUT rng=specialValues!A6:G7 cDim=1 merge
text="Special values of Parameter vOUT written with user defined output strings:" rng=specialValues!A5
text="Scalar S / 0 /:" rng=specialValues!I6
par=S rng=specialValues!I7

* reset the strings for special values back to default und write vOUT again
resetOut
set=v    rng=specialValues!A10:G10 cDim=1
par=vOUT rng=specialValues!A10:G11 cDim=1 merge
text="Special values of Parameter vOUT written with default output strings:" rng=specialValues!A9
text="Scalar S / 0 /:" rng=specialValues!I10
par=S rng=specialValues!I11
$offEcho

execute 'gdxxrw specialValues.gdx output=exampleData.xlsx @howToWrite.txt trace=0';