Topic
  • 13 replies
  • Latest Post - ‏2013-08-20T20:46:46Z by MauricioSirolla
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
    1719 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
    1719 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.