AutoFiltry Excela bez Excela w Delphi: HotXLS

HotXLS ocenia kryteria AutoFilter już zapisane w pliku XLSX bez uruchamiania Excela. Od v2.384.136 TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter i AutoFilterRowVisible wykonują listy wartości, grupy dat, filtry Top/Bottom-n i procentowe, filtry dat względnych i średnich, filtry koloru wypełnienia i fontu oraz filtry ikon formatowania warunkowego, pod zamrożoną datą referencyjną i jawnymi limitami zasobów

Zwykłym wyzwalaczem jest skoroszyt, który ktoś przefiltrował w Excelu, i serwer, który ma na nim działać. Ktoś ustawia "Top 10 po przychodzie", "W tym miesiącu" na dacie zamówienia i czerwone wypełnienie na zaległych wierszach, zapisuje i wgrywa plik. Twoja usługa Delphi potrzebuje teraz zestawu wierszy, jaki pokazałby Excel: żeby go policzyć, wyeksportować albo zapisać plik z powrotem z ukrytymi właściwymi wierszami. Plik trzyma tylko definicję. Przed tym wydaniem HotXLS umiał ocenić zwykłe kryteria własne, ale zaawansowane rodzaje siedziały w zapisanym XML bez niczego, co by je wykonywało, więc uczciwą odpowiedzią było "otwórz w Excelu"

Co HotXLS faktycznie wykonuje z zapisanego AutoFiltra?

HotXLS wykonuje jeden rodzaj filtra na kolumnę, wzięty z elementu filterColumn, który zapisał Excel, i łączy kolumny przez AND, dokładnie jak dropdowny Excela. Elementy pochodzą z grupy AutoFilter w ECMA-376 Part 1 §18.3.2, i każdy mapuje się na odrębną ścieżkę oceny:

• filters z dziećmi filter i dateGroupItem: odhaczona lista wartości, opcjonalny wpis pusty i kalendarzowe grupy dat od roku aż do sekundy, sklejone OR-em
• customFilters: jedno albo dwa kryteria porównania sklejone AND-em albo OR-em, ścieżka, która już istniała
• top10: Top albo Bottom n pozycji, albo n procent, z val ograniczonym do 1-500 pozycji albo 1-100 procent
• dynamicFilter: dziś, wczoraj, jutro, ten/poprzedni/następny tydzień, miesiąc, kwartał i rok, od początku roku, M1-M12, Q1-Q4, powyżej średniej i poniżej średniej
• colorFilter i iconFilter: efektywny kolor wypełnienia albo fontu rozwiązany przez styl różnicowy i ikony formatowania warunkowego po zestawie ikon i numerze ikony liczonym od zera

Wszystko poza tą listą zawodzi z EXLSXAutoFilterUnsupported zamiast produkować zgadywany zestaw wierszy. To obejmuje typy powyżej i poniżej odchylenia standardowego ze ST_DynamicFilterType, niegregoriańskie wartości calendarType, nieznane elementy rozszerzeń, nierozwiązane referencje dxfId i kolumnę, która jakoś niesie dwa rodzaje filtrów naraz. Silnik filtra, który po cichu zwraca "mniej więcej" pasujące wiersze, jest gorszy niż taki, który odmawia, bo nikt nie zauważa, dopóki raport nie wyjdzie ze złymi klientami w środku

Najpierw podgląd, potem zastosowanie

HotXLS dzieli pracę na podgląd tylko-do-odczytu i transakcyjne zastosowanie, a oba przyjmują ten sam rekord TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows zwraca liczbę pasujących wierszy danych i nigdy nie dotyka widoczności wierszy. ApplyAutoFilter(AResetVisible, Options) przygotowuje najpierw każdą decyzję o wierszu i dopiero potem publikuje nowy zestaw ukrytych wierszy w jednym kroku, więc wyjątek podczas przygotowania zostawia arkusz dokładnie takim, jakim był

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Options: TXLSXAutoFilterExecutionOptions;
  Matching: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders-filtered-by-sales.xlsx');
    Sheet := Book.Sheets[1];   // indeksator Sheets jest liczony od 1

    // Zawsze startuj od domyślnych, potem zmieniaj pojedyncze pola
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // tylko liczenie
    WriteLn('Rows Excel would show: ', Matching);

    // True: pasujące wiersze stają się widoczne, nawet jeśli były wcześniej ukryte
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Inicjalizuj rekord przez XLSDefaultAutoFilterExecutionOptions za każdym razem. Funkcja zeruje rekord, a potem ustawia każdy limit, a kontekst wykonania odrzuca rekord, którego limity nie są wszystkie dodatnie, wyjątkiem EXLSXAutoFilterLimit. Rekord zadeklarowany na stosie i wypełniany pole po polu będzie zwykle nieść śmieci albo zera w polach, o których zapomniałeś. Przeciążenia bezparametrowe (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) używają tych samych domyślnych, z bieżącą datą jako referencją

