TXLSXDataValidation / TXLSXDataValidations classes

Per-worksheet dataValidation rules. Each rule restricts cell input to a numeric range, a date / time, a text length, a list of choices, or an arbitrary custom formula. The collection lives on TXLSXWorksheet.DataValidations. Declared in lxHandleX.

TXLSXDataValidationType declaration

type
  TXLSXDataValidationType = (
    xlsxDvNone,
    xlsxDvWhole,
    xlsxDvDecimal,
    xlsxDvList,
    xlsxDvDate,
    xlsxDvTime,
    xlsxDvTextLength,
    xlsxDvCustom);

TXLSXDvOperator declaration

type
  TXLSXDvOperator = (
    xlsxDvOpBetween,
    xlsxDvOpNotBetween,
    xlsxDvOpEqual,
    xlsxDvOpNotEqual,
    xlsxDvOpGreaterThan,
    xlsxDvOpLessThan,
    xlsxDvOpGreaterOrEqual,
    xlsxDvOpLessOrEqual);

TXLSXDataValidation declaration

type
  TXLSXDataValidation = class
    constructor Create(const ARange: WideString; AType: TXLSXDataValidationType;
      AOp: TXLSXDvOperator; const AFormula1, AFormula2: WideString);
    property Range: WideString;
    property ValidationType: TXLSXDataValidationType;
    property Op: TXLSXDvOperator;
    property Formula1: WideString;
    property Formula2: WideString;
    property AllowBlank: Boolean;
    property ShowInputMessage: Boolean;
    property ShowErrorMessage: Boolean;
  end;

TXLSXDataValidation members

Range	OOXML `sqref` string — the cells the rule applies to.
ValidationType	The validation category. `xlsxDvList` uses Formula1 as the source list (range or quoted comma-separated literal).
Op	Comparison operator. Most relevant for numeric / date / time / textLength categories.
Formula1, Formula2	Validation operands. Formula2 is only used for between / notBetween operators.
AllowBlank	When `True` (default), an empty cell is treated as valid.
ShowInputMessage / ShowErrorMessage	Toggles for Excel's input-tip popup and the error dialog when invalid data is entered.

TXLSXDataValidations declaration

type
  TXLSXDataValidations = class
    function Add(const ARange: WideString; AType: TXLSXDataValidationType;
      AOp: TXLSXDvOperator; const AFormula1: WideString): Integer; overload;
    function Add(const ARange: WideString; AType: TXLSXDataValidationType;
      AOp: TXLSXDvOperator; const AFormula1, AFormula2: WideString): Integer; overload;
    function AddList(const ARange, AItems: WideString): Integer;
    procedure Clear;
    property Count: Integer;
    property Items[Index: Integer]: TXLSXDataValidation; default;
  end;

Worksheet shortcuts: Worksheet.AddDataValidation and Worksheet.AddListValidation(Range, Items).

TXLSXDataValidations members

Add overloads	Generic add for any validation type. Pass `Formula2` for between / notBetween rules.
AddList(Range, Items)	Convenience for the dropdown case. If `Items` looks like a range reference (contains a `!`) it is used as-is; otherwise it is wrapped in double quotes (so `'Yes,No,Maybe'` becomes `"Yes,No,Maybe"` on disk, which is what Excel expects).

Example

begin
  // Dropdown list of three values in A1:A5.
  Worksheet.AddListValidation('A1:A5', 'Yes,No,Maybe');

  // Numeric range: B1:B10 must be a whole number between 1 and 100.
  Worksheet.AddDataValidation('B1:B10', xlsxDvWhole, xlsxDvOpBetween, '1', '100');

  // Text length up to 20 characters in C1:C100.
  Worksheet.AddDataValidation('C1:C100', xlsxDvTextLength, xlsxDvOpLessOrEqual, '20');
end;