Reading a nested array Using SheetRead from Excel file in IBM ILOG CPLEX OPL

Question

I am trying to read a set of players P that play in position Q from excel. A sample of how the sets look is given below -

// set of positions q
Q = {"GK", "DEF", "MID", "FWD"}; 

//  set of players p allowed to play in Q position
Pq = [{"Raya", "Alisson", "De Gea", "Pope", "Sa"},
      {"Trippier", "White", "Schar", "Mings", "Van Dijk", "Shaw", "Saliba"},
      {"De Bruyne", "Salah", "Rashford", "Martinelli", "Almiron", "Trossard", "Eze", "Son"},
      {"Haaland", "Watkins", "Wilson", "Jesus", "Mitrovic", "Firmino"}
     ];

How do I use SheetRead to get data in this way? As each row is an array on its own.

I First called Pq in the Mod file as -

{string} Pq[Q]= ...; 

Then I tried using SheetRead in the following manner which Obviosly gave me an error in the Datafile -

Pq["GK"] from SheetRead(FplGwData, "PricePoints!A2:A6")}

Answer 1

What you can do is read all cells in an array and then you will process that array as you need.

Example at https://github.com/AlexFleischerParis/oplexcel/blob/main/zooexcelstrings.mod

/*
Sometimes we do not know exactly what the spreadsheets looks like and we need to parse it out of string content
*/

    string excelstrings[1..100][1..26]=...;
    
    assert excelstrings[1][1]=="nbKids";
    assert excelstrings[1][2]=="zizeBuses";
    assert excelstrings[1][3]=="costBuses";
    
    int nbKids=atoi(excelstrings[2][1]);
    

    // a tuple is like a struct in C, a class in C++ or a record in Pascal
    tuple bus
    {
    key int nbSeats;
    float cost;
    }

    // This is a tuple set
    {bus} buses={ <intValue(excelstrings[i][2]),floatValue(excelstrings[i][3])>|i in 2..100 :excelstrings[i][2]!=""};

    // asserts help make sure data is fine
    assert forall(b in buses) b.nbSeats>0;
    assert forall(b in buses) b.cost>0;

    // decision variable array
    dvar int+ nbBus[buses];

    // objective
    minimize
     sum(b in buses) b.cost*nbBus[b];
     
    // constraints
    subject to
    {
     sum(b in buses) b.nbSeats*nbBus[b]>=nbKids;
    }

    // output 
    execute
    {
     excelstrings[1][5]="result";
     for(var b in buses) excelstrings[Opl.ord(buses,b)+2][5]=nbBus[b];
     
   }     
    

/*
and in the .dat we have
SheetConnection s("zoo.xlsx");
excelstrings from SheetRead(s,"A1:Z100");
excelstrings to SheetWrite(s,"A1:Z100");
*/