Topic
  • 5 replies
  • Latest Post - ‏2014-02-28T09:49:31Z by ilikepi
MWK
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 |

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");
 
 Prob from SheetRead(sheet1,"prob!B2:B11"); 

 X from SheetRead(sheet1,"d1!A1:D10");   Y from SheetRead(sheet1,"d1!E1:H10");   Z from SheetRead(sheet1,"d1!I1:L10");

 
 SheetConnection sheet2("Question2.xlsx");

 P from SheetRead(sheet2,"P!B2:E2");

 

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

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
    AlexFleischer
    1658 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");
    Gasolines from SheetRead(sheet,"'gas data'!A2:A4");
    Oils from SheetRead(sheet,"'oil data'!A2:A4");
    Gas from SheetRead(sheet,"'gas data'!B2:E4");
    Oil from SheetRead(sheet,"'oil data'!B2:E4");
    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);
    Gasolines from SheetRead(sheet,s2);
    Oils from SheetRead(sheet,s3);
    Gas from SheetRead(sheet,s4);
    Oil from SheetRead(sheet,s5);
    MaxProduction = 14000;
    ProdCost = 4;


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

     

    if you add

    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
    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");
    Gasolines from SheetRead(sheet,"'gas data'!A2:A4");
    Oils from SheetRead(sheet,"'oil data'!A2:A4");
    Gas from SheetRead(sheet,"'gas data'!B2:E4");
    Oil from SheetRead(sheet,"'oil data'!B2:E4");
    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);
    Gasolines from SheetRead(sheet,s2);
    Oils from SheetRead(sheet,s3);
    Gas from SheetRead(sheet,s4);
    Oil from SheetRead(sheet,s5);
    MaxProduction = 14000;
    ProdCost = 4;


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

     

    if you add

    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
    AlexFleischer
    1658 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,

     

    in documentation IDE and OPL > Optimization Programming Language (OPL) > IBM ILOG Script Reference Manual > OPL Classes

    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.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(); 
    }
    

     

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

     

    Regards

  • Q861_Munendra_Parmar
    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,

     

    in documentation IDE and OPL > Optimization Programming Language (OPL) > IBM ILOG Script Reference Manual > OPL Classes

    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
    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");
    Gasolines from SheetRead(sheet,"'gas data'!A2:A4");
    Oils from SheetRead(sheet,"'oil data'!A2:A4");
    Gas from SheetRead(sheet,"'gas data'!B2:E4");
    Oil from SheetRead(sheet,"'oil data'!B2:E4");
    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);
    Gasolines from SheetRead(sheet,s2);
    Oils from SheetRead(sheet,s3);
    Gas from SheetRead(sheet,s4);
    Oil from SheetRead(sheet,s5);
    MaxProduction = 14000;
    ProdCost = 4;


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

     

    if you add

    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?