Originally posted by: CholeYu

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

Does anybody know why the error occurs? I'll really appreciated your help.

The error is like:" excel: the range is unknown."

 

.mod

range AllPoint=0..N;  //all points in the network

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

{int} V0=asSet(0..NbCustomers);

dvar boolean x_ij[AllPoint][AllPoint][K];
minimize sum (k in K, i in V0, j in AllPoint: j!=i) Dij[i][j]*x_ij[i][j][k];

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 "network1.xlsx", there is only one sheet, and I named a big range as "xij".

 

Regards,

Chole

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

you need to use a valid range:

the following example works fine:

.mod

int N=5;
int NbCustomers=2;

 range AllPoint=0..N;  //all points in the network

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

{int} V0=asSet(0..NbCustomers);

int Dij[V0][AllPoint];
dvar boolean x_ij[AllPoint][AllPoint][K];
minimize sum (k in K, i in V0, j in AllPoint: j!=i) Dij[i][j]*x_ij[i][j][k];

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, "A1:D180");

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

Alex,

Thank you very much for your reply.

I changed the range but the errors still occur. It says:Excel range is unknown. Is that because I set the value of parameters in .dat?  Here is my complete code.

.mod

int NbCustomers=...; 

int NbStations=...;

range K=1..NK;     
int N=NbCustomers+1+(5*NbStations);      //dummy vertices for multivisits

range AllPoint=0..N;

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

{int} V0=asSet(0..NbCustomers);

 

minimize

sum (k in K, i in V0, j in AllPoint: j!=i) x_ij[i][j][k];

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

NbCustomers=5;      
NbStations=3;          
NK=3;

 

SheetConnection outputfile("network1. xlsx");

xSet to SheetWrite(outputfile, "A1:D1323");

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

I set the range "A1:D1323" by calculating 21(value of AllPoint) *21(value of AllPoint)*3 (number of vehicles), am I right?

 

Regards,

Chole

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

I would rather write

xSet to SheetWrite(outputfile, "A1:D1452");

1452=22*22*3

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

Hi, Alex.

Thank you very much!!! It works! 

BTW,  the range size in the SheetRead must be the exact size of the output data? Can it be a little bigger?

 

Regards,

Chole

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi

the range may be bigger.

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

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

Alex,

Thanks a lot !

And I have lots of decision variables value to output into one Excel file, so I set different sheets in the same Excel.

 

The data file is like this: 

SheetConnection outputfile("network1.xlsx");

xSet to SheetWrite(outputfile, "xij!A1:D1452");
arvT to SheetWrite(outputfile,"arriveT!A1:A22");
y to SheetWrite(outputfile,"remainBattery!A1:C66");
RemainCargo to SheetWrite(outputfile,"remainCargo!A1:C66");

 

The error is like: excel: range width does not fit the arity of the tuple. Why this error comes?

Thank you.

Regards,

Chole

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

xSet has 4 components and that is why you wrote

xSet to SheetWrite(outputfile, "xij!A1:D1452");

If you change 4 to other values, you should change D too.

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

Alex,

Yes. I set the size of another parameter wrong. Thank you.

I saw an example about multidimensional data output you have written. I wrote the following similar to the example but it did not work. I want to know why.

In the .mod, I tried this:

.mod

minimize...;

subject to {...};

uple 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};

string xij="A1:D";

execute{

xij+=AllPoint.UB*AllPoint.UB*K.UB;

.dat

SheetConnection outputfile ("network1.xlsx");

Xset to SheetWrite(outputfile, xij);

 

Thank you.

Chole

 

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

again

execute{

xij+=(AllPoint.UB+1)*(AllPoint.UB+1)*K.UB;
}

will work much better.

.mod

 int NbCustomers=...;

int NbStations=...;

int NK=...;

range K=1..NK;     
int N=NbCustomers+1+(5*NbStations);      //dummy vertices for multivisits

range AllPoint=0..N;

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

 {int} V0=asSet(0..NbCustomers);

int Dij[V0][AllPoint];

minimize sum (k in K, i in V0, j in AllPoint: j!=i) Dij[i][j]*x_ij[i][j][k];

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};

string xij="A1:D";

execute{

xij+=(AllPoint.UB+1)*(AllPoint.UB+1)*K.UB;
}

.dat

  NbCustomers=5;      
NbStations=3;          
NK=3;

 

 SheetConnection outputfile ("network1.xlsx");

Xset to SheetWrite(outputfile, xij);

work fine

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: CholeYu

I get it!!  Thank you sooo much!!

#DecisionOptimization
#OPLusingCPLEXOptimizer