HotXLS Dropdown Lists and Input Validation in Delphi

HotXLS reads Excel data validation dropdown lists directly from Delphi code. TryGetCellDropdownItems returns the candidate strings for a cell, SelectCellDropdownItem writes one of them by index or by text, and ValidateCellInput reports whether a typed value would pass the cell's rule before anything is stored. The same methods exist on TXLSWorksheet for Classic XLS and on TXLSXWorksheet for XLSX, and both take 1-based row and column numbers

The usual trigger for needing this is a template you did not design. Someone on the business side ships a workbook with a Status column locked to Draft, Review and Final, a Channel column fed from a range on a hidden sheet, and a quantity column that only accepts 1 to 100. Your import job, your web form or your batch filler now has to respect those rules, and the rules live in the workbook, not in your code. Hard-coding the allowed values next to the template is how the two drift apart the first time someone edits the list in Excel

Why do code writes ignore Excel data validation?

Data validation is metadata attached to a range, and enforcing it is the job of whatever editor the user types into. In XLSX the rule is a dataValidation element (ECMA-376 Part 1, §18.3.1.32) carrying the type, the operator, formula1 and formula2, the prompt and the error alert. In a Classic XLS file the same information sits in the DV record described in [MS-XLS]. Neither format marks the cell itself, so nothing stops a program from storing a value that breaks the rule. Excel behaves the same way: it checks typed entries, and an out-of-range value that arrived by other means only shows up when someone runs Circle Invalid Data

HotXLS follows that model on purpose. An assignment such as Sheet.Cells[3, 2].Value := 250 stores 250 under a 1-to-100 rule without complaint, exactly like the bundled DataValidation demo does to prove the point. Checking is opt-in, and the rest of this article is about opting in at the right place

Reading the dropdown items behind a cell

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) returns True only when the cell is covered by a list-type rule, and fills AItems with a TArray<WideString> of candidates. HotXLS resolves the four ways a list source is written in practice, so the caller never parses formula1 by hand:

• A quoted inline list such as "Draft,Review,Final", split on commas
• An inline list whose items are separated by #0 characters, a form found in Classic XLS rules
• A range on the same sheet or on another sheet, such as =$G$2:$G$10 or =Lists!$A$2:$A$7, evaluated to its current cell values
• A defined name such as =ProductList that points at one of those ranges

Merged cells are handled too. Ask for any cell inside a merge and HotXLS looks up the rule at the merge anchor, which is where Excel keeps both the rule and the value

The AShowDropDown flag deserves a note, because the file formats store it backwards. In XLSX, showDropDown="1" actually hides the in-cell arrow, and in BIFF8 the equivalent bit is a "suppress combo" flag. HotXLS flips both on read, so AShowDropDown = True means what it says: Excel would show the arrow. A list rule with the arrow hidden still restricts input, which is why TryGetCellDropdownItems returns the items either way

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2: row 2, column 6, both 1-based
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

If you only need the strings, GetCellDropdownItems(ARow, ACol) returns the array directly and yields an empty array when no list applies. TryGetCellValidationPrompt returns the input-message title and text, which is handy when you want to show the template author's own hint next to a web form field

Selecting an item by index or by text

SelectCellDropdownItem writes a list entry into the cell and returns False when the cell has no list rule or the requested entry does not exist. The integer overload takes a 0-based index into the array that TryGetCellDropdownItems returned. The text overload looks for an exact match first and then falls back to a case-insensitive match, so 'review' still picks Review

Both overloads write through WriteAutoValue, not through a raw string assignment. That matters for range-backed lists of numbers: the items come back as strings like '3', but the stored value is the number 3, which is what Excel stores when a user picks from the arrow and what any formula referencing the cell expects. The DataValidation demo checks exactly this after a save and reopen in XLS, XLSX and XLSB

// Same calls on TXLSWorksheet (XLS) and TXLSXWorksheet (XLSX)
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 4th item, 0-based
  Writeln('F2: index 3 is out of range');

How do you validate typed input before writing it?

ValidateCellInput(ARow, ACol, AText) tests a candidate string against the rule covering the cell and never modifies the cell. It returns True when no rule applies or the candidate passes. The string goes through the same text-to-value conversion that WriteAutoValue uses, so the check and the eventual write see the same value. On the XLSX side that conversion is XlsxAutoTextValue: it recognises TRUE/FALSE, error literals, ISO dates, m/d/yyyy and d.m.yyyy dates and invariant-format numbers, and it keeps 0123 as text the way Excel does. The Classic XLS engine has its own converter that mirrors its own WriteAutoValue

Two companions complete the set. ValidateCellValue(ARow, ACol) checks the value already stored in the cell and treats blank cells as valid. TryGetCellValidationError returns the rule's error-alert title, text and style, and returns False when the rule shows no error alert at all. The style type differs per engine, so match it to the worksheet class: TXLSDvErrorStyle from lxDataCheck for TXLSWorksheet, TXLSXDvErrorStyle for TXLSXWorksheet

One behaviour you have to reproduce yourself: Excel does not validate an empty entry or anything starting with =. HotXLS applies that skip in the viewer's edit path, not inside ValidateCellInput, so a server-side gate should do the same before calling it:

uses
  lxDataCheck, lxHandle;

// Returns True when AText may be written, mirroring Excel's alert styles
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel skips validation for cleared cells and formulas
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // No error alert configured: Excel lets anything through
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop rejects; Warning and Information leave the decision to the caller
  Result := Style <> xlsDvErrStop;
end;

// Caller (Msg: WideString): validate, then store with the same conversion
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Pairing ValidateCellInput with WriteAutoValue is the point of the example. Validate with one conversion and store with another, and sooner or later a value passes the check as a number and lands in the cell as text

Quoted list items and the Classic XLS escaping trap

Inline list items that contain double quotes are an easy way to lose a rule silently. An inline list is stored as a formula string, and a formula string escapes an embedded " by doubling it. Wrap the items in quotes without doubling the inner ones, and a list like He said "hi",No produces a formula that does not compile. There is no exception: the rule is simply not created, and the dropdown goes with it. TXLSWorksheet.AddListValidation and its XLSX counterpart both double embedded quotes, so such items survive intact

TXLSWorksheet.AddListValidation also has an array of WideString overload, matching the one on TXLSXWorksheet. It is convenient, but read what it does: it joins the items with commas and hands the result to the string overload. An item that itself contains a comma therefore turns into two items, and on the Classic engine a source string containing ! is taken as a sheet-qualified reference rather than an inline list. For captions like those, put the values in a cell range and point the rule at it

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Confirm the rule exists before relying on it
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

After a save and reopen, TryGetCellDropdownItems on H2 returns He said "hi" with its quotes intact, in XLS, XLSX, XLSB and ODS output alike

Letting TXLSWorkbookViewer enforce the rules

When users edit cells on screen, TXLSWorkbookViewer applies the same checks for you. With Editable set to True and EnforceDataValidation left at its default of True, a typed edit that breaks a rule with an error alert is checked before it is written: Stop rejects it, Warning asks, Information informs. The viewer also draws the dropdown arrow on the active cell, opens the list with a click or Alt+Down, and shows the yellow input prompt below the cell. The details of the grid itself are covered in the article on building a custom spreadsheet grid with TXLSWorkbookViewer

Setting CircleInvalidData to True draws red circles around stored values that violate their rule, like Excel's command of the same name. It only scans the visible area, rules out most cells quickly through FindDataValidationAt, and never circles an empty cell

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // reopen the editor with the rejected text
  else
    Action := xvaAccept;
  Handled := True;        // suppress the built-in dialog
end;

Leave Handled at False and the viewer shows its own alert dialog, which works under both VCL and LCL. That default bites in automated tests: a test that drives edits without an OnValidationError handler setting Handled will block on a modal dialog. The viewer's style enum is a third type, TXLSViewerValidationErrorStyle with xveStop, xveWarning and xveInformation, distinct from the two worksheet enums

What should you know before relying on HotXLS input validation?

The checks are faithful for list, number, date, time and text-length rules, and a few engine-specific details are worth knowing:

• On Classic XLS worksheets, ValidateCellInput reports custom-formula rules as valid for typed candidates, because a candidate cannot be placed in a BIFF cell temporarily without disturbing the dependency chain. Check the stored value with ValidateCellValue, which evaluates the custom formula, or use the XLSX engine, which evaluates custom rules for candidates as well
• On Classic XLS, list matching treats numerically equal values as the same item, so 5 and 5.0 both match an item of 5
• In the viewer, pasted content skips validation, as in Excel. EditSelectedCell has no editor to reopen, so a Retry result there behaves like Cancel
• Plain Cells[...].Value assignments are never checked, by design. Validation happens only where your code asks for it

Creating the rules in the first place, including whole-number, date, text-length and custom types, is covered in the HotXLS guide to data validation, AutoFilter and tables. XLSB workbooks load into TXLSXWorkbook, so every TXLSXWorksheet call shown here works unchanged on them, as described in the article on reading and writing XLSB binary workbooks in Delphi

Dropdown resolution, item selection and input validation ship in the HotXLS Delphi Excel component for Delphi, C++Builder and Lazarus, together with the DataValidation and WorkbookViewer demos that exercise every call in this article