Description
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
Small Model of Type : GAMS
Category : GAMS Test library
Main file : gdxxrw9.gms
$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!