Listy dropdown i walidacja danych wejściowych w HotXLS

HotXLS czyta listy rozwijane walidacji danych Excela wprost z kodu Delphi. TryGetCellDropdownItems zwraca kandydujące stringi dla komórki, SelectCellDropdownItem zapisuje jedną z nich po indeksie albo po tekście, a ValidateCellInput raportuje, czy wpisana wartość przeszłaby regułę komórki, zanim cokolwiek zostanie zapisane. Te same metody istnieją na TXLSWorksheet dla Classic XLS i na TXLSXWorksheet dla XLSX, a obie biorą numery wierszy i kolumn liczone od 1

Typowym wyzwalaczem potrzeby jest szablon, którego nie projektowałeś. Ktoś od strony biznesowej wysyła skoroszyt z kolumną Status zablokowaną do Draft, Review i Final, kolumną Channel zasilaną z zakresu na ukrytym arkuszu i kolumną ilości przyjmującą tylko od 1 do 100. Twoje zadanie importu, twój formularz webowy albo twój filler wsadowy musi teraz te reguły respektować, a reguły mieszkają w skoroszycie, nie w twoim kodzie. Zahardkodowanie dozwolonych wartości obok szablonu to sposób na to, żeby oba rozjechały się przy pierwszej edycji listy w Excelu

Dlaczego zapisy z kodu ignorują walidację danych Excela?

Walidacja danych to metadane przypięte do zakresu, a egzekwowanie jej to robota edytora, w który użytkownik wpisuje. W XLSX reguła to element dataValidation (ECMA-376 Part 1, §18.3.1.32) niosący typ, operator, formula1 i formula2, prompt i alert błędu. W pliku Classic XLS te same informacje siedzą w rekordzie DV opisanym w [MS-XLS]. Żaden format nie oznacza samej komórki, więc nic nie powstrzyma programu przed zapisaniem wartości łamiącej regułę. Excel zachowuje się tak samo: kontroluje wpisywane pozycje, a wartość spoza zakresu, która przyszła inną drogą, wychodzi na jaw dopiero, gdy ktoś puszcza Circle Invalid Data

HotXLS celowo trzyma się tego modelu. Przypisanie jak Sheet.Cells[3, 2].Value := 250 zapisze 250 pod regułą 1-do-100 bez słowa protestu, dokładnie jak dołączony demo DataValidation, żeby to udowodnić. Sprawdzanie jest opt-in, a reszta tego artykułu jest o tym, gdzie dobrze optować

Czytanie elementów dropdown stojących za komórką

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) zwraca True tylko wtedy, gdy komórka jest pokryta regułą typu lista, i wypełnia AItems tablicą TArray<WideString> kandydatów. HotXLS rozwiązuje cztery sposoby, w jakie w praktyce zapisuje się źródło listy, więc wywołujący nigdy nie parsuje formula1 ręcznie:

• Cytowana lista inline jak "Draft,Review,Final", dzielona po przecinkach
• Lista inline, której pozycje są rozdzielone znakami #0, forma spotykana w regułach Classic XLS
• Zakres na tym samym arkuszu albo na innym, jak =$G$2:$G$10 albo =Lists!$A$2:$A$7, ewaluowany do bieżących wartości komórek
• Nazwa zdefiniowana jak =ProductList wskazująca na jeden z takich zakresów

Komórki scalone też są obsłużone. Zapytaj o dowolną komórkę wewnątrz scalenia, a HotXLS zajrzy do reguły pod kotwicą scalenia, bo tam Excel trzyma i regułę, i wartość

Flaga AShowDropDown zasługuje na notkę, bo formaty plików trzymają ją odwróconą. W XLSX showDropDown="1" faktycznie chowa strzałkę w komórce, a w BIFF8 odpowiedni bit to flaga „suppress combo". HotXLS odwraca oba przy odczycie, więc AShowDropDown = True znaczy to, co mówi: Excel pokazałby strzałkę. Reguła listy z ukrytą strzałką nadal ogranicza dane wejściowe, dlatego TryGetCellDropdownItems zwraca pozycje w obu przypadkach

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: wiersz 2, kolumna 6, obie liczone od 1
    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;

Jeśli potrzebujesz samych stringów, GetCellDropdownItems(ARow, ACol) zwraca tablicę bezpośrednio i daje pustą tablicę, gdy żadna lista nie obowiązuje. TryGetCellValidationPrompt zwraca tytuł i treść komunikatu wejściowego, co jest wygodne, gdy chcesz pokazać własną podpowiedź autora szablonu obok pola formularza webowego

