AIMMS Excel Library - AXLL
The AimmsXLLibrary can communicate with Excel files in server environments where Excel is not installed. This is particularly useful when you deploy applications on AIMMS PRO which is typically installed on a machine with no Office / Excel instance.
The workflow for integrating external Excel files with your AIMMS model is illustrated below.
The AimmsXLLibrary is a collection of functions to do this and more. It is part of the library repository and you can add it to your project from the library manager, as detailed in Add AIMMS Libraries
You can read/write data both in tabular (a list or composite table) or matrix formats. Matrix formats are particularly popular when solving network problems because you need the distances between each nodes in your network and this is typically represented in the form of a distance matrix.
The functions in this library can be accessed by their prefix,
AXLL:: which displays a list of available functions. Documentation of an individual function can be accessed by Right click -> Attributes. The comments for that function explain each attribute.
When using the
AXLL functions, the Excel file will be loaded into memory. This loading will happen in the background and you will not see any file opened on your computer, as this library works without using an Excel installation.
To open an Excel file or load it into memory:
axll::OpenWorkBook(WorkbookFilename : "filename.xlsx" );
To close an Excel file or to remove it from memory:
axll::CloseWorkBook(WorkbookFilename : "filename.xlsx" );
A call to the function
axll::OpenWorkBook will raise an error if that file was already opened before. So, checking if the file is already open or closing it before opening is recommended. There are three options for you to choose from:
if not axll::WorkBookIsOpen(WorkbookFilename : "filename.xlsx" ) then axll::OpenWorkBook(WorkbookFilename : "filename.xlsx" ); endif;
axll::CloseWorkBook(WorkbookFilename : "filename.xlsx"); axll::OpenWorkBook(WorkbookFilename : "filename.xlsx" );
axll::CloseAllWorkbooks; axll::OpenWorkBook(WorkbookFilename : "filename.xlsx" );
Instead of typing the string “filename.xlsx” multiple times, we recommend you use a string parameter to store the file name and use that string parameter in all calls to
AXLL functions. This also lets you use the auto-complete feature of AIMMS, making it easier to write long and complex data import/export procedures:
spFileName := "filename.xlsx" axll::CloseAllWorkbooks; axll::OpenWorkBook(WorkbookFilename : spFileName );
After opening a file, you must select the sheet your data is in before you can read them in:
axll::SelectSheet(SheetName : "SheetName" );
Many functions are available to read different kinds of data from an Excel file. Most commonly used are:
axll::ReadSetRead in data to a set. Data in Excel is in a single column or a single row:
axll::ReadSet( SetReference : sSetinAIMMS , SetRange : "A2:A33" , ExtendSuperSets : 1, MergeWithExistingElements : 0, SkipEmptyCells : 0);
axll::ReadListRead in data to an indexed parameter. Data in Excel must be in a list / composite table format:
axll::ReadList( IdentifierReference : paraminAIMMS , RowHeaderRange : "A2:A33" , DataRange : "D2:D33" , ModeForUnknownElements : 0, MergeWithExistingData : 0);
axll::ReadTableRead in data to an indexed parameter (with 2+ indices in the index domain). Data in Excel must be in a matrix format:
axll::ReadTable( IdentifierReference : multidimParamInAIMMS , RowHeaderRange : "A2:A33" , ColumnHeaderRange : "B1:AG1" , DataRange : "B2:AG33", ModeForUnknownElements : 0, MergeWithExistingData : 0);
axll::ReadSingleValueRead in data to a scalar parameter. Data in Excel is in a single cell:
axll::ReadSingleValue( ScalarReference : scalarParaminAIMMS , Cell : "A1" );
By setting a different value for the
axll::ReadList::ModeForUnknownElements argument of
axll::ReadTable, you can skip the call to
axll::ReadTable must be an AIMMS identifier with 2+ (at least 2) indices in its index domain.
Similar to reading data, many functions are available to write out data to Excel files. Commonly used are:
axll::WriteSet: Writes out the contents of a set to a single column/row:
axll::WriteSet( SetReference : sSetinAIMMS , SetRange : "A2:A33" , AllowRangeOverflow : 0);
axll::WriteCompositeTable: Writes out an indexed identifier in the composite table format, very convenient to use:
axll::WriteCompositeTable( IdentifierReference : multidimParamInAIMMS , TopLeftCell : "A1" , WriteZeros : 0, WriteIndexNames : 1);
axll::WriteTable: Writes out an indexed identifier in the matrix format, more options to control:
axll::WriteTable( IdentifierReference : multidimParamInAIMMS, RowHeaderRange : "A2:A33", ColumnHeaderRange : "B1:AZ1", DataRange : "", AllowRangeOverflow : 1, WriteZeros : 1, IncludeEmptyRows : 0, IncludeEmptyColumns : 0, IncludeEmptyRowsColumns : 0);
axll::WriteSingleValue: Writes out a scalar identifier to a single cell in Excel:
axll::WriteSingleValue( ScalarReference : scalarParaminAIMMS , Cell : "A1" );
There is no
WriteList but a one-dimensional identifier with
axll::WriteCompositeTable will you give the same result. An alternative is to use
axll::WriteTable must be a 2+ dimensional identifier but for
axll::WriteCompositeTable, 1+ is sufficient.
An example project which uses most of the functions described above is included below.