AMPLXL - Quick Example

There are multiple ways to import data into AMPL. One of them is amplxl , a table handler for spreadsheets in the .xlsx format.

In this post we will take a quick look on how to use amplxl in the diet problem, available on Chapter 2 of the AMPL book.

set NUTR;
set FOOD;

param cost {FOOD} > 0;
param f_min {FOOD} >= 0;
param f_max {j in FOOD} >= f_min[j];

param n_min {NUTR} >= 0;
param n_max {i in NUTR} >= n_min[i];

param amt {NUTR,FOOD} >= 0;

var Buy {j in FOOD} >= f_min[j], <= f_max[j];

minimize Total_Cost:  sum {j in FOOD} cost[j] * Buy[j];

subject to Diet {i in NUTR}:
   n_min[i] <= sum {j in FOOD} amt[i,j] * Buy[j] <= n_max[i];

The first step is to create a file named “diet.xlsx” with your favourite spreadsheet software.
Afterwards we create a table for the indexing set NUTR and its associated parameters, n_min and n_max. The simplest way to do this is to rename the sheet as nutr and add the data to it, as in the following screenshot:

nutr

Next we create a new sheet, named food, and apply the same process to the FOOD set and the associated parameters cost, f_min and f_max.

food

Finally, we create a sheet named amt for the amt parameter, that is indexed simultaneously by NUTR and FOOD.

amt

Unlike the previous tables, where all the columns started with the name of a set/parameter and had the values after, we set the first column name for the FOOD set, add the values of NUTR to the first row and fill the amt values. This is a 2-dimentional table and the definition of the NUTR set is implicit.

To use amplxl you you need to load it with the command

load amplxl.dll;

Now we need to establish a connection between the data in the spreadsheet and AMPL. For each table in the spreadsheet we need a table declaration.
For the data in the nutr sheet the table declaration is the following:

table nutr IN "amplxl" "diet.xlsx":
    NUTR <- [NUTR], n_min, n_max;

The process is identical for the data in the nutr sheet

table food IN "amplxl" "diet.xlsx":
    FOOD <- [FOOD], cost, f_min, f_max;

and similar for the amt table

table amt IN "amplxl" "2D" "diet.xlsx":
    [NUTR, FOOD], amt;

Note that amt is a 2-dimentional table, you need to specify the 2D keyword in the table declaration. The driver will detect the FOOD indexing set in the first column and assume that the elements in NUTR are the remaining elements of the first row.
Also note that you will need a table for each indexing parameter.

To load the data use the read command

read table nutr;
read table food;
read table amt;

Now we are able to choose a solver and solve the problem.

option solver highs;
solve;

The output should be similar to the following

ampl: include 'example.run';
HiGHS 1.2.2:              HiGHS 1.2.2: optimal solution; objective 88.2
1 simplex iterations
0 barrier iterations
ampl:

It’s also possible to write the solution to another spreadsheet with the following commands

table buy OUT "amplxl" "sol.xlsx":
    FOOD -> [FOOD], Buy;

write table buy;

Now, the process is reversed. The amplxl driver will create a file named “sol.xlsx” with a sheet named buy and write the values of FOOD and Buy into it.

The files for this example are available here.

More information available at the amplxl page.

1 Like