Reading two sets and 2D data in matrix from one sheet in Excel

[ondrejsafka]

Hello,

I tried to figure it out every time I have to solve problem with data matrix, but I haven’t been able to figure it out.

I have two sets in the assignment problem, one for depot (garaz on the picture) and customer (Michle, Prosek, Radlice, Troja), and distances between them in sheet named distance like this:

Until now I had separate sheet for each set and the one for data matrix, but I wonder if there is a way to read all the information at once, a way to say to AMPL: set depot is in the first column, set customer is in the first column and numbers are param distance.

Thank you!

[4er]

The “read table” statement cannot read a 2-dimensional data table and also two 1-dimensional sets at the same time. However, it is possible to read a 2-dimensional data table and also its 2-dimensional index set at the same time; and the AMPL model can create the needed 1-dimensional sets:

set DepCust dimen 2;
set Depots = setof {(i,j) in DepCust} i;
set Customers = setof {(i,j) in DepCust} j;

param distances {Depots,Customers} >= 0;

table disttab IN "amplxl" "garaz.xlsx" "2D":
   DepCust <- [garaz,cust], distances;
read table disttab;

You can try this out with the attached files. The output that you will see is as follows:

ampl: include garaz.run;
set Depots := Garaz1 Garaz2 Garaz3 Garaz4;

set Customers := Michle Prosek Radlice Troja;

distances [*,*]
:      Michle Prosek Radlice Troja    :=
Garaz1     5     22     12     18
Garaz2    15     17      6     10
Garaz3     8     25      5     20
Garaz4    10     12     19     12
;

You can try it out with the attached files.
garaz.run (325 Bytes)
garaz.xlsx (8.4 KB)