Validation

Validation class

Represents data validation.settings.

class Validation;

Example

const { Workbook, ValidationType, CellArea } = AsposeCells;

var workbook = new Workbook();
var validations = workbook.worksheets.get(0).validations;
var area = CellArea.createCellArea(0, 0, 1, 1);
var validation = validations.get(validations.add(area));
validation.type = ValidationType.List;
validation.formula1 = "a,b,c,d";

Properties

PropertyTypeDescription
operatorOperatorTypeRepresents the operator for the data validation.
alertStyleValidationAlertTypeRepresents the validation alert style.
typeValidationTypeRepresents the data validation type.
inputMessagestringRepresents the data validation input message.
inputTitlestringRepresents the title of the data-validation input dialog box.
errorMessagestringRepresents the data validation error message.
errorTitlestringRepresents the title of the data-validation error dialog box.
showInputbooleanIndicates whether the data validation input message will be displayed whenever the user selects a cell in the data validation range.
showErrorbooleanIndicates whether the data validation error message will be displayed whenever the user enters invalid data.
ignoreBlankbooleanIndicates whether blank values are permitted by the range data validation.
formula1stringRepresents the value or expression associated with the data validation.
formula2stringRepresents the value or expression associated with the data validation.
value1VObjectRepresents the first value associated with the data validation.
value2VObjectRepresents the second value associated with the data validation.
inCellDropDownbooleanIndicates whether data validation displays a drop-down list that contains acceptable values.
areasCellArea[]Readonly. Gets all CellArea which contain the data validation settings.

Methods

MethodDescription
getFormula1(boolean, boolean)Gets the value or expression associated with this validation.
getFormula1(boolean, boolean, number, number)Gets the value or expression associated with this validation for specific cell.
getFormula2(boolean, boolean)Gets the value or expression associated with this validation.
getFormula2(boolean, boolean, number, number)Gets the value or expression associated with this validation for specific cell.
setFormula1(string, boolean, boolean)Sets the value or expression associated with this validation.
setFormula2(string, boolean, boolean)Sets the value or expression associated with this validation.
getListValue(number, number)Get the value for list of the validation for the specified cell.
getValue(number, number, boolean)Get the value of validation on the specific cell.
addArea(CellArea)Applies the validation to the area.
addArea(CellArea, boolean, boolean)Applies the validation to the area.
addAreas(CellArea[], boolean, boolean)Applies the validation to given areas.
removeArea(CellArea)Remove the validation settings in the range.
removeAreas(CellArea[])Removes this validation from given areas.
removeACell(number, number)Remove the validation settings in the cell.
copy(Validation, CopyOptions)Copy validation.

operator

Represents the operator for the data validation.

operator : OperatorType;

alertStyle

Represents the validation alert style.

alertStyle : ValidationAlertType;

type

Represents the data validation type.

type : ValidationType;

inputMessage

Represents the data validation input message.

inputMessage : string;

inputTitle

Represents the title of the data-validation input dialog box.

inputTitle : string;

errorMessage

Represents the data validation error message.

errorMessage : string;

errorTitle

Represents the title of the data-validation error dialog box.

errorTitle : string;

showInput

Indicates whether the data validation input message will be displayed whenever the user selects a cell in the data validation range.

showInput : boolean;

showError

Indicates whether the data validation error message will be displayed whenever the user enters invalid data.

showError : boolean;

ignoreBlank

Indicates whether blank values are permitted by the range data validation.

ignoreBlank : boolean;

formula1

Represents the value or expression associated with the data validation.

formula1 : string;

formula2

Represents the value or expression associated with the data validation.

formula2 : string;

value1

Represents the first value associated with the data validation.

value1 : VObject;

value2

Represents the second value associated with the data validation.

value2 : VObject;

inCellDropDown

Indicates whether data validation displays a drop-down list that contains acceptable values.

inCellDropDown : boolean;

areas

Readonly. Gets all CellArea which contain the data validation settings.

areas : CellArea[];

getFormula1(boolean, boolean)

Gets the value or expression associated with this validation.

getFormula1(isR1C1: boolean, isLocal: boolean) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.

Returns

The value or expression associated with this validation.

getFormula1(boolean, boolean, number, number)

Gets the value or expression associated with this validation for specific cell.

getFormula1(isR1C1: boolean, isLocal: boolean, row: number, column: number) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.
rownumberThe row index.
columnnumberThe column index.

Returns

The value or expression associated with this validation.

getFormula2(boolean, boolean)

Gets the value or expression associated with this validation.

getFormula2(isR1C1: boolean, isLocal: boolean) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.

Returns

The value or expression associated with this validation.

getFormula2(boolean, boolean, number, number)

Gets the value or expression associated with this validation for specific cell.

getFormula2(isR1C1: boolean, isLocal: boolean, row: number, column: number) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.
rownumberThe row index.
columnnumberThe column index.

Returns

The value or expression associated with this validation.

setFormula1(string, boolean, boolean)

