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
$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel 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