com.anylogic.engine.connectivity.ExcelFile

All Implemented Interfaces:: Serializable

public class ExcelFile
extends Object
implements Serializable

Microsoft® Excel^TM 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

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

Constructor Summary

Constructor	Description
`ExcelFile(Presentable owner, String packagePrefix, String fileName, boolean saveToSnapshot)`	Creates new Excel^TM file accessor
`ExcelFile(String fileName, boolean saveToSnapshot)`	Deprecated. this constructor is obsolete and will be removed in future

Method Summary

Modifier and Type	Method	Description
`boolean`	`cellExists(int sheetIndex, int rowIndex, int columnIndex)`	Returns `true` if the cell at the given position exists in the workbook
`boolean`	`cellExists(String cellName)`	Returns `true` if the cell at the given position exists in the workbook
`boolean`	`cellExists(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(String cellName)`	Creates new cell at the given position.
`void`	`createCell(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(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(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`.
`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`.
`Date`	`getCellDateValue(String cellName)`	Returns the value of the cell as a date. For strings throws an exception. For blank cells returns `null`.
`Date`	`getCellDateValue(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(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(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`.
`String`	`getCellFormula(int sheetIndex, int rowIndex, int columnIndex)`	Return a formula for the cell, for example, SUM(C4:E4)
`String`	`getCellFormula(String cellName)`	Return a formula for the cell, for example, SUM(C4:E4)
`String`	`getCellFormula(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(String cellName)`	Returns the type of the formula cell. Only valid for formula cells.
`int`	`getCellFormulaType(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(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(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.
`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
`String`	`getCellStringValue(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
`String`	`getCellStringValue(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(String cellName)`	Returns the cell type (numeric, formula, string...)
`int`	`getCellType(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(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(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(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(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(String sheetName)`	Returns the index of the sheet with the given name. Returns `1` if `sheetName` is `null`
`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! All unsaved data (if any) in the workbook is lost after this method is called.
`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, String cellName, boolean dim1AcrossRows)`	Reads one- or two-dimensional `HyperArray` data from the sheet starting at the given cell.
`void`	`readHyperArray(HyperArray array, 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, 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, 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(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(String formula, String cellName)`	Sets formula for this cell. Note, this method only sets the formula string and does not calculate the formula value.
`void`	`setCellFormula(String formula, 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, String cellName)`	Sets a boolean value for the cell.
`void`	`setCellValue(boolean value, 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, String cellName)`	Sets a numeric value for the cell.
`void`	`setCellValue(double value, String sheetName, int rowIndex, int columnIndex)`	Sets a numeric value for the cell.
`void`	`setCellValue(String value, int sheetIndex, int rowIndex, int columnIndex)`	Sets a string value for the cell.
`void`	`setCellValue(String value, String cellName)`	Sets a string value for the cell.
`void`	`setCellValue(String value, String sheetName, int rowIndex, int columnIndex)`	Sets a string value for the cell.
`void`	`setCellValue(Date value, int sheetIndex, int rowIndex, int columnIndex)`	Sets a date value for the cell.
`void`	`setCellValue(Date value, String cellName)`	Sets a date value for the cell.
`void`	`setCellValue(Date value, 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.
`void`	`setFileName(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.
`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, String cellName)`	Writes the given data set to the sheet starting at the given cell.
`int`	`writeDataSet(DataSet dataSet, 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.

Methods inherited from class java.lang.Object

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

Field Details

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 Details

ExcelFile

@Deprecated
public ExcelFile(String fileName,
 boolean saveToSnapshot)

Deprecated.

this constructor is obsolete and will be removed in future

ExcelFile

public ExcelFile(Presentable owner,
 String packagePrefix,
 String fileName,
 boolean saveToSnapshot)

Creates new Excel^TM 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 Excel^TM file. If the file wasn't initially loaded, it isn't loaded during restoring from snapshot.

Method Details

setFileName

public void setFileName(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(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 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 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 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(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(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 String getCellStringValue(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 String getCellFormula(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(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 Date getCellDateValue(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(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(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(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 String getCellStringValue(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 String getCellFormula(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(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 Date getCellDateValue(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(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(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(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(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(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(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,
 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,
 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(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(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 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(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,
 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,
 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(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(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(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(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(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,
 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,
 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(String value,
 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(Date value,
 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(String formula,
 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,
 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,
 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(String value,
 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(Date value,
 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(String formula,
 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 String toString()

Overrides:: toString in class 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,
 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,
 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)

Class ExcelFile

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Details

CELL_TYPE_BLANK

CELL_TYPE_BOOLEAN

CELL_TYPE_ERROR

CELL_TYPE_FORMULA

CELL_TYPE_NUMERIC

CELL_TYPE_STRING

Constructor Details

ExcelFile

ExcelFile

Method Details

setFileName

readFile

isLoaded

close

getCellType

getCellType

getCellBooleanValue

getCellNumericValue

getCellStringValue

getCellFormula

getCellErrorValue

getCellDateValue

getWorkbook

getCellBooleanValue

getCellNumericValue

getCellStringValue

getCellFormula

getCellErrorValue

getCellDateValue

getCellType

getCellBooleanValue

getCellNumericValue

getCellStringValue

getCellFormula

getCellErrorValue

getCellDateValue

getNumberOfSheets

getLastRowNum

getLastRowNum

getFirstRowNum

getFirstRowNum

getFirstCellNum

getFirstCellNum

getLastCellNum

getLastCellNum

cellExists

cellExists

cellExists

clearCell

readTableFunction

readTableFunction

readTableFunction

getCellFormulaType

getCellFormulaType

getCellFormulaType

getSheetName

getSheetIndex

readHyperArray

readHyperArray

readHyperArray

writeFile

writeFile

setChanged

createCell

createCell

createCell

setCellValue

setCellValue

setCellValue

setCellValue

setCellFormula

setCellValue

setCellValue

setCellValue