Topic
  • 31 replies
  • Latest Post - ‏2017-03-06T20:23:16Z by pieterce
hail lab
hail lab
6 Posts

Pinned topic write multidimensional array into excel

‏2009-12-22T21:14:34Z |
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
Updated on 2012-11-20T09:14:59Z at 2012-11-20T09:14:59Z by CplexCui
  • Brummel
    Brummel
    5 Posts

    Re: write multidimensional array into excel

    ‏2010-03-08T08: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
  • davidoff
    davidoff
    55 Posts

    Re: write multidimensional array into excel

    ‏2010-03-09T08:34:25Z  
    • Brummel
    • ‏2010-03-08T08: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
    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
  • Hamed Nikhalat
    Hamed Nikhalat
    8 Posts

    Re: write multidimensional array into excel

    ‏2010-03-17T12:35:41Z  
    • davidoff
    • ‏2010-03-09T08: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 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
    What does it mean to save the array in Excel in a flat way?
  • SystemAdmin
    SystemAdmin
    1883 Posts

    Re: write multidimensional array into excel

    ‏2010-03-19T08:32:08Z  
    What does it mean to save the array in Excel in a flat way?
    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
  • msciaroni
    msciaroni
    3 Posts

    Re: write multidimensional array into excel

    ‏2012-03-23T05:05:16Z  
    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
    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?
  • SystemAdmin
    SystemAdmin
    1883 Posts

    Re: write multidimensional array into excel

    ‏2012-03-23T15:41:36Z  
    • msciaroni
    • ‏2012-03-23T05: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?
    Could you detail how you declared your tuple and initialized your set?
  • SystemAdmin
    SystemAdmin
    1883 Posts

    Re: write multidimensional array into excel

    ‏2012-11-19T20:11:01Z  
    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
    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
  • CplexCui
    CplexCui
    2 Posts

    Re: write multidimensional array into excel

    ‏2012-11-20T09:14:59Z  
    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!
  • 5NK7_Thomas_Cibils
    5NK7_Thomas_Cibils
    1 Post

    Re: write multidimensional array into excel

    ‏2013-05-24T17: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.

  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2013-05-25T19:57:40Z  

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

  • sarah2121
    sarah2121
    1 Post

    Re: write multidimensional array into excel

    ‏2013-07-18T16:06:06Z  

    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




     

  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2013-07-19T18:34:43Z  
    • sarah2121
    • ‏2013-07-18T16:06:06Z

    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




     

    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

  • MauricioSirolla
    MauricioSirolla
    1 Post

    Re: write multidimensional array into excel

    ‏2013-08-20T20:46:46Z  

    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.

  • NRouge
    NRouge
    16 Posts

    Re: write multidimensional array into excel

    ‏2016-06-14T14:27:18Z  
    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

    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.

     

    Attachments

  • Moit1910
    Moit1910
    14 Posts

    Re: write multidimensional array into excel

    ‏2016-06-14T14:54:56Z  
    • NRouge
    • ‏2016-06-14T14: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 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.

  • NRouge
    NRouge
    16 Posts

    Re: write multidimensional array into excel

    ‏2016-06-14T16:12:17Z  
    • Moit1910
    • ‏2016-06-14T14:54:56Z

    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.

    Hi,

    It worked. Many thanks for your quick reply.
     

  • khadeejah
    khadeejah
    100 Posts

    Re: write multidimensional array into excel

    ‏2016-11-29T16: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!

  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2016-11-30T09:31:47Z  

    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

  • ShaCplex
    ShaCplex
    86 Posts

    Re: write multidimensional array into excel

    ‏2016-12-02T09:32:07Z  

    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 2016-12-02T11:03:35Z at 2016-12-02T11:03:35Z by ShaCplex
  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2016-12-02T12:28:44Z  
    • ShaCplex
    • ‏2016-12-02T09: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

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

    regards

  • ShaCplex
    ShaCplex
    86 Posts

    Re: write multidimensional array into excel

    ‏2016-12-02T14: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.. 

    Updated on 2016-12-02T14:48:06Z at 2016-12-02T14:48:06Z by ShaCplex
  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2016-12-02T14:58:45Z  
    • ShaCplex
    • ‏2016-12-02T14: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://www-01.ibm.com/support/docview.wss?uid=swg21401340

    regards

  • pieterce
    pieterce
    9 Posts

    Re: write multidimensional array into excel

    ‏2017-03-05T15:38:49Z  

    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 2017-03-05T15:39:52Z at 2017-03-05T15:39:52Z by pieterce
  • AlexFleischer
    AlexFleischer
    2422 Posts

    Re: write multidimensional array into excel

    ‏2017-03-05T15:49:44Z  
    • pieterce
    • ‏2017-03-05T15: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