Flaga AResetVisible rozstrzyga, co się dzieje z wierszami, które przechodzą filtr, a były już ukryte. True czyni każdy pasujący wiersz widocznym, czyli to, co robi Excel przy ponownym zastosowaniu. False trzyma wcześniejsze ręczne ukrycia na pasujących wierszach i dokłada ukrycia tylko dla wierszy, które nie przechodzą. Wiersze poza zakresem AutoFilter trzymają swój stan w obu przypadkach. Jeśli skoroszyt zmienił się między przygotowaniem a publikacją, ApplyAutoFilter podnosi wyjątek, zamiast publikować decyzje policzone na nieświeżych danych

Dlaczego listy wartości ignorują gwiazdki i znaki zapytania?

Wpisy listy wartości w elemencie filters to literalne tokeny wyświetlania, więc HotXLS porównuje * i ? jako zwykłe znaki. Odhaczony wpis A* w dropdownie znaczy, że komórka pokazuje dokładnie A*, a nie "zaczyna się od A". Dzikie znaki należą do customFilters, gdzie "zaczyna się od" i "zawiera" naprawdę są wzorcami, i one trzymają osobne reguły opisane w jak HotXLS dopasowuje kryteria wieloznaczne Excela i escapowanie tyldą. Pomieszanie tych dwóch semantyk to najszybsza droga do przefiltrowania kolumny numerów części w bezsens

Porównanie idzie po wartości wyświetlanej, co rodzi pytanie, po której wyświetlanej. HotXLS formatuje komórkę jej bazowym formatem liczbowym, łącznie z przechowywanymi nakładkami stylów, i celowo ignoruje format liczbowy, który nakłada na wierzch formatowanie warunkowe. Natywne kontrolki Excela w fixturze HotXLS pokazują, czemu token ma znaczenie: liczbowe 1 i tekst "1" oba pasują do zaznaczenia 1, liczbowe 1 sformatowane jako 0.0 pasuje zamiast tego do 1.0 razem z tekstem "1.0", a tekst "01" zostaje odrębny od obu. Tekst wyglądający jak data nigdy nie jest parsowany na datę, więc tekstowa komórka trzymająca "2026-10-19" nie wpadnie w grupę dat

Jak rozstrzygają filtry Top 10, średnie i dynamiczne dat?

HotXLS liczy progi rangowania i średnich raz na całej kolumnie źródłowej, nie na wierszach, które zostawił widocznymi inny filtr. Do populacji wchodzą tylko liczby skończone, czyli tekst wyglądający liczbowo, jak "100", jest wykluczany, a populacja jest zbierana, zanim zapadnie jakakolwiek decyzja o wierszu. W natywnym skoroszycie kontrolnym filtr powyżej średniej trzyma średnią 27 na całej kolumnie nawet po tym, jak druga kolumna ukryje wartość 50; nie przełącza się na średnią 21,25 pozostałych wierszy

Remisy są włączane na progu, jak w Excelu. Filtr Top 3 po wartościach z dwoma wpisami remisującymi na 20 wybiera cztery wiersze. Tryb procentowy bierze uciętą część populacji liczbowej, z podłogą jednej pozycji, więc Top 50 procent z pięciu wartości wybiera dwie wartości z progiem 30. Warianty Bottom zwierciadlą porównanie. Te liczby pochodzą z własnych zestawów wierszy Excela zapisanych w fixture'ach testowych HotXLS, nie z czytania specyfikacji, która milczy w większości tych punktów

Dynamiczne daty potrzebują zamrożonej daty referencyjnej

TXLSXAutoFilterExecutionOptions.ReferenceDate zamraża znaczenie "dziś" dla całej operacji. Filtry względne, jak ten tydzień albo poprzedni kwartał, i datowe reguły formatowania warunkowego, które zasilają filtry koloru, są wszystkie oceniane względem tej jednej daty, więc podgląd o 23:59:59 i zastosowanie sekundę później się zgadzają. Wartość zero wybiera bieżącą datę raz, gdy operacja startuje; wartości serialne respektują system dat 1900 albo 1904 skoroszytu, łącznie z fikcyjnym numerem seryjnym 60 Excela w systemie 1900 dla filtrów M2 i Q1

