All Packages  Class Hierarchy  This Package  Previous  Next  Index

Class com.ibm.excelaccessor.ExcelRange

java.lang.Object
   |
   +----com.ibm.excelaccessor.ExcelRange

public class ExcelRange

extends Object

implements PropertyChangeListener, Serializable

This bean represents an Excel range and is used to access that range from the ExcelWorkbook supplied to this range. This bean uses the facilities provided by the ExcelWorkbook bean to access the range. The sheet name and the range name identify the range. The user of this bean needs to initialize (turn on) this bean before it can be used for accessing the range properties. Once the bean has been initialized, setting the properties of the range writes to the file.

The following major properties of the range can be accessed using the ExcelRange bean:

Value: This property is the raw value of the cell without any information as to how the value is formatted or whether the value is the result of a formula evaluation. The various types of values returned are Strings, Double, Dates, Boolean. Blank cells are returned as blank Strings. Even time and currency values are returned as Double values.

Formula: This property returns the formula String for each cell. If a particular cell does not contain a formula, the value of the formula property is the constant in the cell, represented as a String.

Format: This property is the format string for the range.

NoteText: This is the note text property of the range.

In the ExcelRange bean, it is possible to specify the range both by giving the range name or the row/column numbers of the range. This bean provides output that is same as the output which would have been available had the particular property of the range been accessed using Excel APIs directly. The value property can be read as the actual type of value (String, Date, Boolean or Double) and can be written both as the actual type of value and as Strings. It is not possible to write to cells that are parts of an array using this bean.

This bean also provides features to clear the contents (either some or all of the above properties) of a range, to check whether the specified cell is part of an Array, to check whether the specified cell contains a Formula, to find the dimensions of the range and insertion and removal of worksheets in a workbook. It is also possible to unprotect worksheets in a workbook using this bean.

ExcelRange()

Constructs an ExcelRange object with properties autoCreateSheet and autoUnprotectSheet set to false.

addExcelRangeErrorListener(ExcelRangeErrorListener)

Adds the specified ExcelRangeErrorListener to receive ExcelRangeError events from the ExcelRange bean.

addExcelRangeListener(ExcelRangeListener)

Adds the specified ExcelRangeListener to receive ExcelRange events from the ExcelRange bean.

addExcelSheetListener(ExcelSheetListener)

Adds the specified ExcelSheetListener to receive ExcelSheet events from the ExcelRange bean.

addFetchArgumentsListener(FetchArgumentsListener)

Adds the specified FetchArgumentsListener to receive FetchArguments events from the ExcelRange bean.

addFetchPasswordListener(FetchPasswordListener)

Adds the specified FetchPasswordListener to receive FetchPassword events from the ExcelRange bean.

addPropertyChangeListener(PropertyChangeListener)

Adds the specified PropertyChangeListener to receive PropertyChange events from the ExcelRange bean.

clear()

Clears all the cell properties (value, formula, format, text and noteText) for this Range.

clearContents()

Clears the cell contents (values and formulae, if any) for this Range.

clearFormats()

Clears all cell formatting information for all the cells in the range, the values, noteText and formulae are retained.

clearNotes()

Clears all the notes for all the cells in the range, the value, formula and format information are retained.

createNamedRange(String)

Assigns a name to the current range.

getColumnHeaders()

Returns a Vector of Strings, which represents the headers of the columns of the range (as integers).

getDefaultText()

Returns the text property of the range as returned by Excel.

getEndCol()

Returns the column number for the bottom right cell of the range.

getEndRow()

Returns the row number for the bottom right cell of the range.

getFormat()

Returns the Format property of the range as a String.

getFormula()

Returns the Formula property for the Range as a Vector of Vectors.

getFormulaAsArray()

Returns the formula property as a 2-Dimensional array of objects.

getNCols()

Returns the number of columns present in the range.

getNoteText()

Returns the noteText for the top-left cell of the range.

getNRows()

Returns the number of rows present in the range.

getRangeName()

Returns the name of the range if the range has been selected using rangeName.

getRowHeaders()

Returns a Vector of Strings, which represents the headers of the rows of the range (as integers).

getStartCol()

Returns the column number for the top left cell of the range.

getStartRow()

Returns the row number for the top left cell of the range.

getText()

Returns the Text property for the Range.

getValue()

Returns the Value property for the Range.

getValueAsArray()

Returns the value property as a 2-Dimensional array of objects.

getWorksheetName()

Returns the name of the worksheet.

hasArray()

Method to check whether the range is part of an array.

hasFormula()

