HotXLS-Dropdownlisten und Eingabeprüfung in Delphi

HotXLS liest Excel-Datenüberprüfungs-Dropdownlisten direkt aus Delphi-Code. TryGetCellDropdownItems liefert die Kandidaten-Strings für eine Zelle, SelectCellDropdownItem schreibt einen davon per Index oder per Text, und ValidateCellInput meldet, ob ein getippter Wert die Zellregel bestehen würde, bevor irgendetwas gespeichert wird. Dieselben Methoden existieren auf TXLSWorksheet für Classic XLS und auf TXLSXWorksheet für XLSX, und beide nehmen 1-basierte Zeilen- und Spaltennummern

Der übliche Auslöser dafür ist eine Vorlage, die du nicht entworfen hast. Jemand aus dem Fachbereich liefert ein Workbook mit einer Status-Spalte, auf Draft, Review und Final festgelegt, einer Kanal-Spalte, gespeist aus einem Bereich auf einem versteckten Sheet, und einer Mengen-Spalte, die nur 1 bis 100 akzeptiert. Dein Import-Job, dein Web-Formular oder dein Batch-Füller muss diese Regeln nun respektieren, und die Regeln leben im Workbook, nicht in deinem Code. Die erlaubten Werte hart neben der Vorlage zu kodieren ist der Weg, wie beide auseinanderdriften, sobald jemand die Liste das erste Mal in Excel editiert

Warum ignorieren Code-Schreibungen die Excel-Datenüberprüfung?

Datenüberprüfung ist Metadaten, angeheftet an einen Bereich, und sie durchzusetzen ist die Aufgabe des Editors, in den der Anwender tippt. In XLSX ist die Regel ein dataValidation-Element (ECMA-376 Part 1, §18.3.1.32), das Typ, Operator, formula1 und formula2, den Prompt und die Fehlerwarnung trägt. In einer Classic-XLS-Datei sitzen dieselben Informationen im DV-Record, beschrieben in [MS-XLS]. Keines der Formate markiert die Zelle selbst, nichts hält also ein Programm davon ab, einen Wert zu speichern, der die Regel bricht. Excel verhält sich genauso: Es prüft getippte Einträge, und ein außerhalb des Bereichs liegender Wert, der auf anderen Wegen ankam, zeigt sich erst, wenn jemand Circle Invalid Data laufen lässt

HotXLS folgt jenem Modell bewusst. Eine Zuweisung wie Sheet.Cells[3, 2].Value := 250 speichert 250 unter einer 1-bis-100-Regel ohne Murren, genau wie die mitgelieferte DataValidation-Demo es tut, um den Punkt zu beweisen. Prüfen ist Opt-in, und der Rest dieses Artikels handelt davon, am richtigen Ort einzuschalten

Die Dropdown-Einträge hinter einer Zelle lesen

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) liefert True, nur wenn die Zelle von einer Listenregel abgedeckt ist, und füllt AItems mit einem TArray<WideString> aus Kandidaten. HotXLS löst die vier Arten auf, wie eine Listenquelle in der Praxis geschrieben wird, der Aufrufer parst also nie formula1 von Hand:

• Eine in Anführungszeichen gesetzte Inline-Liste wie "Draft,Review,Final", an Kommas gespalten
• Eine Inline-Liste, deren Einträge durch #0-Zeichen getrennt sind, eine Form, die in Classic-XLS-Regeln vorkommt
• Ein Bereich auf demselben oder auf einem anderen Sheet, etwa =$G$2:$G$10 oder =Lists!$A$2:$A$7, ausgewertet zu seinen aktuellen Zellwerten
• Ein definierter Name wie =ProductList, der auf einen dieser Bereiche zeigt

Verbundene Zellen werden ebenfalls behandelt. Frag nach einer beliebigen Zelle innerhalb eines Verbunds, und HotXLS schlägt die Regel am Verbindungsanker nach, genau dort, wo Excel sowohl Regel als auch Wert hält

Das AShowDropDown-Flag verdient eine Notiz, denn die Dateiformate speichern es verkehrt herum. In XLSX versteckt showDropDown="1" tatsächlich den Zellpfeil, und in BIFF8 ist das äquivalente Bit ein "Suppress-Combo"-Flag. HotXLS dreht beide beim Lesen um, also bedeutet AShowDropDown = True das, was es sagt: Excel würde den Pfeil zeigen. Eine Listenregel mit verstecktem Pfeil beschränkt die Eingabe weiterhin, weshalb TryGetCellDropdownItems die Einträge in beiden Fällen liefert

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: Zeile 2, Spalte 6, beide 1-basiert
    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;

Brauchst du nur die Strings, liefert GetCellDropdownItems(ARow, ACol) das Array direkt und ergibt ein leeres Array, wenn keine Liste greift. TryGetCellValidationPrompt liefert Titel und Text der Eingabenachricht, praktisch, wenn du den eigenen Hinweis des Vorlagenautors neben einem Web-Formularfeld zeigen willst

