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

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