Excel File

Excel File connectivity tool provides easy platform-independent access to MS Excel (.xls, .xlsx) files within AnyLogic models. Using that object you can:

• Read Excel files and explore their content
• Read individual cell values and formulas of different types
• Read AnyLogic table function contents
• Read AnyLogic 1D and 2D [hyper]arrays
• Create new cells
• Write to individual cells and change their type
• Write AnyLogic datasets to Excel spreadsheets
• Save modified spreadsheets

To add Excel file access tool

1. Drag the Excel File element from the Connectivity palette of the Palette onto the graphical diagram. You can also drag the Excel files from other applications directly onto the AnyLogic graphical diagram. In this case AnyLogic automatically adds a new Excel File element on the graphical diagram containing the added Excel file.
2. In the Properties view, modify the Name of the element. This name will be used to identify and access this object from code.

3. In the File field, specify the Excel file this object will work with. Browse for the file using the Browse button. If the file is stored in the model folder, the relative path will be displayed in File field. Otherwise, the absolute path will be displayed. Upon selection, the source file will automatically appear in the model's Resource folder in the Projects view. This way you will be able to track the current state of the source file, switch between the absolute and relative file paths, etc.
4. Go to the Advanced section. Leave the Load on model startup check box selected. This will automatically load the workbook from the file on model startup and enable your work with this Excel file.
5. If you expect to save some data in the specified Excel file, leave the Save on model termination check box selected. This will save you from calling the function writeFile() of this access object to commit all your changes into the Excel file - this will be automatically done on model termination.

You work with Excel files using the corresponding API of the ExcelFile object. All the functions are listed below. Please note that nearly all the functions have several different argument sets and notations. Therefore we recommend you to search for the operation you need, then expand the corresponding section and find the function that suits your needs best.

Reading from Excel file

Reading Excel file - readFile()

void readFile() - Loads the workbook from the file.
Warning! All unsaved data (if any) in the workbook is lost after this function is called.

Getting a type of the specified cell - getCellType()

The function getCellType() returns the cell type (numeric, formula, string...)

The function has three notations with different argument sets. They differ in the way the cell is addressed.

int getCellType(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

int getCellType(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

int getCellType(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading a boolean value from a cell - getCellBooleanValue()

The function getCellBooleanValue() returns the value of the cell as a boolean. For strings, numbers, and errors, throws an exception. For blank cells returns false.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

boolean getCellBooleanValue(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

boolean getCellBooleanValue(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

boolean getCellBooleanValue(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading a numeric value from a cell - getCellNumericValue()

The function getCellNumericValue() returns the value of the cell as a number. For strings throws an exception. For blank cells we return a 0.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

double getCellNumericValue(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

double getCellNumericValue(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

double getCellNumericValue(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading String value from a cell - getCellStringValue()

The function getCellStringValue() returns the value of the cell as a string - for numeric cells throws an exception. For blank cells returns an empty string. For formula cells that are not string formulas, returns empty string

The function has three notations with different argument sets. They differ in the way the cell is addressed.

String getCellStringValue(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

String getCellStringValue(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

String getCellStringValue(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading Date value from a cell - getCellDateValue()

The function getCellDateValue() returns the value of the cell as a date. For strings throws an exception. For blank cells returns null.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

Date getCellDateValue(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

Date getCellDateValue(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

Date getCellDateValue(String cellName) - the cell is specified by name in the following format: >sheetname>!>columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Getting a formula defined for a specified cell - getCellFormula()

The function getCellFormula() returns a formula for the cell, for example, SUM(C4:E4)

The function has three notations with different argument sets. They differ in the way the cell is addressed.

String getCellFormula(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

String getCellFormula(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

String getCellFormula(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Getting a type of a formula defined for a specified cell - getCellFormulaType()

The function getCellFormulaType() returns the type of the formula cell. Only valid for formula cells.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

int getCellFormulaType(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

int getCellFormulaType(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

int getCellFormulaType(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading value of the cell as an error code - getCellErrorValue()

The function getCellErrorValue() returns the value of the cell as an error code. For strings throws an exception. For blank cells returns null.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

byte getCellErrorValue(int sheetIndex, int rowIndex, int columnIndex)- the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

byte getCellErrorValue(String sheetName, int rowIndex, int columnIndex)- the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

byte getCellErrorValue(String cellName)- the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading data from Excel file into AnyLogic table function - readTableFunction()

The function readTableFunction() reads the data from Excel file into specified table function.

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Function returns the actual number of table function points read from the sheet.

Arguments:
tableFunction - the table function to fill
length - the number of table function points to read

The function has three notations with different argument sets. They differ in the way the cell is addressed.

int readTableFunction(TableFunction tableFunction, int sheetIndex, int rowIndex, int columnIndex, int length) - Reads the table function from the sheet with index sheetIndex starting at the row with index rowIndex:
- arguments are read from column at columnIndex
- values are read from column at columnndex 1

int readTableFunction(TableFunction tableFunction, String sheetName, int rowIndex, int columnIndex, int length) - Reads the table function from the sheet with name sheetName starting at the row with index rowIndex:
- arguments are read from column at columnIndex
- values are read from column at columnndex 1

int readTableFunction(TableFunction tableFunction, String cellName, int length) - Reads the table function from the sheet starting at the row of the given cell:
- arguments are read from column of the given cell
- values are read from column next to the given cell
The cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Reading data from Excel file into AnyLogic hyperarray - readHyperArray()

The function readHyperArray() reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.

Arguments:
array - the HyperArray to write data to, should have 1 or 2 dimensions.
dim1AcrossRows - use true to read the data corresponding to the first dimension, across the sheet rows (e.g. in this mode data for one-dimensional array is loaded from the sheet column)

The function has three notations with different argument sets. They differ in the way the cell is addressed.

void readHyperArray(HyperArray array, int sheetIndex, int rowIndex, int columnIndex, boolean dim1AcrossRows) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

void readHyperArray(HyperArray array, String sheetName, int rowIndex, int columnIndex, boolean dim1AcrossRows) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

void readHyperArray(HyperArray array, String cellName, boolean dim1AcrossRows) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Writing to Excel file

Saving changes into Excel file - writeFile()

void writeFile() - Stores the current workbook to the file. Workbook should be loaded. Unchanged workbooks (workbooks with unsaved data) aren't saved. For saving to another location please call setFileName(String) before this function.

Creating a cell with specified index - createCell()

The function createCell() creates a new cell at the given position.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

void createCell(int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

void createCell(String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

void createCell(String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Writing a value into a cell - setCellValue()

The function setCellValue() sets a given value for the specified cell. The function can take values of all most used types: boolean, double, String, Date

The function has three notations with different argument sets. They differ in the way the cell is addressed.

void setCellValue(<type> value, int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

void setCellValue( <type> value, String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

void setCellValue( <type> value, String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Setting a formula for a specified cell - setCellFormula()

The function setCellFormula() sets a formula for the specified cell. The formula is passed using the function's argument formula, e.g. "SUM(C4:E4)". If this argument is null then the current formula is removed.

This function only sets the formula string and does not calculate the formula value. To set the 'precalculated' value use setCellValue(...) function. To evaluate the formula, call evaluateFormulas() function.

The function has three notations with different argument sets. They differ in the way the cell is addressed.

void setCellFormula(String formula, int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

void setCellFormula(String formula, String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

void setCellFormula(String formula, String cellName) - the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Writing data from AnyLogic dataset into Excel file - writeDataSet()

The function writeDataSet() writes the given data set to the sheet starting at the given cell. Data is written by rows in 2 columns: for X and Y components.

The function has three notations with different argument sets. They differ in the way the cell is addressed:

int writeDataSet(DataSet dataset, int sheetIndex, int rowIndex, int columnIndex) - the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

int writeDataSet(DataSet dataset, String sheetName, int rowIndex, int columnIndex) - the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

int writeDataSet(DataSet dataset, String cellName)- the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Validation and auxiliary operations

Getting a number of sheets in Excel file - getNumberOfSheets()

int getNumberOfSheets() - Returns the number of spreadsheets in the workbook.

Getting a name of the specified sheet - getSheetName()

String getSheetName(int sheetIndex) - Returns the sheet name for the sheet with specified index sheetIndex.

Checking whether the specified cell exists - cellExists()

The function cellExists() returns true if the cell at the given position exists in the workbook.

The function has three notations with different argument sets. They differ in the way the cell is addressed:

boolean cellExists(int sheetIndex, int rowIndex, int columnIndex)- the cell is specified using 3 numbers (one-based): sheet index, row index, column index.

boolean cellExists(String sheetName, int rowIndex, int columnIndex)- the cell is specified using a sheet name and 2 (one-based) numbers row index and column index.

boolean cellExists(String cellName)- the cell is specified by name in the following format: <sheetname>!<columnname> <rownumber>, e.g. Sheet1!A3 (The sheet name can be skipped, then the first sheet is assumed)

Getting a number of the first row of the sheet - getFirstRowNum()

The function getFirstRowNum() returns number of the first logical row of the sheet (one-based indexing is used).

The function has two notations with different argument sets. They differ in the way the sheet is addressed:

int getFirstRowNum(int sheetIndex) - the sheet is specified by its index.

int getFirstRowNum(String sheetName) - the sheet is specified by its name.

Getting a number of the last row of the sheet - getLastRowNum()

The function getLastRowNum() returns number of the last logical row of the sheet (one-based indexing is used).

The function has two notations with different argument sets. They differ in the way the sheet is addressed:

int getLastRowNum(int sheetIndex) - the sheet is specified by its index.

int getLastRowNum(String sheetName) - the sheet is specified by its name.

Getting a number of the first cell in the row - getFirstCellNum()

The function getFirstCellNum() returns the number of the first cell contained in the specified row. In particular, it returns the number of the column (one-based indexing is used) containing the first logical cell in the row, or 0 if the row does not contain any cells.

The function has two notations with different argument sets. They differ in the way the sheet is addressed:

int getFirstCellNum(int sheetIndex, int rowIndex) - both the sheet and the row are specified by indexes.

int getFirstCellNum(String sheetName, int rowIndex) - the sheet is specified by its name; the row - by its index.

Getting a number of the last cell in the row - getLastCellNum()

The function getLastCellNum() returns the number of the last cell contained in the specified row. In particular, it returns the number of the column (one-based indexing is used) containing the last logical cell in the row, or 0 if the row does not contain any cells.

The function has two notations with different argument sets. They differ in the way the sheet is addressed:

int getLastCellNum(int sheetIndex, int rowIndex) - both the sheet and the row are specified by indexes.

int getLastCellNum(String sheetName, int rowIndex) - the sheet is specified by its name; the row - by its index.

Evaluating formulas in Excel file - evaluateFormulas()

void evaluateFormulas(int sheetIndex, int rowIndex) - Evaluates formulas and saves the results for all the cells containing formulas in this workbook.

The cells are left as formula cells. Be aware that your cells will hold both the formula, and the result. If you want the cell replaced with the result of the formula, use function org.apache.poi.ss.usermodel.Cell.evaluateInCell(org.apache.poi.ss.usermodel.Cell)