Description
This model extends the "Reading Set Elements associated with Data or Text" and "Writing Set to Spreadsheet" example of the GDXXRW Documentation, i.e. set elements are read from or written to spreadsheet with the values option and the results are checked afterwards. Contributor: Jan-Erik Justkowiak, September 2018
Small Model of Type : GAMS
Category : GAMS Test library
Main file : gdxxrw13.gms
$title GDXXRW - Testing the values Option when reading or writing Set Elements (GDXXRW13,SEQ=782)
$onText
This model extends the "Reading Set Elements associated with Data or Text" and
"Writing Set to Spreadsheet" example of the GDXXRW Documentation, i.e. set
elements are read from or written to spreadsheet with the values option and
the results are checked afterwards.
Contributor: Jan-Erik Justkowiak, September 2018
$offText
$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"
* Writing set elements to spreadsheet
Set
oneDim / a 'yes', b '1', c '0', d 'no', e '123', f 'YES', g 'NO', h 'TRUE', i 'FALSE', j, k 'hello', l 'N', m 'Y' /
twoDim / I.a 'yes', II.a '1', I.b '0', II.b 'no', I.c '123', II.c 'YES', I.d 'NO', II.d 'TRUE', I.e 'FALSE', II.e, I.f 'hello', II.f 'N', I.g 'Y' /;
$gdxOut out.gdx
$unLoad oneDim twoDim
$gdxOut
$onEcho > howToWrite.txt
set=oneDim rng=A2 cDim=1 values=auto
set=oneDim rng=A6 cDim=1 values=noData
set=oneDim rng=A9 cDim=1 values=YN
set=oneDim rng=A13 cDim=1 values=string
set=twoDim rng=A17:H19 cDim=1 rDim=1 values=auto
set=twoDim rng=A22:H24 cDim=1 rDim=1 values=noData
set=twoDim rng=A27:H29 cDim=1 rDim=1 values=YN
set=twoDim rng=A32:H34 cDim=1 rDim=1 values=string
$offEcho
$call gdxxrw out.gdx output=testWritingValues.xlsx @howToWrite trace=0
$ifE errorLevel<>0 $abort Error writing to spreadsheet!
* Note that the results are checked inside Excel using formulars, since we do
* not want to use the GDXXRW values capability we are testing right now to
* verify the results by reading from the spreadsheet.
* The results of the formulars (booleans: 1-correct, 0-incorrect) will be read and checked instead!
$onEcho > howToRead.txt
par=oneDimAutoS rng=A1 dim=0
par=oneDimNoDataS rng=A5 dim=0
par=oneDimYNS rng=A8 dim=0
par=oneDimStringS rng=A12 dim=0
par=twoDimAutoS rng=A16 dim=0
par=twoDimNoDataS rng=A21 dim=0
par=twoDimYNS rng=A26 dim=0
par=twoDimStringS rng=A31 dim=0
$offEcho
$call gdxxrw testWritingValues.xlsx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading flags from testWritingValuesC.xlsx!
* Expected results
Scalar
oneDimAutoS / 1 /
oneDimNoDataS / 1 /
oneDimYNS / 1 /
oneDimStringS / 1 /
twoDimAutoS / 1 /
twoDimNoDataS / 1 /
twoDimYNS / 1 /
twoDimStringS / 1 /;
$gdxOut controlData.gdx
$unLoad oneDimAutoS oneDimNoDataS oneDimYNS oneDimStringS twoDimAutoS twoDimNoDataS twoDimYNS twoDimStringS
$gdxOut
* Check the results
$call gdxdiff controlData.gdx testWritingValues.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after writing to spreadsheet!
*-------------------------------------------------------------------------------
* Reading set elements from spreadsheet
* Note: Since the data already written to spreadsheet is correct at this point,
* we will use some particular data from testWritingValuesC.xlsx for testing
* the values option when reading from spreadsheet!
$onEcho > howToRead.txt
* At first: values=auto (there are different valueTypes depending on the range
* and dim specification)!
set=oneDimAutoTopLeftCorner rng=A2 cDim=1 values=auto
set=twoDimAutoTopLeftCorner rng=A32 cDim=1 rDim=1 values=auto
set=oneDimAutoBlockEmptyData rng=A6:M7 cDim=1 values=auto
set=twoDimAutoBlockEmptyData rng=A22:H24 cDim=1 rDim=1 values=auto
set=oneDimAutoBlockNonEmptyData rng=A2:M3 cDim=1 values=auto
set=twoDimAutoBlockNonEmptyData rng=A32:H34 cDim=1 rDim=1 values=auto
* values=noData
set=oneDimNoData rng=A2:M3 cDim=1 values=noData
set=twoDimNoData rng=A32:H34 cDim=1 rDim=1 values=noData
* values=sparse
set=oneDimSparse rng=A2:M3 cDim=1 values=sparse
set=twoDimSparse rng=A32:H34 cDim=1 rDim=1 values=sparse
* values=dense
set=oneDimDense rng=A2:M3 cDim=1 values=dense
set=twoDimDense rng=A32:H34 cDim=1 rDim=1 values=dense
$offEcho
$call gdxxrw testWritingValues.xlsx output=testReadingValues.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading set elements from testWritingValuesC.xlsx!
* Expected results
Set
oneDimAutoTopLeftCorner / a 'yes', b '1', c '0', d 'no', e '123', f 'YES', g 'NO', h 'TRUE', i 'FALSE', j, k 'hello', l 'N', m 'Y' /
twoDimAutoTopLeftCorner / I.a, I.c, I.e, I.f, I.g, II.a, II.c, II.d /
oneDimAutoBlockEmptyData / a, b, c, d, e, f, g, h, i, j, k, l, m /
twoDimAutoBlockEmptyData(*,*)
oneDimAutoBlockNonEmptyData / a 'yes', b '1', c '0', d 'no', e '123', f 'YES', g 'NO', h 'TRUE', i 'FALSE', j, k 'hello', l 'N', m 'Y' /
twoDimAutoBlockNonEmptyData / I.a, I.c, I.e, I.f, I.g, II.a, II.c, II.d /
oneDimNoData / a, b, c, d, e, f, g, h, i, j, k, l, m /
twoDimNoData / I.a, I.b, I.c, I.d, I.e, I.f, I.g, II.a, II.b, II.c, II.d, II.e, II.f, II.g /
oneDimSparse / a 'yes', b '1', c '0', d 'no', e '123', f 'YES', g 'NO', h 'TRUE', i 'FALSE', k 'hello', l 'N', m 'Y' /
twoDimSparse / I.a 'yes', II.a '1', I.b '0', II.b 'no', I.c '123', II.c 'YES', I.d 'NO', II.d 'TRUE', I.e 'FALSE', I.f 'hello', II.f 'N', I.g 'Y' /
oneDimDense / a 'yes', b '1', c '0', d 'no', e '123', f 'YES', g 'NO', h 'TRUE', i 'FALSE', j, k 'hello', l 'N', m 'Y' /
twoDimDense / I.a 'yes', II.a '1', I.b '0', II.b 'no', I.c '123', II.c 'YES', I.d 'NO', II.d 'TRUE', I.e 'FALSE', II.e, I.f 'hello', II.f 'N', I.g 'Y', II.g /;
$gdxOut controlData.gdx
$unLoad oneDimAutoTopLeftCorner twoDimAutoTopLeftCorner oneDimAutoBlockEmptyData twoDimAutoBlockEmptyData oneDimAutoBlockNonEmptyData twoDimAutoBlockNonEmptyData oneDimNoData twoDimNoData oneDimSparse twoDimSparse oneDimDense twoDimDense
$gdxOut
* Check the results
$call gdxdiff controlData.gdx testReadingValues.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after reading set elements from spreadsheet!