Okna to półotwarte interwały kalendarzowe: ten tydzień biegnie od niedzieli do następnej niedzieli, ten kwartał od pierwszego dnia kwartału do pierwszego dnia następnego, a od początku roku od 1 stycznia aż do końca dnia referencyjnego. Przypinaj ReferenceDate jawnie w czymkolwiek, co biegnie w zadaniu wsadowym albo w teście. Nocny job przekraczający północ albo test jednostkowy pisany w październiku, który musi przejść w styczniu, wyprodukcją inaczej zestawy wierszy różniące się bez widocznego powodu

Komórki formuł są filtrowane po ich przechowanych wartościach. Silnik filtra nie przelicza po cichu skoroszytu, więc plik zapisany przez writer, który nie zostawił przechowanych wyników, potrzebuje jawnego przebiegu przeliczenia, zanim filtr zobaczy sensowne liczby albo daty

Jakie kolory widzi filtr koloru?

Filtr koloru HotXLS widzi efektywny kolor z bezpośredniego formatowania komórki plus każdej pasującej nakładki formatowania warunkowego, i nic, co pochodzi wyłącznie ze stylu tabeli. Excel zachowuje się tak samo: pasiasty wiersz zamalowany jasnoniebieskim przez wbudowany styl tabeli nie jest "jasnoniebieski" dla dropdowna filtra. W natywnych kontrolkach komórka zamalowana czerwonym przez formatowanie warunkowe jest wybierana przez filtr czerwonego wypełnienia, podczas gdy komórka sformatowana czerwonym bezpośrednio, ale nadpisana aktywną żółtą regułą, jest wykluczana

Trzy udoskonalenia przyszły w wydaniach pogoni. Wersja 2.384.138 traktuje gradientowe wypełnienia liniowe i path jako odrębne od kolorów pełnych i od braku wypełnienia, więc gradient nigdy nie pasuje do kryterium koloru pełnego, nawet gdy jeden z jego stopów ma dokładnie ten kolor. Wersja 2.384.140 dodała filtrowanie koloru fontu dla rich text, gdzie komórka pasuje, gdy dowolny niepusty run niesie żądany RGB, kolor automatyczny albo odziedziczony. Wersja 2.384.150 dokończyła decyzje warunkowe rich text, które okazały się tą dziwną

Natywny Excel potrafi reużyć decyzję koloru między komórką rich text a zwykłą komórką o tym samym tekście, tym samym stylu bazowym i tej samej pasującej ścieżce reguły warunkowej, więc widoczny zestaw wierszy może zależeć od kolejności tych komórek. HotXLS odtwarza to, przygotowując pełny zakres danych w rosnącej kolejności wierszy, nawet gdy pytasz AutoFilterRowVisible tylko o jeden wiersz. Obejrzenie wiersza 40 kosztuje więc przebieg po zakresie, ale inspekcja wiersza, podgląd i zastosowanie zawsze zwracają tę samą odpowiedź dla tego samego wiersza

Każdy zasób ma limit, i co łapać

HotXLS ogranicza każdy rodzaj pracy w operacji AutoFilter własnym polem w TXLSXAutoFilterExecutionOptions, bo przefiltrowany skoroszyt z formularza uploadu to niezaufane wejście. Domyślne to 1048576 dla MaxXmlCharacters, 16384 dla MaxXmlNodes, 4096 dla MaxFilterItems, 4000000 dla MaxExaminedCells, po 1000000 dla MaxEffectiveFormatCells i MaxPopulationValues, 67108864 bajtów dla MaxContextBytes i 16000000 dla MaxConditionalSteps. MaxContextBytes mierzy przetrzymany stan logiczny i zarezerwowane koperty tymczasowe, nie pamięć procesu

type
  TFilterJob = class
  public
    Deadline: TDateTime;
    VisibleRows: TList<Integer>;
    procedure HandleRow(Sender: TObject; SheetIndex, Row: Integer;
      Visible: Boolean; var Cancel: Boolean);
    procedure HandleWork(Sender: TObject; ACompleted: Int64;
      var ACancel: Boolean);
  end;

procedure TFilterJob.HandleRow(Sender: TObject; SheetIndex, Row: Integer;
  Visible: Boolean; var Cancel: Boolean);
begin
  // Wołane dla każdego wiersza danych; Visible to decyzja filtra
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted liczy obejrzane komórki i może stać w miejscu podczas formatowania
  ACancel := Now > Deadline;
end;

