Spuštění Excel AutoFilters bez Excelu v Delphi: HotXLS

HotXLS vyhodnotí kritéria AutoFilter už uložená v souboru XLSX, aniž by spustil Excel. Od v2.384.136 provádějí TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter a AutoFilterRowVisible seznamy hodnot, skupiny dat, filtry Top/Bottom-n a procenta, relativní data a filtry průměru, filtry barvy výplně a fontu a filtry ikon podmíněného formátování, pod zamrazeným referenčním datem a explicitními limity zdrojů

Obvyklým spouštěčem je sešit, který někdo vyfiltroval v Excelu, a server, který na něm musí něco udělat. Někdo nastaví „Top 10 podle obratu", „Tento měsíc" u data objednávky a červenou výplň po termínu, uloží a nahraje soubor. Vaše služba v Delphi teď potřebuje sadu řádků, kterou by Excel ukázal: spočítat ji, exportovat ji, nebo zapsat soubor zpět se správně skrytými řádky. Soubor drží jen definici. Před tímto vydáním uměl HotXLS vyhodnotit obyčejná vlastní kritéria, ale pokročilé druhy seděly v uloženém XML bez ničeho, co by je provedlo, takže poctivá odpověď byla „otevřete to v Excelu"

Co HotXLS z uloženého AutoFilteru doopravdy provede?

HotXLS provádí jeden druh filtru na sloupec, vzatý z elementu filterColumn, který Excel zapsal, a kombinuje sloupce přes AND, přesně jako dropdowny Excelu. Elementy pocházejí ze skupiny AutoFilter v ECMA-376 Part 1 §18.3.2 a každý se mapuje na odlišnou vyhodnocovací cestu:

• filters s dětmi filter a dateGroupItem: zaškrtnutý seznam hodnot, volitelná prázdná položka a kalendářní skupiny dat od roku až po sekundu, spojené OR
• customFilters: jedno nebo dvě srovnávací kritéria spojená AND nebo OR, cesta, která už existovala
• top10: Top nebo Bottom n položek, nebo n procent, s val omezeným na 1–500 položek nebo 1–100 procent
• dynamicFilter: dnes, včera, zítra, tento/minulý/příští týden, měsíc, čtvrtletí a rok, year to date, M1–M12, Q1–Q4, nad průměrem a pod průměrem
• colorFilter a iconFilter: efektivní barva výplně nebo fontu vyřešená přes diferenciální styl a ikony podmíněného formátování podle sady ikon a ikonového id od nuly

Cokoli mimo ten seznam selže s EXLSXAutoFilterUnsupported místo vyprodukce uhádnuté sady řádků. To zahrnuje typy nad a pod směrodatnou odchylkou ST_DynamicFilterType, negregoriánské hodnoty calendarType, neznámé extension elementy, nevyřešené reference dxfId a sloupec, který nějak nese dva druhy filtrů najednou. Filtrační engine, který tiše vrátí „téměř správné" řádky, je horší než ten, který odmítne, protože si toho nikdo nevšimne, dokud nezajde zpráva se špatnými zákazníky

Nejdřív náhled, potom aplikace

HotXLS dělí práci na read-only náhled a transakční aplikaci a obojí bere tentýž záznam TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows vrací počet odpovídajících datových řádků a nikdy nesáhne na viditelnost řádků. ApplyAutoFilter(AResetVisible, Options) nejdřív připraví každé rozhodnutí o řádku a teprve pak publikuje novou sadu skrytých řádků jediným krokem, takže výjimka během přípravy nechá list přesně tak, jak byl

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];   // indexér Sheets se počítá od 1

    // Vždy začínejte od defaultů, pak měňte jednotlivá pole
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // jen počet
    WriteLn('Rows Excel would show: ', Matching);

    // True: odpovídající řádky se stanou viditelnými, i když byly dřív skryté
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Inicializujte záznam přes XLSDefaultAutoFilterExecutionOptions pokaždé. Funkce záznam vyplní nulami a pak nastaví každý limit a kontext provádění odmítá záznam, jehož limity nejsou všechny kladné, s EXLSXAutoFilterLimit. Záznam deklarovaný na zásobníku a plněný pole po poli obvykle nese v zapomenutých polích odpadky nebo nuly. Overloady bez parametrů (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) používají tytéž defaulty, s aktuálním datem jako referencí

Příznak AResetVisible rozhoduje o řádcích, které filtrem projdou, ale už byly skryté. True udělá každý odpovídající řádek viditelným, což dělá Excel, když filtr aplikujete znovu. False ponechá dřívější ruční schování na odpovídajících řádcích a jen přidá schování řádkům, které filtrem neprojdou. Řádky mimo rozsah AutoFilter si drží stav tak jako tak. Změní-li se sešit mezi přípravou a publikací, vyhodí ApplyAutoFilter výjimku místo publikace rozhodnutí spočtených ze zastaralých dat

Proč seznamy hodnot ignorují hvězdičky a otazníky?

