I’m trying to exclude equation e1(i) from a model at compilation time. The value the exclusion depends on is written in an Excel-file.
See example:
* Definitions
set i /i1/;
scalar pEnable /0/;
variable obj;
equation e1(i), e2(i);
$inlineCom { }
* Mathematical Formulation
e1(i){$pEnable}.. obj =e= 0;
e2(i).. obj =e= 0;
* Read range "pEnable" (specified from A1:D1) from "Exclude.xlsx" Excel-file that has the following content
* A1: pEnable
* B1: =
* C1: YES
* D1: ;
$call xls2gms m i="Exclude.xlsx" r1=pEnable o1=tmp_pEnable.txt
$include tmp_pEnable.txt
display pEnable;
* Model definition
$set SQUEEZELIST ""
$ifE pEnable=0 $set SQUEEZELIST %SQUEEZELIST% - e1
$log Building model with all %SQUEEZELIST%
model test / all %SQUEEZELIST% /;
solve test min obj using lp;
However, this does not work. Even when pEnable is set to YES (or 1) in the Excel-file, the model is still built with /all - e1/. (pEnable is initialized with pEnable /0/ on top).
The display statement shows the correct value of “1”.
As of my understanding, this might be because of compilation and execution time. Altough it says that xls2gms is run during compilation time, it does not set the value for pEnable.
Is there any solution to set the value of pEnable based on the value in the Excel-file to exclude the equation?
(I know the equation could be ignored with e1(i)$[pEnable], but I would prefer to not even include the equation if pEnable is set to 0 in the Excel-file).
Robert, don’t use xls2gms. This is an ancient tool and the web site recommends dropping the tool in favor of gdxxrw (see https://www.gams.com/latest/docs/T_XLS2GMS.html). Nowadays there is GAMS Connect (see https://www.gams.com/latest/docs/UG_GAMSCONNECT.html) for platform independent Excel processing. In any case the way you use xls2gms, it produces an execution time statement. As I mentioned in my last post you need the value of pEnable at compile time to exclude the equation from the model statement. There is no issue with at least gdxxrw getting a scalar at compile time:
* Definitions
set i /i1/;
scalar pEnable, qEnable;
$onEcho > gdxxrw.in
i=Book1.xlsx
o=Book1.gdx
par=pEnable rng=Sheet1!B2:B2 rdim=0 cdim=0
par=qEnable rng=Sheet1!C2:C2 rdim=0 cdim=0
$offEcho
$call.checkErrorLevel gdxxrw @gdxxrw.in
$gdxIn Book1.gdx
$load pEnable qEnable
variable obj;
equation e1(i), e2(i);
$inlineCom { }
* Mathematical Formulation
e1(i){$pEnable}.. obj =e= 0;
e2(i).. obj =e= 0;
* Model definition
$set SQUEEZELIST ""
$ifE pEnable=0 $set SQUEEZELIST %SQUEEZELIST% - e1
$log Building model with all %SQUEEZELIST%
model test / all %SQUEEZELIST% /;
solve test min obj using lp;
The Excel file (here Sheet1) looks as follows:
-Michael
Actually I would like to either use gdxxrw or PandasExcelReader with GAMS connect, but my tables in Excel are already formated in a way, that the importing tool should consider the content of a spreadsheet as Text.
Examples:
Than ranges are specified in Excel with the Name Manager and xls2gms is used to read the content of the ranges as following:
If you can’t change the Excel input then you are stuck. Since the Excel input (independent what tool reads it) has an execution time statement to set the scalars. You need the value of the scalar at compile time. Some of the Excel input is compile time input, perhaps you can change the “Definitions” of parameters also to compile time statements:
and then read under a declaration of scalars. A desperate attempt to get the scalars at compile time is to execute the GAMS instructions from Excel at compile time:
