Pinned topic write multidimensional array into excel
I have a problem in writing 3 dimensional array to an excel file.
I wrote the following code:
t to SheetWrite (sheet, "Sheet1!B5:D90");
but I got this exception:
Exception from ILOG Concert: excel: map has more than 2 dimensions
could someone help me with this
thanks a lot
HAIL

Re: write multidimensional array into excel
20100309T08:34:25ZThis is the accepted answer. This is the accepted answer. Brummel
 20100308T08:46:39Z
Hello everybody,
I have the same problem.
Is it possible at all to write 3 dimensional arrays back to Excel?
Thanks in advance,
Sven
Excel is not in 3D, isn't it ?
Well, what do you expect saving a collection of 3 dimensional tuples ?
What you can do instead is saving this collection in a flat way to excel , with 3columns, and then whatever you want in excel (Pivot table for instance)
To do so, the only thing you must take care is the number of records in your collection , since you would have to compute the name of the range according to this size. This can be done easily with OPL too (for instance in a script).
Hope this helps
David Gravot
ROSTUDEL  Operations Research
dgravot@rostudel.com
www.rostudel.com 
Re: write multidimensional array into excel
20100317T12:35:41ZThis is the accepted answer. This is the accepted answer. davidoff
 20100309T08:34:25Z
Hi
Excel is not in 3D, isn't it ?
Well, what do you expect saving a collection of 3 dimensional tuples ?
What you can do instead is saving this collection in a flat way to excel , with 3columns, and then whatever you want in excel (Pivot table for instance)
To do so, the only thing you must take care is the number of records in your collection , since you would have to compute the name of the range according to this size. This can be done easily with OPL too (for instance in a script).
Hope this helps
David Gravot
ROSTUDEL  Operations Research
dgravot@rostudel.com
www.rostudel.com 
Re: write multidimensional array into excel
20100319T08:32:08ZThis is the accepted answer. This is the accepted answer. Hamed Nikhalat
 20100317T12:35:41Z
What does it mean to save the array in Excel in a flat way?
You have an array
int someArray[A][B][C];
and transfer it so a tupleset like
tuple someTuple{
int a;
int b;
int c
int value;
}
{someTuple} someSet = {<i,j,k,someArray[i][j][k]>  i in A, j in B, k in C};
(or alike, I have not checked for correct syntax)
and store this set in Excel.
Hope this helps.
Best regards
Norbert 
Re: write multidimensional array into excel
20120323T05:05:16ZThis is the accepted answer. This is the accepted answer. SystemAdmin
 20100319T08:32:08Z
I suspect what Davidoff meant is the following:
You have an array
int someArray[A][B][C];
and transfer it so a tupleset like
tuple someTuple{
int a;
int b;
int c
int value;
}
{someTuple} someSet = {<i,j,k,someArray[i][j][k]>  i in A, j in B, k in C};
(or alike, I have not checked for correct syntax)
and store this set in Excel.
Hope this helps.
Best regards
Norbert
Anybody have any ideas? 
Re: write multidimensional array into excel
20120323T15:41:36ZThis is the accepted answer. This is the accepted answer. msciaroni
 20120323T05:05:16Z
I am having the same trouble. In my case, I am trying to write out a four dimensional decision variable array, ideally to excel or a delimited format like CSV (or even a delimited text file). I tried the tuple trick, but got an error stating: Cannot use type {<int,int,int,int,dvar float+>} for {someTuple}
Anybody have any ideas? 
Re: write multidimensional array into excel
20121119T20:11:01ZThis is the accepted answer. This is the accepted answer. SystemAdmin
 20100319T08:32:08Z
