Originally posted by: Redhat11

Hi,

 

is there a way to assign a dynamic range to SheetRead similiar to how it works with SheetWrite? I do not want to use named ranges in Excel.

 

Something like:

 

string outputRange; // = "ScheduledTrips!A:L";
int numRows; // = Shipments.size;
// shipment tuple contains 3 components
{shipment} Shipments = ...;
// postprocessing
execute {
numRows = Shipments.size;
outputRange = "ScheduledTrips!A2:" + "C" + (numRows+1);
writeln(outputRange);
}

just for SheetRead

 

this is my .dat file. I want to make the range of D depend on nbProdcuts

prepare {
        function generateCosts(c, name) { 
                for(var s=0; s<c.size; s++) {
                        c[s] = s;
                }
        return true;
        
        }
}

nbProducts =3;
nbSources =3;

weight = [10];


c = /*[1, 2]

        */
        []
        invoke generateCosts
        ;
        
SheetConnection sheet("sourcing_reduced_sheet.xls");

D from SheetRead(sheet,"'demand'!A2:A3");
B from SheetRead(sheet, "'stock'!A2:B3");

 

Thanks!

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

at

https://www.ibm.com/developerworks/community/forums/html/topic?id=5af4d332-2a97-4250-bc06-76595eef1ab0&ps=25

you have an example where the SheetConnexction string is a parameter

You could use the same to send the SheetRead string as a parameter

regards

https://www.linkedin.com/pulse/how-opl-alex-fleischer/

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Redhat11

Hi,

I am not able to create a string in the .dat file where I have the SheetRead execution

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

so let me give you a tiny example

.mod

int n=...;

string st=(n==1)?"B1:B1":
         (n==2)?"B1:B2":
          (n==3)?"B1:B3":
          "B1:B4";

 

int v[1..n]=...;

execute
{
writeln(v);
}

.dat

SheetConnection s("test.xlsx");

n from SheetRead(s,"A1:A1");
v from SheetRead(s,st);

gives

 

[1 2 3]

regards

 

https://www.linkedin.com/pulse/ist-mathematische-optimierung-und-wie-kann-sie-helfen-alex-fleischer/

 

#DecisionOptimization

Originally posted by: Redhat11

Hi AlexFleischer,

thanks, for your answer. This works, however, I have to build a lot of conditions  with

string st=(n==1)?"B1:B1":
         (n==2)?"B1:B2":
          (n==3)?"B1:B3":
          "B1:B4";

 

Isn't there a way to build a String like this?

string st= "B1:B" + n;

 

It says Operator string + int is not available

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

yes in OPL scripting but not in OPL

Do not hesitate to log a wish (RFE)

http://www-01.ibm.com/support/docview.wss?uid=swg21659629

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Redhat11

Is there a way to execute OPL Script first to build the string and afterwards execute the SheetConnection and SheetRead?

Thanks

#DecisionOptimization
#OPLusingCPLEXOptimizer

yes through a main block and this is why I sent you an example a few hours ago

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

PS:

You may also use prepare and invoke in the .dat


See example in https://www.linkedin.com/pulse/tips-tricks-opl-cplex-alex-fleischer/

.mod

https://github.com/AlexFleischerParis/opltipsandtricks/blob/master/read2Darrayfromexcelgeneric.mod

.dat

https://github.com/AlexFleischerParis/opltipsandtricks/blob/master/read2Darrayfromexcelgeneric.dat

------------------------------
ALEX FLEISCHER
------------------------------

Original Message:
Sent: Mon July 20, 2020 06:36 AM
From: ALEX FLEISCHER
Subject: Re: SheetRead dynamic range

yes through a main block and this is why I sent you an example a few hours ago

regards

#DecisionOptimization