Hi,
sometimes spreadsheets are too big and we get
Exception from IBM ILOG Concert: Can not read data from excel
and my advice in the past has been to split. Let me be a bit more explicit.
If on my machine I try to scale the example
How to use SheetWrite / SheetRead with R1C1 format ?
https://www.ibm.com/developerworks/community/forums/html/topic?id=c2469c56-db27-4816-9cf2-f596513ce555&ps=25
to n=10000
I get the error I mentioned.
So what works instead for the write part:
.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=10000;
int cell[i in 1..n][j in 1..n]=i*j;
int cell1[i in 1..n div 2][j in 1..n div 2]=cell[i][j];
int cell2[i in 1..n div 2][j in n div 2+1..n]=cell[i][j];
int cell3[i in n div 2+1..n][j in 1..n div 2]=cell[i][j];
int cell4[i in n div 2+1..n][j in n div 2+1 ..n]=cell[i][j];
string sheetWriteString1;
string sheetWriteString2;
string sheetWriteString3;
string sheetWriteString4;
execute
{
sheetWriteString1=convertR1C1toA1(1,1)+":"+convertR1C1toA1(n/2,n/2);
writeln("sheetWriteString1=",sheetWriteString1);
sheetWriteString2=convertR1C1toA1(1,n/2+1)+":"+convertR1C1toA1(n/2,n);
writeln("sheetWriteString2=",sheetWriteString2);
sheetWriteString3=convertR1C1toA1(n/2+1,1)+":"+convertR1C1toA1(n,n/2);
writeln("sheetWriteString3=",sheetWriteString3);
sheetWriteString4=convertR1C1toA1(n/2+1,n/2+1)+":"+convertR1C1toA1(n,n);
writeln("sheetWriteString4=",sheetWriteString4);
}
.dat
SheetConnection s("f2.xlsx");
cell1 to SheetWrite(s,sheetWriteString1);
cell2 to SheetWrite(s,sheetWriteString2);
cell3 to SheetWrite(s,sheetWriteString3);
cell4 to SheetWrite(s,sheetWriteString4);
and then for the read part
.mod
int n=...;
string sheetWriteString1=...;
string sheetWriteString2=...;
string sheetWriteString3=...;
string sheetWriteString4=...;
int cell1[i in 1..n div 2][j in 1..n div 2]=...;
int cell2[i in 1..n div 2][j in n div 2+1..n]=...;
int cell3[i in n div 2+1..n][j in 1..n div 2]=...;
int cell4[i in n div 2+1..n][j in n div 2+1..n]=...;
int cell[i in 1..n][j in 1..n]=
(i<=n div 2)?((j<=n div 2)?cell1[i][j]:cell2[i][j]):((j<=n div 2)?cell3[i][j]:cell4[i][j]);
assert forall(i,j in 1..n) cell[i][j]==i*j;
.dat
SheetConnection s("f2.xlsx");
n=10000;
sheetWriteString1="A1:GJH5000";
sheetWriteString2="GJI1:NTP5000";
sheetWriteString3="A5001:GJH10000";
sheetWriteString4="GJI5001:NTP10000";
cell1 from SheetRead(s,sheetWriteString1);
cell2 from SheetRead(s,sheetWriteString2);
cell3 from SheetRead(s,sheetWriteString3);
cell4 from SheetRead(s,sheetWriteString4);
and then it works fine
regards
#DecisionOptimization#OPLusingCPLEXOptimizer