Topic
• 9 replies
• Latest Post - ‏2018-05-16T19:14:36Z by obumin
MWK
2 Posts

# Pinned topic How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2013-04-21T16:54:12Z | main

Hello All,

I am stuck with a flow control and main block and could not get a way out from the given CPLEX exmaples.

Actually, i want to solve optimization problem for more than 50 times (days in my case) with different data reading from excel sheet. For each day i have to change the data reading from excel and run it  separately.

I want to implement the for loop ( from 1..number of required days) in main block, so that one for loop iteration will solve single day while excessing particular data for  from .dat file.

Here is the prototype below, explaining what i actually want to do.

/******************************/

/* Model File*/

range sce=1..10;  range hours=1..4;  range days=1..10;

float Prob[sce]=...;

float X[sce][hours]=...;   float Y[sce][hours]=...;    float Z[sce][hours]=...;

float P[hours]=...;

dvar float find1[hours];  dvar float find2[hours];

dexpr float OutCome_h[h in hours]= sum(s in sce)Prob[s]*(find1[h]+find2[h]);

dexpr float OutCome=sum(h in hours) OutCome_h[h];

maximize OutCome;

subject to{

forall(h in hours,s in sce)

find1[h] + find2[h]<=10;

}

main{

var initialmodel = thisOplModel;

initialmodel.generate();

( I am trying to implement some coding here with for loop to excess data from .dat file and solving the .mod file)

cplex.solve()

writeln("X= ",cplex.getObjValue());

}

/* data file*/

SheetConnection sheet1("Question1.xlsx");

SheetConnection sheet2("Question2.xlsx");

/********************************/

P.S: The number of days and data given in the example are very low, in actual i am dealing with large amount of data.

File is also attached containing this prototype example for ready use.

Kindly, give some feedback

Thankyou in anticipation

regards,
waqas

#### Attachments

Updated on 2013-04-21T21:10:04Z at 2013-04-21T21:10:04Z by MWK
• AlexFleischer
3273 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2013-04-23T08:41:21Z

Hi,

in the example oil, you can see in oilSheet.dat

SheetConnection sheet("oilSheet.xls");
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");

This can be replaced by

SheetConnection sheet(s);
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,s6);
Blend to SheetWrite(sheet,s7);

string s="oilSheet.xls";
string s2="'gas data'!A2:A4";
string s3="'oil data'!A2:A4";
string s4="'gas data'!B2:E4";
string s5="'oil data'!B2:E4";

string s6="RESULT!A2:A4";
string s7="RESULT!B2:D4";

in the .mod

In your loop in the main block you can then change the strings in order to read what you need and the same for what you need to write

regards

Alex Fleischer

• lifelongOptimizer
1 Post

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2013-04-23T16:21:46Z

Hi,

in the example oil, you can see in oilSheet.dat

SheetConnection sheet("oilSheet.xls");
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");

This can be replaced by

SheetConnection sheet(s);
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,s6);
Blend to SheetWrite(sheet,s7);

string s="oilSheet.xls";
string s2="'gas data'!A2:A4";
string s3="'oil data'!A2:A4";
string s4="'gas data'!B2:E4";
string s5="'oil data'!B2:E4";

string s6="RESULT!A2:A4";
string s7="RESULT!B2:D4";

in the .mod

In your loop in the main block you can then change the strings in order to read what you need and the same for what you need to write

regards

Alex Fleischer

I have the exact same question. I need to run the same model multiple times, each time using data from a new row in the excel sheet.

Alex, I tried your solution. Two issues -

1) Even though I assigned new strings to data.s1, data.s2 etc in my main block, it gave the error 'Regenerating an immutable OPL model has no effect'. The code also did not jump to the next row in the excel sheet.

2) writing the solution back to the excel sheet using Sheetwrite did not work, even for the first row of data.

Any ideas on how to tackle these issues? Thanks!

- Raj

• AlexFleischer
3273 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2013-04-23T16:42:34Z

I have the exact same question. I need to run the same model multiple times, each time using data from a new row in the excel sheet.

Alex, I tried your solution. Two issues -

1) Even though I assigned new strings to data.s1, data.s2 etc in my main block, it gave the error 'Regenerating an immutable OPL model has no effect'. The code also did not jump to the next row in the excel sheet.

2) writing the solution back to the excel sheet using Sheetwrite did not work, even for the first row of data.

Any ideas on how to tackle these issues? Thanks!

- Raj

Hi,

class IloOplModel

you have an example on how to modify data and run again

```main {
var source = new IloOplModelSource("basicmodel.mod");
var cplex = new IloCplex();
var def = new IloOplModelDefinition(source);
var opl = new IloOplModel(def,cplex);
var data = new IloOplDataSource("basicmodel.dat");
opl.generate();
if (cplex.solve()) {
writeln("OBJ = " + cplex.getObjValue());
} else {
writeln("No solution");
}
var opl2 = new IloOplModel(def,cplex);
var data2= new IloOplDataElements();
data2.maxOfx=11;
opl2.generate();

if (cplex.solve()) {
writeln("OBJ = " + cplex.getObjValue());
} else {
writeln("No solution");
}

opl.end();
opl2.end();
data.end();
def.end();
cplex.end();
source.end();
}
```

For your second question, do not forget that if you want your sheetwrite to be called you need to explicitly call postProcess

Regards

• 3 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2013-06-11T12:14:35Z

Hi,

class IloOplModel

you have an example on how to modify data and run again

