Dropdown seznamy a validace vstupu v HotXLS

HotXLS čte dropdown seznamy Excel data validation přímo z kódu v Delphi. TryGetCellDropdownItems vrátí kandidátní řetězce pro buňku, SelectCellDropdownItem zapíše jeden z nich podle indexu nebo podle textu a ValidateCellInput ohlásí, jestli zapsaná hodnota projde pravidlem buňky, ještě než se cokoliv uloží. Tytéž metody existují na TXLSWorksheet pro Classic XLS a na TXLSXWorksheet pro XLSX a obě berou čísla řádků a sloupců od jedničky

Obvyklý spouštěč potřeby je šablona, kterou jste nenavrhovali. Někdo z byznysu pošle sešit se sloupcem Status uzamčeným na Draft, Review a Final, se sloupcem Channel krmeným z oblasti na skrytém listu a se sloupcem množství přijímajícím jen 1 až 100. Váš importní job, váš webový formulář nebo váš dávkový plnič teď musí ta pravidla respektovat a pravidla žijí v sešitu, ne ve vašem kódu. Natvrdo zapsat povolené hodnoty vedle šablony je způsob, jak se obě věci rozejdou při první úpravě seznamu v Excelu

Proč zápis z kódu ignoruje Excel data validation?

Data validation je metadata přichycená k oblasti a její vynucování je práce editoru, do kterého uživatel píše. V XLSX je pravidlem element dataValidation (ECMA-376 Part 1, §18.3.1.32) nesoucí typ, operátor, formula1 a formula2, výzvu a chybové upozornění. V Classic XLS souboru sedí tytéž informace v záznamu DV popsaném v [MS-XLS]. Ani jeden formát neoznačuje buňku samotnou, takže nic nebrání programu uložit hodnotu, která pravidlo láme. Excel se chová stejně: kontroluje zapsané položky a hodnota mimo rozsah, která přišla jinudy, se ukáže, až někdo spustí Circle Invalid Data

HotXLS se drží toho modelu záměrně. Přiřazení jako Sheet.Cells[3, 2].Value := 250 uloží 250 pod pravidlem 1 až 100 bez námitek, přesně jako to bundlovaná ukázka DataValidation dělá, aby to dokázala. Kontrolování je opt-in a zbytek článku je o tom, kde se opt-in dělá správně

Čtení dropdown položek za buňkou

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) vrací True jen tehdy, když buňku pokrývá pravidlo typu seznam, a naplní AItems polem TArray<WideString> kandidátů. HotXLS rozpozná čtyři způsoby, jak se zdroj seznamu v praxi zapisuje, takže volající nikdy neparuje formula1 ručně:

• Quoted inline seznam jako "Draft,Review,Final", dělený podle čárek
• Inline seznam, jehož položky dělí znaky #0, forma objevující se v pravidlech Classic XLS
• Oblast na stejném listu nebo na jiném listu, jako =$G$2:$G$10 nebo =Lists!$A$2:$A$7, vyhodnocená na aktuální hodnoty buněk
• Definovaný název jako =ProductList mířící na jednu z těch oblastí

Sloučené buňky jsou ošetřené taky. Zeptejte se na jakoukoli buňku uvnitř merge a HotXLS najde pravidlo na merge ukotvení, kde je Excel drží i hodnotu

Příznak AShowDropDown si zaslouží poznámku, protože ho formáty ukládají obráceně. V XLSX showDropDown="1" ve skutečnosti šipku v buňce skrývá a v BIFF8 je ekvivalentní bit příznakem „suppress combo". HotXLS obojí při čtení překlápí, takže AShowDropDown = True znamená to, co říká: Excel by šipku ukázal. Pravidlo seznamu se skrytou šipkou i tak omezuje vstup, a proto TryGetCellDropdownItems vrací položky v obou případech

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: řádek 2, sloupec 6, obojí od jedničky
    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;

Pokud potřebujete jen řetězce, GetCellDropdownItems(ARow, ACol) vrací pole přímo a dá prázdné pole, když žádný seznam neplatí. TryGetCellValidationPrompt vrátí název a text vstupní zprávy, což se hodí, když chcete vedle pole webového formuláře ukázat vlastní nápovědu autora šablony

Výběr položky podle indexu nebo podle textu

SelectCellDropdownItem zapíše položku seznamu do buňky a vrátí False, když buňka nemá pravidlo seznamu nebo požadovaná položka neexistuje. Celočíselný overload bere index od nuly do pole, které vrátila TryGetCellDropdownItems. Textový overload hledá nejdřív přesnou shodu a pak se uchýlí ke shodě bez ohledu na velikost písmen, takže 'review' vybere Review taky

Oba overloady zapisují přes WriteAutoValue, ne přes surové přiřazení řetězce. To má význam u seznamů podložených oblastí s čísly: položky se vrátí jako řetězce jako '3', ale uložená hodnota je číslo 3, což je to, co Excel uloží, když uživatel vybere ze šipky, a co čeká každý vzorec odkazující na buňku. Ukázka DataValidation přesně tohle kontroluje po uložení a znovunačtení v XLS, XLSX i XLSB

// Týž volání na TXLSWorksheet (XLS) a 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. položka, od nuly
  Writeln('F2: index 3 is out of range');

Jak ověřit zapsaný vstup před uložením?

ValidateCellInput(ARow, ACol, AText) otestuje kandidátní řetězec proti pravidlu pokrývajícímu buňku a buňku nikdy nezmění. Vrací True, když žádné pravidlo neplatí nebo kandidát projde. Řetězec projde stejnou konverzí textu na hodnotu, kterou používá WriteAutoValue, takže kontrola a pozdější zápis vidí tutéž hodnotu. Na straně XLSX je tahle konverze XlsxAutoTextValue: poznává TRUE/FALSE, literály chyb, ISO datumy, datumy m/d/yyyy a d.m.yyyy a čísla v invariantním formátu a 0123 si nechává jako text, stejně jako Excel. Classic XLS engine má vlastní konvertor zrcadlící vlastní WriteAutoValue

Soubor doplňků sadu dokonává. ValidateCellValue(ARow, ACol) kontroluje hodnotu už uloženou v buňce a prázdné buňky bere za platné. TryGetCellValidationError vrací název, text a styl chybového upozornění pravidla a vrací False, když pravidlo žádné chybové upozornění nezobrazuje. Typ stylu se liší podle enginu, takže ho párujte s třídou worksheet: TXLSDvErrorStyle z lxDataCheck pro TXLSWorksheet a TXLSXDvErrorStyle pro TXLSXWorksheet

Jedno chování si musíte zreplikovat sami: Excel nevaliduje prázdný vstup ani nic začínající =. HotXLS tenhle skip aplikuje v editační cestě prohlížeče, ne uvnitř ValidateCellInput, takže serverová brána by měla udělat totéž, než ho zavolá:

uses
  lxDataCheck, lxHandle;

// Vrací True, když AText smí být zapsáno, zrcadlí styly upozornění Excelu
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 přeskočí validaci u vymazaných buněk a vzorců
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Bez nakonfigurovaného chybového upozornění Excel pustí cokoliv
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop odmítá; Warning a Information nechávají rozhodnutí volajícímu
  Result := Style <> xlsDvErrStop;
end;

// Volající (Msg: WideString): validuj, pak ukládej se stejnou konverzí
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Párování ValidateCellInput s WriteAutoValue je smysl příkladu. Validujte jednou konverzí a ukládejte jinou a dřív nebo později hodnota projde kontrolou jako číslo a do buňky dopadne jako text

Quoted položky seznamu a past s escapingem Classic XLS

Inline položky seznamu obsahující dvojité uvozovky jsou snadný způsob, jak tiše ztratit pravidlo. Inline seznam se ukládá jako vzorcový řetězec a vzorcový řetězec escapuje vložené " zdvojením. Oblete položky do uvozovek bez zdvojení vnitřních a seznam jako He said "hi",No vyprodukuje vzorec, který se nezkompiluje. Výjimka není: pravidlo se prostě nevytvoří a dropdown jde s ním. TXLSWorksheet.AddListValidation i jeho XLSX protějšek vložené uvozovky zdvojují, takže takové položky přežijí vcelku

