HotXLS dropdown-lister og inputvalidering i Delphi

HotXLS læser Excels datavalidering dropdown-lister direkte fra Delphi-kode. TryGetCellDropdownItems returnerer kandidatstrengene for en celle, SelectCellDropdownItem skriver én af dem efter indeks eller tekst, og ValidateCellInput rapporterer, om en tastet værdi ville bestå cellens regel, før noget gemmes. De samme metoder findes på TXLSWorksheet for Classic XLS og på TXLSXWorksheet for XLSX, og begge tager 1-baserede række- og kolonnenumre

Den sædvanlige anledning til at få brug for det er en skabelon, du ikke selv designede. En forretningsside leverer en workbook med en Status-kolonne låst til Draft, Review og Final, en Channel-kolonne fyldt fra et interval på et skjult ark og en antalskolonne, der kun accepterer 1 til 100. Dit importjob, din webformular eller din batch-udfylder skal nu respektere de regler, og reglerne bor i workbooken, ikke i din kode. Hårdkodede tilladte værdier ved siden af skabelonen er måden, de to driver fra hinanden på, første gang nogen redigerer listen i Excel

Hvorfor ignorerer kodeskrivninger Excels datavalidering?

Datavalidering er metadata knyttet til et interval, og at håndhæve den er opgaven for den editor, brugeren taster i. I XLSX er reglen et dataValidation-element (ECMA-376 Part 1, §18.3.1.32), der bærer typen, operatoren, formula1 og formula2, prompten og fejlalerten. I en Classic XLS-fil sidder samme information i DV-recorden beskrevet i [MS-XLS]. Intet af formaterne markerer selve cellen, så intet forhindrer et program i at gemme en værdi, der bryder reglen. Excel opfører sig på samme måde: den tjekker tastede indtastninger, og en out-of-range-værdi, der kom ad andre veje, viser sig først, når nogen kører Circle Invalid Data

HotXLS følger den model med vilje. En tildeling som Sheet.Cells[3, 2].Value := 250 gemmer 250 under en 1-til-100-regel uden brok, præcis som den medfølgende DataValidation-demo gør for at bevise pointen. Tjek er opt-in, og resten af denne artikel handler om at tage opt-in på det rigtige sted

At læse dropdown-punkterne bag en celle

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) returnerer True kun, når cellen er dækket af en listetyperegel, og fylder AItems med et TArray<WideString> af kandidater. HotXLS løser de fire måder, en listekilde skrives i praksis, så den kaldende aldrig parser formula1 i hånden:

• En citeret inline-liste som "Draft,Review,Final", opdelt på kommaer
• En inline-liste, hvis punkter er adskilt af #0-tegn, en form fundet i Classic XLS-regler
• Et interval på samme ark eller et andet ark, som =$G$2:$G$10 eller =Lists!$A$2:$A$7, evalueret til dets aktuelle celleværdier
• Et defineret navn som =ProductList, der peger på et sådant interval

Flettede celler håndteres også. Bed om enhver celle inde i en fletning, og HotXLS slår reglen op ved fletningsankoret, som er der, Excel opbevarer både reglen og værdien

AShowDropDown-flaget fortjener en note, for filformaterne gemmer det omvendt. I XLSX skjuler showDropDown="1" faktisk pilen i cellen, og i BIFF8 er den ækvivalente bit et "suppress combo"-flag. HotXLS vender begge ved læsning, så AShowDropDown = True betyder, hvad den siger: Excel ville vise pilen. En listeregel med skjult pil begrænser stadig input, og derfor returnerer TryGetCellDropdownItems punkterne uanset

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: række 2, kolonne 6, begge 1-baserede
    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;

Behøver du kun strengene, returnerer GetCellDropdownItems(ARow, ACol) arrayet direkte og giver et tomt array, når ingen liste gælder. TryGetCellValidationPrompt returnerer inputbeskedens titel og tekst, hvilket er praktisk, når du vil vise skabelonforfatterens eget hint ved siden af et webformularfelt