Wybieranie pozycji po indeksie albo po tekście

SelectCellDropdownItem zapisuje pozycję listy do komórki i zwraca False, gdy komórka nie ma reguły listy albo żądana pozycja nie istnieje. Overload całkowitoliczbowy bierze indeks liczony od 0 w tablicę zwróconą przez TryGetCellDropdownItems. Overload tekstowy szuka najpierw dopasowania dokładnego, potem spada na dopasowanie bez rozróżniania wielkości liter, więc 'review' nadal wybierze Review

Oba overloady zapisują przez WriteAutoValue, nie przez surowe przypisanie stringa. To ma znaczenie dla list zasilanych zakresami liczb: pozycje wracają jako stringi jak '3', ale zapisywana wartość to liczba 3 — dokładnie to, co Excel zapisuje, gdy użytkownik wybiera ze strzałki, i to, czego oczekuje każda formuła referująca komórkę. Demo DataValidation sprawdza dokładnie to po zapisie i ponownym otwarciu w XLS, XLSX i XLSB

// Te same wywołania na TXLSWorksheet (XLS) i 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. pozycja, od 0
  Writeln('F2: index 3 is out of range');

Jak walidować wpisywane dane przed zapisaniem?

ValidateCellInput(ARow, ACol, AText) testuje kandydacki string względem reguły pokrywającej komórkę i nigdy komórki nie modyfikuje. Zwraca True, gdy żadna reguła nie obowiązuje albo kandydat przechodzi. String przechodzi przez tę samą konwersję tekst-na-wartość, której używa WriteAutoValue, więc kontrola i docelowy zapis widzą tę samą wartość. Po stronie XLSX tą konwersją jest XlsxAutoTextValue: rozpoznaje TRUE/FALSE, literały błędów, daty ISO, daty m/d/yyyy i d.m.yyyy oraz liczby w formacie niezmennym i trzyma 0123 jako tekst, tak jak Excel. Silnik Classic XLS ma własny konwerter odzwierciedlający własne WriteAutoValue

Dwa towarzysze dopełniają zestaw. ValidateCellValue(ARow, ACol) sprawdza wartość już zapisaną w komórce i traktuje puste komórki jako ważne. TryGetCellValidationError zwraca tytuł, treść i styl alertu błędu reguły, a zwraca False, gdy reguła w ogóle nie pokazuje alertu błędu. Typ stylu różni się per silnik, więc dopasuj go do klasy arkusza: TXLSDvErrorStyle z lxDataCheck dla TXLSWorksheet, TXLSXDvErrorStyle dla TXLSXWorksheet

Jedno zachowanie musisz odtworzyć sam: Excel nie waliduje pustego wpisu ani niczego zaczynającego się od =. HotXLS stosuje to pominięcie w ścieżce edycji przeglądarki, nie wewnątrz ValidateCellInput, więc bramka po stronie serwera powinna zrobić to samo przed jej wołaniem:

uses
  lxDataCheck, lxHandle;

// Zwraca True, gdy AText można zapisać, na wzór stylów alertów Excela
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 pomija walidację dla wyczyszczonych komórek i formuł
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Brak skonfigurowanego alertu błędu: Excel przepuszcza wszystko
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop odrzuca; Warning i Information zostawiają decyzję wywołującemu
  Result := Style <> xlsDvErrStop;
end;

// Wywołujący (Msg: WideString): waliduj, potem zapisz tą samą konwersją
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Połączenie ValidateCellInput z WriteAutoValue to sedno przykładu. Waliduj jedną konwersją, a zapisuj inną, i prędzej czy później wartość przejdzie kontrolę jako liczba, a wyląduje w komórce jako tekst

Cytowane pozycje list i pułapka escapowania Classic XLS

Pozycje list inline zawierające cudzysłowy to łatwy sposób na ciche stracenie reguły. Lista inline jest zapisywana jako string formuły, a string formuły escapuje osadzone " przez podwojenie. Owiń pozycje w cudzysłowy bez podwojenia wewnętrznych, a lista jak He said "hi",No wyprodukuje formułę, która się nie kompiluje. Nie ma wyjątku: reguła po prostu nie powstaje, a dropdown znika razem z nią. TXLSWorksheet.AddListValidation i jego odpowiednik XLSX podwajają oba osadzone cudzysłowy, więc takie pozycje przeżywają nietknięte

