com.extentech.ExtenXLS
Class ValidationHandle

java.lang.Object
  com.extentech.ExtenXLS.ValidationHandle

All Implemented Interfaces:

Handle

public class ValidationHandle
extends Object
implements Handle

ValidationHandle allows for manipulation of the validation cells in Excel

Using the ValidationHandle, the affected range of validations can be modified, along with many of the error messages and actual validation upon the cells. Many of these calls are very self-explanatory and can be found in the api.

Some common use cases:

1. Setting/changing the validation data type.

    // Change validation to only allow a formula ValidationHandle validator =
    theSheet.getValidationHandle("A1");
    validator.setValidationType(ValidationHandle.VALUE_FORMULA); // can use any VALUE static byte here.
    

2. Setting/changing the validation condition. This requires setting the condition type, and a first and second condition. Also make the page error on invalid entry and set the error text.

    // Validate cell is an int between the current values of cell D1 and cell D2.
    ValidationHandle validator = theSheet.getValidationHandle("A1");
    validator.setValidationType(ValidationHandle.VALUE_INTEGER);
    validator.setTypeOperator(ValidationHandle.CONDITION_BETWEEN)// any CONDITION
    static byte validator.setFirstCondition("D1"); // any valid excel formula,
    omitting the '=' validator.setSecondCondition("D2");
    validator.setErrorBoxText
    ("The value is not between the values of D1 and D2");
    validator.setShowErrorMessage(true);
    

3. Change the range the validation is applied to, for instance if one is inseting a number of new rows and wants to grow the range A1:D1 to A1:Z1.

    ValidationHandle validator = theSheet.getValidationHandle("A1");
    validator.setRange("A1:Z1");
    

Version:

5.0

Author:

Nicholas Rab Rab -- Copyright ©2011Extentech Inc.

Field Summary

static byte	CONDITION_BETWEEN
static byte	CONDITION_EQUAL
static byte	CONDITION_GREATER_OR_EQUAL
static byte	CONDITION_GREATER_THAN
static byte	CONDITION_LESS_OR_EQUAL
static byte	CONDITION_LESS_THAN
static byte	CONDITION_NOT_BETWEEN
static byte	CONDITION_NOT_EQUAL
static String[]	CONDITIONS
static byte	ERROR_INFO
static byte	ERROR_STOP
static byte	ERROR_WARN
static short	IME_MODE_DISABLE
static short	IME_MODE_FULL_WIDTH_ALPHA
static short	IME_MODE_FULL_WIDTH_HANKUL
static short	IME_MODE_HALF_WIDTH_ALPHA
static short	IME_MODE_HALF_WIDTH_HANKUL
static short	IME_MODE_HIRAGANA
static short	IME_MODE_KATAKANA
static short	IME_MODE_KATALANA_HALF
static short	IME_MODE_NO_CONTROL
static short	IME_MODE_OFF
static short	IME_MODE_ON
static byte	VALUE_ANY
static byte	VALUE_DATE
static byte	VALUE_DECIMAL
static byte	VALUE_FORMULA
static byte	VALUE_INTEGER
static byte	VALUE_TEXT_LENGTH
static byte	VALUE_TIME
static String[]	VALUE_TYPE
static byte	VALUE_USER_DEFINED_LIST

Constructor Summary

ValidationHandle(com.extentech.formats.XLS.Dv dv)
For internal use only.

Method Summary