At vælge et punkt efter indeks eller efter tekst

SelectCellDropdownItem skriver en listepost ind i cellen og returnerer False, når cellen ikke har en listeregel, eller den efterspurgte post ikke findes. Heltals-overloaden tager et 0-baseret indeks ind i arrayet, som TryGetCellDropdownItems returnerede. Tekst-overloaden søger først en eksakt match og falder derefter tilbage til et case-insensitivt match, så 'review' stadig vælger Review

Begge overloads skriver gennem WriteAutoValue, ikke gennem en rå strengtildeling. Det betyder noget for intervalbaserede lister af tal: punkterne kommer tilbage som strenge som '3', men den gemte værdi er tallet 3, hvilket er det, Excel gemmer, når en bruger vælger fra pilen, og hvad enhver formel, der refererer til cellen, forventer. DataValidation-demoen tjekker præcis dette efter en gemning og genåbning i XLS, XLSX og XLSB

// Samme kald på TXLSWorksheet (XLS) og 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. punkt, 0-baseret
  Writeln('F2: index 3 is out of range');

Hvordan validerer man tastet input, før det skrives?

ValidateCellInput(ARow, ACol, AText) tester en kandidatstreng mod reglen, der dækker cellen, og ændrer aldrig cellen. Den returnerer True, når ingen regel gælder, eller kandidaten består. Strengen går gennem samme tekst-til-værdi-konvertering, som WriteAutoValue bruger, så tjekket og den endelige skrivning ser samme værdi. På XLSX-siden er konverteringen XlsxAutoTextValue: den genkender TRUE/FALSE, fejl-literaler, ISO-datoer, m/d/yyyy- og d.m.yyyy-datoer og invariant-format-tal, og den beholder 0123 som tekst, ligesom Excel. Classic XLS-motoren har sin egen konverter, der spejler sin egen WriteAutoValue

To ledsagere kompletterer sættet. ValidateCellValue(ARow, ACol) tjekker den allerede gemte værdi i cellen og behandler tomme celler som gyldige. TryGetCellValidationError returnerer regelens fejlalert-titel, tekst og stil og returnerer False, når reglen slet ikke viser en fejlalert. Stiltyppen afhænger af motoren, så match den til worksheet-klassen: TXLSDvErrorStyle fra lxDataCheck til TXLSWorksheet, TXLSXDvErrorStyle til TXLSXWorksheet

Én adfærd skal du genskabe selv: Excel validerer ikke en tom indtastning eller noget, der starter med =. HotXLS anvender det spring i viewerens redigeringssti, ikke inde i ValidateCellInput, så en server-side port bør gøre det samme, før den kalder den:

uses
  lxDataCheck, lxHandle;

// Returnerer True, når AText må skrives, i spejl af Excels alert-stile
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 springer validering over for ryddede celler og formler
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Ingen fejlalert konfigureret: Excel slipper alt igennem
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop afviser; Warning og Information overlader beslutningen til den kaldende
  Result := Style <> xlsDvErrStop;
end;

// Kaldende (Msg: WideString): valider, og gem så med samme konvertering
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

At parre ValidateCellInput med WriteAutoValue er poenget med eksemplet. Valider med én konvertering og gem med en anden, og før eller siden passerer en værdi tjekket som tal og lander i cellen som tekst

Citerede listepunkter og Classic XLS-escaping-fælden

Inline-listepunkter med dobbelte anførselstegn er en nem måde at miste en regel lydløst på. En inline-liste gemmes som en formelstreng, og en formelstreng escaper et indlejret " ved at fordoble det. Pak punkterne ind i anførselstegn uden at fordoble de indre, og en liste som He said "hi",No producerer en formel, der ikke kompilerer. Der er ingen exception: reglen bliver simpelthen ikke oprettet, og dropdownen ryger med. TXLSWorksheet.AddListValidation og dens XLSX-modstykke fordobler begge indlejrede anførselstegn, så sådanne punkter overlever intakt