TXLSWorksheet.AddListValidation ma też overload array of WideString, zgodny z tym na TXLSXWorksheet. Jest wygodny, ale przeczytaj, co robi: skleja pozycje przecinkami i podaje wynik overloadowi stringowemu. Pozycja, która sama zawiera przecinek, zamienia się więc w dwie pozycje, a na silniku Classic źródłowy string zawierający ! jest brany jako referencja kwalifikowana arkuszem, nie jako lista inline. Dla podpisów w tym stylu włóż wartości do zakresu komórek i skieruj regułę na niego

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Potwierdź, że reguła istnieje, zanim na niej polegisz
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

Po zapisie i ponownym otwarciu TryGetCellDropdownItems na H2 zwraca He said "hi" z cudzysłowami nietkniętymi, w wyjściu XLS, XLSX, XLSB i ODS bez wyjątku

Pozwolenie TXLSWorkbookViewer egzekwować reguły

Gdy użytkownicy edytują komórki na ekranie, TXLSWorkbookViewer stosuje te same kontrole za ciebie. Z Editable ustawionym na True i EnforceDataValidation pozostawionym na domyślnym True edycja wpisana łamiąca regułę z alertem błędu jest sprawdzana przed zapisem: Stop odrzuca, Warning pyta, Information informuje. Przeglądarka rysuje też strzałkę dropdown na aktywnej komórce, otwiera listę kliknięciem albo Alt+Down i pokazuje żółty prompt wejściowy pod komórką. Detale samej siatki opisuje artykuł o budowaniu własnej siatki arkusza z TXLSWorkbookViewer

Ustawienie CircleInvalidData na True rysuje czerwone kółka wokół zapisanych wartości łamiących swoją regułę, jak polecenie Excela o tej samej nazwie. Skanuje tylko widoczny obszar, szybko odsiewa większość komórek przez FindDataValidationAt i nigdy nie kółkuje pustej komórki

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    // otwórz edytor ponownie z odrzuconym tekstem
  else
    Action := xvaAccept;
  Handled := True;        // wycisz wbudowane okno dialogowe
end;

Zostaw Handled na False, a przeglądarka pokaże własne okno alertu, działające pod VCL i LCL. Ten domyślny stan gryzie w testach automatycznych: test prowadzący edycje bez handlera OnValidationError ustawiającego Handled zablokuje się na modalnym oknie. Enum stylów przeglądarki to trzeci typ, TXLSViewerValidationErrorStyle z xveStop, xveWarning i xveInformation, odrębny od dwóch enumów arkusza

Co warto wiedzieć, zanim oprzesz się na walidacji wejścia HotXLS?

Kontrole są wierne dla reguł listy, liczby, daty, czasu i długości tekstu, a kilka detali zależnych od silnika warto znać:

• Na arkuszach Classic XLS ValidateCellInput raportuje reguły custom-formula jako ważne dla kandydatów wpisywanych, bo kandydat nie da się tymczasowo włożyć do komórki BIFF bez poruszenia łańcucha zależności. Sprawdź zapisaną wartość przez ValidateCellValue, która ewaluuje formułę custom, albo użyj silnika XLSX, który ewaluuje reguły custom także dla kandydatów
• Na Classic XLS dopasowanie listy traktuje wartości równe liczbowo jako tę samą pozycję, więc 5 i 5.0 obie pasują do pozycji 5
• W przeglądarce wklejona treść pomija walidację, jak w Excelu. EditSelectedCell nie ma edytora do ponownego otwarcia, więc wynik Retry zachowuje się tam jak Cancel
• Gołe przypisania Cells[...].Value nigdy nie są sprawdzane, z założenia. Walidacja dzieje się tylko tam, gdzie twój kod o nią prosi

Tworzenie reguł od zera, łącznie z typami liczba-całkowita, data, długość-tekstu i custom, opisuje przewodnik HotXLS po walidacji danych, AutoFilter i tabelach. Skoroszyty XLSB ładują się do TXLSXWorkbook, więc każde pokazane tu wywołanie TXLSXWorksheet działa na nich bez zmian, jak opisano w artykule o czytaniu i pisaniu binarnych skoroszytów XLSB w Delphi

Rozwiązywanie dropdown, wybieranie pozycji i walidacja wejścia płyną w komponencie HotXLS Delphi Excel dla Delphi, C++Builder i Lazarus, razem z demami DataValidation i WorkbookViewer ćwiczącymi każde wywołanie z tego artykułu