Description

All possible input combinations (summarized in the table of the Excel Ranges
Section in the GDXXRW Documentation) of the rng option are checked when reading
from or writing to spreadsheet.

Contributor: Jan-Erik Justkowiak, September 2018

Small Model of Type : GAMS

Category : GAMS Test library

Main file : gdxxrw14.gms

$title GDXXRW - Testing different Excel Range Specifications (GDXXRW14,SEQ=783)

$onText
All possible input combinations (summarized in the table of the Excel Ranges
Section in the GDXXRW Documentation) of the rng option are checked when reading
from or writing to spreadsheet.

Contributor: Jan-Erik Justkowiak, September 2018
$offText

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

* Reading from spreadsheet
$onEcho > howToRead.txt
par=rng_empty               rDim=1 cDim=1
par=rng_QuotationMark rng=! rDim=1 cDim=1
nc=1
par=rng_NameNC1 rng=SheetName rDim=1 cDim=1
nc=0
par=rng_NameNC0 rng=F9:H11 rDim=1 cDim=1
par=rng_NameQuotationMark rng=SheetName! rDim=1 cDim=1
par=rng_QuotationMarkName rng=!F9..H11 rDim=1 cDim=1
par=rng_Name1QuotationMarkName2 rng=SheetName!F9..H11 rDim=1 cDim=1
$offEcho

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

* Expected results
Parameter
   rng_empty                    / a.I  1, a.II  2, b.I  3, b.II  4 /
   rng_QuotationMark            / a.I  1, a.II  2, b.I  3, b.II  4 /
   rng_NameNC1                  / x.I 11, x.II 12, y.I 13, y.II 14 /
   rng_NameNC0                  / a.I  5, a.II  6, b.I  7, b.II  8 /
   rng_NameQuotationMark        / x.I 11, x.II 12, y.I 13, y.II 14 /
   rng_QuotationMarkName        / a.I  5, a.II  6, b.I  7, b.II  8 /
   rng_Name1QuotationMarkName2  / x.I 15, x.II 16, y.I 17, y.II 18 /;

$gdxOut controlData.gdx
$unLoad rng_empty rng_QuotationMark rng_NameNC1 rng_NameNC0 rng_NameQuotationMark rng_QuotationMarkName rng_Name1QuotationMarkName2
$gdxOut

* Check the results
$call gdxdiff controlData.gdx testExcelRAnges.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after reading from spreadsheet!
*-------------------------------------------------------------------------------


* Writing to spreadsheet
* We modify the parameter rng_empty and rng_QuotationMark, since both will be
* written to the cell A1 of the first sheet in order to see a difference when
* using a single GDXXRW call and a single check afterwards
Parameter
   rng_empty_mod         / a.I 1, a.II 2, a.III 999, b.I 3, b.II 4, b.III 999, c.I 999, c.II 999, c.III 999 /
   rng_QuotationMark_mod / a.I 1, a.II 2, b.I 3, b.II 999 /;

$gdxOut controlData.gdx
$unLoad rng_empty_mod rng_QuotationMark_mod rng_NameNC1 rng_NameNC0 rng_NameQuotationMark rng_QuotationMarkName rng_Name1QuotationMarkName2
$gdxOut

$onEcho > howToWrite.txt
par=rng_empty_mod rDim=1 cDim=1
par=rng_QuotationMark_mod rng=! rDim=1 cDim=1 merge
nc=1
par=rng_NameNC1 rng=SheetName rDim=1 cDim=1
nc=0
par=rng_NameNC0 rng=F9:H11 rDim=1 cDim=1
par=rng_NameQuotationMark rng=SheetName1! rDim=1 cDim=1
par=rng_QuotationMarkName rng=!C15..E17 rDim=1 cDim=1
par=rng_Name1QuotationMarkName2 rng=SheetName2!F9..H11 rDim=1 cDim=1
$offEcho

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

* Since the "reading test" was succesfully accomplished at this point, we use
* GDXXRW to read and verify the results of the last call
$onEcho > howToRead.txt
par=rng_empty_QuotationMark rng=Sheet1!A1:D4 rDim=1 cDim=1
par=rng_NameNC1 rng=SheetName!A1:C3 rDim=1 cDim=1
par=rng_NameNC0 rng=Sheet1!F9:H11 rDim=1 cDim=1
par=rng_NameQuotationMark rng=SheetName1!A1:C3 rDim=1 cDim=1
par=rng_QuotationMarkName rng=Sheet1!C15..E17 rDim=1 cDim=1
par=rng_Name1QuotationMarkName2 rng=SheetName2!F9..H11 rDim=1 cDim=1
$offEcho

$call gdxxrw out.xlsx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading out.xlsx!

Parameter
   rng_empty_QuotationMark / a.I 1, a.II 2, a.III 999, b.I 3, b.II 999, b.III 999, c.I 999, c.II 999, c.III 999 /;

$gdxOut controlData.gdx
$unLoad rng_empty_QuotationMark rng_NameNC1 rng_NameNC0 rng_NameQuotationMark rng_QuotationMarkName rng_Name1QuotationMarkName2
$gdxOut

$call gdxdiff controlData.gdx out.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after writing to spreadsheet!