Položky seznamu hodnot v elementu filters jsou literální zobrazovací tokeny, takže HotXLS srovnává * a ? jako obyčejné znaky. Zaškrtnutá položka A* v dropdownu znamená, že buňka ukazuje přesně A*, ne „začíná na A". Wildcardy patří do customFilters, kde „začíná na" a „obsahuje" opravdu jsou patterny, a ty drží oddělená pravidla popsaná v jak HotXLS porovnává wildcard kritéria Excelu a escape tildou. Splést tu dvě sémantiku je nejrychlejší cesta, jak vyfiltrovat sloupec dílů do nesmyslů

Srovnání jde proti zobrazené hodnotě, což otevírá otázku, kterého zobrazení. HotXLS formátuje buňku jejím základním číselným formátem, včetně uložených style overlayů, a záměrně ignoruje číselný formát, který na ni nanese podmíněné formátování. Nativní kontroly Excelu v fixture HotXLS ukazují, proč token záleží: numerická 1 i text „1" obojí sedí na výběr 1, numerická 1 formátovaná jako 0.0 místo toho sedí na 1.0 spolu s textem „1.0" a text „01" zůstává odlišný od obou. Text vypadající jako datum se nikdy neparsuje na datum, takže textová buňka držící „2026-10-19" nespadne do skupiny dat

Jak se rozhodují filtry Top 10, průměr a dynamická data?

HotXLS spočítá pořadí i průměrové prahy jednou přes celý zdrojový sloupec, ne přes řádky, které nechal viditelné jiný filtr. Do populace vstupují jen konečná čísla, což znamená, že text vypadající numericky, třeba „100", se vylučuje, a populace se sbírá dřív, než se udělá jakékoli rozhodnutí o řádku. V nativním kontrolním sešitu drží filtr nad průměrem průměr 27 přes celý sloupec, i poté, co druhý sloupec skryje hodnotu 50; nepřepne se na průměr 21,25 zbývajících řádků

Remízy se u prahu zahrnují, jako v Excelu. Filtr Top 3 nad hodnotami se dvěma položkami remizujícími na 20 vybere čtyři řádky. Procentní režim bere oříznutý podíl číselné populace s podlahou jedné položky, takže Top 50 procent pěti hodnot vybere dvě hodnoty s prahem 30. Bottom varianty zrcadlí srovnání. Tato čísla pocházejí z vlastních sad řádků Excelu zaznamenaných ve fixture HotXLS, ne ze čtení specifikace, která o většině z nich mlčí

Dynamická data potřebují zamrazené referenční datum

TXLSXAutoFilterExecutionOptions.ReferenceDate zamrazuje význam „dnes" pro celou operaci. Relativní filtry jako tento týden nebo minulé čtvrtletí i pravidla dat podmíněného formátování, která krmí color filtry, se všechna vyhodnocují proti tomu jednomu datu, takže náhled v 23:59:59 a aplikace o sekundu později souhlasí. Nulová hodnota vybere aktuální datum jednou na začátku operace; serial hodnoty respektují datový systém sešitu 1900 nebo 1904, včetně fiktivního serialu 60 Excelu v systému 1900 u filtrů M2 a Q1

Okna jsou půlotevné kalendářní intervaly: tento týden běží od neděle do následující neděle, toto čtvrtletí od prvního dne čtvrtletí do prvního dne dalšího a year to date od 1. ledna až do konce referenčního dne. Pinujte ReferenceDate explicitně v čemkoli, co běží v dávce nebo testu. Noční úloha překračující půlnoc, nebo unit test psaný v říjnu, který musí projít v lednu, jinak vyprodukují sady řádků rozdílné bez viditelného důvodu

Buňky s formulěmi se filtrují podle jejich cachovaných hodnot. Filtrační engine tiše nepřepočítává sešit, takže soubor uložený writerem, který nezanechal cachované výsledky, potřebuje explicitní průchod výpočtem, než filtr uvidí smysluplná čísla nebo data

Jaké barvy vidí color filtr?

Color filtr HotXLS vidí efektivní barvu z přímého formátování buňky plus každé odpovídající přepsání podmíněným formátováním a nic, co pochází jen ze stylu tabulky. Excel se chová stejně: pruhovaný řádek namalovaný světle modře vestavěným stylem tabulky není „světle modrý" pro filter dropdown. V nativních kontrolách se buňka namalovaná červeně podmíněným formátováním vybere červeným fill filtrem, zatímco buňka formátovaná červeně přímo, ale přepsaná aktivním žlutým pravidlem, se vyloučí

Tři vylepšení přišla v navazujících vydáních. Verze 2.384.138 bere lineární a path gradient výplně jako odlišné od plných barev i od žádné výplně, takže gradient nikdy nesedí na kritérium plné barvy, i když má některá z jeho zastávek přesně tu barvu. Verze 2.384.140 přidala filtrování barvy fontu pro rich text, kde buňka sedí, když jakýkoli neprázdný run nese požadovanou RGB, automatickou nebo děděnou barvu. Verze 2.384.150 dokončila rozhodování podmíněného rich textu, které se ukázalo jako to zvláštní

