The Excel File connectivity tool provides easy platform-independent access to MS Excel (.xls, .xlsx) files within AnyLogic models. With this object you can:
- Read Excel files and explore their contents, Demo model: Reading and Writing Excel Files Open the model page in AnyLogic Cloud. There you can run the model or download it (by clicking Model source files).
- Read individual cell values and formulas of various types, Demo model: Reading Data of Various Types from Cells in Excel Open the model page in AnyLogic Cloud. There you can run the model or download it (by clicking Model source files).
- 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, Demo model: Displaying the Model Output as a Chart in Excel Open the model page in AnyLogic Cloud. There you can run the model or download it (by clicking Model source files).
- Save modified spreadsheets.
To add the Excel file access tool
-
Drag the Excel File element from the Connectivity palette onto the graphical diagram.
The system dialog will appear, allowing you to select a file on your computer. Browse to the file and select it as usual.
Once selected, the source file will automatically appear in the model’s Connectivity folder in the elements view. - In the Properties view, change the Name of the element. This name will be used to identify and access this object from the code.
- Go to the Expert section. Leave the Load on model startup check box selected. This will automatically load the workbook from the file on model startup and allow you to work with this Excel file.
- 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 writeFile() function of the access tool to commit all your changes to the Excel file: this will be done automatically at model exit.
To work with Excel files, use the API of the ExcelFile object. The most useful of API functions are listed below, in the API section. Most of them have several different argument sets and notations. Look for the operation you need, then expand the appropriate section and find the function that best suits your needs.
- General
-
Name — The name of the element. It is used to identify and access it from the code.
Show name — If selected, the name of the element is displayed in the presentation diagram.
Ignore — If selected, the element is excluded from the model.
File — The XLS or XLSX file from which this Excel file element originates. Click in the field to select a file from those already attached to the model, or click the Browse icon to the right to upload a new file from your computer.
- Expert
-
Load on model startup — If selected, AnyLogic automatically loads the workbook from the file on model startup and allows you to work with the Excel file. Otherwise, use the readFile() function before performing any operations with the Excel file.
Save on model termination — If selected, AnyLogic automatically saves the changes made to the Excel file when you exit the running model. Otherwise, use the writeFile() function to commit your changes to an Excel file.
Visible — The visibility of the element. When selected, the element is visible during model runtime.
- Reading Excel file: readFile()
-
Function Description void readFile() Loads the workbook from the file. All unsaved data (if any) in the workbook is lost after this function is called. - Getting the type of the specified cell: getCellType()
-
The getCellType() function returns the cell type (numeric, formula, string, …).
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description CellType getCellType(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. CellType getCellType(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. CellType getCellType(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading a Boolean value from a cell: getCellBooleanValue()
-
The getCellBooleanValue() function returns the value of the cell as a boolean. For strings, numbers, and errors, throws an exception. For empty cells, returns false.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description boolean getCellBooleanValue(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. boolean getCellBooleanValue(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. boolean getCellBooleanValue(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading a numeric value from a cell: getCellNumericValue()
-
The getCellNumericValue() function returns the value of the cell as a number. For strings, it throws an exception. For empty cells, returns 0.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description double getCellNumericValue(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. double getCellNumericValue(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. double getCellNumericValue(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading a string value from a cell: getCellStringValue()
-
The getCellStringValue() function returns the value of the cell as a string. For numeric cells, it throws an exception. For empty cells, returns an empty string. For formula cells that are not string formulas, returns an empty string.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description String getCellStringValue(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. String getCellStringValue(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. String getCellStringValue(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading a Date value from a cell: getCellDateValue()
-
The getCellDateValue() function returns the value of the cell as a date. For strings, it throws an exception. For empty cells, returns null.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description Date getCellDateValue(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. Date getCellDateValue(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. Date getCellDateValue(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Getting the formula defined for a specified cell: getCellFormula()
-
The getCellFormula() function returns the formula for the cell, for example: SUM(C4:E4).
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description String getCellFormula(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. String getCellFormula(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. String getCellFormula(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Getting the type of the formula defined for a specified cell: getCellFormulaType()
-
The getCellFormulaType() function returns the type of the formula cell. Valid for formula cells only.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description CellType getCellFormulaType(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. CellType getCellFormulaType(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. CellType getCellFormulaType(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading the value of the cell as an error code: getCellErrorValue()
-
The getCellErrorValue() function returns the value of the cell as an error code. For strings throws an exception. For empty cells, returns null.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description byte getCellErrorValue(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. byte getCellErrorValue(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. byte getCellErrorValue(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Reading data from the Excel file into AnyLogic table function: readTableFunction()
-
The readTableFunction() function reads the data from the Excel file into the specified table function.
If there is not enough data in the sheet to fill in the length, then the table function gets fewer points.
The 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 3 notations with different argument sets. They differ in the way they address the cell.Function Description 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 the index rowIndex: - The arguments are read from the column at columnIndex,
- The values are read from the column at columnIndex 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 the index rowIndex: - The arguments are read from the column at columnIndex,
- The values are read from the column at columnIndex 1.
int readTableFunction(TableFunction tableFunction, String cellName, int length) Reads the table function from the sheet starting at the row of the given cell: - The arguments are read from the column of the given cell
- The values are read from the column next to the given cell
The sheet name can be omitted, in which case the first sheet is used. - Reading data from Excel file into AnyLogic hyperarray: readHyperArray()
-
The readHyperArray() function 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 (that is, in this mode data for one-dimensional array is loaded from the sheet column).
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description 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: <sheet name>!<column name><rownumber>, for example, Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used.
- Saving changes into Excel file: writeFile()
-
Function Description void writeFile() Stores the current workbook to the file. The workbook should be loaded. Unchanged workbooks (workbooks with unsaved data) are not saved. To save to a different location, call the setFileName(String fileName) function before writeFile(). - Creating a cell with specified index: createCell()
-
The createCell() function creates a new cell at the given position.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description void createCell(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. void createCell(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. void createCell(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Writing a value into a cell: setCellValue()
-
The setCellValue() function sets a given value for the specified cell. The function can take values of all the most common types: boolean, double, String, Date.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description void setCellValue(<type> value, int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. void setCellValue(<type> value, String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. void setCellValue(<type> value, String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Setting a formula for a specified cell: setCellFormula()
-
The setCellFormula() function sets a formula for the specified cell. The formula is passed using the function’s argument formula, that is, "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 evaluate the formula, call evaluateFormulas() function.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description void setCellFormula(String formula, int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. void setCellFormula(String formula, String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. void setCellFormula(String formula, String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Writing data from AnyLogic dataset into Excel file: writeDataSet()
-
The writeDataSet() function 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 3 notations with different argument sets. They differ in the way they address the cell. The function returns the size of the data set.Function Description int writeDataSet(DataSet dataset, int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. int writeDataSet(DataSet dataset, String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. int writeDataSet(DataSet dataset, String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used.
- Getting a number of sheets in Excel file: getNumberOfSheets()
-
Function Description int getNumberOfSheets() Returns the number of spreadsheets in the workbook. - Getting a name of the specified sheet: getSheetName()
-
Function Description String getSheetName(int sheetIndex) Returns the sheet name for the sheet with the specified index sheetIndex. - Checking whether the specified cell exists: cellExists()
-
The cellExists() function returns true if the cell at the given position exists in the workbook.
The function has 3 notations with different argument sets. They differ in the way they address the cell.Function Description boolean cellExists(int sheetIndex, int rowIndex, int columnIndex) The cell is specified by 3 numbers (one-based): sheet index, row index, column index. boolean cellExists(String sheetName, int rowIndex, int columnIndex) The cell is specified by a sheet name and 2 (one-based) numbers, row index, and column index. boolean cellExists(String cellName) The cell is specified by its name in the following format: <sheet name>!<column name> <rownumber>, for example:Sheet1!A3.
The sheet name can be omitted, in which case the first sheet is used. - Getting a number of the first row of the sheet: getFirstRowNum()
-
The getFirstRowNum() function returns the 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.Function Description 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 getLastRowNum() function returns the 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.Function Description 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 getFirstCellNum() function 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 and row are addressed.Function Description int getFirstCellNum(int sheetIndex, int rowIndex) Both the sheet and the row are specified by indices. 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 getLastCellNum() function 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.Function Description int getLastCellNum(int sheetIndex, int rowIndex) Both the sheet and the row are specified by indices. int getLastCellNum(String sheetName, int rowIndex) The sheet is specified by its name; the row — by its index. - Evaluating formulas in Excel file: evaluateFormulas()
-
Function Description void evaluateFormulas() Evaluates formulas and saves the results for all the cells containing formulas in this workbook.
The cells remain as formula cells. Note that your cells will hold both the formula, and the result. If you want the cell to be replaced with the result of the formula, use the following function: org.apache.poi.ss.usermodel.Cell.evaluateInCell(org.apache.poi.ss.usermodel.Cell)
-
How can we improve this article?
-