Hi,

 

R1C1 format is not allowed in OPL for SheetRead and SheetWrite

In the documentation we can read

OPL does not support the R1C1 reference style to specify the range when reading / writing data to an Excel spreadsheet.

But many people asked me how to do that, as if there was a way!

 

That's possible through scripting and once again let us use the fact that OPL scripting relies on Javascript which is quite popular.

 

Let me give you first an example for SheetWrite:

 

.mod

execute
{

// http://cwestblog.com/2013/09/05/javascript-snippet-convert-number-to-column-name/
function toColumnName(num) {
  for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
    ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
  }
  return ret;
}

// 1,1 => A1  1,4 => D1 2,27 => AA2
function convertR1C1toA1(r,c)
{
return(toColumnName(c)+r);
}

}

int n=1000;

int cell[i in 1..n][j in 1..n]=i*j;

string sheetWriteString;

execute
{
sheetWriteString=convertR1C1toA1(1,1)+":"+convertR1C1toA1(n,n);
writeln("sheetWriteString=",sheetWriteString);
}

.dat

SheetConnection s("f.xlsx");

cell to SheetWrite(s,sheetWriteString);

And then the SheetRead

.mod

int n=...;
string sheetWriteString=...;

int cell[i in 1..n][j in 1..n]=...;

assert forall(i,j in 1..n) cell[i][j]==i*j;

.dat

SheetConnection s("f.xlsx");

n=1000;
sheetWriteString="A1:ALL1000";

cell from SheetRead(s,sheetWriteString);

regards

 

PS:

Many other tricks at https://www.linkedin.com/pulse/how-opl-alex-fleischer/

 

#DecisionOptimization
#OPLusingCPLEXOptimizer