How to SheetRead in Cplex with variable column length?

Question

In my .dat file, I want to fill an array with values of a column from an Excel Sheet. Depending on the problem, the number of values are different, that means there might be values from A1:A10 or from A1:A20. The number is specified in another Excel cell and is also used as a variable in the model and therefore also read. How can I now Sheetread the array with its correct size? Maybe string concatonation would work. I search for something like:

numberRows from SheetRead(DataSource, "Data!B1");
input = "Data!A1:A" + numberRows;
variable from SheetRead(DataSource, input);

Is something similar possible in CPLEX?

Answer 1

Example in OPL EXCEL : https://github.com/AlexFleischerParis/oplexcel/blob/main/mainvariablesheetreadstring.mod

The main idea is to rely on a main in order to update the strings that will be used in SheetRead.

main
{
  var source = new IloOplModelSource("variablesheetreadstring.mod");
  var cplex = new IloCplex();
  var def = new IloOplModelDefinition(source);
  var opl = new IloOplModel(def,cplex);
  var data1=new IloOplDataElements();
  
  data1.paramread="params!A2";
  data1.parambuses="buses!A2:B3";
  data1.paramwrite="buses!E2:F3";
  
  opl.addDataSource(data1);
  
  var data2 = new IloOplDataSource("variablesheetreadstring.dat");
  opl.addDataSource(data2);
  opl.generate();
  if (cplex.solve()) {
     writeln("OBJ = " + cplex.getObjValue());
     opl.postProcess();

  } else {
     writeln("No solution");
  }
  

  opl.end();
  
  
}

Or you may rely on a less subtle idea : read all cells and then parse.

Parse all strings from a spreadsheet