HotXLS: walidacja danych, autofiltr i tabele w Delphi

Trzy funkcje w HotXLS dzielą arkusz, ale operują na całkowicie różnych obiektach, a kłopoty zaczynają się, gdy zakładasz, że robią podobne rzeczy. Walidacja danych doczepia do zakresu regułę ograniczającą to, co użytkownik może w niego wpisać. Autofiltr doczepia do obszaru zapisaną definicję kryteriów i zmienia to, które wiersze widzi oglądający. Tabela owija zakres w nazwaną, otypowaną strukturę z pasiastym stylowaniem. Jedna ogranicza wejście, druga zapisuje widok, trzecia narzuca schemat. Żadna z nich sama z siebie nie rusza ani jednej wartości komórki, a autofiltr myli ludzi szczególnie, bo samo słowo sugeruje działanie, podczas gdy przechowuje wyłącznie definicję. Wiedza o tym, którego obiektu dotyka każde wywołanie i kiedy efekt naprawdę się materializuje, oddziela skoroszyt zachowujący się w Excelu tak samo jak w twoich testach od takiego, który po cichu się rozjeżdża

Autofiltr przechowuje definicję, nie przycina wierszy

Autofiltr w zapisanym pliku to rekord kryteriów. Ukrywanie wierszy dzieje się później, gdy Excel otwiera skoroszyt i wylicza kryteria na danych. HotXLS zapisuje ten rekord wiernie i niczego nie przycina: każdy odfiltrowany wiersz jest nadal fizycznie obecny w pliku. Potok, który nakłada filtr, żeby wyrzucić odrzucone zamówienia, a potem czyta skoroszyt z powrotem, zobaczy je wszystkie, łącznie z odrzuconymi, i kod jest poprawny według API, a błędny wobec modelu myślowego autora. W arkuszu XLSX SetAutoFilter deklaruje filtrowany obszar, a AddAutoFilterColumn doczepia kryteria do jednej jego kolumny. Kiedy kod po stronie serwera potrzebuje faktycznego wyniku, do liczby wierszy w podsumowaniu albo do przekazania dalej tylko pasujących wierszy, biblioteka wylicza kryteria za ciebie, zamiast udawać, że plik się zmienił:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Identyfikator kolumny 3 = czwarta kolumna WEWNĄTRZ zakresu filtru (przesunięcie od zera)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible zgadza się teraz z tym, co Excel pokaże po otwarciu pliku

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible odpowiada dla pojedynczego wiersza, a PreviewAutoFilterRows przechodzi cały obszar przez wywołanie zwrotne, gdy potrzebujesz pasującego zbioru w jednym przebiegu. Jest przypadek, w którym żadne z nich nie jest właściwą odpowiedzią: jeśli wymaganie brzmi, że wykluczone wiersze nie mogą w ogóle istnieć w pliku, czyli chodzi o cięcie ze względu na prywatność, a nie o widok, usuń te wiersze wprost. Filtr jest tam złym narzędziem, bo każdy odbiorca czyści go jednym kliknięciem i dane, które chciałeś zatrzymać, wracają na ekran

Identyfikator kolumny to przesunięcie, a nie numer kolumny

Komentarz w powyższym fragmencie zaznacza pułapkę, która kosztuje w tym API najwięcej czasu na debugowanie. AddAutoFilterColumn identyfikuje swój cel przez pozycję liczoną od zera wewnątrz zakresu filtru, a nie przez kolumnę arkusza. Dla filtru na A1:E500 oba systemy numeracji różnią się akurat o jeden, co jest dokładnie takim niedopasowaniem, które przeżywa szybki test i psuje się w chwili, gdy kolega filtruje inną kolumnę. Dla filtru zaczynającego się w kolumnie C identyfikator 0 znaczy kolumnę C i rozjazd staje się szybko oczywisty. Kiedy zakres filtru wyliczany jest w czasie działania, wyprowadź identyfikator kolumny z tej samej zmiennej, która zbudowała ciąg zakresu, nigdy ze stałej kolumny arkusza. Każda kolumna przyjmuje drugi warunek przez przeciążenie biorące dwa operatory, dwa kryteria i spójnik i/lub, co odzwierciedla okno filtru niestandardowego w Excelu. Fasada XLS pokrywa ten sam teren przez SetAutoFilter plus ApplyAutoFilter, których parametry kryteriów i operatora idą za starszymi konwencjami w stylu COM i numerują pole od 1. Zmiana fasady oznacza zmianę bazy indeksowania, więc miejsce wywołania zasługuje na komentarz mówiący, która z nich jest w grze

Reguły walidacji to kontrakt, pod którym edytują twoi użytkownicy

Z tych trzech funkcji walidacja jest jedyną, która aktywnie ogranicza przyszłe wejście, i zbiera najwięcej uwagi projektowej w skoroszytach, które wychodzą do wypełnienia i wracają do przetworzenia. Wariant listowy niesie większość tej pracy:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Ilości: liczby całkowite, zero lub więcej
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Poza listami i liczbami całkowitymi ta sama rodzina pokrywa liczby dziesiętne, daty, czasy, długość tekstu i dowolne formuły przez AddCustomValidation, a ogólne AddDataValidation wystawia pełną macierz typów i operatorów dla budowniczych reguł napędzanych konfiguracją. Styl błędu znaczy więcej, niż sugeruje jego nazwa. xlsxDvErrStop odrzuca złe wejście wprost; style ostrzeżenia i informacji przepuszczają wartość po jednym kliknięciu. Wybieraj osobno dla każdej kolumny w zależności od tego, czy kod czytający skoroszyt z powrotem zniesie wartość spoza reguły. Dwie granice należą do tekstu podpowiedzi albo do pliku README, który wysyłasz razem z plikiem. Walidacja w Excelu pilnuje pisania, ale wklejenie bloku na walidowany zakres prześlizguje się obok reguły, więc każdy kod czytający dane z powrotem musi walidować ponownie, zamiast ufać komórkom. A reguła obejmuje dosłownie ten zakres, który jej podałeś, co znaczy, że doczepienie walidacji, zanim znasz ostateczną liczbę wierszy, zostawia dopisany ogon bez ochrony. Najpierw zapisz dane, potem wymierz reguły do faktycznego zasięgu

