$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!