I suspect what Davidoff meant is the following:
You have an array
int someArray[A][B][C];
and transfer it so a tupleset like
tuple someTuple{
int a;
int b;
int c
int value;
}
{someTuple} someSet = {<i,j,k,someArray[i][j][k]>  i in A, j in B, k in C};
(or alike, I have not checked for correct syntax)
and store this set in Excel.
Hope this helps.
Best regards
Norbert
your explanation regarding the creation of a tuple in order to store the data in Excel was quite helpful for me so far since I have the same problem with multidimensional decision variables.
Could you please also add an example on how the command line would be in the dat.file in order to write the data into excel?
Thanks and kind regards,
Till 
Re: write multidimensional array into excel
20130524T17:19:33ZThis is the accepted answer. This is the accepted answer.Same problem.
I'd like to save the values in the same way than they are saved in the solutions. One column with parameter 1, second with parameter 2, third with parameter 3, and fourth with the value. Is is possible to say it to CPLEX ?
I did'nt find anything on the doc about it.

Re: write multidimensional array into excel
20130525T19:57:40ZThis is the accepted answer. This is the accepted answer. 5NK7_Thomas_Cibils
 20130524T17:19:33Z
Same problem.
I'd like to save the values in the same way than they are saved in the solutions. One column with parameter 1, second with parameter 2, third with parameter 3, and fourth with the value. Is is possible to say it to CPLEX ?
I did'nt find anything on the doc about it.
Hi,
have you tried what Norbert suggested in this thread ?
int someArray[A][B][C];
and transfer it so a tupleset like
tuple someTuple{
int a;
int b;
int c
int value;
}
{someTuple} someSet = {<i,j,k,someArray[i][j][k]>  i in A, j in B, k in C};
(or alike, I have not checked for correct syntax)
and store this set in Excel.regards

Re: write multidimensional array into excel
20130718T16:06:06ZThis is the accepted answer. This is the accepted answer.Hi friends,
I am a beginner with CPLEX studio
I have the same problem with a 4dimensional array. I applied the proposed solution in the following way but it did not worked. Could you please guide me more?
dvar boolean someArray[1..1200][1..15][1..2][1..3];
tuple someTuple{
int a;
int b;
int c;
int d;
int value;
}
{someTuple} someSet = {<i,j,t,s,someArray[i][j][t][s]>  i in 1..1200, j in 1 .. 15, t in 1..2, s in 1..3 };
Erros:
Decision variable (or expression) "someArray" not allowed.Impossible to load model

Re: write multidimensional array into excel
20130719T18:34:43ZThis is the accepted answer. This is the accepted answer. sarah2121
 20130718T16:06:06Z
Hi friends,
I am a beginner with CPLEX studio
I have the same problem with a 4dimensional array. I applied the proposed solution in the following way but it did not worked. Could you please guide me more?
dvar boolean someArray[1..1200][1..15][1..2][1..3];
tuple someTuple{
int a;
int b;
int c;
int d;
int value;
}
{someTuple} someSet = {<i,j,t,s,someArray[i][j][t][s]>  i in 1..1200, j in 1 .. 15, t in 1..2, s in 1..3 };
Erros:
Decision variable (or expression) "someArray" not allowed.Impossible to load model
Hi,
are you trying to write into an excel sheet or read from it ?
This trick was good to write into excel and
{someTuple} someSet = {<i,j,t,s,someArray[i][j][t][s]>  i in 1..1200, j in 1 .. 15, t in 1..2, s in 1..3 };
was after the subject to block
regards

Re: write multidimensional array into excel
20130820T20:46:46ZThis is the accepted answer. This is the accepted answer.Hello everybody,
I wrote a script few weeks ago. It might be useful for you.
Assuming you have the following structure in your model:
tuple some {
int job;
int operation;
int machine;
...
}
{some} this = ...;
...
dvar interval task[t in this] optional;
The script allows writing a n dimensional array to an excel file or just save data that you are interested to.
You must add something like this into your model:
tuple post{
key int orderId;
int stage;
...
}
{post} result;
This tuple will contain all data you want to save.
After creating that, you must add something like this into the main block of your model:
main {
thisOplModel.generate();
var model = thisOplModel;
cp.startNewSearch();
if (cp.solve()) {
for (var AN in model.THIS) {
if (model.TASK[AN].present){ //If task is optional
*model.result.add(AN.job,AN. operation, ... ,model.TASK[AN].size,model.TASK[AN].start,model.TASK[AN].end);
};
}
;
} else {
writeln("No solution");};
thisOplModel.postProcess();
}
Modify tuple "post" and line "model.result.add"..
Best wishes,
Mauricio. 
Re: write multidimensional array into excel
20160614T14:27:18ZThis is the accepted answer. This is the accepted answer. SystemAdmin
 20100319T08:32:08Z
