ILOG CPLEX / OPL dynamic Excel sheet referencing

Question

I'm trying to dynamically reference Excel sheets or tables within the .dat for a Mixed Integer Problem in Vehicle Routing that I'm trying to solve in CPLEX (OPL).

The setup is a: .mod = model, .dat = data and a MS Excel spreadsheet

I have a 2 dimensional array with customer demand data = Excel range (for coding convenience I did not format the excel data as a table yet)

The decision variable in .mod looks like this:

dvar boolean x[vertices][vertices][scenarios]

in .dat:

vertices from SheetRead (data, "Table!vertices"); and

scenarios from SheetRead (data, "dont know how to yet"); this might not be needed

without the scenario Index everything is fine. But as the demand for the customers changes in this model I'd like to include this via changing the data base reference. Now what I'd like to do is one of 2 things:

Either: Change the spreadsheet in Excel so that depending on the scenario I get something like that in .dat:

scenario = 1:

vertices from SheetRead (data, "table-scenario-1!vertices");

scenario = 2:

vertices from SheetRead (data, "table-scenario-2!vertices");

so changing the spreadsheet for new base data, or: Change the range within the same spreadsheet:

scenario = 1:

vertices from SheetRead (data, "table!vertices-1");

scenario = 2:

vertices from SheetRead (data, "table!vertices-2");

either way would be fine.

Knowing how 3D Tables in Excel are created using multiple spreadsheets with 2D Tables grouped, the more natural approach seems to be, to have vertices always reference the same range in every Excel spreadsheet while depending on the scenario the spreadsheet/page is switched, but I just don't know how to.

Thanks for the advice.

Would it be an option to just read *all* the data and then pick the appropriate data in the .mod file? For example, you could add an additional scenario index to everything. Then in the `.mod` file you can choose what scenario you want to work with by fixing the scenario index. — Daniel Junglas, Nov 27 '19 at 18:15
How do I do this i CPLEX? Wouldn't it still be needed to then group the larger Excel range into scenario subgroups? Or do i have to change the used data in .mod in a preprocessing block? — anonymthe, Nov 28 '19 at 13:21
Yes, you would still have to group data by scenario. The problem is that the arguments to `SheetConnection` and `SheetRead` are not dynamic. So you have to move the "dynamism" some place else. With my suggestion you would always read the saeme (hardcoded) Excel tables but would dynamically filter in the .mod file. There are different ways to do this filtering. One is to use pre-processing, another one is to have a parameter in the .mod that specifies the current scenario to use. — Daniel Junglas, Dec 09 '19 at 08:01
Hm ok, as of now i read all data from the sheet. My problem now is that while it does sort the scenario demands in the end it while take a route across scenarios so for example start from depot 0 to customer 3in scenario 1 and then from customer 3 to 2 in scenario 2 and so on. Which of your suggestions would be more likely and easy to do to yield feasable solutions. The point is I'm trying to solve the TWAVRP (Time Window assignment vehicle routing problem) where TIme windows are assigned prior to demand being known. It then solves over all possible d scenarios and finds the optimal. — anonymthe, Dec 09 '19 at 08:57
If you have tours that cross scenarios then you are probably missing some constraints. In your case, I would expect that basically each constraint definition starts with something like `forall (s in Scenarios)` and then you have the per-scenario constraint. If you can show the full .mod, .dat, and Excel files then we might be able to provide more debugging help. — Daniel Junglas, Dec 09 '19 at 11:43
That was the right hint right there, thanks alot! I did not forget the `forall (s in scenarios)` but I made the mistake adding one `sum(s in scenarios)` to this constraint. This was so off my possible error set that I must've overloked it a dozen times. Thanks for the help. Nevertheless also helpful to have learned that a `SheetConnection` cannot be dynamic. — anonymthe, Dec 09 '19 at 21:04

score 1 · Accepted Answer · answered Dec 10 '19 at 12:52

Unfortunately, the arguments to SheetConnection must be a string literal or an Id (see the OPL grammar in the user manual here). And similarly for SheetRead. This means, you cannot have dynamic sources for a sheet connection.

As we discussed in the comments, one option is to add an additional index to all data: the scenario. Then always read the data for all scenarios and in the .mod file select what you want to actually use.

score 1 · Answer 2 · answered Dec 10 '19 at 18:05

at https://www.ibm.com/developerworks/community/forums/html/topic?id=5af4d332-2a97-4250-bc06-76595eef1ab0&ps=25 I shared an example where you can set a dynamic name for the Excel file. The same way you could have a dynamic range, the trick is to use flow control.

sub.mod

float maxOfx = 2;
string fileName=...;
dvar float x;

maximize x;
subject to {
  x<=maxOfx;
}

execute
{
 writeln("filename= ",fileName);
}

and then the main model is

main {
  var source = new IloOplModelSource("sub.mod");
  var cplex = new IloCplex();
  var def = new IloOplModelDefinition(source);
  var opl = new IloOplModel(def,cplex);


  for(var k=11;k<=20;k++)
  {
  var opl = new IloOplModel(def,cplex);

  var data2= new IloOplDataElements();
  data2.fileName="file"+k;
  opl.addDataSource(data2);
  opl.generate();

  if (cplex.solve()) {
     writeln("OBJ = " + cplex.getObjValue());
  } else {
     writeln("No solution");
  }
  opl.postProcess();
 opl.end();


}  

}

ILOG CPLEX / OPL dynamic Excel sheet referencing

2 Answers2

Linked