Method to check whether the cells in the range contain formulae.

initRange()

A call to this method initializes the range and essentially turns it on for further use.

insertWorksheet()

Inserts a new worksheet in the workbook and renames it to the worksheetName of this range.

isAutoCreateSheet()

Returns the value of the autoCreateSheet property.

isAutoUnprotectSheet()

Returns the value of the autoUnprotectSheet property.

isInitialized()

Returns the status of the initialization of the range bean.

propertyChange(PropertyChangeEvent)

The listener method for property change events.

removeExcelRangeErrorListener(ExcelRangeErrorListener)

Removes the specified ExcelRangeErrorListener so that it no longer receives ExcelRangeErrorEvents from this bean.

removeExcelRangeListener(ExcelRangeListener)

Removes the specified ExcelRangeListener so that it no longer receives ExcelRangeEvents from this bean.

removeExcelSheetListener(ExcelSheetListener)

Removes the specified ExcelSheetListener so that it no longer receives ExcelSheetEvents from this bean.

removeFetchArgumentsListener(FetchArgumentsListener)

Removes the specified FetchArgumentsListener so that it no longer receives FetchArgumentsEvents from this bean.

removeFetchPasswordListener(FetchPasswordListener)

Removes the specified FetchPasswordListener so that it no longer receives FetchPasswordEvents from this bean.

removePropertyChangeListener(PropertyChangeListener)

Removes the specified PropertyChangeListener so that it no longer receives PropertyChangeEvents from this bean.

removeWorksheet()

Removes a worksheet from the workbook.

setAutoCreateSheet(boolean)

Sets the value of the autoCreateSheet property.

setAutoUnprotectSheet(boolean)

Sets the value of the autoUnprotectSheet property.

setCellR1C1(int, int)

This method can be used to identify a single cell as the range to be accessed using the cell row and column numbers.

setFormat(String)

Sets the format property of this Range.

setFormula(Vector)

Sets the formula property of this Range.

setFormulaAsObject(Object)

Sets the formula for this range.

setNoteText(String)

Sets the noteText for the top-left cell of this range.

setRangeName(String)

Sets the name of the Range to be accessed.

setRangeR1C1(int, int, int, int)

This method can be used to identify the range to be accessed using the positions of the top-left and bottom-right cells

setValue(Vector)

Sets the value property of this Range.

setValueAsObject(Object)

Sets the value for this range.

setWorksheetName(String)

Sets the name of the worksheet to be accessed.

setWorksheetProtectionPassword(String)

Sets the worksheet protection password to be used while unprotecting the worksheet.

setXlWorkbook(ExcelWorkbook)

Sets the reference to the ExcelWorkbook bean that this Range bean operates on.

unprotectWorksheet()

Unprotects the worksheet.

 public ExcelRange()

Constructs an ExcelRange object with properties autoCreateSheet and autoUnprotectSheet set to false. The range to be accessed needs to be identified (by worksheetName and rangeName) and then initialized before the properties of the range can be accessed.

 public synchronized void addExcelRangeErrorListener(ExcelRangeErrorListener l)

Adds the specified ExcelRangeErrorListener to receive ExcelRangeError events from the ExcelRange bean.

Parameters:

l - The ExcelRangeErrorEvent listener.

See Also:

ExcelRangeErrorEvent, ExcelRangeErrorListener

 public synchronized void addExcelSheetListener(ExcelSheetListener l)

Adds the specified ExcelSheetListener to receive ExcelSheet events from the ExcelRange bean.

Parameters:

l - The ExcelSheetEvent listener.

See Also:

ExcelSheetEvent, ExcelSheetListener

 public synchronized void addFetchArgumentsListener(FetchArgumentsListener l)

Adds the specified FetchArgumentsListener to receive FetchArguments events from the ExcelRange bean.

Parameters:

l - The FetchArgumentsEvent listener.

See Also:

FetchArgumentsEvent, FetchArgumentsListener

 public synchronized void addFetchPasswordListener(FetchPasswordListener l)

Adds the specified FetchPasswordListener to receive FetchPassword events from the ExcelRange bean.

Parameters:

l - The FetchPasswordEvent listener.

See Also:

FetchPasswordEvent, FetchPasswordListener

 public synchronized void addPropertyChangeListener(PropertyChangeListener l)

Adds the specified PropertyChangeListener to receive PropertyChange events from the ExcelRange bean.

Parameters:

l - The PropertyChangeEvent listener.

See Also:

PropertyChangeEvent, PropertyChangeListener

 public synchronized void addExcelRangeListener(ExcelRangeListener l)