void	addRange(String range) Adds an additional range to the existing ranges in this validationhandle
static byte	getConditionNumber(String conditionType) Get the byte representing the condition type string passed in.
String	getErrorBoxText() Get the text from the error box.
String	getErrorBoxTitle() Get the title from the error box
byte	getErrorStyle() Return a byte representing the error style for this ValidationHandle These map to the static final ints ERROR_* in ValidationHandle
String	getFirstCondition() Get the first condition of the validation as a string representation
short	getIMEMode() Get the IME mode for this validation
String	getPromptBoxText() Return the text in the prompt box
String	getPromptBoxTitle() Return the title in the prompt box
String	getRange() Return the range of data this ValidationHandle refers to as a string Will not contain worksheet identifier, as ValidationHandles are specific to a worksheet.
String	getSecondCondition() Get the second condition of the validation as a string representation
boolean	getShowInputMsg() Show prompt box if cell selected?
byte	getTypeOperator() Get the type operator of this validation as a byte.
byte	getValidationType() Get the validation type of this ValidationHandle as a byte These bytes map to the VALUE_* static values in ValidationHandle
static byte	getValueNumber(String valueType) Get the byte representing the value type string passed in.
String	getXML() Return an xml representation of the ValidationHandle
boolean	isAllowBlank() Allow blank cells in the validation area?
boolean	isInRange(String celladdy) Determines if the ValidationHandle contains the cell address passed in
boolean	isShowErrorMsg() Show error box if invalid values entered?
boolean	isStrLookup() In list type validity the string list is explicitly given in the formula
boolean	isSuppressCombo() Suppress the drop down arrow in list type validity
boolean	isValid(Object value) Determine if the value passed in is valid for this validation
boolean	isValid(Object value, boolean throwException) Determine if the value passed in is valid for this validation
void	setAllowBlank(boolean allowBlank) Allow blank cells in the validation area?
void	setErrorBoxText(String textError) Set the text for the error box
void	setErrorBoxTitle(String textError) Set the title for the error box
void	setErrorStyle(byte errstyle) Set the error style for this ValidationHandle record These map to the static final ints ERROR_* from ValidationHandle
void	setFirstCondition(Object firstCond) Set the first condition of the validation This value must conform to the Value Type of this validation or unexpected results may occur.
void	setIMEMode(short mode) set the IME mode for this validation
void	setPromptBoxText(String text) Set the text for the prompt box
void	setPromptBoxTitle(String text) Set the title for the prompt box
void	setRange(String range) Set the range this ValidationHandle refers to.
void	setSecondCondition(Object secondCond) Set the first condition of the validation utilizing a string.
void	setShowErrorMsg(boolean showErrMsg) Set show error box if invalid values entered?
void	setShowInputMsg(boolean showInputMsg) Set show prompt box if cell selected?
void	setStrLookup(boolean strLookup) In list type validity the string list is explicitly given in the formula
void	setSuppressCombo(boolean suppressCombo) Suppress the drop down arrow in list type validity
void	setTypeOperator(byte typOperator) set the type operator of this validation as a byte.
void	setValidationType(byte valtype) Set the validation type of this ValidationHandle as a byte These bytes map to the VALUE_* static values in ValidationHandle

Methods inherited from class java.lang.Object

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

Field Detail

VALUE_ANY

public static final byte VALUE_ANY

See Also:

Constant Field Values

VALUE_INTEGER

public static final byte VALUE_INTEGER

See Also:

Constant Field Values

VALUE_DECIMAL

public static final byte VALUE_DECIMAL

See Also:

Constant Field Values

VALUE_USER_DEFINED_LIST

public static final byte VALUE_USER_DEFINED_LIST

See Also:

Constant Field Values

VALUE_DATE

public static final byte VALUE_DATE

See Also:

Constant Field Values

VALUE_TIME

public static final byte VALUE_TIME

See Also:

Constant Field Values

VALUE_TEXT_LENGTH

public static final byte VALUE_TEXT_LENGTH

See Also:

Constant Field Values

VALUE_FORMULA

public static final byte VALUE_FORMULA

See Also:

Constant Field Values

ERROR_STOP

public static byte ERROR_STOP

ERROR_WARN

public static byte ERROR_WARN

ERROR_INFO

public static byte ERROR_INFO

CONDITION_BETWEEN

public static final byte CONDITION_BETWEEN

See Also:

Constant Field Values

CONDITION_NOT_BETWEEN

public static final byte CONDITION_NOT_BETWEEN

See Also:

Constant Field Values

CONDITION_EQUAL

public static final byte CONDITION_EQUAL

See Also:

Constant Field Values

CONDITION_NOT_EQUAL

public static final byte CONDITION_NOT_EQUAL

See Also:

Constant Field Values

