gdxxrw8.gms : GDXXRW - Testing the Option intAsText

Description

Set elements are written to spreadsheet. Some of them are proper integers,
others are not. The results for intAsText enabled and disabled will be checked.

Contributor: Jan-Erik Justkowiak, September 2018


Small Model of Type : GAMS


Category : GAMS Test library


Main file : gdxxrw8.gms

$title GDXXRW - Testing the Option intAsText (GDXXRW8,SEQ=777)

$onText
Set elements are written to spreadsheet. Some of them are proper integers,
others are not. The results for intAsText enabled and disabled will be checked.

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!';

* Set elements to be written with intAsText enabled and disabled
Set i / i1, 1i, 5, '1 2', '3.14', '3,14', 007, '007.1', '007,1', '-8', 770, '7.7E+02', '7,7E+02', '$5', '5$', '123', "234", "'345'", '"456"' /;

$gdxOut set.gdx
$unload i
$gdxOut

* Use values=noData to prevent the clearing of the formulars in Excel, which will
* indicate if the format of the value written to the cell is text or a number
$onEcho > howToWrite.txt
set=i rng=A2 intAsText=y values=noData
set=i rng=A6 intAsText=n values=noData
$offEcho

$call gdxxrw set.gdx output=testIntAsText.xlsx @howToWrite.txt trace=0
$ifE errorLevel<>0 $abort Error writing to spreadsheet!

* Expected results
Alias (intAsText_Y, i);

* Those are proper integers and should be written as integers if intAsText=N
Set intAsText_N / 5, 007, '-8', 770, 123, 234 /;

$gdxOut expectedResults
$unload intAsText_Y intAsText_N
$gdxOut

* Read the data written with intAsText enabled and disabled
* The values=yn option can be used, since the check in Excel converts the
* booleans to 'Y' resp. 'N' by using the formular =IF(ISTEXT(A2);"Y";"N") and =IF(ISNUMBER(A6);"Y";"N")
* We will skip some rows for the set intAsText_N in order to get the original set elements
$onEcho > howToRead.txt
set=intAsText_Y rng=A2 cDim=1 values=yn
set=intAsText_N rng=A2 cDim=1 values=yn ignoreRows=3:6
$offEcho

$call gdxxrw testIntAsText.xlsx output=results.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading from spreadsheet!

* Check the results
$call gdxdiff expectedResults.gdx results.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct!