Reading columns of data from Excel

AIMMS provides various methods for reading Excel data.

  1. A prepackaged module of procedures and functions, using the prefix spreadsheet::.
  2. AimmsXLLibrary, a system library of functions and procedures.
  3. Datalink with the provider 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);
    }
}

The spreadsheet:: functions and procedures

The spreadsheet:: module includes these functions and procedures:

  1. Create and close workbooks
  2. Helper functions to create ranges
  3. Support for exchanging data between ranges on an Excel sheet with scalar, one-dimensional and multi-dimensional AIMMS parameters. Note that these ranges can be named ranges.

The code to read Excel data using looks as follows:

empty p_price, p_maxavail, p_amtPerPackage ;

spreadsheet::RetrieveTable(
    workbook                :  sp_dataFilename,
    parameter               :  p_price,
    DataRange               :  "Price",
    RowsRange               :  "colrange",
    sheet                   :  "Sheet1",
    automaticallyExtendSets :  1 );

spreadsheet::RetrieveTable(
    workbook                :  sp_dataFilename,
    parameter               :  p_maxAvail,
    DataRange               :  "maxAvail",
    RowsRange               :  "colrange",
    sheet                   :  "Sheet1",
    automaticallyExtendSets :  1 );

spreadsheet::RetrieveTable(
    workbook                :  sp_dataFilename,
    parameter               :  p_amtPerPackage,
    DataRange               :  "amtPerPackage",
    RowsRange               :  "colrange",
    sheet                   :  "Sheet1",
    automaticallyExtendSets :  1 );

spreadsheet::CloseWorkBook(sp_dataFilename,0);

Note the following:

  1. The implementation of these functions uses Excel itself, and therefore this module is less suited for application development; it would require the presence of Excel on the client computer or AIMMS PRO Server.
  2. This library supports 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 signficantly more complicated.
  4. This library doesn’t make any assumptions about the placing of data in columns.
  5. For more information about these functions, see: AIMMS The Function Reference: Chapter “Spreadsheet Functions”.

AXLL functions and procedures

The AXLL:: system library includes these functions and procedures:

  1. Create and close workbooks
  2. Helper functions to create ranges
  3. Support for exchanging data between ranges on an Excel sheet with scalar, one-dimensional and multi-dimensional AIMMS parameters. Note that these ranges can be named ranges.

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();

Note the following:

  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 signficantly more complicated.
  4. This library doesn’t make any assumptions about the placing of data in columns.
  5. For more information about these functions, see also Overview: AIMMS Excel Library and Link to Excel Files .