CONDITION_GREATER_THAN

public static final byte CONDITION_GREATER_THAN

See Also:

Constant Field Values

CONDITION_LESS_THAN

public static final byte CONDITION_LESS_THAN

See Also:

Constant Field Values

CONDITION_GREATER_OR_EQUAL

public static final byte CONDITION_GREATER_OR_EQUAL

See Also:

Constant Field Values

CONDITION_LESS_OR_EQUAL

public static final byte CONDITION_LESS_OR_EQUAL

See Also:

Constant Field Values

IME_MODE_NO_CONTROL

public static short IME_MODE_NO_CONTROL

IME_MODE_ON

public static short IME_MODE_ON

IME_MODE_OFF

public static short IME_MODE_OFF

IME_MODE_DISABLE

public static short IME_MODE_DISABLE

IME_MODE_HIRAGANA

public static short IME_MODE_HIRAGANA

IME_MODE_KATAKANA

public static short IME_MODE_KATAKANA

IME_MODE_KATALANA_HALF

public static short IME_MODE_KATALANA_HALF

IME_MODE_FULL_WIDTH_ALPHA

public static short IME_MODE_FULL_WIDTH_ALPHA

IME_MODE_HALF_WIDTH_ALPHA

public static short IME_MODE_HALF_WIDTH_ALPHA

IME_MODE_FULL_WIDTH_HANKUL

public static short IME_MODE_FULL_WIDTH_HANKUL

IME_MODE_HALF_WIDTH_HANKUL

public static short IME_MODE_HALF_WIDTH_HANKUL

CONDITIONS

public static String[] CONDITIONS

VALUE_TYPE

public static String[] VALUE_TYPE

Constructor Detail

ValidationHandle

public ValidationHandle(com.extentech.formats.XLS.Dv dv)

For internal use only. Creates a Validation Handle based of the Dv passed in.

Parameters:

dv -

Method Detail

getConditionNumber

public static byte getConditionNumber(String conditionType)

Get the byte representing the condition type string passed in. Options are' "between", "notBetween", "equal", "notEqual", "greaterThan", "lessThan", "greaterOrEqual", "lessOrEqual"

Returns:

getValueNumber

public static byte getValueNumber(String valueType)

Get the byte representing the value type string passed in. Options are' "any", "integer", "decimal", "userDefinedList", "date", "time", "textLength", "formula"

Returns:

getRange

public String getRange()

Return the range of data this ValidationHandle refers to as a string Will not contain worksheet identifier, as ValidationHandles are specific to a worksheet. If the Validation effects multiple ranges they are separated by a space

Returns:

ptgRef.toString()

isValid

public boolean isValid(Object value)
                throws com.extentech.formats.XLS.ValidationException

Determine if the value passed in is valid for this validation

Parameters:

value -

Returns:

Throws:

com.extentech.formats.XLS.ValidationException

isValid

public boolean isValid(Object value,
                       boolean throwException)
                throws RuntimeException

Determine if the value passed in is valid for this validation

Parameters:

value -

Returns:

Throws:

RuntimeException

setRange

public void setRange(String range)

Set the range this ValidationHandle refers to. Pass in a range string, sans worksheet. This range will overwrite all other ranges this ValidationHandle refers to. In order to handle multiple ranges, use the addRange(String range) method

Parameters:

range - = standard excel range without worksheet information ("A1" or "A1:A10")

addRange

public void addRange(String range)

Adds an additional range to the existing ranges in this validationhandle

Parameters:

range -

getErrorBoxText

public String getErrorBoxText()

Get the text from the error box.

Returns:

setErrorBoxText

public void setErrorBoxText(String textError)

Set the text for the error box

Parameters:

textError -

getPromptBoxText

public String getPromptBoxText()

Return the text in the prompt box

Returns:

setPromptBoxText

public void setPromptBoxText(String text)

Set the text for the prompt box

Parameters:

text -

setErrorBoxTitle

public void setErrorBoxTitle(String textError)

Set the title for the error box

Parameters:

textError -

getErrorBoxTitle

public String getErrorBoxTitle()

Get the title from the error box

