Topic
  • 2 replies
  • Latest Post - ‏2013-12-27T13:38:31Z by DidacR
DidacR
DidacR
6 Posts

Pinned topic Reading data sequentially from different pages in Excel

‏2013-12-21T11:37:24Z |

Hi there,

 

I am replicating 25 different instances of a a Stochastic MIP model.

Each instance reads from a different Excel spreadsheet.

The call to the spreadsheet is:

 

/*data*/

SheetConnection results("EVS.xls");
 
Var_1 to SheetWrite(results,"Sheet01!A1:X1");

Var_2 to SheetWrite(results,"Sheet01!A3:X26");

Var_3 to SheetWrite(results,"Sheet01!A28:X82");

 

However, for each instance I need to change the number of the sheet to to be read by hand.

Is there a way to do this directly from /* main{} */?

 

Many thanks,

 

Didac

 

 

Updated on 2013-12-25T01:29:15Z at 2013-12-25T01:29:15Z by DidacR
  • AlexFleischer
    AlexFleischer
    1658 Posts
    ACCEPTED ANSWER

    Re: Reading data sequentially from different pages in Excel

    ‏2013-12-25T20:17:59Z  

    Hi,

    let me a small example.

     

    The sub model sub.mod

     

    float maxOfx = 2;
    string fileName=...;
    dvar float x;

    maximize x;
    subject to {
      x<=maxOfx;
    }

    execute
    {
     writeln("filename= ",fileName);
    }

     

    and then you have the main model

     

    main {
      var source = new IloOplModelSource("sub.mod");
      var cplex = new IloCplex();
      var def = new IloOplModelDefinition(source);
      var opl = new IloOplModel(def,cplex);
     
     
      for(var k=11;k<=20;k++)
      {
      var opl = new IloOplModel(def,cplex);
        
      var data2= new IloOplDataElements();
      data2.fileName="file"+k;
      opl.addDataSource(data2);
      opl.generate();

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

     

    which will display

     

    OBJ = 2
    filename= file11
    OBJ = 2
    filename= file12
    OBJ = 2
    filename= file13
    OBJ = 2
    filename= file14
    OBJ = 2
    filename= file15
    OBJ = 2
    filename= file16
    OBJ = 2
    filename= file17
    OBJ = 2
    filename= file18
    OBJ = 2
    filename= file19
    OBJ = 2
    filename= file20

     

    You may then use fileName for your SheetConnexion  to do your sheetRead or sheetWrite

     

    Regards

  • AlexFleischer
    AlexFleischer
    1658 Posts

    Re: Reading data sequentially from different pages in Excel

    ‏2013-12-25T20:17:59Z  

    Hi,

    let me a small example.

     

    The sub model sub.mod

     

    float maxOfx = 2;
    string fileName=...;
    dvar float x;

    maximize x;
    subject to {
      x<=maxOfx;
    }

    execute
    {
     writeln("filename= ",fileName);
    }

     

    and then you have the main model

     

    main {
      var source = new IloOplModelSource("sub.mod");
      var cplex = new IloCplex();
      var def = new IloOplModelDefinition(source);
      var opl = new IloOplModel(def,cplex);
     
     
      for(var k=11;k<=20;k++)
      {
      var opl = new IloOplModel(def,cplex);
        
      var data2= new IloOplDataElements();
      data2.fileName="file"+k;
      opl.addDataSource(data2);
      opl.generate();

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

     

    which will display

     

    OBJ = 2
    filename= file11
    OBJ = 2
    filename= file12
    OBJ = 2
    filename= file13
    OBJ = 2
    filename= file14
    OBJ = 2
    filename= file15
    OBJ = 2
    filename= file16
    OBJ = 2
    filename= file17
    OBJ = 2
    filename= file18
    OBJ = 2
    filename= file19
    OBJ = 2
    filename= file20

     

    You may then use fileName for your SheetConnexion  to do your sheetRead or sheetWrite

     

    Regards

  • DidacR
    DidacR
    6 Posts

    Re: Reading data sequentially from different pages in Excel

    ‏2013-12-27T13:38:31Z  

    Hi,

    let me a small example.

     

    The sub model sub.mod

     

    float maxOfx = 2;
    string fileName=...;
    dvar float x;

    maximize x;
    subject to {
      x<=maxOfx;
    }

    execute
    {
     writeln("filename= ",fileName);
    }

     

    and then you have the main model

     

    main {
      var source = new IloOplModelSource("sub.mod");
      var cplex = new IloCplex();
      var def = new IloOplModelDefinition(source);
      var opl = new IloOplModel(def,cplex);
     
     
      for(var k=11;k<=20;k++)
      {
      var opl = new IloOplModel(def,cplex);
        
      var data2= new IloOplDataElements();
      data2.fileName="file"+k;
      opl.addDataSource(data2);
      opl.generate();

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

     

    which will display

     

    OBJ = 2
    filename= file11
    OBJ = 2
    filename= file12
    OBJ = 2
    filename= file13
    OBJ = 2
    filename= file14
    OBJ = 2
    filename= file15
    OBJ = 2
    filename= file16
    OBJ = 2
    filename= file17
    OBJ = 2
    filename= file18
    OBJ = 2
    filename= file19
    OBJ = 2
    filename= file20

     

    You may then use fileName for your SheetConnexion  to do your sheetRead or sheetWrite

     

    Regards

    Thank you very much Alex!