$title GDXXRW - Testing the Option checkDate (GDXXRW9,SEQ=778)

$onText
The functionality of checkDate is tested when:
1. Reading from spreadsheet and writing to GDX
2. Reading from GDX and writing to Spreadsheet.

Contributor: Jan-Erik Justkowiak, September 2018
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"

* 1. Reading from spreadsheet and writing to GDX
* At first, create the spreadsheet temp.xlsx
$onEcho > createSpreadsheet.gms
Scalar A / 1 /;

$gdxOut temp
$unLoad A
$gdxOut

* Write scalar A to spreadsheet
$call gdxxrw temp.gdx output=temp.xlsx par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error writing scalar A to temp.xlsx!
$offEcho

$call gams createSpreadsheet.gms lo=%GAMS.lo%
$ifE errorLevel<>0 $abort Error calling createSpreadsheet!

* Read the data from temp.xlsx and write to dataBackup.gdx
$call gdxxrw temp.xlsx output=dataBackup.gdx par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error Reading scalar A from spreadsheet temp.xlsx!

* Overwrite temp.gdx with different data
Scalar A / 2 /;

* Wait a moment to trigger the checkDate option
$call sleep 2

$gdxOut temp.gdx
$unLoad A
$gdxOut

* Call GDXXRW again with checkDate enabled
$call gdxxrw temp.xlsx output=temp.gdx checkDate par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error Reading scalar A from spreadsheet temp.xlsx with checkDate enabled!

* Since the original Excel file temp.xlsx has not been modified and checkDate
* is enabled, the previous call of GDXXRW did not overwrite temp.gdx, i.e.
* A=1 in dataBackup.gdx and A=2 in temp.gdx
$call gdxdiff temp.gdx dataBackup.gdx > %system.nullfile%
$ifE errorLevel=0 $abort Option checkDate did not work - temp.gdx was rewritten!
* ------------------------------------------------------------------------------

* 2. Reading from GDX and writing to Spreadsheet
* At first, the scalar A is written from temp.gdx (A=2 in this file) to data.xlsx
$call gdxxrw temp.gdx output=data.xlsx par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error writing scalar A to data.xlsx!

* Wait a moment to trigger the checkDate option
$call sleep 2

* Now, data.xlsx is overwriten with temp.xlsx (A=1 in this file)
$call cp -f temp.xlsx data.xlsx
$ifE errorLevel<>0 $abort Error overwriting data.xlsx!

* Write to data.xlsx again with checkData enabled
$call gdxxrw temp.gdx output=data.xlsx checkDate par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error writing scalar A to data.xlsx with checkDate enabled!

* Read from data.xlsx and write to data.gdx to verify the results afterwards
$call gdxxrw data.xlsx output=data.gdx par=A rng=A1 dim=0 trace=0
$ifE errorLevel<>0 $abort Error Reading scalar A from spreadsheet data.xlsx!

* Since temp.gdx has not been modified, but data.xlsx has been modified, the values of A in
* temp.gdx (A=2) and data.gdx (A=1) must be different, if checkDate has been enabled.
$call gdxdiff temp.gdx data.gdx > %system.nullfile%
$ifE errorLevel=0 $abort Option checkDate did not work - data.xlsx was rewritten!