CODEWITHSUNDEEP
excelcplexopl
Yan H.
Yan H.

Reputation: 49

Filling a tuple from excel with the execute block

I have to fill the follwing tuple for my CPLEX CPL model:

tuple Assignment{
       int id; //ID of the Operation O_ij, connects opearation and assignment
       int worker; //worker l for operation O_ij
       int machine; //machine k for operation O_ij
       int time; //processing time of the operation O_ij
     }

Now the problem is that each element of the tuple is supposed to be filled out in a different way, i.e. "id" would come from the iteration step, for "worker" I have to get a set of integers from a single cell in Excel, for "machine" I have to check if a cell in Excel is empty or not and finally for "time" I have to multiply some Excel cells.

My initial idea was to do it with a bunch of for loops in an execute block, since I would have to move the cell range depending on the iteration. The problem is that I can not use SheetConnection in my .mod file and I also can not use excecute blocks in the .data file.

Is there any way that I cann access Excel from .mod file or use execute blocks in .data file?

Upvotes: 0

Views: 115

Answers (1)

Alex Fleischer
Alex Fleischer

Reputation: 10037

As written in https://www.linkedin.com/pulse/javascript-within-opl-cplex-alex-fleischer/

In OPL we can use scripting for preprocessing and postprocessing,for flow control, but also within the data file .dat to process data.

Example in https://github.com/AlexFleischerParis/oplscripting/blob/main/zooscriptingindata.dat

prepare {                                      
   function priceDecrease(s, name) {  
      writeln("before price decrease");
      writeln("s=",s);                
      for(i=0;i<s.size;i++) Opl.item(s,i).cost-=100;
      writeln("after price decrease");
      writeln("s=",s); 
      return true;
   }   
}

nbKids= 300;

buses={<40,500>,<30,400>} invoke priceDecrease;

You may also use SheetRead without SheetRead as can be seen in https://github.com/AlexFleischerParis/oplexcel/blob/main/readwithoutsheetread.mod

// Read from an Excel spreadsheet without SheetRead
// which means you can use this on non Windows platform

execute
    {

    function read_excel(filename,sheetname,skiprows,nrows,cols,datfilename,resname)
    {
        var quote="\"";
        
        var python=new IloOplOutputFile("c:\\temp\\readexcel.py");
        
        python.writeln("import pandas as pd");
        python.writeln("import xlrd");
        python.writeln("df=pd.read_excel('"+filename+"'"+",sheet_name = '"+sheetname+"'"+
        ",skiprows = "+skiprows+  ",nrows= "+nrows+ ","
        +"header=None,usecols = '"+cols+"')");
        python.writeln("print(df)");
        
        
        
        python.writeln("res = open(",quote,datfilename,quote,",",quote,"w",quote,")");
        python.writeln("res.write(",quote,resname,"=[",quote,")");
        python.writeln("res.write(",quote,"\\","n",quote,")");
        python.writeln("for i, row in enumerate(df.values):");
       
        python.writeln("   res.write(",quote,"[",quote,")");
        
        python.writeln("   for j in row:");
       
        python.writeln("      if (j==j):");
        python.writeln("         res.write(str(j))");
        python.writeln("         res.write(\",\")");
       
        python.writeln("   res.write(\"],\")    ");
        python.writeln("   res.write(",quote,"\\","n",quote,")");
        python.writeln("res.write(\"];\")");
        python.writeln("res.close()");
        python.close();
       
        python.close();
        
        IloOplExec("C:\\Python36\\python.exe c:\\temp\\readexcel.py",true);
        
    }
    read_excel("c:\\\\temp\\\\read2Darray.xls","Sheet1",0,2,"B:D","c:\\\\temp\\\\resexcel","res");
}

Upvotes: 1

Related Questions