Originally posted by: hail lab

Hi,

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
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Brummel

Hello everybody,

I have the same problem.

Is it possible at all to write 3 dimensional arrays back to Excel?

Thanks in advance,

Sven
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: davidoff

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 3-columns, 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
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Hamed Nikhalat

What does it mean to save the array in Excel in a flat way?
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

I suspect what Davidoff meant is the following:
You have an array
int someArray[A][B][C];

and transfer it so a tuple-set 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
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: msciaroni

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?
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

Could you detail how you declared your tuple and initialized your set?
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

Hello 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
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: NRouge

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.

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Moit1910

Hi,

the tuple you created just has 3 integers (3 dimensional) but you need 4 integers in the tuple because it is 4-dimensional.

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.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: NRouge

Hi,

It worked. Many thanks for your quick reply.
 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CplexCui

I have the same problem, even 5 to 7 index for parameters and variables. Anyone can gave the OPL description on reading and writing them from and to Excel files? Thanks!
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: 5NK7_Thomas_Cibils

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.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

 

have you tried what Norbert suggested in this thread ?

 

int someArray[A][B][C];

and transfer it so a tuple-set 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

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Kamran_Sr

Hi Alex,

 

I am trying to write the solution results to excel using the trick you suggest here, in the main control flow for an iterative run:

 

in .mod:

dvar float+ Wojts [port][customers][periods];

and after subject to block:

tuple someTuple{

float a;

float b;

float c;

float value;

}

{someTuple} someSet = {<i,j,k,Wojts[i][j][k]> | i in port, j in customers, k in periods};

in the main I use the following to write the results to excel:

 