TXLSWorksheet.AddListValidation má i overload s array of WideString, odpovídající tomu na TXLSXWorksheet. Je pohodlný, ale čtěte, co dělá: položky spojí čárkami a výsledek předá string overloadu. Položka, která sama obsahuje čárku, se z toho stane dvěma položkami a na Classic enginu se zdrojový řetězec obsahující ! bere jako reference kvalifikovaná listem, ne jako inline seznam. Pro titulky takovéhoto ražení dejte hodnoty do oblasti buněk a namiřte pravidlo na ni

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Ověřte, že pravidlo existuje, než se na něj spolehnete
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Po uložení a znovunačtení vrátí TryGetCellDropdownItems na H2 He said "hi" s uvozovkami vcelku, a to stejně v XLS, XLSX, XLSB i ODS výstupu

Nechat TXLSWorkbookViewer pravidla vynucovat

Když uživatelé editují buňky na obrazovce, aplikuje TXLSWorkbookViewer tytéž kontroly za vás. S Editable nastaveným na True a EnforceDataValidation ponechaným na defaultu True se zapsaná editace lamající pravidlo s chybovým upozorněním kontroluje, ještě než se zapíše: Stop odmítne, Warning se zeptá, Information informuje. Prohlížeč taky kreslí dropdown šipku na aktivní buňku, otevírá seznam kliknutím nebo Alt+Down a ukazuje žlutý vstupní prompt pod buňkou. Detaily samotné mřížky pokrývá článek o stavbě vlastní spreadsheet mřížky s TXLSWorkbookViewer

Nastavení CircleInvalidData na True kreslí červené kruhy kolem uložených hodnot, které porušují své pravidlo, jako Excelův příkaz stejného jména. Skenuje jen viditelnou oblast, většinu buněk rychle vyřadí přes FindDataValidationAt a prázdnou buňku nikdy neokruží

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    // znovu otevřít editor s odmítnutým textem
  else
    Action := xvaAccept;
  Handled := True;        // potlačit vestavěný dialog
end;

Necháte-li Handled na False, prohlížeč ukáže vlastní alert dialog, který funguje pod VCL i LCL. Tenhle default kousne v automatizovaných testech: test řídící editace bez handleru OnValidationError nastavujícího Handled se zasekne na modálním dialogu. Style enum prohlížeče je třetí typ, TXLSViewerValidationErrorStyle s xveStop, xveWarning a xveInformation, odlišný od obou worksheet enumů

Co vědět, než se na HotXLS validaci vstupu spolehnete?

Kontroly jsou věrné u pravidel seznamu, čísla, datumu, času a délky textu a pár engine-specifických detailů stojí za znalost:

• Na worksheetech Classic XLS hlásí ValidateCellInput pravidla s vlastním vzorcem jako platná pro kandidáty z textu, protože kandidáta nelze do BIFF buňky dočasně umístit, aniž by se rozvrtal řetězec závislostí. Uloženou hodnotu zkontrolujte ValidateCellValue, která vlastní vzorec vyhodnocuje, nebo použijte XLSX engine, který vlastní pravidla vyhodnocuje i pro kandidáty
• Na Classic XLS bere shoda v seznamu číselně rovné hodnoty za tutéž položku, takže 5 i 5.0 se shodují s položkou 5
• V prohlížeči vkládaný obsah (paste) validaci přeskočí, jako v Excelu. EditSelectedCell nemá editor k znovuotevření, takže se tam výsledek Retry chová jako Cancel
• Obyčejná přiřazení Cells[...].Value se nekontrolují nikdy, záměrně. Validace probíhá jen tam, kde si ji váš kód žádá

Tvorba pravidel jako taková, včetně typů celé číslo, datum, délka textu a vlastní vzorec, je pokrytá v průvodci HotXLS data validation, AutoFilter a tabulkami. Sešity XLSB se načítají do TXLSXWorkbook, takže každé volání TXLSXWorksheet ukázané tady funguje na nich beze změny, jak popisuje článek o čtení a zápisu binárních sešitů XLSB v Delphi

Řešení dropdownů, výběr položek a validaci vstupu dodává HotXLS Delphi Excel component pro Delphi, C++Builder a Lazarus, spolu s ukázkami DataValidation a WorkbookViewer, které protáhnou každé volání z tohohle článku