Description
This example demonstrates how to read several scalars (i.e. scalar names and their associated values) for your model from a spreadsheet file, without declaring and reading every single scalar manually. However, we assume that the scalars are stored in a listed format (see file exampleData.xlsx sheet indus89Scalars). All scalar names and their associated values are read as a single parameter over the domain set consisting of all scalar names first. Afterwards, we use the Put Writing Facility to write a file 'scalars.gms', containing all scalar statements in the following form: Scalar Scalarname1 / Scalarvalue1 /; Scalar Scalarname2 / Scalarvalue2 /; ... Finally, 'scalars.gms' is included to the model.
Category : GAMS Data Utilities library
Main file : GDXXRWExample17.gms includes : exampleData.xlsx GDXXRWExample17.gms
$title Reading several Scalars from Spreadsheet (GDXXRWExample17,SEQ=119)
$onText
This example demonstrates how to read several scalars (i.e. scalar names and
their associated values) for your model from a spreadsheet file, without declaring
and reading every single scalar manually. However, we assume that the scalars
are stored in a listed format (see file exampleData.xlsx sheet indus89Scalars).
All scalar names and their associated values are read as a single parameter over
the domain set consisting of all scalar names first. Afterwards, we use the
Put Writing Facility to write a file 'scalars.gms', containing all scalar
statements in the following form:
Scalar Scalarname1 / Scalarvalue1 /;
Scalar Scalarname2 / Scalarvalue2 /;
...
Finally, 'scalars.gms' is included to the model.
This model is referenced in "Reading several Scalars from Spreadsheet"
from the GDXXRW Documentation.
$offText
$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"
* ------------------------------------------------------------------------------
* Naturally, one could declare and read every scalar individually (shortened):
$onText
Scalar baseyear, repco;
$call gdxxrw exampleData.xlsx output=indus89Scalars.gdx par=baseyear rng=indus89Scalars!B2 dim=0 par repco rng=indus89Scalars!B3 dim=0
$gdxIn indus89Scalars.gdx
$load baseyear repco
display baseyear, repco;
$offText
* ------------------------------------------------------------------------------
* Write GAMS model 'mkScalar.gms', reading all scalars as a single parameter first
* Use the Put Writing Facility afterwards to create 'scalars.gms'
$onEchoV > mkScalar.gms
Set scalarNames;
Parameter scalarValues(scalarNames);
$call gdxxrw exampleData.xlsx output=indus89Scalars.gdx set=scalarNames rng=indus89Scalars!A2:A22 rDim=1 par=scalarValues rng=indus89Scalars!A2:B22 rDim=1 trace=0
$ifE errorLevel<>0 $abort Error reading exampleData.xlsx with GDXXRW!
$gdxIn indus89Scalars.gdx
$load scalarNames scalarValues
File fs / 'scalars.gms' /;
put fs;
loop(scalarNames, put / 'Scalar ' scalarNames.tl ' / ' scalarValues(scalarNames):>20:10 ' /;';);
$offEcho
$call gams mkScalar lo=%GAMS.lo%
$ifE errorLevel<>0 $abort Error calling mkScalar!
$include scalars.gms
display baseyear, repco, gr, growthq, drc, the1, lstd, trcap, twcap, twefac
labfac, cowf, buff, explimitgr, big, pawat, pafod, tolcnl, tolpr, tolnwfp
betaf;