Pinned topic Flow Scripts and Excel Input/Output
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:
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!
Laval 27000340XM16 PostsACCEPTED ANSWER
Re: Flow Scripts and Excel Input/Output2010-09-29T16:04:17Z in response to Claus1980Hi,
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 110000D4XK1883 PostsACCEPTED ANSWER
Re: Flow Scripts and Excel Input/Output2010-09-30T05:29:02Z in response to LavalHowdy,
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.
ArnaudS 2700038GVE11 PostsACCEPTED ANSWER
Re: Flow Scripts and Excel Input/Output2010-11-18T08:49:58Z in response to LavalHi,
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.
Q861_Munendra_Parmar 270005Q8613 PostsACCEPTED ANSWER
Re: Flow Scripts and Excel Input/Output2013-06-11T12:21:08Z in response to 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:
var def = thisOplModel.modelDefinition;
var data = thisOplModel.dataElements;
writeln("Setting initial solution");
var cplex2 = new IloCplex();
var opl2 = new IloOplModel(def, cplex2);
var vectors = new IloOplCplexVectors();
// We attach the values (defined as data) as starting solution
// for the variables x.
cplex2.epgap = 0.1;
Many many thanksUpdated on 2013-06-11T12:37:38Z at 2013-06-11T12:37:38Z by Q861_Munendra_Parmar