Connecte Excel file to Cplex

Question

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?

Answer 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.

Answer 2

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.