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:

../../_images/data1.PNG ../../_images/data2.PNG

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:

../../_images/CommonNameManager.PNG

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