Search code examples
c++exceldatabasecplex

Import Sets from Excel to CPLEX

I have the following Sets:

{int} Test={1,3,6,8,10};

setof (int) Problem[Test]=[{1,3,5},{8,4},{2,6,7},{2,4},{1}];

Execution works fine. Problem look like this:

enter image description here

As I do not want to insert the numbers manually to CPLEX, I would like to import it from an ExcelFile.

Importing Test via:

{int} Test=...;

with:

SheetConnection Data("Data.xlsx");
Test from SheetRead(Data, "Data1!A1:A6");

In the .dat file.

With the Excel File Format, like this: enter image description here

Is no Problem.

{int} Test=...;
setof (int) Problem[Test]=[{1,3,5},{8,4},{2,6,7},{2,4},{1}];

works...

Now I also would like to read

Problem[Test]=[{1,3,5},{8,4},{2,6,7},{2,4},{1}];

From an ExcelFile. The Data look like this:

enter image description here

BUT:

{int} Test=...;
setof (int) Problem[Test]=...;

With

SheetConnection Data("Data.xlsx");
Test from SheetRead(Data, "Data1!A1:A6");
Problem from SheetRead(Data, "Data1!B2:B6");

Leads to the following Error Messsage:

The Data element "Problem" of Type {int}[Test] is not supported for Spreadsheets

.

Does somebody now how the Excel Input must look like, so that is it supported?

Thanks a lot! Regards

In Addition:

I have one further question. Your answer works perfectly for Excel file format like this: enter image description here So, one row with more than one value. That helped me a lot.

But what about this case: enter image description here

It is a set F[m,i] which might have more than one value in one cell, like e.g. F[1,11] = {1 2}

How can I read that one from an Excel file. Manually it would look like this:

> setof (int) F[X][Y]=[[{},{},{},{},{},{},{},{},{},{},{1,2}],[{},{},{},{},{1},{},{2},{},{},{},{}],[{},{4,5},{6},{},{},{},{},{},{},{},{}],[{},{},{},{},{},{},{},{4},{5},{6},{}],[{},{},{},{},{},{8},{9},{},{},{},{}]];

Whith:

{int} X={1,8,11,35,68};
{int} Y={1,2,3,4,5,6,7,8,9,10,11};

Thanks a lot!

Addition:

I tried your links but actually I don´t really know how to adopt it to my case :( I would be to thankful if you could help me one last time with this.

Let´s assume my excel data file looks like this: enter image description here

So each value for j has as many columns as values. In this case there are two column for j = 2 and j = 11 each.

The goal is that my set in CPLEX looks like this: enter image description here

That would help me very much.

Thank you!

Solution

  • You can do that through strings:

    .mod

    range r=1..4;

string temp[r][1..26]=...;
{int} set[i in r]={ intValue(temp[i][j])| j in 1..26:temp[i][j]!=""};

execute
{
  writeln(set);
}

/*

which gives

[{1 2} {6} {7 8 9} {3 4}]

*/

    .dat

    SheetConnection sh("readarrayofsets.xlsx");

temp from SheetRead(sh,"A1:Z4");

    which is in OPL Excel examples