Sets the value or expression associated with this validation.

setFormula1(formula: string, isR1C1: boolean, isLocal: boolean) : void;

Parameters:

ParameterTypeDescription
formulastringThe value or expression associated with this format condition.
isR1C1booleanWhether the formula is R1C1 formula.
isLocalbooleanWhether the formula is locale formatted.

setFormula2(string, boolean, boolean)

Sets the value or expression associated with this validation.

setFormula2(formula: string, isR1C1: boolean, isLocal: boolean) : void;

Parameters:

ParameterTypeDescription
formulastringThe value or expression associated with this format condition.
isR1C1booleanWhether the formula is R1C1 formula.
isLocalbooleanWhether the formula is locale formatted.

getListValue(number, number)

Get the value for list of the validation for the specified cell.

getListValue(row: number, column: number) : VObject;

Parameters:

ParameterTypeDescription
rownumberThe row index.
columnnumberThe column index.

Returns

The value to produce the list of this validation for the specified cell. If the list references to a range, then the returned value will be a ReferredArea object; Otherwise the returned value may be null, object[], or simple object.

Remarks

Only for validation whose type is List and has been applied to given cell, otherwise null will be returned.

getValue(number, number, boolean)

Get the value of validation on the specific cell.

getValue(row: number, column: number, isValue1: boolean) : VObject;

Parameters:

ParameterTypeDescription
rownumberThe row index.
columnnumberThe column index.
isValue1booleanIndicates whether getting the first value.

addArea(CellArea)

Applies the validation to the area.

addArea(cellArea: CellArea) : void;

Parameters:

ParameterTypeDescription
cellAreaCellAreaThe area.

Remarks

It is equivalent to use AddArea(Aspose.Cells.CellArea,bool,bool) with checking intersection and edge.

addArea(CellArea, boolean, boolean)

Applies the validation to the area.

addArea(cellArea: CellArea, checkIntersection: boolean, checkEdge: boolean) : void;

Parameters:

ParameterTypeDescription
cellAreaCellAreaThe area.
checkIntersectionbooleanWhether check the intersection of given area with existing validations’ areas. /// If one validation has been applied in given area(or part of it), /// then the existing validation should be removed at first from given area. /// Otherwise corruption may be caused for the generated Validations. /// If user is sure that the added area does not intersect with any existing area, /// this parameter can be set as false for performance consideration.
checkEdgebooleanWhether check the edge of this validation’s applied areas. /// Validation’s internal settings depend on the top-left one of its applied ranges, /// so if given area will become the new top-left one of the applied ranges, /// the internal settings should be changed and rebuilt, otherwise unexpected result may be caused. /// If user is sure that the added area is not the top-left one, /// this parameter can be set as false for performance consideration.

Remarks

In this method, we will remove all old validations in given area. For the top-left one of Validation’s applied ranges, firstly its StartRow is smallest, secondly its StartColumn is the smallest one of those areas who have the same smallest StartRow.

addAreas(CellArea[], boolean, boolean)

Applies the validation to given areas.

addAreas(areas: CellArea[], checkIntersection: boolean, checkEdge: boolean) : void;

Parameters:

ParameterTypeDescription
areasCellArea[]The areas.
checkIntersectionbooleanWhether check the intersection of given area with existing validations’ areas. /// If one validation has been applied in given area(or part of it), /// then the existing validation should be removed at first from given area. /// Otherwise corruption may be caused for the generated Validations. /// If user is sure that all the added areas do not intersect with any existing area, /// this parameter can be set as false for performance consideration.
checkEdgebooleanWhether check the edge of this validation’s applied areas. /// Validation’s internal settings depend on the top-left one of its applied ranges, /// so if one of given areas will become the new top-left one of the applied ranges, /// the internal settings should be changed and rebuilt, otherwise unexpected result may be caused. /// If user is sure that no one of those added areas is the top-left, /// this parameter can be set as false for performance consideration.

Remarks

In this method, we will remove all old validations in given area. For the top-left one of Validation’s applied ranges, firstly its StartRow is smallest, secondly its StartColumn is the smallest one of those areas who have the same smallest StartRow.

removeArea(CellArea)

Remove the validation settings in the range.

removeArea(cellArea: CellArea) : void;

Parameters:

ParameterTypeDescription
cellAreaCellAreathe areas where this validation settings should be removed.

removeAreas(CellArea[])

Removes this validation from given areas.

removeAreas(areas: CellArea[]) : void;

Parameters:

ParameterTypeDescription
areasCellArea[]the areas where this validation settings should be removed.

removeACell(number, number)

Remove the validation settings in the cell.

removeACell(row: number, column: number) : void;

Parameters:

ParameterTypeDescription
rownumberThe row index.
columnnumberThe column index.

copy(Validation, CopyOptions)

Copy validation.

copy(source: Validation, copyOption: CopyOptions) : void;

Parameters:

ParameterTypeDescription
sourceValidationThe source validation.
copyOptionCopyOptionsThe copy option.