2

I'm to solve an optimization problem using Cplex.

In my file.dat I use SheetConnection my_sheet("ExcelFile.xls") to link Excel file to my cplex program, and after that I use SheetRead() to read data from Excel file.

But after I run my configuration I've got the following error:

"sheet data not supported on this platform"
"Processing failed"

I found that reading excel spreadsheet on ILOG CPLEX optimization studio is not supported on Linux

Any ideas please?

rkersh
  • 4,447
  • 2
  • 22
  • 31
AIA
  • 23
  • 1
  • 5
  • Did you try searching the web on "sheet data not supported on this platform"? –  Jan 12 '18 at 18:28
  • Yes I start searching the web first before I post my question, but I didn't find a solution. – AIA Jan 12 '18 at 18:40
  • So, _how_ are the many results you get for that search not related? Show what you have tried. –  Jan 12 '18 at 18:41
  • I found that reading excel spreadsheet on ILOG CPLEX optimization studio is not supported on Linux (which I should have mentioned on my question) but I could not find an alternative to use, The data that I should use are on excel spreadsheets. – AIA Jan 12 '18 at 18:57
  • Hi could you turn the excel file into csv ? Then you could read csv from opl – Alex Fleischer Jan 12 '18 at 20:55

2 Answers2

1

There are Java classes, for example the Apache POI classes, that can read and write Excel files. You can use those classes to implement an external/custom data source and call this via IloOplCallJava. This you can then either call directly from your .mod file in an 'execute' block or you can use it from your .dat file via the 'prepare' and 'invoke'. I have done the former in the past and it worked well.

I have also used Python to transform Excel files into something that is easier to consume with OPL on platforms on which OPL does not support Excel.

Daniel Junglas
  • 11
  • 1
1

I had the same problem. My solution was to transform my data into csv and use IloOplInputFile to read it. Example:

Suppose you have the following export.cvs file:

Nicolas;2; 
Alexander;3;

You can use the following code to convert it into your data.

tuple t
{
   string firstname;
   int number;
}

{t} s={};

execute
{
   var f=new IloOplInputFile("export.csv");
   while (!f.eof){
      var str=f.readline();
      var ar=str.split(";");
      if (ar.length==3) s.add(ar[0],Opl.intValue(ar[1]));
   }
   f.close();
}

execute
{
    writeln(s);
}

Which will read the csv file and compute the tuple set s:

{<"Nicolas" 2> <"Alexander" 3>}

As you can see in the answer in IBM Forum.

Michella Aguiar Coelho
  • 76
  • 6
  • 1
    That works well. And you could also use some OPL code that calls python to read the csv and then this can be pretty generic : https://www.ibm.com/developerworks/community/forums/html/topic?id=476469ad-eec4-46f5-93a5-7b04e87b2836&ps=25 regards – Alex Fleischer Jun 10 '18 at 09:15
  • 1
    Thanks @AlexFleischer! Your answer in IBM Forum was essential for my project progress. – Michella Aguiar Coelho Jun 10 '18 at 21:25
  • 1
    Thank you very much Michella and Alex. Your answers really helped me. – AIA Aug 03 '18 at 14:41