TXLSWorksheet.AddListValidation har også en array of WideString-overload, svarende til den på TXLSXWorksheet. Den er praktisk, men læs, hvad den gør: den sammensætter punkterne med kommaer og giver resultatet videre til streng-overloaden. Et punkt, der selv indeholder et komma, bliver derfor til to punkter, og på Classic-motoren opfattes en kildestreng med ! som en arkkvalificeret reference frem for en inline-liste. Til tekster som dem, læg værdierne i et celleinterval og peg reglen på det

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Bekræft, at reglen findes, før du bygger på den
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Efter en gemning og genåbning returnerer TryGetCellDropdownItems på H2 He said "hi" med anførselstegnene intakte, i XLS-, XLSX-, XLSB- og ODS-output ens

At lade TXLSWorkbookViewer håndhæve reglerne

Når brugere redigerer celler på skærmen, anvender TXLSWorkbookViewer de samme tjek for dig. Med Editable sat til True og EnforceDataValidation på sin default True tjekkes en tastet redigering, der bryder en regel med en fejlalert, før den skrives: Stop afviser, Warning spørger, Information oplyser. Vieweren tegner også dropdown-pilen på den aktive celle, åbner listen med et klik eller Alt+Down og viser den gule input-prompt under cellen. Detaljerne om gitteret selv er dækket i artiklen om at bygge et custom spreadsheet-gitter med TXLSWorkbookViewer

Sætter du CircleInvalidData til True, tegnes røde cirkler rundt om gemte værdier, der bryder deres regel, ligesom Excels kommando med samme navn. Den scanner kun det synlige område, udelukker de fleste celler hurtigt gennem FindDataValidationAt og cirkler aldrig en tom celle

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    // genåbn editoren med den afviste tekst
  else
    Action := xvaAccept;
  Handled := True;        // undertryk den indbyggede dialog
end;

Efterlad Handled på False, og vieweren viser sin egen alert-dialog, som virker under både VCL og LCL. Den default bider i automatiserede tests: en test, der driver redigeringer uden en OnValidationError-handler, der sætter Handled, blokerer på en modal dialog. Viewerens stil-enum er en tredje type, TXLSViewerValidationErrorStyle med xveStop, xveWarning og xveInformation, adskilt fra de to worksheet-enums

Hvad bør du vide, før du bygger på HotXLS inputvalidering?

Tjekkene er trofaste for liste-, tal-, dato-, klokkeslæts- og tekstlængde-regler, og nogle få motorspecifikke detaljer er værd at kende:

• På Classic XLS-worksheets rapporterer ValidateCellInput custom-formelregler som gyldige for tastede kandidater, for en kandidat kan ikke midlertidigt placeres i en BIFF-celle uden at forstyrre afhængighedskæden. Tjek den gemte værdi med ValidateCellValue, som evaluerer den custom formel, eller brug XLSX-motoren, som evaluerer custom regler for kandidater også
• På Classic XLS behandler listematching numerisk ens værdier som samme punkt, så 5 og 5.0 matcher begge et punkt på 5
• I vieweren springer indhold, der indsættes, valideringen over, som i Excel. EditSelectedCell har ingen editor at genåbne, så et Retry-resultat opfører sig der som Cancel
• Blotte Cells[...].Value-tildelinger tjekkes aldrig, af design. Validering sker kun, hvor din kode beder om det

At oprette reglerne i første omgang, inklusiv hel-tal-, dato-, tekstlængde- og custom-typer, er dækket i HotXLS-guiden til datavalidering, AutoFilter og tabeller. XLSB-workbooks loades ind i TXLSXWorkbook, så hvert TXLSXWorksheet-kald vist her virker uændret på dem, som beskrevet i artiklen om at læse og skrive XLSB-binære workbooks i Delphi

Dropdown-opløsning, punktvalg og inputvalidering følger med i HotXLS Delphi Excel component til Delphi, C++Builder og Lazarus, sammen med DataValidation- og WorkbookViewer-demoerne, der gennemgår hvert kald i denne artikel