Starsza fasada oferuje te same rodziny reguł z jedną różnicą ergonomiczną. Kreatory po stronie XLS, mianowicie AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation i AddCustomValidation, zwracają obiekt TDataValidation wprost, a nie indeks, więc konfiguracja podpowiedzi i błędu wiąże się ze zwróconą referencją zamiast z wyszukiwaniem. Wyliczenie operatorów (xlsDvBetween, xlsDvGreaterThan i reszta) odzwierciedla zestaw z XLSX, więc kod budujący reguły przenosi się między fasadami poza tą różnicą w stylu zwracania. Sam tekst podpowiedzi zasługuje na tyle samo namysłu co reguła. Lista rozwijana, która odrzuca wejście pustym okienkiem błędu, uczy użytkowników pisać maile do IT; taka, która nazywa dozwolone stany, uczy ich poprawić komórkę i iść dalej

Jedno odwrócenie polaryzacji, które biblioteka bierze na siebie

Każdy, kto czytał ręcznie XML walidacji OOXML, spotkał odwrócony atrybut showDropDown: w ISO/IEC 29500 wartość prawdy znaczy "ukryj strzałkę listy rozwijanej", czyli przeciwieństwo tego, jak czyta się nazwę. HotXLS odwraca to wewnętrznie, więc właściwość ShowDropDown na regule walidacji znaczy to, co mówi, a prawda pokazuje listę rozwijaną. Jedyny sposób, by się na tym sparzyć, to mieszanie poziomów prawdy: ustawianie właściwości z kodu, podczas gdy kolega audytuje zapisany XML i "poprawia" atrybut, który wygląda mu na odwrócony. Zdecyduj, czy dla narzędzi przeglądu miarodajna jest właściwość, czy surowy XML, i zapisz to odwrócenie tam, gdzie mieszka ta decyzja

Tabele dają zakresowi schemat i nazwę

Tabela arkusza, w terminologii Excela ListObject, owija zakres w nazwę, otypowane kolumny, pasiaste stylowanie i wsparcie dla referencji strukturalnych. To ta funkcja sprawia, że wygenerowany skoroszyt wydaje się skończony, gdy użytkownicy zaczynają go sortować i rozszerzać. Tworzenie jest symetryczne w obu fasadach, a AddTable bierze nazwę, zakres i listę kolumn:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Po stronie XLSX powstały obiekt tabeli wystawia StyleName (wbudowana rodzina TableStyleMedium2 i jej rodzeństwo), przełączniki pasków i flagę wiersza sum, więc nałożenie firmowego stylowania to przypisanie właściwości, a nie ręczny przebieg formatowania. W starszych plikach .xls to samo wywołanie zapisuje rekordy tabeli BIFF8, a fasada oferuje też AddPivotTable dla widoków podsumowujących budowanych z pól wiersza, kolumny i danych — przypomnienie, że "tabele" w starszym formacie sięgają dalej niż ListObject z OOXML. Nazywaj tabele tak, jak nazywasz widoki bazodanowe. Kod niżej w łańcuchu, który czyta Orders[Amount] przez referencję strukturalną, przeżywa zmianę kolejności kolumn, która psuje kod pozycyjny

Dwie konwencje oszczędzają późniejszego sprzątania. Excel wymaga, by nazwy tabel były unikalne w całym skoroszycie, więc generator emitujący jeden arkusz na region potrzebuje schematu w rodzaju Orders_EMEA zamiast wielokrotnego użycia Orders. Duplikat nie zawodzi przy zapisie; wychodzi jako okno naprawy, gdy użytkownik otwiera plik, czyli w najgorszym możliwym miejscu na jego odkrycie. Druga konwencja dotyczy wiersza sum: włączony siedzi bezpośrednio pod zakresem danych, więc każdy kod dopisujący później przez "ostatni użyty wiersz plus jeden" pisze w pasmo sum zamiast za nim. Śledź zasięg danych osobno od zasięgu tabeli, a dopisania wylądują tam, gdzie się spodziewasz

Te trzy funkcje naturalnie się komponują w produktach do wprowadzania danych. Tabela definiuje edytowalny obszar, walidacja ogranicza kolumny, w które użytkownicy piszą, a wstępnie ustawiony filtr oszczędza odbiorcy kilku pierwszych kliknięć. Jest uczciwy argument za wysyłaniem filtru już nałożonego, żeby skoroszyt otwierał się skupiony na wierszach, które mają znaczenie, o ile pamiętasz, że wykluczone wiersze nadal są w pliku, a ciekawski odbiorca potrafi je odsłonić. Sprawne wprowadzenie wyników zapytań do arkusza, czyli górna połowa tego potoku, jest omówione w artykule eksport wyników bazodanowych do Excela z Delphi, a skoroszyty, w których formuły podsumowują walidowane dane, zyskują na nazwach zdefiniowanych dla stabilnych referencji międzyarkuszowych

Walidacja, filtry i tabele to różnica między wysłaniem siatki wartości a wysłaniem małej aplikacji. Pełna referencja reguł, filtrów i tabel jest na stronie produktu HotXLS Delphi Component