Using the AIMMSXLLibrary
The workflow for integrating external Excel files with your AIMMS model is illustrated below.
Import the library in your project
AimmsXLLibrary is a collection of functions to do this and more. It is provided as a system library 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.
Open and close files
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
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::WriteSetWrites out the contents of a set to a single column/row:
axll::WriteSet( SetReference : sSetinAIMMS , SetRange : "A2:A33" , AllowRangeOverflow : 0);
axll::WriteCompositeTableWrites out an indexed identifier in the composite table format, very convenient to use:
axll::WriteCompositeTable( IdentifierReference : multidimParamInAIMMS , TopLeftCell : "A1" , WriteZeros : 0, WriteIndexNames : 1);
axll::WriteTableWrites 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::WriteSingleValueWrites 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::WriteTablemust be a 2+ dimensional identifier but for
axll::WriteCompositeTable, 1+ is sufficient.
axll::WriteTablewill look at the top parent set for the base index of the identifier. When passing an index pointing at a subset, this top parent set is used and the subset is ignored.