Hi,

I have a question that I am struggling to get the answer for. I would like to read data from external excel file using the following code.

execute {
  var excelFile = "path/to/excel/file.xlsx";
  var dataSource = new IloOplExcelDataSource(excelFile);
  dataSource.addTable("ProcessingTimes", "A1:D100");
  thisOplModel.addDataSource(dataSource);
}

int processingTime[allActivities];
int energyConsumption[allActivities];

execute {
  for (var da in allActivities) {
    var productType = da.activity.product_type;
    var activityType = da.activity.activity_type;

    // Get processing time and energy consumption from data source
    var processingTimes = oplModel.datasources.ProcessingTimes;
    var processingTimeRow = processingTimes[productType][activityType][1];
    var energyConsumptionRow = processingTimes[productType][activityType][2];
    var numRows = processingTimeRow.getSize();

    // Sample processing time and energy consumption from table
    var row = opl.rand(numRows)+1;
    processingTime[da] = processingTimeRow[row];
    energyConsumption[da] = energyConsumptionRow[row];
  }
}

However, I receive the following error:
Line 104: Element "IloOplExcelDataSource" does not exist in OPL model.

Is there a way to solve that issue. I am using IBM ILOG CPLEX Optimization Studio Version: 22.1.0.0.

Best regards,

------------------------------
Mohamed Awad
------------------------------

Hi,

Let me try to address your 2 questions:

1) How to call java from OPL ?

IloOplCallJava

/*
IloOplCallJava : This function calls a Java method from a scripting block
*/

execute
{
  
var convert180toRadian = IloOplCallJava("java.lang.Math","toRadians", "", 180);
writeln("180 degrees in radian : ",convert180toRadian);
			    
}	

/*
180 degrees in radian : 3.141592654
*/	

execute
{
var title="title";
var msg="hello";  

IloOplCallJava("javax.swing.JOptionPane", "showMessageDialog",
"(Ljava/awt/Component;Ljava/lang/Object;Ljava/lang/String;I)V", 
null, msg, title, 1);	 
}  

execute{ 
writeln("5 random numbers");
var rnd = IloOplCallJava("java.util.Random", "<init>", "()");
rnd.setSeed(1);	
for(var i=1;i<=5;i++)
{			    			  
 var t = rnd.nextDouble();
 writeln(t);
}
writeln("Gaussian");
for(var i=1;i<=5;i++)
{			    			  
 var t = rnd.nextGaussian();
 writeln(t);
}
}

2) Read an excel file in OPL : Use SheetRead

https://github.com/AlexFleischerParis/oplexcel/blob/main/read1cell.mod

int value=...; 

execute
{
  writeln("value=",value);
}

subject to
{
  
}

int value2=value+1;

/*
which gives
value=4
*/

https://github.com/AlexFleischerParis/oplexcel/blob/main/read1cell.dat

SheetConnection s("read1cell.xlsx");

value from SheetRead(s,"B1");

value2 to SheetWrite(s,"B5");

Hi Alex,

Thank you for you reply. It will help to achieve what I am looking for. I have a question on:

var rnd = IloOplCallJava("java.util.Random", "<init>", "()");

 
How can I use IloOplCallJava to generate random integer number between upper and lower limits. I tried the following but it didn't work: 

min + rnd.nextInt(max - min + 1);

Could you please suggest a way to do that.

Best regards,
Mohamed 

 

/*IloOplCallJava : This function calls a Java method from a scripting block*/execute{  var convert180toRadian = IloOplCallJava("java.lang.Math","toRadians", "", 180);writeln("180 degrees in radian : ",convert180toRadian);			    }	/*180 degrees in radian : 3.141592654*/	execute{var title="title";var msg="hello";  IloOplCallJava("javax.swing.JOptionPane", "showMessageDialog","(Ljava/awt/Component;Ljava/lang/Object;Ljava/lang/String;I)V", null, msg, title, 1);	 }  execute{ writeln("5 random numbers");var rnd = IloOplCallJava("java.util.Random", "<init>", "()");rnd.setSeed(1);	for(var i=1;i<=5;i++){			    			   var t = rnd.nextDouble(); writeln(t);}writeln("Gaussian");for(var i=1;i<=5;i++){			    			   var t = rnd.nextGaussian(); writeln(t);}}

int value=...; execute{  writeln("value=",value);}subject to{  }int value2=value+1;/*which givesvalue=4*/

SheetConnection s("read1cell.xlsx");value from SheetRead(s,"B1");value2 to SheetWrite(s,"B5");

Hi,

many How to With OPL.

To generate random numbers, no need to use java call from OPL, you can use rand:

int m=10;
int M=15;

int sampleSize=20;

int sample[i in 1..sampleSize]=rand(M+1-m)+m;

execute
{
  writeln(sample);
}

which gives

[11 11 11 10 12 15 13 15 13 10 15 11 15 10 11 14 12 14 10 15]

var rnd = IloOplCallJava("java.util.Random", "<init>", "()");

min + rnd.nextInt(max - min + 1);

/*IloOplCallJava : This function calls a Java method from a scripting block*/execute{  var convert180toRadian = IloOplCallJava("java.lang.Math","toRadians", "", 180);writeln("180 degrees in radian : ",convert180toRadian);			    }	/*180 degrees in radian : 3.141592654*/	execute{var title="title";var msg="hello";  IloOplCallJava("javax.swing.JOptionPane", "showMessageDialog","(Ljava/awt/Component;Ljava/lang/Object;Ljava/lang/String;I)V", null, msg, title, 1);	 }  execute{ writeln("5 random numbers");var rnd = IloOplCallJava("java.util.Random", "<init>", "()");rnd.setSeed(1);	for(var i=1;i<=5;i++){			    			   var t = rnd.nextDouble(); writeln(t);}writeln("Gaussian");for(var i=1;i<=5;i++){			    			   var t = rnd.nextGaussian(); writeln(t);}}

int value=...; execute{  writeln("value=",value);}subject to{  }int value2=value+1;/*which givesvalue=4*/

SheetConnection s("read1cell.xlsx");value from SheetRead(s,"B1");value2 to SheetWrite(s,"B5");