gdxxrw11.gms : GDXXRW - Reading and writing special Values

Description

This model extends the "Reading Special Values from and writing to Spreadsheet"
example of the GDXXRW Documentation, i.e. a broader range of values is used and
the results are checked afterwards. Also, the option acronyms is tested on the
fly (to verify the results).

In particular, the following options are used/tested:
   NaIn, squeeze, EpsOut, pInfOut, mInfOut, UndfOut, zeroOut, NaOut, resetOut, acronyms

Contributor: Jan-Erik Justkowiak, September 2018


Small Model of Type : GAMS


Category : GAMS Test library


Main file : gdxxrw11.gms

$title GDXXRW - Reading and writing special Values (GDXXRW11,SEQ=780)

$onText
This model extends the "Reading Special Values from and writing to Spreadsheet"
example of the GDXXRW Documentation, i.e. a broader range of values is used and
the results are checked afterwards. Also, the option acronyms is tested on the
fly (to verify the results).

In particular, the following options are used/tested:
   NaIn, squeeze, EpsOut, pInfOut, mInfOut, UndfOut, zeroOut, NaOut, resetOut, acronyms

Contributor: Jan-Erik Justkowiak, September 2018
$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 special values from spreadsheet
$call gdxxrw specialValues.xlsx output=vIN NaIn=N/A squeeze=n par=vIN rng=A1:AB2 cDim=1 trace=0
$ifE errorLevel<>0 $abort Error reading special values from spreadsheet!

* Expected results
$onUndf
Parameter vIN /  v1 Eps,   v2 Inf,   v3 -Inf,   v4 Inf,   v5 NA,   v6 NA,    v7 Undf
                 v8 Undf,  v9 Undf, v10  Undf, v11 Undf, v12 770, v13 Undf, v14    3
                v15   -1, v16 0,    v17 -1,    v18 0,    v19 0,   v20 5,    v21    5
                v22 Undf, v23 0.05, v24 5.75,  v25 0.5,  v26 5,   v27 42             /;

$gdxOut controlData_vIN
$unload vIN
$gdxOut

* Check the results
$call gdxdiff controlData_vIN.gdx vIN.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after reading from spreadsheet!
*-------------------------------------------------------------------------------


* Writing special values to spreadsheet
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 /;

$gdxOut vOUT
$unload v vOUT S
$gdxOut

$onEcho > howToWrite.txt
* defining new strings to be used when writing special values:
EpsOut=0 pInfOut=bigNumber mInfOut=smallNumber 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=A1:G1 cDim=1
par=vOUT  rng=A1:G2 cDim=1 merge
text="v8" rng=H1
par=S     rng=H2

* reset the strings for special values back to default und write vOUT again
resetOut
set=v     rng=A4:G4 cDim=1
par=vOUT  rng=A4:G5 cDim=1 merge
text="v8" rng=H4
par=S     rng=H5
$offEcho

$call gdxxrw vOUT.gdx @howToWrite.txt trace=0
$ifE errorLevel<>0 $abort Error writing special values to spreadsheet!

* Expected results. Note that we use acronyms here to check the "acronyms" option
* on the fly, while it is shorter to use sets with explanatory text instead (as
* demonstrated in the commented section at the end)
Acronym
   bigNumber, smallNumber, undefined, notAvailible, zero;

Parameter
   vOUT1 / v1 0, v2 bigNumber, v3 smallNumber, v4 bigNumber, v5 undefined, v7 notAvailible, v8 zero /
   vOUT2 / v1 Eps, v2 +Inf, v3 -Inf, v4 +Inf, v5 Undf, v7 NA, v8 0 /;

$gdxOut controlData_vOUT
$unload vOUT1 vOUT2
$gdxOut

$call gdxxrw vOut.xlsx acronyms=1 par=vOUT1 rng=A1:H2 cDim=1 par=vOUT2 rng=A4:H5 cDim=1 trace=0
$ifE errorLevel<>0 $abort Error reading vOUT.xlsx!

* Check the results
$call gdxdiff controlData_vOUT.gdx vOUT.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after writing to spreadsheet!
*-------------------------------------------------------------------------------

$onText
* Expected results
Set
   vOUT1 / v1 '0', v2 'bigNumber', v3 'smallNumber', v4 'bigNumber', v5 'undefined', v6, v7 'notAvailible', v8 'zero' /
   vOUT2 / v1 'Eps', v2 '+Inf', v3 '-Inf', v4 '+Inf', v5 'Undf', v6, v7 'NA', v8 '0' /;

$gdxOut controlData_vOUT
$unload vOUT1 vOUT2
$gdxOut

* Read the spreadsheet vOUT.xlsx and write to GDX to compare the results with controlData_vOUT
$call gdxxrw vOUT.xlsx set=vOUT1 rng=A1:H2 values=dense cDim=1 set=vOUT2 rng=A4:H5 values=dense cDim=1 trace=0
$ifE errorLevel<>0 $abort Error reading vOUT.xlsx!

* Check the results
$call gdxdiff controlData_vOUT.gdx vOUT.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after writing to spreadsheet!
$offText