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.