Topic
4 replies Latest Post - ‏2013-06-11T12:21:08Z by Q861_Munendra_Parmar
Claus1980
Claus1980
1 Post
ACCEPTED ANSWER

Pinned topic Flow Scripts and Excel Input/Output

‏2010-09-29T08:24:59Z |
Hello everyone,

I wrote a flow script that combines two optimization models with each other.

The results of the first model is written to an Excel Sheet output.xls. This I implemented by adding the following lines in the .dat file:

SheetConnection output("output.xls");
y to SheetWrite(output,"DataInput!I2:I1954");
e to SheetWrite(output,"DataInput!J2:J1954");

The results written into Excel are the input of the second model. Therefore the command to read from Excel was added to the .dat file of the second model respictively.

Now here is my problem: If a run a configuration of the first model, it does what I expect and writes the result to Excel. If I then run the second model it reads the results and everything works like I want it to work. However, if I execute both models in the right order via flowscript, the whole Excel Input/Output part does not work. I have the feeling that the commands for read/write are not executed then.

Any ideas how to solve this issue? Thanks in advance!
Updated on 2010-11-18T08:49:58Z at 2010-11-18T08:49:58Z by ArnaudS
  • Laval
    Laval
    16 Posts
    ACCEPTED ANSWER

    Re: Flow Scripts and Excel Input/Output

    ‏2010-09-29T16:04:17Z  in response to Claus1980
    Hi,
    Your feeling is right. I think (quite sure) when have a opl script, Opl does not exectute write commands (sheetwrite for example). I have done some tests and have the same behaviour as yours... when you execute the confirguration alone is Ok but when you use some scripts and execute it, what is happen is that he reads the data but writing commmands are not executed.

    Here is the solution to your problem. you should use arrays instead of reading/writing to Excel, its faster and easier.
    All you need is to define an array for each variable and when you execute your first model, copy these results (values of variables) into those arrays and then for the second model, read those arrays (with new values) and copy them into the parameters of your second model and that's all. I hope is clear. Let me known if is ok otherwise I can send you some code that illustrate this later...
    • SystemAdmin
      SystemAdmin
      1883 Posts
      ACCEPTED ANSWER

      Re: Flow Scripts and Excel Input/Output

      ‏2010-09-30T05:29:02Z  in response to Laval
      Howdy,

      another workaround would be to write to a dat-file instead of Excel
      and to add this dat-file as a data source to your second model.
      This should work out as well.

      Regards
      Norbert
    • ArnaudS
      ArnaudS
      11 Posts
      ACCEPTED ANSWER

      Re: Flow Scripts and Excel Input/Output

      ‏2010-11-18T08:49:58Z  in response to Laval
      Hi,
      The SheetWrite is done during the post-processing.
      When you use flow control (main block) if you want to execute the post processing, you must do it explicitly by calling postProcess() on the model.
      Bye,
      Arnaud
      • Q861_Munendra_Parmar
        3 Posts
        ACCEPTED ANSWER

        Re: Flow Scripts and Excel Input/Output

        ‏2013-06-11T12:21:08Z  in response to ArnaudS

        hi ArnaudS,

        Would you please elaborate more on this as I am still facing the same issue. I have tried your suggestion of calling postprocessing explicitly from main block by using "opl1.postProcess()"   but fails to write results in excel. Although it is doing everything else in postprocessing.

        Please find snippet of the code as below:

        main{
          thisOplModel.generate();  
          var def = thisOplModel.modelDefinition;  
          var data = thisOplModel.dataElements;
         writeln("Setting initial solution");
          var cplex2 = new IloCplex();
          var opl2 = new IloOplModel(def, cplex2);
          opl2.addDataSource(data);
          opl2.generate();
          var vectors = new IloOplCplexVectors();
          // We attach the values (defined as data) as starting solution
          // for the variables x.
          vectors.attach(opl2.NbCall,opl2.values);
          vectors.setVectors(cplex2);
          cplex2.epgap = 0.1;  
          cplex2.solve();   
          writeln(opl2.printSolution());  
          opl2.postProcess();
          opl2.end();
          cplex2.end();
          0;

        }

        Many many thanks

        Updated on 2013-06-11T12:37:38Z at 2013-06-11T12:37:38Z by Q861_Munendra_Parmar