I suspect what Davidoff meant is the following:
You have an array
int someArray[A][B][C];
and transfer it so a tupleset like
tuple someTuple{
int a;
int b;
int c
int value;
}
{someTuple} someSet = {<i,j,k,someArray[i][j][k]>  i in A, j in B, k in C};
(or alike, I have not checked for correct syntax)
and store this set in Excel.
Hope this helps.
Best regards
NorbertHi,
I'm new to CPLEX. Your instruction is very helpful to many of my problems, but in 1 of them, it doesn't work. This is my case:
...
dvar boolean x[node,node,vehicle];
...
tuple xTuple {
int node;
int vehicle;
int value;
}
{xTuple}xSet={<i,j,k,x[i,j,k]> i in node,j in node,k in vehicle};The error is "Cannot use type {<int,int,int,dvar boolean>} for {xTuple}.". I think the problem is i and j are in the same range, but it has to be like that. I don't know how to solve this problem, please help. I attached my mod file for more details.
Thank you.
Attachments

 truck.mod
 2 KB

Re: write multidimensional array into excel
20160614T14:54:56ZThis is the accepted answer. This is the accepted answer. NRouge
 20160614T14:27:18Z
Hi,
I'm new to CPLEX. Your instruction is very helpful to many of my problems, but in 1 of them, it doesn't work. This is my case:
...
dvar boolean x[node,node,vehicle];
...
tuple xTuple {
int node;
int vehicle;
int value;
}
{xTuple}xSet={<i,j,k,x[i,j,k]> i in node,j in node,k in vehicle};The error is "Cannot use type {<int,int,int,dvar boolean>} for {xTuple}.". I think the problem is i and j are in the same range, but it has to be like that. I don't know how to solve this problem, please help. I attached my mod file for more details.
Thank you.
Hi,
the tuple you created just has 3 integers (3 dimensional) but you need 4 integers in the tuple because it is 4dimensional.
Try this one:
tuple xTuple {
int node1;int node2;
int vehicle;
int value;
}
{xTuple}xSet={<i,j,k,x[i,j,k]> i in node,j in node,k in vehicle};This should work.

Re: write multidimensional array into excel
20160614T16:12:17ZThis is the accepted answer. This is the accepted answer. Moit1910
 20160614T14:54:56Z
Hi,
the tuple you created just has 3 integers (3 dimensional) but you need 4 integers in the tuple because it is 4dimensional.
Try this one:
tuple xTuple {
int node1;int node2;
int vehicle;
int value;
}
{xTuple}xSet={<i,j,k,x[i,j,k]> i in node,j in node,k in vehicle};This should work.
Hi,
It worked. Many thanks for your quick reply.

Re: write multidimensional array into excel
20161129T16:13:14ZThis is the accepted answer. This is the accepted answer.i didnt get in this thread where is the command that i'll write in cplex, so as to display the output into excel for all the decion variables, those which are multidimensional and others which are not!

Re: write multidimensional array into excel
20161130T09:31:47ZThis is the accepted answer. This is the accepted answer. khadeejah
 20161129T16:13:14Z
i didnt get in this thread where is the command that i'll write in cplex, so as to display the output into excel for all the decion variables, those which are multidimensional and others which are not!
Hi,
let me give a complete example:
.mod
range A=1..2;
range B=1..3;
range C=1..4;
dvar int X[A][B][C];subject to
{
forall(a in A,b in B,c in C) X[a][b][c]==a*b*c;
}tuple someTuple{
int a;
int b;
int c;
int value;
};
{someTuple} someSet = {<i,j,k,X[i][j][k]>  i in A, j in B, k in C};.dat
SheetConnection sheet("excel.xlsx");
someSet to SheetWrite(sheet,"A1:D24");
regards