<pre class="code" dir="ltr">main { var source = new IloOplModelSource("basicmodel.mod"); var cplex = new IloCplex(); var def = new IloOplModelDefinition(source); var opl = new IloOplModel(def,cplex); var data = new IloOplDataSource("basicmodel.dat"); opl.addDataSource(data); opl.generate(); if (cplex.solve()) { writeln("OBJ = " + cplex.getObjValue()); } else { writeln("No solution"); } var opl2 = new IloOplModel(def,cplex); var data2= new IloOplDataElements(); data2.maxOfx=11; opl2.addDataSource(data2); opl2.generate(); if (cplex.solve()) { writeln("OBJ = " + cplex.getObjValue()); } else { writeln("No solution"); } opl.end(); opl2.end(); data.end(); def.end(); cplex.end(); source.end(); } </pre>

For your second question, do not forget that if you want your sheetwrite to be called you need to explicitly call postProcess

Regards

Hi,

I am facing the same situation and tried explicitly calling Postprocessing from Main block by using" opl1.postProcess()". It does all the postprocessing but still not calling sheetWrite written in .dat file.

• ilikepi
1 Post

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2014-02-28T09:49:31Z

Hi,

in the example oil, you can see in oilSheet.dat

SheetConnection sheet("oilSheet.xls");
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");

This can be replaced by

SheetConnection sheet(s);
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,s6);
Blend to SheetWrite(sheet,s7);

string s="oilSheet.xls";
string s2="'gas data'!A2:A4";
string s3="'oil data'!A2:A4";
string s4="'gas data'!B2:E4";
string s5="'oil data'!B2:E4";

string s6="RESULT!A2:A4";
string s7="RESULT!B2:D4";

in the .mod

In your loop in the main block you can then change the strings in order to read what you need and the same for what you need to write

regards

Alex Fleischer

Hi,

I am having a problem similar to this one but am having trouble implementing the suggested solution.  My problem involves reading data from different spreadsheet files.  To try and do this,  I have added string s="oilSheet.xls"; (with my own variable names replacing the ones in the example) in the .mod file but can't find any information on how to modify the string in the main block to change the file in each loop.

Can you offer some assistance on how to implement the solution suggested here?

• Kamran_Sr
22 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2018-04-17T12:22:52Z

Hi,

class IloOplModel

you have an example on how to modify data and run again

<pre class="code" dir="ltr">main { var source = new IloOplModelSource("basicmodel.mod"); var cplex = new IloCplex(); var def = new IloOplModelDefinition(source); var opl = new IloOplModel(def,cplex); var data = new IloOplDataSource("basicmodel.dat"); opl.addDataSource(data); opl.generate(); if (cplex.solve()) { writeln("OBJ = " + cplex.getObjValue()); } else { writeln("No solution"); } var opl2 = new IloOplModel(def,cplex); var data2= new IloOplDataElements(); data2.maxOfx=11; opl2.addDataSource(data2); opl2.generate(); if (cplex.solve()) { writeln("OBJ = " + cplex.getObjValue()); } else { writeln("No solution"); } opl.end(); opl2.end(); data.end(); def.end(); cplex.end(); source.end(); } </pre>

For your second question, do not forget that if you want your sheetwrite to be called you need to explicitly call postProcess

Regards

Hi Alex,

I have a problem that want to solve a model with different data for demand parameter for different scenarios. For example, if I have 10 scenarios, I have 10 different value for demand parameter. I want to solve the model for each scenario and use the objective value of each function into another problem. For solving the model with different scenarios, I want to use the example you wrote here in this post. However, I am not sure how I can use these different values of demand. I mean, in each part of the code you have written I can apply the new data? should I have a different .dat file for every scenario? or all scenario data should be in one .dat file? in which part of this code I can use the different data to solve the model?

I would be appreciated if you could help me with this.

Regards

• Kamran_Sr
22 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2018-05-16T18:23:50Z

Hi,

in the example oil, you can see in oilSheet.dat

SheetConnection sheet("oilSheet.xls");
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");

This can be replaced by

SheetConnection sheet(s);
MaxProduction = 14000;
ProdCost = 4;

a to SheetWrite(sheet,s6);
Blend to SheetWrite(sheet,s7);

string s="oilSheet.xls";
string s2="'gas data'!A2:A4";
string s3="'oil data'!A2:A4";
string s4="'gas data'!B2:E4";
string s5="'oil data'!B2:E4";

string s6="RESULT!A2:A4";
string s7="RESULT!B2:D4";

in the .mod

In your loop in the main block you can then change the strings in order to read what you need and the same for what you need to write

regards

Alex Fleischer

Hi Alex,

In the example of Oil I did some modification to in main script to read data from different excel range to change the parameter in each loop iteration.

The model can run, but after the run is finished nothing shown as outcome in any tab !!!

I have attached the relevant files. I would be grateful if you could have a look and help me with that.

Regards,

Kamran

#### Attachments

• AlexFleischer
3273 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2018-05-16T19:10:53Z
• Kamran_Sr
• ‏2018-05-16T18:23:50Z

Hi Alex,

In the example of Oil I did some modification to in main script to read data from different excel range to change the parameter in each loop iteration.

The model can run, but after the run is finished nothing shown as outcome in any tab !!!

I have attached the relevant files. I would be grateful if you could have a look and help me with that.

Regards,

Kamran

Hi,

do you mean even the scripting log is empty if you do some writeln ?

regards

• obumin
7 Posts

#### Re: How to implement for loop in main block (flow control) while accessing different data from excel for each loop.

‏2018-05-16T19:14:36Z

Hello Alex @AlexFleischer, sorry to bother you, I codded a PVRP in OPL CPLEX and i wanted a constraint which finds subtours and add constraints in order to neglect them, in that sense I used IBM TSP example subtour algorithm; however when I run the model, it gets into loop and stuck into one solution, May I kindly ask you to look at my model.

Best regards,

Onur