Einen Eintrag per Index oder per Text wählen

SelectCellDropdownItem schreibt einen Listeneintrag in die Zelle und liefert False, wenn die Zelle keine Listenregel hat oder der angefragte Eintrag nicht existiert. Der Integer-Overload nimmt einen 0-basierten Index in das Array, das TryGetCellDropdownItems lieferte. Der Text-Overload sucht zuerst nach exaktem Match und fällt dann auf einen case-insensitiven Match zurück, also wählt 'review' weiterhin Review

Beide Overloads schreiben über WriteAutoValue, nicht über eine rohe String-Zuweisung. Das zählt für bereichsgestützte Nummernlisten: Die Einträge kommen als Strings wie '3' zurück, aber der gespeicherte Wert ist die Zahl 3, genau das, was Excel speichert, wenn ein Anwender am Pfeil wählt, und was jede Formel erwartet, die die Zelle referenziert. Die DataValidation-Demo prüft genau das nach einem Sichern und Wiederöffnen in XLS, XLSX und XLSB

// Dieselben Aufrufe auf TXLSWorksheet (XLS) und 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  // 4. Eintrag, 0-basiert
  Writeln('F2: index 3 is out of range');

Wie prüfst du getippte Eingaben, bevor du sie schreibst?

ValidateCellInput(ARow, ACol, AText) testet einen Kandidaten-String gegen die Regel, die die Zelle abdeckt, und verändert die Zelle nie. Es liefert True, wenn keine Regel greift oder der Kandidat besteht. Der String läuft durch dieselbe Text-zu-Wert-Konvertierung, die WriteAutoValue verwendet, Prüfung und späteres Schreiben sehen also denselben Wert. Auf der XLSX-Seite ist jene Konvertierung XlsxAutoTextValue: Sie erkennt TRUE/FALSE, Fehlerliterale, ISO-Daten, m/d/yyyy- und d.m.yyyy-Daten sowie Zahlen im invarianten Format und behält 0123 als Text, so wie Excel es tut. Die Classic-XLS-Engine hat ihren eigenen Konverter, der ihr eigenes WriteAutoValue spiegelt

Zwei Begleiter vervollständigen den Satz. ValidateCellValue(ARow, ACol) prüft den bereits in der Zelle gespeicherten Wert und behandelt leere Zellen als gültig. TryGetCellValidationError liefert Titel, Text und Stil der Fehlerwarnung der Regel und liefert False, wenn die Regel gar keine Fehlerwarnung zeigt. Der Stil-Typ unterscheidet sich je Engine, also matche ihn auf die Worksheet-Klasse: TXLSDvErrorStyle aus lxDataCheck für TXLSWorksheet, TXLSXDvErrorStyle für TXLSXWorksheet

Ein Verhalten, das du selbst nachbilden musst: Excel validiert keinen leeren Eintrag und nichts, das mit = beginnt. HotXLS wendet diesen Aussprung im Editierpfad des Viewers an, nicht innerhalb von ValidateCellInput, also sollte ein serverseitiges Tor dasselbe tun, bevor es ihn ruft:

uses
  lxDataCheck, lxHandle;

// Liefert True, wenn AText geschrieben werden darf, spiegelt Excels Warnstile
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 überspringt die Validierung für geleerte Zellen und Formeln
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Keine Fehlerwarnung konfiguriert: Excel lässt alles durch
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop weist ab; Warning und Information überlassen die Entscheidung dem Aufrufer
  Result := Style <> xlsDvErrStop;
end;

// Aufrufer (Msg: WideString): prüfen, dann mit derselben Konvertierung speichern
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

ValidateCellInput mit WriteAutoValue zu paaren ist der Punkt des Beispiels. Mit einer Konvertierung prüfen und mit einer anderen speichern, und früher oder später passiert ein Wert die Prüfung als Zahl und landet als Text in der Zelle

Quotierte Listeneinträge und die Classic-XLS-Escaping-Falle

Inline-Listeneinträge mit doppelten Anführungszeichen sind ein bequemer Weg, eine Regel lautlos zu verlieren. Eine Inline-Liste wird als Formel-String gespeichert, und ein Formel-String escaped ein eingebettetes " durch Verdopplung. Wickel die Einträge in Anführungszeichen, ohne die inneren zu verdoppeln, und eine Liste wie He said "hi",No erzeugt eine Formel, die nicht kompiliert. Es gibt keine Exception: Die Regel wird schlicht nicht erzeugt, und die Dropdownliste geht mit unter. TXLSWorksheet.AddListValidation und sein XLSX-Pendant verdoppeln beide eingebettete Anführungszeichen, solche Einträge überleben also unangetastet

TXLSWorksheet.AddListValidation hat außerdem einen array of WideString-Overload, passend zumjenigen auf TXLSXWorksheet. Bequem ist er, aber lies, was er tut: Er verbindet die Einträge mit Kommas und übergibt das Ergebnis an den String-Overload. Ein Eintrag, der selbst ein Komma enthält, wird also zu zwei Einträgen, und auf der Classic-Engine wird ein Quellstring mit ! als sheet-qualifizierte Referenz statt als Inline-Liste genommen. Für Beschriftungen wie jene lege die Werte in einen Zellbereich und zeige die Regel darauf

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Bestätige, dass die Regel existiert, bevor du dich auf sie verlässt
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Nach einem Sichern und Wiederöffnen liefert TryGetCellDropdownItems auf H2 He said "hi" mit intakten Anführungszeichen, in XLS-, XLSX-, XLSB- und ODS-Ausgabe gleichermaßen

TXLSWorkbookViewer die Regeln durchsetzen lassen

Wenn Anwender Zellen am Bildschirm editieren, setzt TXLSWorkbookViewer dieselben Prüfungen für dich durch. Mit Editable auf True und EnforceDataValidation auf seinem Default True lasend, wird eine getippte Änderung, die eine Regel mit Fehlerwarnung bricht, vor dem Schreiben geprüft: Stop weist ab, Warning fragt, Information informiert. Der Viewer zeichnet zudem den Dropdown-Pfeil auf der aktiven Zelle, öffnet die Liste per Klick oder Alt+Down und zeigt den gelben Eingabeprompt unter der Zelle. Die Details des Gitters selbst behandelt der Artikel zum Bauen eines eigenen Spreadsheet-Gitters mit TXLSWorkbookViewer

CircleInvalidData auf True zu setzen zeichnet rote Kreise um gespeicherte Werte, die ihre Regel verletzen, wie Excels Befehl desselben Namens. Er scannt nur den sichtbaren Bereich, scheidet die meisten Zellen schnell über FindDataValidationAt aus und kreist nie eine leere Zelle ein

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    // den Editor mit dem abgewiesenen Text wieder öffnen
  else
    Action := xvaAccept;
  Handled := True;        // den eingebauten Dialog unterdrücken
end;

Lass Handled auf False, und der Viewer zeigt seinen eigenen Warndialog, der unter VCL wie LCL funktioniert. Dieser Default beißt bei automatisierten Tests: Ein Test, der Änderungen fährt, ohne dass ein OnValidationError-Handler Handled setzt, blockiert auf einem modalen Dialog. Der Stil-Enum des Viewers ist ein dritter Typ, TXLSViewerValidationErrorStyle mit xveStop, xveWarning und xveInformation, getrennt von den beiden Worksheet-Enums

Was solltest du wissen, bevor du dich auf HotXLS-Eingabevalidierung verlässt?

Die Prüfungen sind getreu für Listen-, Zahlen-, Datums-, Zeit- und Textlängenregeln, und ein paar engine-spezifische Details sind gut zu wissen:

• Auf Classic-XLS-Worksheets meldet ValidateCellInput Custom-Formel-Regeln als gültig für getippte Kandidaten, denn ein Kandidat lässt sich nicht temporär in einer BIFF-Zelle platzieren, ohne die Abhängigkeitskette zu stören. Prüfe den gespeicherten Wert mit ValidateCellValue, das die Custom-Formel auswertet, oder nutze die XLSX-Engine, die Custom-Regeln für Kandidaten ebenfalls auswertet
• Auf Classic XLS behandelt der Listenabgleich numerisch gleiche Werte als denselben Eintrag, 5 und 5.0 matchen also beide einen Eintrag von 5
• Im Viewer überspringt eingefügter Inhalt die Validierung, wie in Excel. EditSelectedCell hat keinen Editor zum Wiederöffnen, ein Retry-Ergebnis verhält sich dort also wie Cancel
• Schlichte Cells[...].Value-Zuweisungen werden nie geprüft, per Design. Validierung passiert nur dort, wo dein Code danach fragt

Die Regeln erst zu erzeugen, inklusive Ganzzahl-, Datums-, Textlängen- und Custom-Typen, behandelt der HotXLS-Leitfaden zu Datenüberprüfung, AutoFilter und Tabellen. XLSB-Workbooks laden in TXLSXWorkbook, also funktioniert jeder hier gezeigte TXLSXWorksheet-Aufruf unverändert auf ihnen, wie im Artikel zum Lesen und Schreiben binärer XLSB-Workbooks in Delphi beschrieben

Dropdown-Auflösung, Eintragswahl und Eingabevalidierung kommen mit der HotXLS Delphi Excel component für Delphi, C++Builder und Lazarus, zusammen mit den DataValidation- und WorkbookViewer-Demos, die jeden Aufruf dieses Artikels ausführen