Decision Optimization

Expand all | Collapse all

How to import Excel data to CPLEX

  • 1.  How to import Excel data to CPLEX

    Posted 6 days ago

    Hi,

    I have a question. I tried to import Excel data to CPLEX but it doesn't work.

    There is a problem accessing the Excel file. Can anyone help me how to solve this problem? Here I give the file.data of my file

    depot={"D1", "D2"};

    customer={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12" };

    node={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "D1", "D2" };

    vehicle={"V1", "V2"};

    SheetConnection excelsheet("Exceldata.xlsx");

    dist from SheetRead(excelsheet, "dist");

    fcost = [100, 100];

    D= [20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20];

    VC=[140, 140];

    VD=[280, 280];

    Thank you in advanced.



    ------------------------------
    Farahanim Misni
    ------------------------------


  • 2.  RE: How to import Excel data to CPLEX

    Posted 4 days ago
    Hi,

    in https://www.linkedin.com/pulse/making-decision-optimization-simple-alex-fleischer/

    let me use https://github.com/AlexFleischerParis/zooopl/blob/master/zooexcel.mod

    where you can see

    SheetConnection s("zoo.xlsx");
    params from SheetRead(s,"params!A2");
    buses from SheetRead(s,"buses!A2:B3");
    results to SheetWrite(s,"buses!E2:F3");
    
    or if we prefer to rely on named range
    
    SheetConnection s("zoonamedrange.xlsx");
    params from SheetRead(s,"nbkids");
    buses from SheetRead(s,"buses");
    results to SheetWrite(s,"result");​



    in your example your named range may be wrong, can you try with somthing like E2:F7 ?

    Or share your files so that other users could try

    regards



    ------------------------------
    [Alex] [Fleischer]
    [EMEA CPLEX Optimization Technical Sales]
    [IBM]
    ------------------------------



  • 3.  RE: How to import Excel data to CPLEX

    Posted 4 days ago
    Your command to access excel file looks okay.

    You need to make sure 
    1. The excel file exists already
    2. Its in the same location as the model file (or point to the location )

    ------------------------------
    Nzube Nzube
    ------------------------------



  • 4.  RE: How to import Excel data to CPLEX

    Posted 4 days ago
      |   view attached
    I already put the Excel file in the same location of the project file. However it is still not working.
    Here I attach the file.mod , file.data and also the Excel file to be checked if something wrong that i did in the coding. 
    Thank you. Regards.

    file.mod:

    {string} depot=...;

    {string} customer=...;

    {string} vehicle=...;

    {string} node=...;

    int N=card(customer);

    float dist[node][node]=...;

    float fcost[depot]=...;

    float D[customer]=...;

    float VC[vehicle]=...;

    float VD[depot]=...;

    dvar boolean x[node][node][vehicle];

    dvar boolean z[depot];

    dvar boolean y[depot][customer];

    dvar float+ u[customer][vehicle];

    dexpr float facility=sum(i in node, j in node:j!=i, k in vehicle)x[i][j][k]*dist[i][j];

    dexpr float travel=sum(i in depot)fcost[i]*z[i];

    dexpr float cost=facility + travel;

     

    minimize cost;

    subject to {

    forall(j in customer) sum(i in node, k in vehicle)x[i][j][k]==1;

    forall(k in vehicle) sum(i in depot, j in customer)x[i][j][k]<=1;

    forall(k in vehicle) sum(i in node, j in customer:j!=i)D[j]*x[i][j][k]<=VC[k];

    forall(i in node, k in vehicle) (sum(j in node)x[i][j][k])- (sum(j in node)x[j][i][k])==0;

    forall(i in depot) sum(j in customer)D[j]*y[i][j]<=VD[i]*z[i];

    forall(j in customer) sum(i in depot)y[i][j]==1;

    forall(l in customer, j in customer, k in vehicle) u[l][k]-u[j][k]+N*x[l][j][k]<= N-1;

    forall(i in depot, j in customer, k in vehicle) (sum(h in node)(x[i][h][k]+x[h][j][k]))-y[i][j]<=1;

    }

    file.mod

    depot={"D1", "D2"};

    customer={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12" };

    node={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "D1", "D2" };

    vehicle={"V1", "V2"};

    SheetConnection excelsheet("Exceldata.xlsx");

    dist from SheetRead(excelsheet, "dist");

    fcost = [100, 100];

    D= [20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20];

    VC=[140, 140];

    VD=[280, 280];





    Attachment(s)

    xlsx
    Exceldata.xlsx   8 KB 1 version


  • 5.  RE: How to import Excel data to CPLEX

    Posted 4 days ago
    Hi

    with

    dist from SheetRead(excelsheet, "Excel1!D3:Q16");​


    Your model works fine

    regards




    ------------------------------
    [Alex] [Fleischer]
    [EMEA CPLEX Optimization Technical Sales]
    [IBM]
    ------------------------------



  • 6.  RE: How to import Excel data to CPLEX

    Posted 4 days ago
      |   view attached
    Hi

    I had a look the code seems okay.

    Looking at the excel file, the range was not named. 

    Alex's reply will work just fine.
    Also i have gone ahead to name the range on the excel file. 



    ------------------------------
    Nzube Nzube
    ------------------------------

    Attachment(s)

    xlsx
    Exceldata.xlsx   9 KB 1 version


  • 7.  RE: How to import Excel data to CPLEX

    Posted 2 hours ago
    Hi,
    Actually I already changed the name according to Alex's suggestion but still got the same problem which is "problem accessing excel file". I tried to do a new opl project and create a new excel file, but still didnt work as well. Is it because of my excel file or my programme system in my laptop? Or i did wrong in steps to import the excel file.
    Thank you. Regards.