Read Columns of Data from Excel

AIMMS provides various methods for reading Excel data.

1. AimmsXLLibrary, a system library of functions and procedures.

2. Datalink with the XLSProvider, two libraries from the AIMMS repository of libraries.

Let’s compare some features of these libraries, and you can decide which method is best for your application.

Example used

To make this comparison, we’ll read from two spreadsheets:

In this practical example, these two spreadsheets contain similar data, but with different order and number of columns. This may happen when various analysts provide the necessary data in a spreadsheet, but not in the same column order.

The name manager in both sheets covers for the difference in columns:

The data is read in to the following parameters:

DeclarationSection Potential_Excel_input_data {
    Set s_SKU {
        SubsetOf: Integers;
        Index: i_sku;
    }
    Set s_Vendors {
        Index: i_Vendor;
    }
    Parameter p_price {
        IndexDomain: (i_sku,i_vendor);
    }
    Parameter p_maxavail {
        IndexDomain: (i_sku,i_Vendor);
    }
    Parameter p_amtPerPackage {
        IndexDomain: (i_sku,i_Vendor);
    }
}

You can download the example from AIMMS Project Download

AXLL functions and procedures

The code to read Excel data using AXLL looks as follows:

empty p_price, p_maxavail, p_amtPerPackage ;
axll::OpenWorkBook( sp_dataFilename );
axll::SelectSheet("Sheet1");

axll::ReadList(
    IdentifierReference    :  p_price,
    RowHeaderRange         :  "colrange",
    DataRange              :  "Price",
    ModeForUnknownElements :  1,
    MergeWithExistingData  :  0);

axll::ReadList(
    IdentifierReference    :  p_maxAvail,
    RowHeaderRange         :  "colrange",
    DataRange              :  "maxAvail",
    ModeForUnknownElements :  1,
    MergeWithExistingData  :  0);

axll::ReadList(
    IdentifierReference    :  p_amtPerPackage,
    RowHeaderRange         :  "colrange",
    DataRange              :  "amtPerPackage",
    ModeForUnknownElements :  1,
    MergeWithExistingData  :  0);

axll::CloseAllWorkBooks();

1. These functions access the workbook directly without the need to use Excel. This makes the library more suited for application development.

2. This library doesn’t support Excel macros.

3. In this example, the name manager of Excel provided consistent names in the named ranges. When names are not consistent, the code would become significantly more complicated.

4. This library does not make any assumptions about the placing of data in columns.

5. For more information about these functions, see * AIMMS Excel Library - AXLL

DataLink with the XLSProvider

The XLSProvider uses a simple strategy to understand the structure of the data in an Excel worksheet. The top row with data is assumed to be the header, containing the names of the columns. You have to map these names onto identifier names in the AIMMS model. Then DataLink can read by making the XLSProvider scan the worksheet row by row and use the mapping to send the data to the appropriate identifiers.

The code to read the Excel data looks as follows:

dl::DataTables += 'Sheet1' ;
SKUMapping(dl::dt,dl::idn,dl::cn,dl::dn) := data {
   ( 'Sheet1', 's_SKU'           , 1, 1 ) : "SKU",
   ( 'Sheet1', 's_Vendors'       , 2, 2 ) : "Vendor",
   ( 'Sheet1', 'p_price'         , 3, 0 ) : "Price",
   ( 'Sheet1', 'p_maxavail'      , 4, 0 ) : "maxAvail",
   ( 'Sheet1', 'p_amtPerPackage' , 5, 0 ) : "amtPerPackage"
};

dl::RemoveDataSourceMapping("SKUData");
dl::AddDataSourceMapping(
    MapName        :  "SKUData",
    DataMap        :  SKUMapping,
    ColDepend      :  dl::DependEmpty,
    TableAttribute :  dl::TableAttributesEmpty,
    ColAttribute   :  dl::ColAttributeEmpty);
sp_readAttribute :=  { 'DataProvider': xlsprovider::DataLink };
dl::DataRead(
    DataSource     :  sp_dataFilename,
    MapName        :  "SKUData",
    ReadAttributes :  sp_readAttribute);

1. This library accesses the workbook directly without the need to use Excel. This makes the library more suited for application development.

2. This library also doesn’t support Excel macros.

3. Data order is abstracted from the column order in the datalink library, independently of the name manager.

4. This library assumes that the data is presented in columns on the sheet; this library is less suited for data that is scattered on an Excel sheet.

5. See also * Read and Write CSV Files and * XLSProvider for datalink