Re: write multidimensional array into excel
20161202T09:32:07ZThis is the accepted answer. This is the accepted answer. AlexFleischer
 20161130T09:31:47Z
Hi,
let me give a complete example:
.mod
range A=1..2;
range B=1..3;
range C=1..4;
dvar int X[A][B][C];subject to
{
forall(a in A,b in B,c in C) X[a][b][c]==a*b*c;
}tuple someTuple{
int a;
int b;
int c;
int value;
};
{someTuple} someSet = {<i,j,k,X[i][j][k]>  i in A, j in B, k in C};.dat
SheetConnection sheet("excel.xlsx");
someSet to SheetWrite(sheet,"A1:D24");
regards
Hi Alex,
instead of A, B,C if I have :
tuple a{
int ID;
int number;
int sum;
}
{a}A=...;
tuple b{
int ID;
int Time;
}
{b}B=...;
dvar boolean X[A] [A] [B];
How will this be done?
Regards,
sha
Updated on 20161202T11:03:35Z at 20161202T11:03:35Z by ShaCplex 
Re: write multidimensional array into excel
20161202T12:28:44ZThis is the accepted answer. This is the accepted answer. ShaCplex
 20161202T09:32:07Z
Hi Alex,
instead of A, B,C if I have :
tuple a{
int ID;
int number;
int sum;
}
{a}A=...;
tuple b{
int ID;
int Time;
}
{b}B=...;
dvar boolean X[A] [A] [B];
How will this be done?
Regards,
sha
hi
see
regards

Re: write multidimensional array into excel
20161202T14:46:57ZThis is the accepted answer. This is the accepted answer. AlexFleischer
 20161202T12:28:44Z
hi
see
regards
Hi Alex,
But here is my code which is not working:
.mod file
tuple TRIPS{
int tripID;
int depT;
int arrT;
};
{TRIPS} trips={<1,1,1>,<2,2,2>};tuple AIRCRAFTS{
int aircraftID;
int MGTime;
}
{AIRCRAFTS} aircrafts={<1,1>,<2,2>,<3,3>};dvar boolean X [trips][trips][aircrafts];
float cost [trips][trips][aircrafts]=...;dexpr float TotalCost=sum(t1 in trips,t2 in trips, a in aircrafts) cost[t1][t2][a] * X [t1][t2][a];
/*subject to
{
forall(a1 in trips,a2 in trips, b in aircrafts) X[a1][a2][b]==a1.number*a2.number*b.Time mod 2;
}
*/tuple someTuple{
int a;
int b;
int c;
float cost;
};{someTuple} someSet = {<i.tripID,i2.tripID,j.aircraftID,cost[i][i2][j]>  i in trips, i2 in trips, j in aircrafts};
.dat
SheetConnection sheet("excel.xlsx");
cost from SheetRead(sheet,"H1:H8")
someSet to SheetWrite(sheet,"J1:K8");It fails with the following error:
Array data element "cost" of type float with 3 dimensions not supported for sheets.
Kindly assist...
I want to provide the cost explicitly. there is no formula..
Updated on 20161202T14:48:06Z at 20161202T14:48:06Z by ShaCplex 
Re: write multidimensional array into excel
20161202T14:58:45ZThis is the accepted answer. This is the accepted answer. ShaCplex
 20161202T14:46:57Z
