TExcelFile.CheckDataValidation Method

Overloads

TExcelFile.CheckDataValidation(Integer, Integer)

Checks if the data in the cells where the data validation applies conforms to the data validation specifications.

Syntax

Unit: FlexCel.Core

function TExcelFile.CheckDataValidation(const index: Integer; const maxErrors: Integer): TArray<TCellAddress>; overload; virtual; abstract;

Parameters

<-> Parameter Type Description
const index Integer Position in the list of data validations. (1 based)
const maxErrors Integer The maximum number of errors reported by this method. To avoid getting a too big list, set it to a number like 100. If set to 0 or a negative number, the full list of errors will be returned, which can be very big.

Returns

An array with the first maxErrors cells that do not conform to the data validation. And empty array if all the cells conform.

See also

TExcelFile.CheckDataValidation(Integer, Integer, TCellValue, Boolean)

Returns -1 if the value complies with all the data validations at the cell in row, col, or the position of the data validation that causes the error if the value is not valid. The position returned is 1 based, and you can use GetDataValidationInfo to get all information of the data validation.

When the parameter setIsValid is true and the data is valid, then this will also set the cell to value.

Syntax

Unit: FlexCel.Core

function TExcelFile.CheckDataValidation(const row: Integer; const col: Integer; const value: TCellValue; const setIfValid: Boolean): Integer; overload; virtual; abstract;

Parameters

<-> Parameter Type Description
const row Integer Row for the cell where we would like to enter the value. (1 based)
const col Integer Column for the cell where we would like to enter the value. (1 based)
const value TCellValue Value that we want to check if valid.
const setIfValid Boolean When true, the we will also set the cell value if the condition is valid. When false, we will only check if the value is valid, but keep the old value on the cell.

Returns

-1 if the value is valid for the cell according with the data validations on it, the position of the data validation causing the error otherwise. The position returned is 1 based, and you can use GetDataValidationInfo to get all information of the data validation.

Examples

To set a value in a cell if valid, and if not show the error associated to this data validation, you could use code like:

  xls := TXlsFile.Create(sourceFileName, true);
  try
    xls.ActiveSheetByName := 'Sheet With Data';
    dv := xls.CheckDataValidation(row, col, $B, true);  //We will try to enter the number 11 at cell (row, col).
    if dv > 0 then  //There was an error. The cell value is not modified
    begin
      dvInfo := xls.GetDataValidationInfo(dv);
      LogMessage(dvInfo.ErrorBoxText);
    end;
  finally
    xls.Free;
  end;

See also