Adds the specified ExcelRangeListener to receive ExcelRange events from the ExcelRange bean.

Parameters:

l - the ExcelRangeEvent listener.

See Also:

ExcelRangeEvent, ExcelRangeListener

 public synchronized void clear()

Clears all the cell properties (value, formula, format, text and noteText) for this Range. Fires ExcelRangeErrorEvent on failure.

 public synchronized void clearContents()

Clears the cell contents (values and formulae, if any) for this Range. The formats and noteTexts are retained. Fires ExcelRangeErrorEvent on failure.

 public synchronized void clearFormats()

Clears all cell formatting information for all the cells in the range, the values, noteText and formulae are retained. Fires ExcelRangeErrorEvent on failure.

 public synchronized void clearNotes()

Clears all the notes for all the cells in the range, the value, formula and format information are retained. Fires ExcelRangeErrorEvent on failure.

 public synchronized void createNamedRange(String name)

Assigns a name to the current range. Fires ExcelRangeErrorEvent on failure.

Parameters:

name - The name to be assigned to this range.

 public synchronized Vector getColumnHeaders()

Returns a Vector of Strings, which represents the headers of the columns of the range (as integers).

Returns:

Vector of Strings containing the column headers.

 public synchronized String getDefaultText()

Returns the text property of the range as returned by Excel. Excel returns null if the text for all cells in the specified range is not homogeneous. If the text is homogeneous, the text is returned as a String.

Returns:

The text property.

 public synchronized int getEndCol()

Returns the column number for the bottom right cell of the range.

Returns:

The column number for the bottom right cell of the range.

 public synchronized int getEndRow()

Returns the row number for the bottom right cell of the range.

Returns:

The row number for the bottom right cell of the range.

 public synchronized String getFormat()

Returns the Format property of the range as a String. If the format for all cells in the specified range is not homogeneous, null is returned.

Returns:

The format property as a String.

 public synchronized Vector getFormula()

Returns the Formula property for the Range as a Vector of Vectors. The inner Vectors represent the data for the respective rows. For the cells that do not have a formula, the value in that cell is returned as a String.

Returns:

The Formula as a 2-D Vector.

 public synchronized Object getFormulaAsArray()

Returns the formula property as a 2-Dimensional array of objects. The inner arrays represent the data for the respective rows. If the range is a single cell, a String object representing the formula for that cell is returned.

Returns:

The formula property of the range, as a 2-D array or as a String if the range is a single cell.

See Also:

getFormula

 public synchronized int getNCols()

Returns the number of columns present in the range.

Returns:

The number of columns in the range.

 public synchronized String getNoteText()

Returns the noteText for the top-left cell of the range.

Returns:

The noteText property of the range.

 public synchronized int getNRows()

Returns the number of rows present in the range.

Returns:

The number of rows in the range.

 public String getRangeName()

Returns the name of the range if the range has been selected using rangeName.

Returns:

The name of the range.

 public synchronized Vector getRowHeaders()

Returns a Vector of Strings, which represents the headers of the rows of the range (as integers).

Returns:

Vector of Strings containing the row headers.

 public synchronized int getStartCol()

Returns the column number for the top left cell of the range.

Returns:

The column number for the top left cell of the range.

 public synchronized int getStartRow()

Returns the row number for the top left cell of the range.

Returns:

The row number for the top left cell of the range.

 public synchronized Vector getText()

Returns the Text property for the Range. It is a Vector of Vectors, containing the text strings for all cells in this range. (this is not the default behavior of Excel). The inner Vectors represent the data for the respective rows.

Returns:

The text property of the range as a 2-D Vector.

See Also:

getDefaultText

 public synchronized Vector getValue()

Returns the Value property for the Range. Value is returned as a Vector of Vectors. The inner Vectors represent the data for the respective rows. Values can be of various types such as Strings, Double, Dates, Boolean. Blank cells are returned as blank Strings. Time and currency values are returned as double values.

Returns:

The value property as a 2-D Vector.

 public synchronized Object getValueAsArray()

Returns the value property as a 2-Dimensional array of objects. The inner arrays represent the data for the respective rows. If the range is a single cell, an object representing the value for that cell is returned.

Returns:

The value property of the range, as a 2-D array or as an object if the range is a single cell.

See Also:

getValue

 public synchronized String getWorksheetName()

Returns the name of the worksheet.

Returns:

The name of the worksheet.

 public synchronized Boolean hasArray()

Method to check whether the range is part of an array.

Returns:

True if this range is part of an Array. null if the range is heterogeneous, i.e. contains some cells that are part of an array. False if no cells are part of an array.

 public synchronized Boolean hasFormula()

Method to check whether the cells in the range contain formulae.

Returns:

True if all cells in the range contain formulae, false if no cell in the range contains a formula or null otherwise.

 public synchronized void initRange()

A call to this method initializes the range and essentially turns it on for further use. After the range has been initialized, the properties of the range are available as bound properties on this object. Calling this method fires FetchArgumentsEvent which can be used to identify the range to be initialized. If the autoCreate flag is set to true, and if the worksheet does not exist in the workbook, it is inserted in the workbook during range initialization. During sheet insertion, if the workbook structure is protected, it is automatically unprotected. If the sheet is protected and autoUnprotect is true it tries to unprotect the sheet. If unprotection fails an error event is fired and range initialization continues. This method has to be called before this bean can be used in a valid manner to access the properties of the range. It fires rangeInitialized( ExcelRangeEvent ) on successful initialization. It fires ExcelRangeErrorEvent on failure.

See Also:

FetchArgumentsEvent, ExcelRangeErrorEvent, ExcelRangeEvent

 public synchronized void insertWorksheet()

Inserts a new worksheet in the workbook and renames it to the worksheetName of this range. The range need not be initialized to perform this operation. Before inserting the sheet, the FetchArgumentsEvent is fired which can be used to set the worksheetName. During insertion, if the workbook structure is protected, it is automatically unprotected. It fires sheetInserted( ExcelSheetEvent ) on success. It fires ExcelRangeErrorEvent if an error occurs.

See Also:

ExcelSheetEvent

 public boolean isInitialized()

Returns the status of the initialization of the range bean.

Returns:

True if the range has been initialized, false otherwise.

 public boolean isAutoCreateSheet()

Returns the value of the autoCreateSheet property. This property is used at the time of range initialization.

Returns:

The autoCreateSheet property.

 public boolean isAutoUnprotectSheet()

Returns the value of the autoUnprotectSheet property. This value is used only at the time of initializing the Range.

Returns:

The autoUnprotectSheet property.

 public synchronized void propertyChange(PropertyChangeEvent evt)

The listener method for property change events. This object is a listener on the workbook bean. When the workbook changes, the range gets uninitialized and rangeUninitialized( ExcelRangeEvent ) is fired.

Parameters:

evt - The PropertyChangeEvent.

 public synchronized void removeExcelRangeErrorListener(ExcelRangeErrorListener l)

Removes the specified ExcelRangeErrorListener so that it no longer receives ExcelRangeErrorEvents from this bean.

Parameters:

l - The ExcelRangeErrorListener.

See Also:

ExcelRangeErrorEvent, ExcelRangeErrorListener

 public synchronized void removeExcelSheetListener(ExcelSheetListener l)

Removes the specified ExcelSheetListener so that it no longer receives ExcelSheetEvents from this bean.

Parameters:

l - The ExcelSheetListener.

See Also:

ExcelSheetEvent, ExcelSheetListener

 public synchronized void removeFetchArgumentsListener(FetchArgumentsListener l)

Removes the specified FetchArgumentsListener so that it no longer receives FetchArgumentsEvents from this bean.

Parameters:

l - The FetchArgumentsListener.

See Also:

FetchArgumentsEvent, FetchArgumentsListener

 public synchronized void removeFetchPasswordListener(FetchPasswordListener l)

Removes the specified FetchPasswordListener so that it no longer receives FetchPasswordEvents from this bean.

Parameters:

l - the FetchPasswordListener.

See Also:

FetchPasswordEvent, FetchPasswordListener

 public synchronized void removePropertyChangeListener(PropertyChangeListener l)

Removes the specified PropertyChangeListener so that it no longer receives PropertyChangeEvents from this bean.

Parameters:

l - the PropertyChangeListener.

See Also:

PropertyChangeEvent, PropertyChangeListener

 public synchronized void removeExcelRangeListener(ExcelRangeListener l)

Removes the specified ExcelRangeListener so that it no longer receives ExcelRangeEvents from this bean.

Parameters:

l - the ExcelRangeListener.

See Also:

ExcelRangeEvent, ExcelRangeListener

 public synchronized void removeWorksheet()

Removes a worksheet from the workbook. The name of the sheet removed is the property worksheetName. The range need not be initialized to perform this operation. Before removing the sheet, the FetchArgumentsEvent is fired which can be used to set the worksheetName. During removal, if the workbook structure is protected, it is automatically unprotected. It fires sheetRemoved (ExcelSheetEvent) on success. It fires ExcelRangeErrorEvent if an error occurs.

See Also:

ExcelSheetEvent

 public synchronized void setAutoCreateSheet(boolean bValue)

Sets the value of the autoCreateSheet property. This property is used while initializing the range. If set to true, if the worksheet does not exist in the workbook, it is automatically created.

Parameters:

bValue - The value to set.

 public synchronized void setAutoUnprotectSheet(boolean bValue)

Sets the value of the autoUnprotectSheet property. This value is used only at the time of initializing the Range. If set to true, the worksheet is unprotected at range initialization.

Parameters:

bValue - The value to set.

 public void setCellR1C1(int row,
                         int col)

This method can be used to identify a single cell as the range to be accessed using the cell row and column numbers.

Parameters:

row - The row number of the cell.

col - The column number of the cell.

 public synchronized void setFormat(String strFormat)

Sets the format property of this Range. The format string for all the cells in this range will be set to the specified parameter. Fires ExcelRangeErrorEvent on failure.

Parameters:

strFormat - String specifying the format.

 public synchronized void setFormula(Vector objFormula)

Sets the formula property of this Range. The behavior in case of different dimensions of the range and the parameter supplied is the same as the default Excel behavior in that case. Fires ExcelRangeErrorEvent on failure.

Parameters:

objFormula - A Vector of formulae for the cells. In case the vector further contains vectors, all the inner vectors should be of the same size. The inner Vectors are taken to represent the rows of the range.

 public synchronized void setFormulaAsObject(Object objFormula)

Sets the formula for this range. The parameter supplied could be an array, a Vector or just a single object.

Parameters:

objFormula - The formula to be set.

See Also:

setFormula

 public synchronized void setNoteText(String strText)

Sets the noteText for the top-left cell of this range. Fires ExcelRangeErrorEvent on failure.

Parameters:

strText - String containing the noteText.

 public void setRangeName(String strName)

Sets the name of the Range to be accessed. The name of the range can be a name that is previously assigned or an "A1" style reference. Eg. A cell, say 'B4' would be referenced as "B4". A range, say from cell 'B4' to cell 'F6', would be referenced as "B4:F6". If the range is initialized, calling this setter would result in the range becoming uninitialized. (Note: The beans do not have support for ranges that are composed of multiple areas.)

Parameters:

strName - The name of the Range to be accessed.

 public void setRangeR1C1(int topRow,
                          int leftCol,
                          int bottomRow,
                          int rightCol)

This method can be used to identify the range to be accessed using the positions of the top-left and bottom-right cells

Parameters:

topRow - The row number of the top-left corner of the range.

leftCol - The column number of the top-left corner of the range.

bottomRow - The row number of the bottom right corner of the range.

rightCol - The column number of the bottom right corner of the range.

 public void setWorksheetProtectionPassword(String strPass)

Sets the worksheet protection password to be used while unprotecting the worksheet. Note that this method does not change the worksheet protection password in the Excel workbook, it simply uses the supplied password, if required when unprotecting the worksheet.

Parameters:

strPass - The worksheet protection password.

 public synchronized void setValue(Vector objValue)

Sets the value property of this Range. The behavior in case of different dimensions of the range and the parameter supplied is the same as the default Excel behavior in that case. Values supplied can be of types Double, String, Boolean or Date. For objects not of these types the toString() implementation is written to the range. Fires ExcelRangeErrorEvent on failure.

Parameters:

objValue - A Vector of values for the cells. In case the vector further contains vectors, all the inner vectors should be of the same size. The inner Vectors are taken to represent the rows of the range.

 public synchronized void setValueAsObject(Object objValue)

Sets the value for this range. The parameter supplied could be an array, a Vector or just a single object.

Parameters:

objValue - The value to be set.

See Also:

setValue

 public void setWorksheetName(String strName)

Sets the name of the worksheet to be accessed.

Parameters:

strName - The name of the worksheet to be accessed.

 public void setXlWorkbook(ExcelWorkbook workbookBean)

Sets the reference to the ExcelWorkbook bean that this Range bean operates on.

Parameters:

workbookBean - The ExcelWorkbook on which all operations will be performed

 public synchronized void unprotectWorksheet()

Unprotects the worksheet. Throws a FetchPasswordEvent in case the worksheet is password protected. Fires the ExcelRangeErrorEvent if unprotection fails.

All Packages  Class Hierarchy  This Package  Previous  Next  Index