Nativní Excel může znovu použít barevné rozhodnutí mezi rich-text buňkou a prostou buňkou se stejným textem, stejným základním stylem a stejnou cestou odpovídajícího podmíněného pravidla, takže viditelná sada řádků může záviset na pořadí těchto buněk. HotXLS to reprodukuje tím, že připraví kompletní datový rozsah ve vzestupném pořadí řádků, i když se na jednu řádku ptáte jen přes AutoFilterRowVisible. Inspekce řádku 40 proto stojí průchod přes rozsah, ale inspekce řádku, náhled i aplikace vždy vrátí pro tutéž řádku tutéž odpověď

Každý zdroj má limit a co chytat

HotXLS ohraničuje každý druh práce v operaci AutoFilter vlastním polem v TXLSXAutoFilterExecutionOptions, protože filtrovaný sešit z upload formuláře je nedůvěryhodný vstup. Defaulty jsou 1048576 pro MaxXmlCharacters, 16384 pro MaxXmlNodes, 4096 pro MaxFilterItems, 4000000 pro MaxExaminedCells, po 1000000 pro MaxEffectiveFormatCells a MaxPopulationValues, 67108864 bajtů pro MaxContextBytes a 16000000 pro MaxConditionalSteps. MaxContextBytes měří udržovaný logický stav a rezervované dočasné obálky, ne paměť 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
  // Voláno pro každý datový řádek; Visible je rozhodnutí filtru
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted počítá prozkoumané buňky a může během formátování stát na místě
  ACancel := Now > Deadline;
end;

Row callback PreviewAutoFilterRows dostává každý datový řádek s jeho rozhodnutím, ne jen odpovídající, a funkce pořád vrací počet odpovídajících. Nastavení jeho parametru Cancel zastaví návštěvu dřív bez výjimky. OnWork je hák na zrušení: nastavení ACancel na True vyhodí EXLSXAutoFilterCancelled. Dokud operace připravuje rozhodnutí, drží read lease na sešitu, takže žádný callback nesmí ten sešit měnit

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // tužší strop pro uploadované soubory
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;

Všechny tři specifické výjimky dědí z EXLSXAutoFilterExecution, která sama pokrývá neplatný stav, jako referenční datum mimo podporovaný kalendář nebo sešit, který se změnil před publikací, takže base třídu držte jako poslední. V každém případě se viditelnost řádků listu nezmění, což dělá z „zaloguj, přeskoč, označ soubor k ruční revizi" bezpečnou výchozí politiku

Kde leží hranice

Pokrytí je široké, ale pár hran stojí za znalost, než zákazníkovi slíbíte paritu:

• Dynamické filtry směrodatné odchylky se odmítají, dokud neexistuje ověřená politika, i když enumerace pro ně má hodnoty
• Týdenní okna začínají nedělí; sešity spoléhající na jiný začátek týdne se nemodelují
• Výsledky formulí pocházejí jen z uložené cache, bez přepočítání uvnitř volání filtru
• Barvy stylu tabulky nikdy nesedí na color filtr, záměrně a v souladu s Excelem
• Neznámá rich-text struktura nebo neznámá barevná metadata selžou explicitně, zatímco známé nebarevné vlastnosti jako rodina fontu color match neblokují
• XLS fasáda a její úložiště BIFF8 jsou samostatná cesta, popsaná v jak HotXLS zapisuje BIFF8 AutoFilter DOPER kritéria

Pokud se pořád rozhodujete, jak spolu v sešitu, který generujete sami, souvisejí definice AutoFilter, data validation a tabulky, začněte s data validation, AutoFilter a tabulkami HotXLS v Delphi, které pokrývá SetAutoFilter, id sloupců a kdy filtr skutečně naberou

FAQ

Mění PreviewAutoFilterRows to, které řádky jsou skryté?

Ne. PreviewAutoFilterRows jen vyhodnotí uložená kritéria a hlásí rozhodnutí přes návratovou hodnotu a volitelný callback. Jen ApplyAutoFilter publikuje novou sadu skrytých řádků, a to až poté, co se připraví všechna rozhodnutí, takže selhání nechá původní viditelnost netknutou

Proč dává relativní datový filtr jiné řádky v jiné dny?

Filtry jako dnes, tento týden nebo minulý měsíc jsou definované relativně k aktuálnímu datu, přesně jako v Excelu. Nastavte ReferenceDate v TXLSXAutoFilterExecutionOptions na pevné datum, když potřebujete opakovatelné výsledky; nulová hodnota znamená datum, kdy operace začíná

Může HotXLS filtrovat podle barev ze stylu tabulky?

Ne, a Excel to taky neumí. Color filtry HotXLS sedí jen na přímé formátování buněk a podporovaná přepsání podmíněným formátováním. Barvy namalované jen vestavěným nebo vlastním stylem tabulky se neúčastní, takže výsledek color filtru souhlasí s tím, co by vybral dropdown Excelu

Pokročilé provádění AutoFilter dodává XLSX worksheet API HotXLS, komponenty Delphi pro čtení a zápis souborů Excel bez Excelu, od v2.384.136 výš, se zpracováním gradient, rich-text a podmíněného rich-text barev přidaným do v2.384.150