# AMPLXL - Quick Example

**URL:** <https://discuss.ampl.com/t/amplxl-quick-example/317>\
**Category:** Announcements\
**Tags:** data\
**Created:** [January 17, 2023, 3:11pm UTC](https://discuss.ampl.com/t/amplxl-quick-example/317 "2023-01-17T15:11:03Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nicolau\_Santos](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.ampl.com/nicolau_santos/32/61_2.png) [@Nicolau\_Santos](https://discuss.ampl.com/u/Nicolau_Santos)\
**Post date:** [January 17, 2023, 3:11pm UTC](https://discuss.ampl.com/t/amplxl-quick-example/317/1 "2023-01-17T15:11:03Z")

</div>

There are multiple ways to import data into AMPL. One of them is [amplxl](https://amplplugins.readthedocs.io/en/latest/rst/amplxl.html) , 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](https://ampl.com/learn/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](https://us1.discourse-cdn.com/flex019/uploads/ampl/original/1X/9867db6adbc6b287cfdd40bf898f1ebee9d19ddd.png)

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](https://us1.discourse-cdn.com/flex019/uploads/ampl/original/1X/194c47d772dab234550ef432a5a623e6c86e35e8.png)

Finally, we create a sheet named _amt_ for the _amt_ parameter, that is indexed simultaneously by _NUTR_ and _FOOD_.

![amt](https://us1.discourse-cdn.com/flex019/uploads/ampl/original/1X/69ea74d1be42b9d2645efd49fe69d526a5e4922b.png)

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](https://portal.ampl.com/~nfbvs/amplxl/diet2D.zip).

More information available at the [amplxl](https://amplplugins.readthedocs.io/en/latest/rst/amplxl.html) page.