Returns:

getPromptBoxTitle

public String getPromptBoxTitle()

Return the title in the prompt box

Returns:

setPromptBoxTitle

public void setPromptBoxTitle(String text)

Set the title for the prompt box

Parameters:

text -

getErrorStyle

public byte getErrorStyle()

Return a byte representing the error style for this ValidationHandle These map to the static final ints ERROR_* in ValidationHandle

Returns:

setErrorStyle

public void setErrorStyle(byte errstyle)

Set the error style for this ValidationHandle record These map to the static final ints ERROR_* from ValidationHandle

getIMEMode

public short getIMEMode()

Get the IME mode for this validation

Returns:

setIMEMode

public void setIMEMode(short mode)

set the IME mode for this validation

isAllowBlank

public boolean isAllowBlank()

Allow blank cells in the validation area?

Returns:

setAllowBlank

public void setAllowBlank(boolean allowBlank)

Allow blank cells in the validation area?

getFirstCondition

public String getFirstCondition()

Get the first condition of the validation as a string representation

Returns:

setFirstCondition

public void setFirstCondition(Object firstCond)

Set the first condition of the validation This value must conform to the Value Type of this validation or unexpected results may occur. For example, entering a string representation of a date here will not work if your validation is an integer... A java.util.Date object can also be passed in. This value will be translated into an integer as excel stores dates. If you need to manipulate/retrieve this value later utilize the DateConverter tool to transform the value String passed in should be a vaild XLS formula. Does not need to include the "=" Types of conditions Integer values Decimal values User defined list Date Time Text length Formula Be sure that your validation type (getValidationType()) matches the type of data.

Parameters:

firstCond - = the first condition for the validation

getSecondCondition

public String getSecondCondition()

Get the second condition of the validation as a string representation

Returns:

setSecondCondition

public void setSecondCondition(Object secondCond)

Set the first condition of the validation utilizing a string. This value must conform to the Value Type of this validation or unexpected results may occur. For example, entering a string representation of a date here will not work if your validation is an integer... String passed in should be a vaild XLS formula. Does not need to include the "=" Types of conditions Integer values Decimal values User defined list Date Time Text length Formula Be sure that your validation type (getValidationType()) matches the type of data.

isShowErrorMsg

public boolean isShowErrorMsg()

Show error box if invalid values entered?

Returns:

setShowErrorMsg

public void setShowErrorMsg(boolean showErrMsg)

Set show error box if invalid values entered?

getShowInputMsg

public boolean getShowInputMsg()

Show prompt box if cell selected?

Returns:

setShowInputMsg

public void setShowInputMsg(boolean showInputMsg)

Set show prompt box if cell selected?

Parameters:

showInputMsg -

isStrLookup

public boolean isStrLookup()

In list type validity the string list is explicitly given in the formula

Returns:

boolean

setStrLookup

public void setStrLookup(boolean strLookup)

In list type validity the string list is explicitly given in the formula

Parameters:

strLookup -

isSuppressCombo

public boolean isSuppressCombo()

Suppress the drop down arrow in list type validity

Returns:

boolean

setSuppressCombo

public void setSuppressCombo(boolean suppressCombo)

Suppress the drop down arrow in list type validity

getTypeOperator

public byte getTypeOperator()

Get the type operator of this validation as a byte. These bytes map to the CONDITION_* static values in ValidationHandle

Returns:

setTypeOperator

public void setTypeOperator(byte typOperator)

set the type operator of this validation as a byte. These bytes map to the CONDITION_* static values in ValidationHandle

getValidationType

public byte getValidationType()

Get the validation type of this ValidationHandle as a byte These bytes map to the VALUE_* static values in ValidationHandle

Returns:

setValidationType

public void setValidationType(byte valtype)

Set the validation type of this ValidationHandle as a byte These bytes map to the VALUE_* static values in ValidationHandle

isInRange

public boolean isInRange(String celladdy)

Determines if the ValidationHandle contains the cell address passed in

Parameters:

range -

Returns:

getXML

public String getXML()

Return an xml representation of the ValidationHandle

Returns: