HotXLS-dropdownlijsten en inputvalidatie in Delphi

HotXLS leest Excel-datavalidatie-dropdownlijsten rechtstreeks vanuit Delphi-code. TryGetCellDropdownItems geeft de kandidaat-strings voor een cel terug, SelectCellDropdownItem schrijft er één weg op index of op tekst, en ValidateCellInput meldt of een getypte waarde door de regel van de cel zou komen voordat er iets wordt opgeslagen. Dezelfde methodes bestaan op TXLSWorksheet voor Classic XLS en op TXLSXWorksheet voor XLSX, en beide nemen rij- en kolomnummers op 1-based basis

De gebruikelijke aanleiding om dit nodig te hebben is een template die jij niet ontwierp. Iemand van de business stuurt een workbook met een Status-kolom die vastzit aan Draft, Review en Final, een Channel-kolom die wordt gevoed vanuit een bereik op een verborgen werkblad en een hoeveelheidskolom die alleen 1 tot 100 accepteert. Je importklus, je webformulier of je batch-invuljob moet die regels nu respecteren, en de regels staan in het workbook, niet in jouw code. De toegestane waarden naast de template hardcoden is precies hoe de twee uit elkaar druipen zodra iemand de lijst voor het eerst in Excel bewerkt

Waarom negeren schrijfacties vanuit code Excel-datavalidatie?

Datavalidatie is metadata die aan een bereik hangt, en afdwingen is het werk van welke editor de gebruiker ook in typt. In XLSX is de regel een dataValidation-element (ECMA-376 Part 1, §18.3.1.32) dat het type, de operator, formula1 en formula2, de prompt en de error alert meedraagt. In een Classic XLS-bestand zit dezelfde informatie in het DV-record dat in [MS-XLS] staat beschreven. Geen van beide formaten merkt de cel zelf, dus niets houdt een programma tegen een waarde op te slaan die de regel breekt. Excel gedraagt zich net zo: hij controleert getypte invoer en een waarde buiten bereik die via een andere weg binnenkwam komt pas boven water als iemand Circle Invalid Data draait

HotXLS volgt dat model met opzet. Een toewijzing zoals Sheet.Cells[3, 2].Value := 250 slaat 250 op onder een 1-tot-100-regel zonder mopperen, precies zoals de meegeleverde DataValidation-demo doet om dat punt te bewijzen. Controle is opt-in, en de rest van dit artikel gaat over op het juiste moment in-opten

De dropdownitems achter een cel lezen

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) geeft alleen True terug wanneer de cel door een regel van het type lijst wordt gedekt, en vult AItems met een TArray<WideString> kandidaten. HotXLS lost de vier manieren waarop een lijstbron in de praktijk wordt opgeschreven vanzelf op, dus de aanroeper hoeft formula1 nooit met de hand te parsen:

• Een inline lijst tussen aanhalingstekens zoals "Draft,Review,Final", gesplitst op komma's
• Een inline lijst waarvan de items worden gescheiden door #0-tekens, een vorm die in Classic XLS-regels voorkomt
• Een bereik op hetzelfde werkblad of op een ander werkblad, zoals =$G$2:$G$10 of =Lists!$A$2:$A$7, geëvalueerd naar zijn huidige celwaarden
• Een gedefinieerde naam zoals =ProductList die naar een van die bereiken wijst

Ook samengevoegde cellen worden afgehandeld. Vraag om een cel binnen een samenvoeging en HotXLS zoekt de regel op het samenvoegingsanker, wat de plek is waar Excel zowel de regel als de waarde bewaart

De vlag AShowDropDown verdient een kanttekening, want de bestandsformaten slaan hem omgekeerd op. In XLSX verbergt showDropDown="1" feitelijk de pijl in de cel, en in BIFF8 is het equivalente bit een vlag "suppress combo". HotXLS draait beide bij het lezen om, dus AShowDropDown = True betekent wat hij zegt: Excel zou de pijl tonen. Een lijstregel met verborgen pijl beperkt de invoer nog steeds, en daarom geeft TryGetCellDropdownItems de items hoe dan ook terug

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: rij 2, kolom 6, beide 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;

Heb je alleen de strings nodig, dan geeft GetCellDropdownItems(ARow, ACol) de array rechtstreeks terug en levert een lege array wanneer geen lijst geldt. TryGetCellValidationPrompt geeft de titel en tekst van het invoerbericht terug, handig wanneer je de eigen hint van de templateauteur naast een webformulierveld wilt tonen

Een item kiezen op index of op tekst

SelectCellDropdownItem schrijft een lijstitem in de cel en geeft False terug wanneer de cel geen lijstregel heeft of het gevraagde item niet bestaat. De integer-overload neemt een 0-based index in de array die TryGetCellDropdownItems teruggaf. De tekst-overload zoekt eerst naar een exacte match en valt daarna terug op een match zonder hoofdlettergevoeligheid, dus 'review' pikt nog steeds Review

Beide overloads schrijven via WriteAutoValue, niet via een rauwe stringtoewijzing. Dat telt voor bereikgebaseerde lijsten van getallen: de items komen terug als strings zoals '3', maar de opgeslagen waarde is het getal 3, wat Excel opslaat wanneer een gebruiker uit de pijl kiest en wat elke formule die naar de cel verwijst verwacht. De DataValidation-demo controleert precies dit na een opslag en heropenen in XLS, XLSX en XLSB

// Dezelfde aanroepen op TXLSWorksheet (XLS) en 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  // 4e item, 0-based
  Writeln('F2: index 3 is out of range');

Hoe valideer je getypte invoer voordat je hem wegschrijft?

ValidateCellInput(ARow, ACol, AText) toetst een kandidaatstring aan de regel die de cel dekt en wijzigt de cel nooit. Hij geeft True terug wanneer geen regel geldt of de kandidaat erdoor komt. De string gaat door dezelfde tekst-naar-waarde-conversie die WriteAutoValue gebruikt, dus de controle en het uiteindelijke schrijven zien dezelfde waarde. Aan de XLSX-kant is die conversie XlsxAutoTextValue: hij herkent TRUE/FALSE, foutliterals, ISO-datums, m/d/yyyy- en d.m.yyyy-datums en getallen in invariant formaat, en hij houdt 0123 als tekst, net zoals Excel. De Classic XLS-engine heeft een eigen converter die zijn eigen WriteAutoValue spiegelt

Twee metgezellen maken de set compleet. ValidateCellValue(ARow, ACol) controleert de waarde die al in de cel staat en behandelt lege cellen als geldig. TryGetCellValidationError geeft de titel, tekst en stijl van de error alert van de regel terug en geeft False terug wanneer de regel helemaal geen error alert toont. Het stijltype verschilt per engine, dus koppel hem aan de worksheetklasse: TXLSDvErrorStyle uit lxDataCheck voor TXLSWorksheet, TXLSXDvErrorStyle voor TXLSXWorksheet

Eén gedrag moet je zelf nabouwen: Excel valideert geen lege invoer en niets dat met = begint. HotXLS past die overslag toe in het editpad van de viewer, niet binnen ValidateCellInput, dus een server-side poort moet hetzelfde doen voordat hij hem aanroept:

uses
  lxDataCheck, lxHandle;

// Geeft True terug wanneer AText weggeschreven mag worden, naar de alert-stijlen van Excel
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 slaat validatie over voor geleegde cellen en formules
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Geen error alert ingesteld: Excel laat alles door
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop weigert; Warning en Information laten de beslissing aan de aanroeper
  Result := Style <> xlsDvErrStop;
end;

// Aanroeper (Msg: WideString): valideer en sla op met dezelfde conversie
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

ValidateCellInput koppelen aan WriteAutoValue is het punt van het voorbeeld. Valideer met de ene conversie en sla op met de andere, en vroeger of later komt een waarde door de controle als getal en landt hij als tekst in de cel

Lijstitems tussen aanhalingstekens en de Classic XLS-escapevalkuil

Inline lijstitems met dubbele aanhalingstekens zijn een makkelijke manier om stilletjes een regel te verliezen. Een inline lijst wordt opgeslagen als formulestring, en een formulestring escapet een ingesloten " door hem te verdubbelen. Zet je de items tussen aanhalingstekens zonder de binnenste te verdubbelen, dan levert een lijst zoals He said "hi",No een formule op die niet compileert. Er komt geen exception: de regel wordt simpelweg niet aangemaakt en de dropdown gaat mee de fles in. TXLSWorksheet.AddListValidation en zijn XLSX-tegenhanger verdubbelen beide ingesloten aanhalingstekens, dus zulke items overleven intact

TXLSWorksheet.AddListValidation heeft ook een array of WideString-overload, gelijk aan die op TXLSXWorksheet. Die is handig, maar lees wat hij doet: hij plakt de items aan elkaar met komma's en geeft het resultaat door aan de stringoverload. Een item dat zelf een komma bevat wordt daardoor twee items, en op de Classic-engine wordt een bronstring met ! opgevat als een bladgekwalificeerde verwijzing in plaats van een inline lijst. Voor bijschriften zoals die: zet de waarden in een celbereik en richt de regel erop

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Bevestig dat de regel bestaat voordat je erop vertrouwt
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Na een opslag en heropenen geeft TryGetCellDropdownItems op H2 He said "hi" terug met zijn aanhalingstekens intact, en dat geldt voor XLS-, XLSX-, XLSB- en ODS-output

TXLSWorkbookViewer de regels laten afdwingen

Als gebruikers cellen op het scherm bewerken, past TXLSWorkbookViewer dezelfde controles voor je toe. Met Editable op True en EnforceDataValidation op zijn default True wordt een getypte bewerking die een regel met error alert breekt gecontroleerd voordat hij wordt weggeschreven: Stop weigert, Warning vraagt, Information informeert. De viewer tekent ook de dropdownpijl op de actieve cel, opent de lijst met een klik of Alt+Down en toont de gele invoerprompt onder de cel. De details van het raster zelf worden behandeld in het artikel over het bouwen van een eigen spreadsheetraster met TXLSWorkbookViewer

CircleInvalidData op True zetten tekent rode cirkels rond opgeslagen waarden die hun regel overtreden, zoals het gelijknamige commando van Excel. Hij scant alleen het zichtbare gebied, schaart de meeste cellen snel weg via FindDataValidationAt en cirkelt nooit een lege cel

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    // open de editor opnieuw met de geweigerde tekst
  else
    Action := xvaAccept;
  Handled := True;        // onderdruk de ingebouwde dialog
end;

Laat Handled op False en de viewer toont zijn eigen alert-dialog, die onder zowel VCL als LCL werkt. Die default bijt in geautomatiseerde tests: een test die bewerkingen aandrijft zonder een OnValidationError-handler die Handled zet, blokkeert op een modaal dialog. De stijl-enum van de viewer is een derde type, TXLSViewerValidationErrorStyle met xveStop, xveWarning en xveInformation, te onderscheiden van de twee worksheet-enums

Wat moet je weten voordat je op HotXLS-inputvalidatie vertrouwt?

De controles zijn getrouw voor lijst-, getal-, datum-, tijd- en tekstlengte-regels, en een paar enginespecifieke details zijn goed om te weten:

• Op Classic XLS-werkbladen meldt ValidateCellInput custom-formula-regels als geldig voor getypte kandidaten, want een kandidaat kan niet tijdelijk in een BIFF-cel worden gezet zonder de afhankelijkheidsketen te verstoren. Controleer de opgeslagen waarde met ValidateCellValue, dat de custom formule evalueert, of gebruik de XLSX-engine, die custom regels ook voor kandidaten evalueert
• Op Classic XLS behandelt lijstmatching numeriek gelijke waarden als hetzelfde item, dus 5 en 5.0 matchen allebei een item van 5
• In de viewer slaat geplakte inhoud de validatie over, zoals in Excel. EditSelectedCell heeft geen editor om te heropenen, dus een Retry-resultaat gedraagt zich daar als Cancel
• Kale Cells[...].Value-toewijzingen worden nooit gecontroleerd, bij ontwerp. Validatie gebeurt alleen waar jouw code erom vraagt

De regels zelf aanmaken, inclusief de types geheel getal, datum, tekstlengte en custom, wordt behandeld in de HotXLS-gids voor datavalidatie, AutoFilter en tabellen. XLSB-workbooks laden in TXLSXWorkbook, dus elke hier getoonde TXLSXWorksheet-aanroep werkt er ongewijzigd, zoals beschreven in het artikel over het lezen en schrijven van XLSB-binaire workbooks in Delphi

Dropdownresolutie, itemkeuze en inputvalidatie worden meegeleverd met de HotXLS Delphi Excel-component voor Delphi, C++Builder en Lazarus, samen met de DataValidation- en WorkbookViewer-demo's die elke aanroep uit dit artikel uitproberen