GDXXRWExample18.gms : Reading Sets from Spreadsheet

Description

This program illustrates reading set elements from spreadsheet, including
reading explanatory text and reading set elements from lists with duplications.

This model is referenced in "Reading Set from Spreadsheet", "Reading Set and
Explanatory Text", "Reading Set from Data Tables" and "Reading Set from Lists
with Duplication" from the GDXXRW Documentation.

Category : GAMS Data Utilities library

Main file : GDXXRWExample18.gms   includes :  exampleData.xlsx  GDXXRWExample18.gms

$title Reading Sets from Spreadsheet (GDXXRWExample18,SEQ=120)

$onText
This program illustrates reading set elements from spreadsheet, including
reading explanatory text and reading set elements from lists with duplications.

This model is referenced in "Reading Set from Spreadsheet", "Reading Set and
Explanatory Text", "Reading Set from Data Tables" and "Reading Set from Lists
with Duplication" from the GDXXRW Documentation.
$offText

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


* Reading Set from Spreadsheet
Set
   i1  'dataType "set", no data range, to be read from the second row (horizontal direction)'
   i1a 'dataType "dSet", no data range, to be read from the second row (horizontal direction)'
   j1  'dataType "set", no data range, to be read from column A (vertical direction)'
   j1a 'dataType "dSet", no data range, to be read from column A (vertical direction)';

* Reading the sets i1, i1a, j1, j1a. Note that there will be no difference
* between i1, i1a and j1, j1a, since there is no explanatory text nor duplicates.
* An option file is used to increase readability
$onEcho > howtoRead.txt
 set=i1  rng=readingSets!A2:C2   cDim=1
dSet=i1a rng=readingSets!A2:C2   cDim=1
 set=j1  rng=readingSets!A35:A37 rDim=1
dset=j1a rng=readingSets!A35:A37 rDim=1
$offEcho

$call gdxxrw exampleData.xlsx output=sets.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading sets i1, i1a, j1, j1a from spreadsheet!

$gdxIn sets.gdx
$load i1 i1a j1 j1a
$gdxIn

display i1, i1a, j1, j1a;
* ------------------------------------------------------------------------------


* Reading Set and Explanatory Text
Set i3 'dataType "set", values option is by default "dense" based on rng, cDim and rDim, i.e. all tuples will be included while the string in the associated data range will be interpreted as explanatory text';

$call gdxxrw exampleData.xlsx output=sets set=i3 rng=readingSets!A9:E10 cDim=1 trace=0
$ifE errorLevel<>0 $abort Error reading set i3!

$gdxIn sets.gdx
$load i3
$gdxIn

display i3;
* ------------------------------------------------------------------------------


* Reading Set from Data Tables
Set
   i6  'dataType "set", values option is set to noData to prevent reading the values of the data table as explanatory text'
   i6a 'dataType "dSet", the values of the data table are not interpreted as explanatory text'
   i6c 'dataType "set", values option is set to noData to prevent reading the values of the data table as explanatory text'
   j4  'dataType "dSet", read the set elements in the vertical direction, the values of the data table are not interpreted as explanatory text';

* An option file is used to increase readability when reading the sets i6, i6a, i6c
$onEcho > howToRead.txt
 set=i6  rng=readingSets!B20:D20 cDim=1 values=noData
dSet=i6a rng=readingSets!B20:D20 cDim=1
 set=i6c rng=readingSets!B20:D21 cDim=1 values=noData
dSet=j4  rng=readingSets!A21:A23 rDim=1
$offEcho

$call gdxxrw exampleData.xlsx output=sets @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading sets i6, i6a, i6c, j4 from spreadsheet!

$gdxIn sets.gdx
$load i6 i6a i6c j4
$gdxIn

display i6, i6a, i6c, j4;
* ------------------------------------------------------------------------------


* Reading Set from Lists with Duplication
Set
   i7  'dataType "dSet", duplicates are removed automatically'
   i8  'dataType "dSet", duplicates are removed automatically'
   i7a 'dataType "set", maxDupeErrors is used to oppress an error when reading duplicates'
   i8a 'dataType "set", maxDupeErrors is used to oppress an error when reading duplicates';

* An option file is used to increase readability when reading the sets i6, i6a, i6c
$onEcho > howToRead.txt
dSet=i7 rng=readingSets!B26:E26 cDim=1
dSet=i8 rng=readingSets!B27:E27 cDim=1
maxDupeErrors=4
set=i7a rng=readingSets!B26:E26 cDim=1 values=noData
set=i8a rng=readingSets!B27:E27 cDim=1 values=noData
$offEcho

$call gdxxrw exampleData.xlsx output=sets @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading sets i7, i8, i7a, i8a from spreadsheet!

$gdxIn sets.gdx
$load i7 i8 i7a i8a
$gdxIn

display i7, i8, i7a, i8a;
* ------------------------------------------------------------------------------