Hi Alex,
But here is my code which is not working:
.mod file
tuple TRIPS{
int tripID;
int depT;
int arrT;
};
{TRIPS} trips={<1,1,1>,<2,2,2>};tuple AIRCRAFTS{
int aircraftID;
int MGTime;
}
{AIRCRAFTS} aircrafts={<1,1>,<2,2>,<3,3>};dvar boolean X [trips][trips][aircrafts];
float cost [trips][trips][aircrafts]=...;dexpr float TotalCost=sum(t1 in trips,t2 in trips, a in aircrafts) cost[t1][t2][a] * X [t1][t2][a];
/*subject to
{
forall(a1 in trips,a2 in trips, b in aircrafts) X[a1][a2][b]==a1.number*a2.number*b.Time mod 2;
}
*/tuple someTuple{
int a;
int b;
int c;
float cost;
};{someTuple} someSet = {<i.tripID,i2.tripID,j.aircraftID,cost[i][i2][j]>  i in trips, i2 in trips, j in aircrafts};
.dat
SheetConnection sheet("excel.xlsx");
cost from SheetRead(sheet,"H1:H8")
someSet to SheetWrite(sheet,"J1:K8");It fails with the following error:
Array data element "cost" of type float with 3 dimensions not supported for sheets.
Kindly assist...
I want to provide the cost explicitly. there is no formula..
Hi,
this is not any more about writing to excel but reading.
You should have a look at http://www01.ibm.com/support/docview.wss?uid=swg21401340
regards

Re: write multidimensional array into excel
20170305T15:38:49ZThis is the accepted answer. This is the accepted answer. AlexFleischer
 20161130T09:31:47Z
Hi,
let me give a complete example:
.mod
range A=1..2;
range B=1..3;
range C=1..4;
dvar int X[A][B][C];subject to
{
forall(a in A,b in B,c in C) X[a][b][c]==a*b*c;
}tuple someTuple{
int a;
int b;
int c;
int value;
};
{someTuple} someSet = {<i,j,k,X[i][j][k]>  i in A, j in B, k in C};.dat
SheetConnection sheet("excel.xlsx");
someSet to SheetWrite(sheet,"A1:D24");
regards
Hi Alex,
I have tried using your structure in my CPLEX but it keeps on giving the same error: range size is not wide enough for set of tuples: missing rows.
Also I do not understand why you define the fourth integer `value´ inside the tuple.
I have the following code added:
mod.
range I = 1..5;
range T = 1..6; //number of months > note that the decision variable whose values I want to write to Excel goes from 0 to 6, not 1 to 6.
range S = 1..5;
dvar int+ IG[I][0..6][S];
tuple Tuple{
int i;
int t;
int s;
int value;
};{Tuple} Set = {<i,j,k,IG[i][j][k]>  i in I, j in 0..6, k in S};
dat.
Set to SheetWrite(sheetOutput,"IG!ig");
PS, can I just duplicate this code if I want to repeat this procedure for a second decision variable?
Looking forward to hearing back from you soon. I appreciate your help.
Kindest regards
Pieter
Updated on 20170305T15:39:52Z at 20170305T15:39:52Z by pieterce 
Re: write multidimensional array into excel
20170305T15:49:44ZThis is the accepted answer. This is the accepted answer. pieterce
 20170305T15:38:49Z
Hi Alex,
I have tried using your structure in my CPLEX but it keeps on giving the same error: range size is not wide enough for set of tuples: missing rows.
Also I do not understand why you define the fourth integer `value´ inside the tuple.
I have the following code added:
mod.
range I = 1..5;
range T = 1..6; //number of months > note that the decision variable whose values I want to write to Excel goes from 0 to 6, not 1 to 6.
range S = 1..5;
dvar int+ IG[I][0..6][S];
tuple Tuple{
int i;
int t;
int s;
int value;
};{Tuple} Set = {<i,j,k,IG[i][j][k]>  i in I, j in 0..6, k in S};
dat.
Set to SheetWrite(sheetOutput,"IG!ig");
PS, can I just duplicate this code if I want to repeat this procedure for a second decision variable?
Looking forward to hearing back from you soon. I appreciate your help.
Kindest regards
Pieter
Hi,
.mod
range I = 1..5;
range T = 1..6; //number of months > note that my decision variable goes from 0 to 6, not 1 to 6.
range S = 1..5;
dvar int+ IG[I][0..6][S];
subject to
{}
tuple Tuple{
int i;
int t;
int s;
int value;
};{Tuple} Set = {<i,j,k,IG[i][j][k]>  i in I, j in 0..6, k in S};
.dat
SheetConnection sheetOutput("f.xlsx");
Set to SheetWrite(sheetOutput,"A1:D175");will work
regards