MPL allows you to import the elements for an index directly from a Excel spreadsheet. In the INDEX section, where you define the index, enter the keyword EXCELRANGE after the assignment symbol (:=) followed by parentheses containing the Excel workbook filename and the Excel range name you want to import from.
INDEX
cuts := EXCELRANGE("Cutstock.xls","CutsRange");
In the above example, MPL will open the Excel spreadsheet Cutstock.xls, locate the CutsRange, and then read in the entries for the index cuts.
To make sure there are no name conflicts when sending the problem to the solver, keep at least the first three characters of the named subscripts distinct. If that is difficult, you can abbreviate the names by importing another range from the spreadsheet containing shorter names. To import the short name range enter the keyword BECOMES followed by the range name inside the parenthesis. Here is an example:
INDEX
cuts := EXCELRANGE("Cutstock.xls", "CutsRange", BECOMES "CutsShort");
It is not always desirable to have the same subscript length for all indexes in the model. Therefore you can use a different length for each named index by following the index definition with a colon and a new length. For example:
INDEX
cuts := EXCELRANGE("Cutstock.xls","CutsRange"):4;
This definition sets the subscript length for the month index to 4.
The EXCELRANGE command will read every cell in the range by default until an empty cell is encountered. In some cases, you will want to read only a specific column from the range. MPL allows you to specify which column to read by entering a comma and the column number after the range name. For example:
INDEX
cuts := EXCELRANGE("Cutstock.xls","CutsTable", 1);
This will read only the first column of the CutsTable range. Also note, that if you are reading multiple indexes and data vectors from the same Excel spreadsheet, you can omit the workbook filename on all entries after the first one.
For further information on importing indexes please refer to Chapter 11.1: Import Indexes from Database