Decision Optimization

 View Only
Expand all | Collapse all

Accessing Spreadsheet in LINUX (CPLEX)

  • 1.  Accessing Spreadsheet in LINUX (CPLEX)

    Posted Sun December 13, 2020 11:53 PM
    Previously I was working in Windows. I had to run my CPLEX model in the LINUX platform, but I got to know that LINUX does not support Spreadsheet data (I was using MS Excel with named ranges as input data and also for writing output while working in Windows)

    As per the suggestions provided in the link below to transform Excel files data into OPL on platforms on which OPL does not support Excel (for my case LINUX).
    opl - Connecte Excel file to Cplex - Stack Overflow

    I tried to implement it through Java classes (Apache POI classes) to read Excel files and call this via IloOplCallJava and used "execute' block in .mod file to call the named ranges in MS Excel.

    The small part of the big size program in .mod file to access the spreadsheet data using "ReadExcel.jar" is as follows: "YearI" and "UR" are the named ranges in excel.
             execute {
                writeln("Read Start");
                    IloOplImportJava("ReadExcel.jar");
                          var sheetin = IloOplCallJava("JavaExcel","<init>","","C:\\Users\\gyanw\\OneDrive\\Desktop\\NEPAL\\Input.xlsx");
                            YearI = sheetin.getNamedNumbers("YearI")[0][0];
                              writeln("YearI: " + YearI);
                               UR = sheetin.getNamedNumbers("UR");
                                 writeln("UR: " + UR);
                                   writeln("UR[150][1]: " + UR[150][1]);
                                   sheetin.closeFile();
                                     writeln("Read completed");
                                    }

    The integer, ranges and float used are defined as follows in the same .mod file.
                       int YearI;
                       int D = 365;
                       int M = 4;
                       range DR = 0..D-1;
                       range MR = 0..M-1;         
                       float UR[DR][MR] ;

    The scripting log returns following information
                   Read Start
                   YearI: 5
                   UR: [object Object]
                   UR[150][1]: 0.5
                   Read completed
    It seems like the OPL is accessing the spreadsheet data correctly.

    But the problem is, the model is reading "YearI" as [0] and all the data ranges for "UR" as [0, 0, 0, 0], [0, 0, 0, 0], ....., [0, 0, 0, 0] which is not correct.
    Following warnings are displayed in the problems section of CPLEX.
    "Not a model element "UR", use 'var' to declare local scripting variables."

    Could you please suggest me the solution to the problem I am facing.
    Perhaps I am defining "UR" or "YearI" incorrectly.
    Or there may be some problem in "ReadExcel.jar" I created to read the MS Excel.

    Thank you very much in advance.



    ------------------------------
    Khem Gyanwali
    Assistant Professor
    Tribhuvan University
    Kathmandu
    9851138521
    ------------------------------

    #DecisionOptimization


  • 2.  RE: Accessing Spreadsheet in LINUX (CPLEX)

    Posted Tue December 15, 2020 07:08 AM
    Hi,

    how would I read from an Excel spreadsheet on non Windows platforms ?

    I would use an external program call to some python code I would generate from OPL.

    See example at https://github.com/AlexFleischerParis/oplexcel/blob/main/readwithoutsheetread.mod

    The input is



    Which generates python code

    import pandas as pd
import xlrd
df=pd.read_excel('c:\\temp\\read2Darray.xls',sheet_name = 'Sheet1',skiprows = 0,nrows= 2,header=None,usecols = 'B:D')
print(df)
res = open("c:\\temp\\resexcel","w")
res.write("res=[")
res.write("\n")
for i, row in enumerate(df.values):
   res.write("[")
   for j in row:
      if (j==j):
         res.write(str(j))
         res.write(",")
   res.write("],")    
   res.write("\n")
res.write("];")
res.close()​

    which when run gives a .dat that you can use in OPL:

    res=[
[5,6,7,],
[8,9,10,],
];​


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

    Original Message