AnyLogic AnyLogic
Expand
Font size
  • java.lang.Object
    • com.anylogic.engine.connectivity.ExcelFile
All Implemented Interfaces:
java.io.Serializable

public class ExcelFile
extends java.lang.Object
implements java.io.Serializable
Microsoft® ExcelTM File access utility

This class is a representation of a workbook.

File access
This class have 2 methods for the file access: readFile() and writeFile(). Also it has ability loading another file or saving to other location - see setFileName(String).

Data access
Data may be read from and written to a workbook using various getCell*(...) and setCell*(...) methods. This object allows reading table functions (readTableFunction()) and hyper arrays (with 1 or 2 dimensions, see readHyperArray) from the sheet. Also, data sets may be written using writeDataSet.
New cells may need to be created before writing data: createCell(...) (cell may be checked using cellExists())

Cell access
All cell-access methods have 3 forms of cell location specification:

  • 3 numbers (one-based): sheet index, row index, column index
  • sheet name and 2 (one-based) numbers row index and column index
  • cell name in the following format:
    <sheet name>!<column name><row number>
    The sheet name can be skipped, then the first sheet is assumed.
    Examples (without quotes):
    "Sheet1!A3", "Sheet2!AAB100", "B2"

Model Snapshot serialization notes
This workbook may include all unsaved data (if any) to the model snapshot - this is controlled by parameter saveToSnapshot of constructor.

Author:
AnyLogic North America, LLC https://anylogic.com
See Also:
Serialized Form

Field Summary

Fields 
static int CELL_TYPE_BLANK
Blank Cell type
static int CELL_TYPE_BOOLEAN
Boolean Cell type
static int CELL_TYPE_ERROR
Error Cell type
static int CELL_TYPE_FORMULA
Formula Cell type
static int CELL_TYPE_NUMERIC
Numeric Cell type
static int CELL_TYPE_STRING
String Cell type
Modifier and Type Field Description

Constructor Summary

Constructors 
ExcelFile​(Presentable owner, java.lang.String packagePrefix, java.lang.String fileName, boolean saveToSnapshot)
Creates new ExcelTM file accessor
ExcelFile​(java.lang.String fileName, boolean saveToSnapshot)
Deprecated.
this constructor is obsolete and will be removed in future
Constructor Description

Method Summary

All Methods Instance Methods Concrete Methods 
boolean cellExists​(int sheetIndex, int rowIndex, int columnIndex)
Returns true if the cell at the given position exists in the workbook
boolean cellExists​(java.lang.String cellName)
Returns true if the cell at the given position exists in the workbook
boolean cellExists​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns true if the cell at the given position exists in the workbook
void clearCell​(int sheetIndex, int rowIndex, int columnIndex)
Clears type and value of the cell.
void close()
Closes the workbook.
void createCell​(int sheetIndex, int rowIndex, int columnIndex)
Creates new cell at the given position.
void createCell​(java.lang.String cellName)
Creates new cell at the given position.
void createCell​(java.lang.String sheetName, int rowIndex, int columnIndex)
Creates new cell at the given position.
void evaluateFormulas()
Evaluates formulas and saves the results for all the cells containing formulas in this workbook.
The cells are left as formula cells.
boolean getCellBooleanValue​(int sheetIndex, int rowIndex, int columnIndex)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
boolean getCellBooleanValue​(java.lang.String cellName)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
boolean getCellBooleanValue​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
java.util.Date getCellDateValue​(int sheetIndex, int rowIndex, int columnIndex)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null.
java.util.Date getCellDateValue​(java.lang.String cellName)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null.
java.util.Date getCellDateValue​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null.
byte getCellErrorValue​(int sheetIndex, int rowIndex, int columnIndex)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
byte getCellErrorValue​(java.lang.String cellName)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
byte getCellErrorValue​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
java.lang.String getCellFormula​(int sheetIndex, int rowIndex, int columnIndex)
Return a formula for the cell, for example, SUM(C4:E4)
java.lang.String getCellFormula​(java.lang.String cellName)
Return a formula for the cell, for example, SUM(C4:E4)
java.lang.String getCellFormula​(java.lang.String sheetName, int rowIndex, int columnIndex)
Return a formula for the cell, for example, SUM(C4:E4)
int getCellFormulaType​(int sheetIndex, int rowIndex, int columnIndex)
Returns the type of the formula cell.
Only valid for formula cells.
int getCellFormulaType​(java.lang.String cellName)
Returns the type of the formula cell.
Only valid for formula cells.
int getCellFormulaType​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the type of the formula cell.
Only valid for formula cells.
double getCellNumericValue​(int sheetIndex, int rowIndex, int columnIndex)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
double getCellNumericValue​(java.lang.String cellName)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
double getCellNumericValue​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
java.lang.String getCellStringValue​(int sheetIndex, int rowIndex, int columnIndex)
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
java.lang.String getCellStringValue​(java.lang.String cellName)
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
java.lang.String getCellStringValue​(java.lang.String sheetName, int rowIndex, int columnIndex)
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
int getCellType​(int sheetIndex, int rowIndex, int columnIndex)
Returns the cell type (numeric, formula, string...)
int getCellType​(java.lang.String cellName)
Returns the cell type (numeric, formula, string...)
int getCellType​(java.lang.String sheetName, int rowIndex, int columnIndex)
Returns the cell type (numeric, formula, string...)
int getFirstCellNum​(int sheetIndex, int rowIndex)
Returns the number of the first cell contained in this row (the 1-based column number of the first cell).
int getFirstCellNum​(java.lang.String sheetName, int rowIndex)
Returns the number of the first cell contained in this row (the 1-based column number of the first cell).
int getFirstRowNum​(int sheetIndex)
Returns the first row on the sheet
int getFirstRowNum​(java.lang.String sheetName)
Returns the first row on the sheet
int getLastCellNum​(int sheetIndex, int rowIndex)
Returns the index of the last cell contained in this row (the 1-based column number of the last cell).
int getLastCellNum​(java.lang.String sheetName, int rowIndex)
Returns the index of the last cell contained in this row (the 1-based column number of the last cell).
int getLastRowNum​(int sheetIndex)
Returns the number of the last row on the sheet.
Owing to idiosyncrasies in the excel file format, if the result of calling this method is one, you can't tell if that means there are zero rows on the sheet, or one at the first position.
For that case, additionally call org.apache.poi.ss.usermodel.Sheet.getPhysicalNumberOfRows() to find out if there is a row at position zero or not.
int getLastRowNum​(java.lang.String sheetName)
Returns the number of the last row on the sheet.
Owing to idiosyncrasies in the excel file format, if the result of calling this method is one, you can't tell if that means there are zero rows on the sheet, or one at the first position.
For that case, additionally call org.apache.poi.ss.usermodel.Sheet.getPhysicalNumberOfRows() to find out if there is a row at position zero or not.
int getNumberOfSheets()
Returns the number of spreadsheets in the workbook
int getSheetIndex​(java.lang.String sheetName)
Returns the index of the sheet with the given name.
Returns 1 if sheetName is null
java.lang.String getSheetName​(int sheetIndex)
Returns the sheet name for the specified index
org.apache.poi.ss.usermodel.Workbook getWorkbook()
Returns internal class of the workbook, null if file isn't not loaded.
Please note that if you change workbook using API of returned object and want to save workbook to a file, you need to call setChanged()
boolean isLoaded()
Returns true if workbook is loaded from file.
void readFile()
Loads the workbook from the file.
Warning!
void readHyperArray​(HyperArray array, int sheetIndex, int rowIndex, int columnIndex, boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
void readHyperArray​(HyperArray array, java.lang.String cellName, boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
void readHyperArray​(HyperArray array, java.lang.String sheetName, int rowIndex, int columnIndex, boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
int readTableFunction​(TableFunction tableFunction, int sheetIndex, int rowIndex, int columnIndex, int length)
Reads the table function from the sheet starting at the row with index rowIndex:
- arguments are read from column at columnIndex
- values are read from column at columnIndex + 1

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
int readTableFunction​(TableFunction tableFunction, java.lang.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

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
int readTableFunction​(TableFunction tableFunction, java.lang.String sheetName, int rowIndex, int columnIndex, int length)
Reads the table function from the sheet starting at the row with index rowIndex:
- arguments are read from column at columnIndex
- values are read from column at columnIndex + 1

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
void setCellFormula​(java.lang.String formula, int sheetIndex, int rowIndex, int columnIndex)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value.
void setCellFormula​(java.lang.String formula, java.lang.String cellName)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value.
void setCellFormula​(java.lang.String formula, java.lang.String sheetName, int rowIndex, int columnIndex)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value.
void setCellValue​(boolean value, int sheetIndex, int rowIndex, int columnIndex)
Sets a boolean value for the cell.
void setCellValue​(boolean value, java.lang.String cellName)
Sets a boolean value for the cell.
void setCellValue​(boolean value, java.lang.String sheetName, int rowIndex, int columnIndex)
Sets a boolean value for the cell.
void setCellValue​(double value, int sheetIndex, int rowIndex, int columnIndex)
Sets a numeric value for the cell.
void setCellValue​(double value, java.lang.String cellName)
Sets a numeric value for the cell.
void setCellValue​(double value, java.lang.String sheetName, int rowIndex, int columnIndex)
Sets a numeric value for the cell.
void setCellValue​(java.lang.String value, int sheetIndex, int rowIndex, int columnIndex)
Sets a string value for the cell.
void setCellValue​(java.lang.String value, java.lang.String cellName)
Sets a string value for the cell.
void setCellValue​(java.lang.String value, java.lang.String sheetName, int rowIndex, int columnIndex)
Sets a string value for the cell.
void setCellValue​(java.util.Date value, int sheetIndex, int rowIndex, int columnIndex)
Sets a date value for the cell.
void setCellValue​(java.util.Date value, java.lang.String cellName)
Sets a date value for the cell.
void setCellValue​(java.util.Date value, java.lang.String sheetName, int rowIndex, int columnIndex)
Sets a date value for the cell.
void setChanged()
This method may be used to tell AnyLogic that this workbook has unsaved changes and should be written on writeFile() or included to the model snapshot if it has such setting.
This method should be used when you manually change the workbook via getWorkbook().
All setCell*() etc. methods automatically mark workbook as 'changed'
void setFileName​(java.lang.String fileName)
Switches this object to work with another file.
Method does nothing if the given file name is the same as at the current workbook.
Any loaded or unsaved data (if any) in this workbook remains as is until you manually call readFile().
Method may be used for loading workbook from another file as well as for saving changed workbook to some other location.
java.lang.String toString()  
int writeDataSet​(DataSet dataSet, int sheetIndex, int rowIndex, int columnIndex)
Writes the given data set to the sheet starting at the given cell.
int writeDataSet​(DataSet dataSet, java.lang.String cellName)
Writes the given data set to the sheet starting at the given cell.
int writeDataSet​(DataSet dataSet, java.lang.String sheetName, int rowIndex, int columnIndex)
Writes the given data set to the sheet starting at the given cell.
void writeFile()
Stores the current workbook to the file.
Workbook should be loaded.
Unchanged workbooks (workbooks without any unsaved modifications) aren't saved.
For saving to another location please call setFileName(String) before this method.
void writeFile​(boolean force)
Stores the current workbook to the file.
Workbook should be loaded.
For saving to another location please call setFileName(String) before this method.
Modifier and Type Method Description

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

CELL_TYPE_BLANK

public static final int CELL_TYPE_BLANK
Blank Cell type
See Also:
Constant Field Values

CELL_TYPE_BOOLEAN

public static final int CELL_TYPE_BOOLEAN
Boolean Cell type
See Also:
Constant Field Values

CELL_TYPE_ERROR

public static final int CELL_TYPE_ERROR
Error Cell type
See Also:
Constant Field Values

CELL_TYPE_FORMULA

public static final int CELL_TYPE_FORMULA
Formula Cell type
See Also:
Constant Field Values

CELL_TYPE_NUMERIC

public static final int CELL_TYPE_NUMERIC
Numeric Cell type
See Also:
Constant Field Values

CELL_TYPE_STRING

public static final int CELL_TYPE_STRING
String Cell type
See Also:
Constant Field Values

Constructor Detail

ExcelFile

@Deprecated
public ExcelFile​(java.lang.String fileName,
                 boolean saveToSnapshot)
Deprecated.
this constructor is obsolete and will be removed in future

ExcelFile

public ExcelFile​(Presentable owner,
                 java.lang.String packagePrefix,
                 java.lang.String fileName,
                 boolean saveToSnapshot)
Creates new ExcelTM file accessor
Parameters:
owner - the presentable object owning this shape
packagePrefix - the package name of original agent where this image is defined, formatted using '/' characters; with '/' character at the beginning and at the end
fileName - the name of the file
saveToSnapshot -
  • true: Store all unsaved data (in any) in the workbook to the model snapshot file
    If the workbook is changed during model execution and isn't saved to the file, it is stored to the model snapshot - this mode is turned
  • false: When snapshot is loaded, this class reads the original ExcelTM file. If the file wasn't initially loaded, it isn't loaded during restoring from snapshot.

Method Detail

setFileName

public void setFileName​(java.lang.String fileName)
Switches this object to work with another file.
Method does nothing if the given file name is the same as at the current workbook.
Any loaded or unsaved data (if any) in this workbook remains as is until you manually call readFile().
Method may be used for loading workbook from another file as well as for saving changed workbook to some other location.
Parameters:
fileName - the new file name

readFile

public void readFile()
Loads the workbook from the file.
Warning! All unsaved data (if any) in the workbook is lost after this method is called.
See Also:
writeFile()

isLoaded

public boolean isLoaded()
Returns true if workbook is loaded from file.
Returns:
true if workbook is loaded from file
See Also:
readFile()

close

public void close()
Closes the workbook. This function may be useful when doing some log output (huge log workbook may be immediately closed after saving to file - to release memory).
After this function is called, various get***() functions will throw error, until someone calls readFile()

getCellType

public int getCellType​(int sheetIndex,
                       int rowIndex,
                       int columnIndex)
Returns the cell type (numeric, formula, string...)
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the cell type - one of CELL_TYPE_* constants

getCellType

public int getCellType​(java.lang.String sheetName,
                       int rowIndex,
                       int columnIndex)
Returns the cell type (numeric, formula, string...)
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the cell type - one of CELL_TYPE_* constants

getCellBooleanValue

public boolean getCellBooleanValue​(int sheetIndex,
                                   int rowIndex,
                                   int columnIndex)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a boolean

getCellNumericValue

public double getCellNumericValue​(int sheetIndex,
                                  int rowIndex,
                                  int columnIndex)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a number

getCellStringValue

public java.lang.String getCellStringValue​(int sheetIndex,
                                           int rowIndex,
                                           int columnIndex)
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
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a string

getCellFormula

public java.lang.String getCellFormula​(int sheetIndex,
                                       int rowIndex,
                                       int columnIndex)
Return a formula for the cell, for example, SUM(C4:E4)
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
formula for the cell

getCellErrorValue

public byte getCellErrorValue​(int sheetIndex,
                              int rowIndex,
                              int columnIndex)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as an error code

getCellDateValue

public java.util.Date getCellDateValue​(int sheetIndex,
                                       int rowIndex,
                                       int columnIndex)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null. See org.apache.poi.ss.usermodel.DataFormatter class for formatting this date into a string similar to how excel does.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a Date

getWorkbook

public org.apache.poi.ss.usermodel.Workbook getWorkbook()
Returns internal class of the workbook, null if file isn't not loaded.
Please note that if you change workbook using API of returned object and want to save workbook to a file, you need to call setChanged()
Returns:
internal class of the workbook, null if file isn't not loaded

getCellBooleanValue

public boolean getCellBooleanValue​(java.lang.String sheetName,
                                   int rowIndex,
                                   int columnIndex)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a boolean

getCellNumericValue

public double getCellNumericValue​(java.lang.String sheetName,
                                  int rowIndex,
                                  int columnIndex)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a number

getCellStringValue

public java.lang.String getCellStringValue​(java.lang.String sheetName,
                                           int rowIndex,
                                           int columnIndex)
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
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a string

getCellFormula

public java.lang.String getCellFormula​(java.lang.String sheetName,
                                       int rowIndex,
                                       int columnIndex)
Return a formula for the cell, for example, SUM(C4:E4)
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
formula for the cell

getCellErrorValue

public byte getCellErrorValue​(java.lang.String sheetName,
                              int rowIndex,
                              int columnIndex)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as an error code

getCellDateValue

public java.util.Date getCellDateValue​(java.lang.String sheetName,
                                       int rowIndex,
                                       int columnIndex)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null. See org.apache.poi.ss.usermodel.DataFormatter class for formatting this date into a string similar to how excel does.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the value of the cell as a Date

getCellType

public int getCellType​(java.lang.String cellName)
Returns the cell type (numeric, formula, string...)
Parameters:
cellName - the full name of the cell
Returns:
the cell type - one of CELL_TYPE_* constants

getCellBooleanValue

public boolean getCellBooleanValue​(java.lang.String cellName)
Returns the value of the cell as a boolean.
For strings, numbers, and errors, throws an exception.
For blank cells returns false.
Parameters:
cellName - the full name of the cell
Returns:
the value of the cell as a boolean

getCellNumericValue

public double getCellNumericValue​(java.lang.String cellName)
Returns the value of the cell as a number.
For strings throws an exception.
For blank cells we return a 0.
Parameters:
cellName - the full name of the cell
Returns:
the value of the cell as a number

getCellStringValue

public java.lang.String getCellStringValue​(java.lang.String cellName)
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
Parameters:
cellName - the full name of the cell
Returns:
the value of the cell as a string

getCellFormula

public java.lang.String getCellFormula​(java.lang.String cellName)
Return a formula for the cell, for example, SUM(C4:E4)
Parameters:
cellName - the full name of the cell
Returns:
formula for the cell

getCellErrorValue

public byte getCellErrorValue​(java.lang.String cellName)
Returns the value of the cell as an error code.
For strings, numbers, and booleans, throws an exception.
For blank cells returns a 0.
Parameters:
cellName - the full name of the cell
Returns:
the value of the cell as an error code

getCellDateValue

public java.util.Date getCellDateValue​(java.lang.String cellName)
Returns the value of the cell as a date.
For strings throws an exception.
For blank cells returns null. See org.apache.poi.ss.usermodel.DataFormatter class for formatting this date into a string similar to how excel does.
Parameters:
cellName - the full name of the cell
Returns:
the value of the cell as a Date

getNumberOfSheets

public int getNumberOfSheets()
Returns the number of spreadsheets in the workbook
Returns:
the number of spreadsheets in the workbook

getLastRowNum

public int getLastRowNum​(int sheetIndex)
Returns the number of the last row on the sheet.
Owing to idiosyncrasies in the excel file format, if the result of calling this method is one, you can't tell if that means there are zero rows on the sheet, or one at the first position.
For that case, additionally call org.apache.poi.ss.usermodel.Sheet.getPhysicalNumberOfRows() to find out if there is a row at position zero or not.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
Returns:
the number of the last row contained in this sheet, one-based.

getLastRowNum

public int getLastRowNum​(java.lang.String sheetName)
Returns the number of the last row on the sheet.
Owing to idiosyncrasies in the excel file format, if the result of calling this method is one, you can't tell if that means there are zero rows on the sheet, or one at the first position.
For that case, additionally call org.apache.poi.ss.usermodel.Sheet.getPhysicalNumberOfRows() to find out if there is a row at position zero or not.
Parameters:
sheetName - the name of the sheet
Returns:
the number of the last row contained in this sheet, one-based.

getFirstRowNum

public int getFirstRowNum​(int sheetIndex)
Returns the first row on the sheet
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
Returns:
the number of the first logical row on the sheet, one-based

getFirstRowNum

public int getFirstRowNum​(java.lang.String sheetName)
Returns the first row on the sheet
Parameters:
sheetName - the name of the sheet
Returns:
the number of the first logical row on the sheet, one-based

getFirstCellNum

public int getFirstCellNum​(int sheetIndex,
                           int rowIndex)
Returns the number of the first cell contained in this row (the 1-based column number of the first cell).
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
Returns:
the number representing the first logical cell in the row (one-based), or 0 if the row does not contain any cells.

getFirstCellNum

public int getFirstCellNum​(java.lang.String sheetName,
                           int rowIndex)
Returns the number of the first cell contained in this row (the 1-based column number of the first cell).
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
Returns:
the number representing the first logical cell in the row (one-based), or 0 if the row does not contain any cells.

getLastCellNum

public int getLastCellNum​(int sheetIndex,
                          int rowIndex)
Returns the index of the last cell contained in this row (the 1-based column number of the last cell).
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
Returns:
short representing the last logical cell in the row (one-based), or 0 if the row does not contain any cells.

getLastCellNum

public int getLastCellNum​(java.lang.String sheetName,
                          int rowIndex)
Returns the index of the last cell contained in this row (the 1-based column number of the last cell).
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
Returns:
short representing the last logical cell in the row (one-based), or 0 if the row does not contain any cells.

cellExists

public boolean cellExists​(int sheetIndex,
                          int rowIndex,
                          int columnIndex)
Returns true if the cell at the given position exists in the workbook
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
true if the cell at the given position exists in the workbook, false otherwise

cellExists

public boolean cellExists​(java.lang.String sheetName,
                          int rowIndex,
                          int columnIndex)
Returns true if the cell at the given position exists in the workbook
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
true if the cell at the given position exists in the workbook, false otherwise

cellExists

public boolean cellExists​(java.lang.String cellName)
Returns true if the cell at the given position exists in the workbook
Parameters:
cellName - the full name of the cell
Returns:
true if the cell at the given position exists in the workbook, false otherwise

clearCell

public void clearCell​(int sheetIndex,
                      int rowIndex,
                      int columnIndex)
Clears type and value of the cell. If cell doesn't exist do nothing.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

readTableFunction

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

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
Parameters:
tableFunction - the table function to fill
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
length - the number of table function points to read
Returns:
the actual number of table function points read from the sheet

readTableFunction

public int readTableFunction​(TableFunction tableFunction,
                             java.lang.String sheetName,
                             int rowIndex,
                             int columnIndex,
                             int length)
Reads the table function from the sheet starting at the row with index rowIndex:
- arguments are read from column at columnIndex
- values are read from column at columnIndex + 1

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
Parameters:
tableFunction - the table function to fill
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
length - the number of table function points to read
Returns:
the actual number of table function points read from the sheet

readTableFunction

public int readTableFunction​(TableFunction tableFunction,
                             java.lang.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

If there is not enough data in the sheet to fill in the length, then table function gets less points.
Method returns the actual number of table function points read from the sheet.
Parameters:
tableFunction - the table function to fill
cellName - the full name of the cell
length - the number of table function points to read
Returns:
the actual number of table function points read from the sheet

getCellFormulaType

public int getCellFormulaType​(int sheetIndex,
                              int rowIndex,
                              int columnIndex)
Returns the type of the formula cell.
Only valid for formula cells.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
one of (CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR) depending on the cached value of the formula

getCellFormulaType

public int getCellFormulaType​(java.lang.String sheetName,
                              int rowIndex,
                              int columnIndex)
Returns the type of the formula cell.
Only valid for formula cells.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
one of (CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR) depending on the cached value of the formula

getCellFormulaType

public int getCellFormulaType​(java.lang.String cellName)
Returns the type of the formula cell.
Only valid for formula cells.
Parameters:
cellName - the full name of the cell
Returns:
one of (CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR) depending on the cached value of the formula

getSheetName

public java.lang.String getSheetName​(int sheetIndex)
Returns the sheet name for the specified index
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
Returns:
the sheet name for the specified index

getSheetIndex

public int getSheetIndex​(java.lang.String sheetName)
Returns the index of the sheet with the given name.
Returns 1 if sheetName is null
Parameters:
sheetName - the sheet name
Returns:
the index of the spreadsheet with the specified name, 1-based

readHyperArray

public void readHyperArray​(HyperArray array,
                           int sheetIndex,
                           int rowIndex,
                           int columnIndex,
                           boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
Parameters:
array - the HyperArray to write data to, should have 1 or 2 dimensions.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
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)

readHyperArray

public void readHyperArray​(HyperArray array,
                           java.lang.String sheetName,
                           int rowIndex,
                           int columnIndex,
                           boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
Parameters:
array - the HyperArray to write data to, should have 1 or 2 dimensions.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
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)

readHyperArray

public void readHyperArray​(HyperArray array,
                           java.lang.String cellName,
                           boolean dim1AcrossRows)
Reads one- or two-dimensional HyperArray data from the sheet starting at the given cell.
Parameters:
array - the HyperArray to write data to, should have 1 or 2 dimensions.
cellName - the full name of the cell
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)

writeFile

public void writeFile()
Stores the current workbook to the file.
Workbook should be loaded.
Unchanged workbooks (workbooks without any unsaved modifications) aren't saved.
For saving to another location please call setFileName(String) before this method.
See Also:
writeFile(boolean), readFile(), setFileName(String)

writeFile

public void writeFile​(boolean force)
Stores the current workbook to the file.
Workbook should be loaded.
For saving to another location please call setFileName(String) before this method.
Parameters:
force - if true the workbook will be saved to the file even if it is unchanged
See Also:
writeFile(), readFile(), setFileName(String), setChanged()

setChanged

public void setChanged()
This method may be used to tell AnyLogic that this workbook has unsaved changes and should be written on writeFile() or included to the model snapshot if it has such setting.
This method should be used when you manually change the workbook via getWorkbook().
All setCell*() etc. methods automatically mark workbook as 'changed'
See Also:
writeFile(), getWorkbook()

createCell

public void createCell​(int sheetIndex,
                       int rowIndex,
                       int columnIndex)
Creates new cell at the given position.
Parameters:
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

createCell

public void createCell​(java.lang.String sheetName,
                       int rowIndex,
                       int columnIndex)
Creates new cell at the given position.
Parameters:
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

createCell

public void createCell​(java.lang.String cellName)
Creates new cell at the given position.
Parameters:
cellName - the full name of the cell

setCellValue

public void setCellValue​(boolean value,
                         int sheetIndex,
                         int rowIndex,
                         int columnIndex)
Sets a boolean value for the cell.
Parameters:
value - the boolean value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than boolean are changed to boolean cells.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(double value,
                         int sheetIndex,
                         int rowIndex,
                         int columnIndex)
Sets a numeric value for the cell.
Parameters:
value - the numeric value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(java.lang.String value,
                         int sheetIndex,
                         int rowIndex,
                         int columnIndex)
Sets a string value for the cell.
Parameters:
value - the value to set this cell to. For formula cells the formula string is set. Cells with types other than string are changed to string cells. If value is null then cell is changed to a Blank cell.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(java.util.Date value,
                         int sheetIndex,
                         int rowIndex,
                         int columnIndex)
Sets a date value for the cell.
Parameters:
value - the date value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellFormula

public void setCellFormula​(java.lang.String formula,
                           int sheetIndex,
                           int rowIndex,
                           int columnIndex)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value. To set the 'precalculated' value use setCellValue(...) method
Parameters:
formula - the formula to set, e.g. "SUM(C4:E4)". If this argument is null then the current formula is removed.
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(boolean value,
                         java.lang.String sheetName,
                         int rowIndex,
                         int columnIndex)
Sets a boolean value for the cell.
Parameters:
value - the boolean value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than boolean are changed to boolean cells.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(double value,
                         java.lang.String sheetName,
                         int rowIndex,
                         int columnIndex)
Sets a numeric value for the cell.
Parameters:
value - the numeric value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(java.lang.String value,
                         java.lang.String sheetName,
                         int rowIndex,
                         int columnIndex)
Sets a string value for the cell.
Parameters:
value - the value to set this cell to. For formula cells the formula string is set. Cells with types other than string are changed to string cells. If value is null then cell is changed to a Blank cell.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(java.util.Date value,
                         java.lang.String sheetName,
                         int rowIndex,
                         int columnIndex)
Sets a date value for the cell.
Parameters:
value - the date value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellFormula

public void setCellFormula​(java.lang.String formula,
                           java.lang.String sheetName,
                           int rowIndex,
                           int columnIndex)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value. To set the 'precalculated' value use setCellValue(...) method
Parameters:
formula - the formula to set, e.g. "SUM(C4:E4)". If this argument is null then the current formula is removed.
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based

setCellValue

public void setCellValue​(boolean value,
                         java.lang.String cellName)
Sets a boolean value for the cell.
Parameters:
value - the boolean value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than boolean are changed to boolean cells.
cellName - the full name of the cell

setCellValue

public void setCellValue​(double value,
                         java.lang.String cellName)
Sets a numeric value for the cell.
Parameters:
value - the numeric value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
cellName - the full name of the cell

setCellValue

public void setCellValue​(java.lang.String value,
                         java.lang.String cellName)
Sets a string value for the cell.
Parameters:
value - the value to set this cell to. For formula cells the formula string is set. Cells with types other than string are changed to string cells. If value is null then cell is changed to a Blank cell.
cellName - the full name of the cell

setCellValue

public void setCellValue​(java.util.Date value,
                         java.lang.String cellName)
Sets a date value for the cell.
Parameters:
value - the date value to set this cell to. For formula cells the 'precalculated' value is set. Cells with types other than numeric are changed to numeric cells.
cellName - the full name of the cell

setCellFormula

public void setCellFormula​(java.lang.String formula,
                           java.lang.String cellName)
Sets formula for this cell.
Note, this method only sets the formula string and does not calculate the formula value. To set the 'precalculated' value use setCellValue(...) method
Parameters:
formula - the formula to set, e.g. "SUM(C4:E4)". If this argument is null then the current formula is removed.
cellName - the full name of the cell

toString

public java.lang.String toString()
Overrides:
toString in class java.lang.Object

writeDataSet

public int writeDataSet​(DataSet dataSet,
                        int sheetIndex,
                        int rowIndex,
                        int columnIndex)
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.
Parameters:
dataSet - the data set to store in the sheet
sheetIndex - the index of the spreadsheet, 1-based
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the size of the given dataset

writeDataSet

public int writeDataSet​(DataSet dataSet,
                        java.lang.String sheetName,
                        int rowIndex,
                        int columnIndex)
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.
Parameters:
dataSet - the data set to store in the sheet
sheetName - the name of the sheet
rowIndex - the index of the cell row, 1-based
columnIndex - the index of the cell column, 1-based
Returns:
the size of the given dataset

writeDataSet

public int writeDataSet​(DataSet dataSet,
                        java.lang.String cellName)
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.
Parameters:
dataSet - the data set to store in the sheet
cellName - the full name of the cell
Returns:
the size of the given dataset

evaluateFormulas

public void evaluateFormulas()
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 method org.apache.poi.ss.usermodel.Cell.evaluateInCell(org.apache.poi.ss.usermodel.Cell)
How can we improve this article?