main { 

for ( var n = 1; n <=7; n++ ) {

var source = new IloOplModelSource("French.mod");

var cplex = new IloCplex();

var def = new IloOplModelDefinition(source);

var model = new IloOplModel(def,cplex);

var writeSheetDataFile = "SheetWrite.dat";

var stringRef = ""+n;

 

var data3 = new IloOplDataElements();

data3.someSet = "AB"+stringRef+":AC"+stringRef+":AD"+stringRef+":AE"+stringRef;

model.addDataSource(data3);

 

and in the .dat :

Wojts to SheetWrite (sheet, someSet);

However, I have faced the error of "Element someSet not defined".

I would be appreciated if you could help me with this.

 

Regards,

Kamran

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

hi

if someSet is the tuple set you need to write then you should write

someSet to SheetWrite(sheet,connectionString)

regards

https://www.linkedin.com/pulse/how-opl-alex-fleischer/

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Kamran_Sr

Thanks for your reply, Alex.

 

I confronted the error of "excel: range size is not wide enough for set of tuples: missing rows". Since I run the model for several input data, I use control flow to write the output into excel for each iteration as follows:

 

for ( var n = 1; n <=7; n++ ) {

var source = new IloOplModelSource("French.mod");

var cplex = new IloCplex();

var def = new IloOplModelDefinition(source);

var model = new IloOplModel(def,cplex);

 

var writeSheetDataFile = "SheetWrite.dat";

var stringRef = ""+ n;

 

var data3 = new IloOplDataElements();

data3.Wstring = "AB"+stringRef+":AC"+stringRef+":AD"+stringRef+":AE"+stringRef;

model.addDataSource(data3);

 

and in .dat use:

someSet to SheetWrite (sheet, Wstring);

 

I don't know how to deal with this error as I think the string range I use for excel is fine.

 

I appreciate you in advance.

 Regards,

Kamran

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

I recommend the example at https://www.ibm.com/developerworks/community/forums/html/topic?id=22d39ee1-16ee-4d16-90ee-4b65fd4083e6&ps=25#repliesPg=0

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: sarah2121

Hi friends,

I am a beginner with CPLEX studio

I have the same problem with a 4-dimensional 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

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

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

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: MauricioSirolla

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.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: khadeejah

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!

#DecisionOptimization
#OPLusingCPLEXOptimizer

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

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: ShaCplex

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

#DecisionOptimization
#OPLusingCPLEXOptimizer

hi

see

https://www.ibm.com/developerworks/community/forums/html/topic?id=702698ee-74d3-436c-ac76-d689ff0bf06b&ps=25#repliesPg=0

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: ShaCplex

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.. 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

this is not any more about writing to excel but reading.

You should have a look at http://www-01.ibm.com/support/docview.wss?uid=swg21401340

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: pieterce

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

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

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

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: pieterce

Hi

 

Thank you for the very quick reply!

 

I understand how you get to that range (I just renamed it as ´q´ in my Excel sheet).

I do however still don't see why you define a 4th integer variable in your tuple when there are only 3 dimensions for our arrays.

 

Also, does it matter if my indices for my set are the same as the integers defined for the tuple (e.g. i)?

 

It still returns the same error;

 

mod.

 

tuple TupleIG{
    int i;
    int t;
    int s;
    int value;
    };

    {TupleIG} SetIG = {<i,j,k,IG[i][j][k]> | i in I, j in 0..6, k in S};

 

dat.

 

SheetConnection sheetIG ("C:/Users/Pieter/Desktop/CPLEX_OUTPUT.xlsx");

SetIG to SheetWrite(sheetIG,"A1:D175");

 

Thanks again for your help.

Best regards

Pieter

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

I do however still don't see why you define a 4th integer variable in your tuple when there are only 3 dimensions for our arrays.

==> Because you write 3 dimensions for the parameters and one additional column for the result.

Which error do you get ?

Can you attach all your files ?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: pieterce

Hi Alex

 

I still get the same error: Excel: range size is not wide enough for set of tuples: missing rows

 

All I want to do is write my 2 decision variables (IG and Q) to their respective sheet in my Excel file. IG ranges from 0-6 for the second array while Q ranges from 1-6.

The range should thus - I suppose - be: 4-175 for IG and 4-150 for Q, but that does not seem to work.

 

You can find my files attached.

 

Thanks for your help. I strongly appreciate it.

Regards

Pieter

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi

SetIG to SheetWrite(sheetIG,"A1:D875");

will help

regards

https://www.ibm.com/developerworks/community/forums/html/threadTopic?id=0d0b2396-3b48-4638-b032-3b9ea74f1a11&ps=25

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: pieterce

Hi Alex

 

Thanks for your help. I appreciate it!

 

Thanks again.

Regards

Pieter

#DecisionOptimization
#OPLusingCPLEXOptimizer

You re welcome.

You could also compute the range in the .mod:

.mod

string SheetWriteString="A1:D";

{TupleIG} SetIG = {<i,j,k,IG[i][j][k]> | i in I, j in 0..6, k in S};
    
    execute
    {
    SetIG;    
    }

execute
{
SheetWriteString+=SetIG.size;
}

.dat

 SetIG to SheetWrite(sheetIG,SheetWriteString);

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: pieterce

Hi Alex

 

Your solution worked just fine. I was wondering if there is a comparable code for setting the size of my Excel range with respect to a variable number of columns when the rows are fixed.

For instance: decision variable X[T][S] with T ( = number of rows) fixed at 6 and S ( = number of columns) variable depending on the size of the set S.

 

Kindest regards

Pieter

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

Hi Alex,

I have read your answers about the multidimensional data output to excel and tried all of them, but still the error occurs.

Could you please help me with me ? Thank you very much!!

.mod

range AllPoint=0..N;

int NK=...;           // number of vehicle types
range K=1..NK; 

dvar boolean x_ij[AllPoint][AllPoint][K];
minimize....

subject to {.......}

tuple xTuple{
int node1;
int node2;
int vehicles;
int value;
};

{xTuple} xSet={<i,j,k,x_ij[i][j][k]>|i in AllPoint, j in AllPoint, k in K};

==================

.dat

SheetConnection outputfile("network1.xlsx");

xSet to SheetWrite(outputfile, "xij");

 

========================================

And in the "netowork1.xlsx", there is only one sheet, and I named a big range as "xij".

The error  is something like : " The range is unknown." 

 

Could you help me? I look forward to your reply.  Thank you.

 

Regards,

Chole

 

 

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

I answered you at https://www.ibm.com/developerworks/community/forums/html/topic?id=22d39ee1-16ee-4d16-90ee-4b65fd4083e6&ps=25#repliesPg=0

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: joaopedrosantos

I have a question.

 

So I'm using this code

 tuple Tuple{
    int i;
    int j;
    int k;
    int value;
};

{Tuple} Set = {<i,j,k,x[i][j][k]> | i in turnos, j in turnos, k in grupos};

 

And I get a error saying decision variable x not allowed.

 

What am I doing wrong? X has 3 dimensions: x[a][b][c]

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: joaopedrosantos

Solved.

 

//Exportar resultados
tuple Tuple {
float t1;
float t2;
float t1day;
float t2day;
float k;
float value;
}

{Tuple} Set = {<i.turno,j.turno,i.day,j.day,k,x[i,j,k]> | i in turnos,j in turnos,k in grupos};

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: LuisaL123

Hi Joao!

 

I can see that you had the same problems as me, maybe you can help me :)

 

I had the same problem: x was not accepted.

This is my code:

 

 

Error: Decision variable (or expression) "x" not allowed.

Obrigada!

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Breemes

Hi, 

i used tuples for decision variables with more than 2 indices. 

My problem is that i don't know how many rows i need for the excel sheetwrite. 

In my example i have four indices

 

t= 1..12,

w = 1..11;

i = 1...200;

n= 11;

 

Y[t,w,i,n]

I tried to multiply (12x11x200x11) but i still get the error:  excel: range size is not wide enough for set of tuples: missing rows.

Can anyone explain how i have to set up colouns and rows when using tuples?

Regards,

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: miamoto

i read all these guides, but i couldn't get my goal.

i have a coeficient that indicated by a coordination, (coefficient[i][j][k]))

and a variable for each of these coordinations (dvar boolean x[i][j][k])

this is my code:

int x=...;

int y=...;

int z=...;

 tuple block {
 int a;
 int b;
 int c;
 int d;
} 

{block} coef=...;   /**coordination and coefficient will be imported together**/
int coeficient[x][y][z]=[i:[j:k:l]|<i,j,k,l> in coef]

the last line has mistake (in part [j:k:l])

i want to give coordination (x,y,z) and get back it's coeficient. 
and also i want to define a decision variable for each coordination. (dvar boolean x[i][j][k])
 

regards.

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: LuisaL123

Hi, I have more or less the same problems.

 

I have a dvar x[1..NbSpecialties][1..NbDays][1..NbBlocks][1..NbORs] and I want to form a tuple, so I did this:

 

But I have an error saying that: Decision variable (or expression) "x" not allowed.

 

Can anyone understand what is wrong?

Thank you so much,

 

Best regards,

Luisa

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: LuisaL123

Hi Alex!

Thank you so much for your quick answer yesterday. I went to the link that you advised me and I changed my code the one below. But I still have the same error. Can you help me in any way?

Thank you so much.

 

int NbSpecialties = ...;
range Specialties = 1..NbSpecialties;
int NbDays = ...;
range Days = 1..NbDays;
int NbBlocks = ...;
range Blocks = 1..NbBlocks;
int NbORs = ...;
range ORs = 1..NbORs;
int Patients[1..NbSpecialties] = ...;

dvar boolean x[Specialties][Days][Blocks][ORs];

 

Error:

Decision variable (or expression) "x" not allowed.

 

Best regards,

Luísa

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

can you try to move

{index} xSet= {<s,d,b,r,x[s][d][b][r]> | s in 1..NbSpecialties, d in 1..NbDays, b in 1..NbBlocks, r in 1..NbORs};

after the subject to block ?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer