$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

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