Callback wierszowy PreviewAutoFilterRows dostaje każdy wiersz danych z jego decyzją, nie tylko pasujące, a funkcja wciąż zwraca liczbę pasujących. Ustawienie parametru Cancel stopuje wizytę wcześnie bez wyjątku. OnWork to haczyk anulowania: ustawienie ACancel na True podnosi EXLSXAutoFilterCancelled. Dopóki operacja przygotowuje decyzje, trzyma dzierżawę odczytu na skoroszycie, więc żaden callback nie może tego skoroszytu modyfikować

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // ciaśniejszy limit dla wgranych plików
Options.OnWork := Job.HandleWork;
try
  Sheet.PreviewAutoFilterRows(Job.HandleRow, Options);
  Sheet.ApplyAutoFilter(True, Options);
except
  on E: EXLSXAutoFilterUnsupported do
    Log('Filter needs Excel: ' + E.Message);
  on E: EXLSXAutoFilterLimit do
    Log('Filter exceeded a budget: ' + E.Message);
  on E: EXLSXAutoFilterCancelled do
    Log('Filter cancelled at deadline');
  on E: EXLSXAutoFilterExecution do
    Log('Filter state invalid: ' + E.Message);
end;

Wszystkie trzy specyficzne wyjątki wywodzą się z EXLSXAutoFilterExecution, który sam pokrywa niewłaściwy stan, jak data referencyjna poza wspieranym kalendarzem albo skoroszyt zmieniony przed publikacją, więc trzymaj klasę bazową na końcu. W każdym przypadku widoczność wierszy arkusza jest niezmieniona, co czyni "zaloguj, pomiń, oflaguj plik do ręcznego przeglądu" bezpieczną domyślną polityką

Gdzie są granice

Pokrycie jest szerokie, ale kilka krawędzi warto znać, zanim obieczesz klientowi parzystość:

• Dynamiczne filtry odchylenia standardowego są odrzucane, dopóki nie istnieje zweryfikowana polityka, choć wyliczenie ma dla nich wartości
• Okna tygodniowe startują w niedzielę; skoroszyty polegające na innym początku tygodnia nie są modelowane
• Wyniki formuł pochodzą wyłącznie z przechowanego cache, bez przeliczania wewnątrz wywołania filtra
• Kolory stylów tabel nigdy nie pasują do filtra koloru, z założenia i w zgodzie z Excelem
• Nieznana struktura rich text albo nieznane metadane koloru zawodzą jawnie, podczas gdy znane własności niekolorowe, jak rodzina fontu, nie blokują dopasowania koloru
• Fasada XLS i jej magazyn BIFF8 to osobna ścieżka, opisana w jak HotXLS zapisuje kryteria DOPER AutoFilter BIFF8

Jeśli dopiero rozstrzygasz, jak definicje AutoFilter, walidacja danych i tabele się mają w skoroszycie generowanym przez ciebie samego, zacznij od walidacji danych, AutoFilter i tabel w HotXLS w Delphi, która pokrywa SetAutoFilter, identyfikatory kolumn i moment, w którym filtr faktycznie zaczyna działać

FAQ

Czy PreviewAutoFilterRows zmienia ukrytość wierszy?

Nie. PreviewAutoFilterRows tylko ocenia przechowane kryteria i zgłasza decyzje przez wartość zwracaną i opcjonalny callback. Tylko ApplyAutoFilter publikuje nowy zestaw ukrytych wierszy i robi to po przygotowaniu wszystkich decyzji, więc porażka zostawia pierwotną widoczność nietkniętą

Dlaczego filtr dat względnych daje różne wiersze w różne dni?

Filtry, jak dziś, ten tydzień albo poprzedni miesiąc, są zdefiniowane względem bieżącej daty, dokładnie jak w Excelu. Ustaw ReferenceDate w TXLSXAutoFilterExecutionOptions na stałą datę, gdy potrzebujesz powtarzalnych wyników; wartość zero znaczy datę startu operacji

Czy HotXLS przefiltruje po kolorach ze stylu tabeli?

Nie, i Excel też nie. Filtry koloru HotXLS dopasowują wyłącznie bezpośrednie formatowanie komórek i wspierane nakładki formatowania warunkowego. Kolory zamalowane tylko przez wbudowany albo własny styl tabeli nie uczestniczą, więc wynik filtra koloru zgadza się z tym, co wybrałby dropdown Excela

Zaawansowane wykonanie AutoFilter jest w API arkusza XLSX HotXLS, komponentu arkuszy Delphi do czytania i pisania plików Excel bez Excela, od v2.384.136 wzwyż, z obsługą kolorów gradientowych, rich text i warunkowych rich text dodawaną do v2.384.150