Reading Excel Data Sheets

Today, during a short customer meeting, I got questions on how to read in data, when that data is presented in an Excel workbook; whether to continue to use a single sheet containing multiple tables, or put each table in a separate sheet - a data sheet; and if so; to continue using AXLL, or switch over to AimmsDEX. Illustrative examples were appreciated.

For an Excel sheet containing multiple tables and scalars; you will need to use AXLL. 

Excel data sheets are one of the de-facto industry standards for exchanging data between programs. In this article, I'll show three ways available with AIMMS to read data sheets in an Excel workbook:

1. Via AXLL library
2. Via AimmsDEX library, using manually created mapping files,
3. Via AimmsDEX library, using annotations to relate data columns to AIMMS identifiers

The running example

The data sheets are:
 

and

These data sheets have:

• No makeup.
• The first row contains the names of the columns.
• Each subsequent row contains a record of data.

The data in these sheets are to be mapped to the AIMMS parameters p_d1(i_a,i_b), p_d2(i_a,i_b), p_e1(i_a,i_b,i_c), p_e2(i_a,i_b,i_c), and p_e3(i_a,i_b,i_c).

Using AXLL library

Reading the parameter p_d1 is achieved using the following code snippet:
 

The row header range is the data in the columns for the indices (line 9).
To avoid reading past the last row containing data, the Range strings are constucted using formatString and the last row number.

The data for p_d2 is read similarly; which means that the index data is read twice.

For Sheet2 the same approach is used.

Note that if someone accidentally switches the columns C and D; this will go unnoticed, but the data read in is different.

Using AimmsDEX with manually created mapping files.

During the reading of data, it is essential to relate the columns in a data sheet to the AIMMS identifiers. AimmsDEX can deploy mapping files in the XML format to relate AIMMS identifiers to columns in Excel data sheets. As an aside, an XML file is a text file, so you can use your favorite text editor to edit such a file. In our example the XML file to specify the mapping looks as follows:

 <AimmsExcelMapping>
    <TableMapping name="Sheet1">
        <RowMapping name="row1">
            <ColumnMapping name="a" binds-to="i_a"/>
            <ColumnMapping name="b" binds-to="i_b"/>
            <ColumnMapping name="d1" maps-to="p_d1(i_a,i_b)"/>
            <ColumnMapping name="d2" maps-to="p_d2(i_a,i_b)"/>
        </RowMapping>
    </TableMapping>
    <TableMapping name="Sheet2">
        <RowMapping name="row2">
            <ColumnMapping name="a" binds-to="i_a"/>
            <ColumnMapping name="b" binds-to="i_b"/>
            <ColumnMapping name="c" binds-to="i_c"/>
            <ColumnMapping name="e1" maps-to="p_e1(i_a,i_b,i_c)"/>
            <ColumnMapping name="e2" maps-to="p_e2(i_a,i_b,i_c)"/>
            <ColumnMapping name="e3" maps-to="p_e3(i_a,i_b,i_c)"/>
        </RowMapping>
    </TableMapping>
</AimmsExcelMapping>

The above, via the TableMapping element, specifies that there are two tables, on the sheets Sheet1 and Sheet2 respectively.

Each column of these two sheets is given one line relating it to an AIMMS identifier, using a binds-to attribute for indices, and a maps-to attribute for parameters.

Given the above mapping file, the reading of these two sheets is now coded as follows:

When comparing this to the AXLL format we note:

• When two columns are switched, the data is still read in properly, as the name in row 1 is used to identify the meaning of each column.
• There is no need to construct strings containing Excel Ranges and worry about the last row.

Via AimmsDEX library, using annotations

Via Annotations, the XML syntax for relating data columns to AIMMS identifiers can be generated; resulting in a more compact, and a more maintainable method for reading data.

Making use of the model structure:

There are three levels here:

1. The outer level, whereby we name the entire data set.
2. The level of tables, there are two tables - each with its own table name.
3. The level of columns, each of the three index (key) columns has its own column name, and each of the five parameters (derived) columns as well.

Starting with the data set:

At the first level, the data set is specified; directly using an annotation.

Next level, the table name:

Again, the table name is specified by an annotation. 
Note that the dex::Dataset annotation is inherited from the encompassing section.

And at the column level:
 

Again, the column name is specified via an annotation.

Note that the dex::Dataset and dex::TableName annotations are inherited from the encompassing declaration section and section.

The code to read the data becomes:

Once you start using this method of reading data, you probably will soon start to appreciate its ease of use, its flexibility and its maintainability.

There is a lot more to using annotations, see the AimmsDEX library documentation.

Please find attached a zip file that contains the Excel work book and three AIMMS projects, illustrating the above.

See also:

• Marcel Roelof's answer in 
• https://documentation.aimms.com/aimmsxllibrary/index.html
• https://documentation.aimms.com/dataexchange/using.html#example-excel-mapping
• https://documentation.aimms.com/dataexchange/standard.html#generating-the-dataset-mapping-files-from-annotations