Topic
  • 6 replies
  • Latest Post - ‏2013-12-09T14:26:24Z by DidacR
DidacR
DidacR
6 Posts

Pinned topic Write to a spreadsheet using IBM ILOG Script for OPL

‏2013-12-08T13:29:13Z |

Hi there,

 

I am running a set of stochastic mixed integer programs using IBM ILOG SCRIPT.

I need to write the results of one run to an excel file, that will be later read by a different script.

In my *.dat file I have

SheetConnection sheet1("EVS_Sols.xls");
 
 OpenBranch to SheetWrite(sheet1,"result");

however, after execution, the excel file appears empty.

I wonder if it is due to the fact that I must do the writing from inside the script, in a way similar to the way we write in a plain text file

var ofile = new IloOplOutputFile("EVS_Sols;.txt");

ofile.writeln(result);
 

Many thanks in advance for any advise.

 

 

 

Updated on 2013-12-08T13:29:58Z at 2013-12-08T13:29:58Z by DidacR
  • AlexFleischer
    AlexFleischer
    1855 Posts
    ACCEPTED ANSWER

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T09:46:58Z  
    • DidacR
    • ‏2013-12-09T09:07:48Z

    Hi Alex,

     

    I am sending a reduced version of main{} with the basic elements.

    Instead uf using the spreadsheet option. I am using a second text file as output.

    ********************************************************************

    main{
     
      thisOplModel.generate();
      var EVS = thisOplModel;
      var Locat= EVS.Locat;
     
      var ofile = new IloOplOutputFile("EVS.txt",true);
      var OpFile = new IloOplOutputFile("EVS_Locat.txt",true);

      if(cplex.solve()){

        var OpLocat = EVS.OpenLocat;
       
        OpFile.writeln(OpLocat);
        
        var Status = cplex.getCplexStatus()
        ofile.writeln("Status =", Status );
        var BestNode = cplex.getObjValue();
        var BestInt = cplex.getBestObjValue();
        if(Status == 1){
        ofile.writeln("Optimal");}
        else{
        var RelGap = (Math.abs(BestNode-BestInt))/(1e-10+Math.abs(BestNode));
        ofile.writeln("Optimallity Gap =",RelGap);
        }
        
        
        ofile.writeln(OpLocat);
        ofile.writeln(BestInt);
        ofile.writeln(BestNode);
     
    }

    ********************************************************************

     

    *******************          dat file                   ************************

    SheetConnection sheet("Locations.xls");

    OpLocat to SheetWrite(sheet,"Sheet1!A1:A400");

    ***********************************************************

    Thanks

     

    Hi,

     

    in order to call the SheetWrite, after

    if(cplex.solve()){

    you should do thisOplModel.postProcess()

     

    regards

  • AlexFleischer
    AlexFleischer
    1855 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T07:49:24Z  

    Hi,

    do you use a main block in scripting ?

    And if yes, did you do the call to postProcess ?

    Anyway you should have a look at the example oil in the product which shows how to connect to an Excel spreadsheet

    Regards

  • DidacR
    DidacR
    6 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T08:42:13Z  

    Hi,

    do you use a main block in scripting ?

    And if yes, did you do the call to postProcess ?

    Anyway you should have a look at the example oil in the product which shows how to connect to an Excel spreadsheet

    Regards

    Hi Alex,

     

    Thank for your answer.

    Unfortunatelly, the oil example does not use a main block. Therefore, as I understand, I can not use execute blocks inside. Will appreciate if you can give me some other hint.

    Thanks,

  • AlexFleischer
    AlexFleischer
    1855 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T08:45:39Z  
    • DidacR
    • ‏2013-12-09T08:42:13Z

    Hi Alex,

     

    Thank for your answer.

    Unfortunatelly, the oil example does not use a main block. Therefore, as I understand, I can not use execute blocks inside. Will appreciate if you can give me some other hint.

    Thanks,

    Hi,

    do you want to attach your projects ?

    Do you call postProcess in your main block ?

    regards

  • DidacR
    DidacR
    6 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T09:07:48Z  
    • DidacR
    • ‏2013-12-09T08:42:13Z

    Hi Alex,

     

    Thank for your answer.

    Unfortunatelly, the oil example does not use a main block. Therefore, as I understand, I can not use execute blocks inside. Will appreciate if you can give me some other hint.

    Thanks,

    Hi Alex,

     

    I am sending a reduced version of main{} with the basic elements.

    Instead uf using the spreadsheet option. I am using a second text file as output.

    ********************************************************************

    main{
     
      thisOplModel.generate();
      var EVS = thisOplModel;
      var Locat= EVS.Locat;
     
      var ofile = new IloOplOutputFile("EVS.txt",true);
      var OpFile = new IloOplOutputFile("EVS_Locat.txt",true);

      if(cplex.solve()){

        var OpLocat = EVS.OpenLocat;
       
        OpFile.writeln(OpLocat);
        
        var Status = cplex.getCplexStatus()
        ofile.writeln("Status =", Status );
        var BestNode = cplex.getObjValue();
        var BestInt = cplex.getBestObjValue();
        if(Status == 1){
        ofile.writeln("Optimal");}
        else{
        var RelGap = (Math.abs(BestNode-BestInt))/(1e-10+Math.abs(BestNode));
        ofile.writeln("Optimallity Gap =",RelGap);
        }
        
        
        ofile.writeln(OpLocat);
        ofile.writeln(BestInt);
        ofile.writeln(BestNode);
     
    }

    ********************************************************************

     

    *******************          dat file                   ************************

    SheetConnection sheet("Locations.xls");

    OpLocat to SheetWrite(sheet,"Sheet1!A1:A400");

    ***********************************************************

    Thanks

     

  • AlexFleischer
    AlexFleischer
    1855 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T09:46:58Z  
    • DidacR
    • ‏2013-12-09T09:07:48Z

    Hi Alex,

     

    I am sending a reduced version of main{} with the basic elements.

    Instead uf using the spreadsheet option. I am using a second text file as output.

    ********************************************************************

    main{
     
      thisOplModel.generate();
      var EVS = thisOplModel;
      var Locat= EVS.Locat;
     
      var ofile = new IloOplOutputFile("EVS.txt",true);
      var OpFile = new IloOplOutputFile("EVS_Locat.txt",true);

      if(cplex.solve()){

        var OpLocat = EVS.OpenLocat;
       
        OpFile.writeln(OpLocat);
        
        var Status = cplex.getCplexStatus()
        ofile.writeln("Status =", Status );
        var BestNode = cplex.getObjValue();
        var BestInt = cplex.getBestObjValue();
        if(Status == 1){
        ofile.writeln("Optimal");}
        else{
        var RelGap = (Math.abs(BestNode-BestInt))/(1e-10+Math.abs(BestNode));
        ofile.writeln("Optimallity Gap =",RelGap);
        }
        
        
        ofile.writeln(OpLocat);
        ofile.writeln(BestInt);
        ofile.writeln(BestNode);
     
    }

    ********************************************************************

     

    *******************          dat file                   ************************

    SheetConnection sheet("Locations.xls");

    OpLocat to SheetWrite(sheet,"Sheet1!A1:A400");

    ***********************************************************

    Thanks

     

    Hi,

     

    in order to call the SheetWrite, after

    if(cplex.solve()){

    you should do thisOplModel.postProcess()

     

    regards

  • DidacR
    DidacR
    6 Posts

    Re: Write to a spreadsheet using IBM ILOG Script for OPL

    ‏2013-12-09T14:26:24Z  

    Hi,

     

    in order to call the SheetWrite, after

    if(cplex.solve()){

    you should do thisOplModel.postProcess()

     

    regards

    Thaks Alex,

     

    Sorted!