Excel AutoFilters draaien zonder Excel in Delphi: HotXLS

HotXLS evalueert de AutoFilter-criteria die al in een XLSX-bestand zijn opgeslagen zonder Excel te starten. Sinds v2.384.136 voeren TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter en AutoFilterRowVisible waardelijsten, datumgroepen, Top/Bottom-n- en percentagefilters, relatieve datum- en gemiddellefilters, vul- en lettertypekleurfilters en conditionele opmaak-iconfilters uit, onder een bevroren referentiedatum en expliciete resourcelimieten

De gebruikelijke aanleiding is een werkboek dat iemand in Excel heeft gefilterd en een server die er iets mee moet. Iemand zet "Top 10 by revenue", "This month" op de orderdatum en een rode vulling op achterstallige rijen, slaat op en uploadt het bestand. Uw Delphi-service heeft nu de rijenset nodig die Excel zou tonen: om die te tellen, te exporteren, of het bestand terug te schrijven met de juiste rijen verborgen. Het bestand bevat alleen de definitie. Vóór deze release kon HotXLS gewone custom criteria evalueren, maar de geavanceerde soorten zaten in de opgeslagen XML met niets dat ze uitvoerde, dus het eerlijke antwoord was "open het in Excel"

Wat voert HotXLS werkelijk uit vanuit een opgeslagen AutoFilter?

HotXLS voert per kolom één filtersoort uit, genomen uit het filterColumn-element dat Excel schreef, en combineert kolommen met AND, precies zoals de Excel-dropdowns. De elementen komen uit de AutoFilter-groep in ECMA-376 Part 1 §18.3.2, en elk mapt op een eigen evaluatiepad:

• filters met filter- en dateGroupItem-kinderen: een aangevinkte waardelijst, een optionele lege entry en kalenderdatumgroepen van jaar tot seconde, met OR samengevoegd
• customFilters: één of twee vergelijkingscriteria verbonden met AND of OR, het pad dat er al was
• top10: Top of Bottom n items, of n procent, met val beperkt tot 1-500 items of 1-100 procent
• dynamicFilter: today, yesterday, tomorrow, this/last/next week, month, quarter en year, year to date, M1-M12, Q1-Q4, above average en below average
• colorFilter en iconFilter: effectieve vul- of lettertypekleur resolvd via de differential style, en conditionele opmaak-icons op icon set en nul-gebaseerde icon-id

Alles buiten die lijst faalt met EXLSXAutoFilterUnsupported in plaats van een gegokte rijenset op te leveren. Daaronder vallen de above- en below-standard deviation-soorten van ST_DynamicFilterType, niet-Gregoriaanse calendarType-waarden, onbekende extensie-elementen, onopgeloste dxfId-referenties en een kolom die om wat voor reden twee filtersoorten tegelijk draagt. Een filterengine die stilletjes "goed genoeg"-rijen teruggeeft is erger dan een die weigert, want niemand merkt het totdat er een rapport de deur uit gaat met de verkeerde klanten erin

Eerst preview, dan apply

HotXLS splitst het werk in een read-only preview en een transactionele apply, en beide nemen hetzelfde record TXLSXAutoFilterExecutionOptions aan. PreviewAutoFilterRows geeft het aantal matchende datarijen terug en raakt de rijzichtbaarheid nooit aan. ApplyAutoFilter(AResetVisible, Options) bereidt eerst elke rijbeslissing voor en publiceert pas daarna de nieuwe set verborgen rijen in één stap, dus een exception tijdens de voorbereiding laat het werkblad exact zoals het was

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];   // de Sheets-indexeerder is 1-based

    // Begin altijd vanuit de defaults, wijzig daarna losse velden
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

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

    // True: matchende rijen worden zichtbaar ook als ze eerder verborgen waren
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Initialiseer het record elke keer met XLSDefaultAutoFilterExecutionOptions. De functie vult het record met nullen en zet daarna elke limiet, en de executiecontext wijst een record waarvan niet alle limieten positief zijn af met EXLSXAutoFilterLimit. Een record op de stack gedeclareerd en veld voor veld gevuld draagt doorgaans rommel of nullen in de velden die u vergat. De parameterloze overloads (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) gebruiken dezelfde defaults, met de huidige datum als referentie

De vlag AResetVisible beslist wat er gebeurt met rijen die door het filter komen maar al verborgen waren. True maakt elke matchende rij zichtbaar, wat Excel doet als u opnieuw toepast. False houdt eerder handmatig verbergen op matchende rijen in stand en voegt alleen verbergen toe voor rijen die falen. Rijen buiten het AutoFilter-bereik houden hun status hoe dan ook. Is het werkboek veranderd tussen voorbereiding en publicatie, dan gooit ApplyAutoFilter een exception in plaats van beslissingen te publiceren die uit verouderde data zijn berekend

Waarom negeren waardelijsten sterretjes en vraagtekens?

Waardelijst-entries in een filters-element zijn letterlijke weergavetokens, dus HotXLS vergelijkt * en ? als gewone tekens. Een aangevinkte entry A* in de dropdown betekent dat de cel exact A* toont, niet "begint met A". Wildcards horen bij customFilters, waar "begins with" en "contains" echt patronen zijn, en die houden de aparte regels aan die hoe HotXLS Excel-wildcardcriteria en tilde-escapes matcht behandelt. De twee semantieken door elkaar halen is de snelste manier om een artikelnummerkolom tot onzin te filteren

De vergelijking gebeurt op de weergegeven waarde, wat de vraag oproept welke weergave. HotXLS formatteert de cel met zijn basisgetalnotatie, inclusief opgeslagen style-overlays, en negeert bewust een getalnotatie die conditionele opmaak erbovenop legt. De native Excel-controles in de HotXLS-fixture laten zien waarom het token telt: numeriek 1 en tekst "1" matchen allebei met een selectie van 1, een numerieke 1 geformatteerd als 0.0 matcht in plaats daarvan 1.0 samen met de tekst "1.0", en tekst "01" blijft van beide onderscheiden. Op data lijkende tekst wordt nooit naar een datum geparseerd, dus een tekstcel met "2026-10-19" valt niet in een datumgroep

Hoe beslissen Top 10-, gemiddelde- en dynamische datumfilters?

HotXLS berekent rangschikking- en gemiddeldegrenzen één keer over de hele bronkolom, niet over de rijen die een ander filter zichtbaar liet. Alleen eindige getallen komen in de populatie, wat betekent dat op getallen lijkende tekst zoals "100" wordt uitgesloten, en de populatie wordt verzameld voordat er een rijbeslissing valt. In het native controlewerkboek houdt een above-average-filter een gemiddelde van 27 over de volledige kolom aan, ook nadat een tweede kolom de waarde 50 verbergt; hij schakelt niet over op het gemiddelde van 21.25 van de resterende rijen

Gelijke standen vallen bij de grens mee, zoals in Excel. Een Top 3-filter over waarden met twee entries gelijk op 20 selecteert vier rijen. Percentagemodus neemt de afgekapte deel van de numerieke populatie, met een vloer van één item, dus Top 50 procent van vijf waarden selecteert twee waarden met een grens van 30. Bottom-varianten spiegelen de vergelijking. Deze getallen komen uit de eigen rijensets van Excel, vastgelegd in de HotXLS-testfixtures, niet uit het lezen van de specificatie, die over de meeste ervan zwijgt

Dynamische data hebben een bevroren referentiedatum nodig

TXLSXAutoFilterExecutionOptions.ReferenceDate bevriest de betekenis van "vandaag" voor een hele operatie. Relatieve filters zoals this week of last quarter, en datumregels van conditionele opmaak die kleurfilters voeden, worden allemaal tegen die ene datum geëvalueerd, dus een preview om 23:59:59 en een apply één seconde later stemmen overeen. Een waarde van nul kiest de huidige datum één keer zodra de operatie begint; seriële waarden respecteren het 1900- of 1904-datumsysteem van het werkboek, inclusief de fictieve seriaal 60 van Excel in het 1900-systeem voor de M2- en Q1-filters

De vensters zijn halfopen kalenderintervallen: this week loopt van zondag tot de volgende zondag, this quarter van de eerste dag van het kwartaal tot de eerste dag van de volgende, en year to date van 1 januari tot het einde van de referentiedag. Pin ReferenceDate expliciet vast in alles dat in een batch of een test draait. Een nachtelijke taak die middernacht kruist, of een unit test geschreven in oktober die in januari moet slagen, levert anders rijensets op die om geen zichtbare reden verschillen

Formulecellen worden op hun gecachte waarden gefilterd. De filterengine herberekent het werkboek niet stilletjes, dus een bestand dat door een writer zonder gecachte resultaten is opgeslagen heeft een expliciete rekenpas nodig voordat het filter zinvolle getallen of data kan zien

Welke kleuren ziet een kleurfilter?

Een HotXLS-kleurfilter ziet de effectieve kleur uit directe celopmaak plus elke matchende override van conditionele opmaak, en niets dat alleen uit een table style komt. Excel doet hetzelfde: een gebande rij lichtblauw geverfd door een ingebouwde table style is voor de filterdropdown geen "light blue". In de native controles wordt een door conditionele opmaak rood geverfde cel geselecteerd door een rood vulfilter, terwijl een cel die direct rood is opgemaakt maar door een actieve gele regel wordt overschreven wordt uitgesloten

Drie verfijningen kwamen in opvolgende releases. Versie 2.384.138 behandelt lineaire en path gradient-vullingen als onderscheiden van zowel solide kleuren als geen vulling, dus een gradient matcht nooit met een solid-color-criterium, ook niet wanneer een van zijn stops exact die kleur heeft. Versie 2.384.140 voegde lettertypekleurfiltering voor rich text toe, waarbij een cel matcht zodra enige niet-lege run de gevraagde RGB-, automatic- of overgeërfde kleur draagt. Versie 2.384.150 voltooide de conditionele rich-text-beslissingen, en dat bleek het vreemde geval

Native Excel kan een kleurbeslissing hergebruiken tussen een rich-text-cel en een gewone cel met dezelfde tekst, dezelfde basisstijl en hetzelfde matchende conditionele regelpad, dus de zichtbare rijenset kan van de volgorde van die cellen afhangen. HotXLS reproduceert dat door de volledige datereeks in oplopende rijvolgorde voor te bereiden, ook wanneer u AutoFilterRowVisible maar over één rij vraagt. Rij 40 inspecteren kost dus een pas over de reeks, maar rijinspectie, preview en apply geven voor dezelfde rij altijd hetzelfde antwoord

Elke resource heeft een limiet, en wat u vangt

HotXLS begrenst elke soort werk in een AutoFilter-operatie met zijn eigen veld in TXLSXAutoFilterExecutionOptions, want een gefilterd werkboek uit een uploadformulier is onbetrouwbare invoer. De defaults zijn 1048576 voor MaxXmlCharacters, 16384 voor MaxXmlNodes, 4096 voor MaxFilterItems, 4000000 voor MaxExaminedCells, 1000000 elk voor MaxEffectiveFormatCells en MaxPopulationValues, 67108864 bytes voor MaxContextBytes en 16000000 voor MaxConditionalSteps. MaxContextBytes meet vastgehouden logische status en gereserveerde tijdelijke enveloppen, niet procesgeheugen

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
  // Wordt aangeroepen voor elke datarij; Visible is de filterbeslissing
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted telt onderzochte cellen en kan tijdens opmaak vlak blijven
  ACancel := Now > Deadline;
end;

De rij-callback van PreviewAutoFilterRows ontvangt elke datarij met zijn beslissing, niet alleen de matchende, en de functie geeft nog steeds het matchende aantal terug. Zijn parameter Cancel zetten stopt het bezoek vroeg zonder exception. OnWork is de annuleerhaak: ACancel op True zetten gooit EXLSXAutoFilterCancelled. Terwijl de operatie beslissingen voorbereidt houdt hij een leaserecht op het werkboek, dus geen van beide callbacks mag dat werkboek wijzigen

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // strakkere limiet voor geüploade bestanden
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;

Alle drie de specifieke exceptions stammen af van EXLSXAutoFilterExecution, die zelf ongeldige status dekt zoals een referentiedatum buiten de ondersteunde kalender of een werkboek dat vóór publicatie veranderde, dus houd de basisklasse als laatste. In elk geval blijft de rijzichtbaarheid van het werkblad onveranderd, wat van "loggen, overslaan, het bestand vlaggen voor handmatige review" een veilig default-beleid maakt

Waar de grenzen liggen

De dekking is breed, maar een paar randen zijn het kennen waard voordat u aan een klant pariteit belooft:

• Dynamische filters op standaarddeviatie worden geweigerd totdat er een geverifieerd beleid bestaat, ook al heeft de enumeratie waarden voor hen
• Weekvensters beginnen op zondag; werkboeken die op een andere weekstart leunen worden niet gemodelleerd
• Formuleresultaten komen alleen uit de opgeslagen cache, zonder herberekening binnen de filteraanroep
• Table style-kleuren matchen nooit met een kleurfilter, van ontwerp en in overeenstemming met Excel
• Onbekende rich-text-structuur of onbekende kleurmetadata faalt expliciet, terwijl bekende niet-kleureigenschappen zoals lettertypefamilie een kleurmatch niet blokkeren
• De XLS-facade en zijn BIFF8-opslag zijn een apart pad, behandeld in hoe HotXLS BIFF8 AutoFilter DOPER-criteria schrijft

Bent u nog aan het beslissen hoe AutoFilter-definities, data validation en tabellen zich verhouden in een werkboek dat u zelf genereert, begin dan met HotXLS data validation, AutoFilter en tabellen in Delphi, dat SetAutoFilter, kolom-id's en wanneer een filter werkelijk ingaat behandelt

FAQ

Verandert PreviewAutoFilterRows welke rijen verborgen zijn?

Nee. PreviewAutoFilterRows evalueert alleen de opgeslagen criteria en meldt beslissingen via zijn retourwaarde en optionele callback. Alleen ApplyAutoFilter publiceert een nieuwe set verborgen rijen, en pas nadat alle beslissingen zijn voorbereid, dus een falen laat de originele zichtbaarheid intact

Waarom geeft een relatief datumfilter op verschillende dagen verschillende rijen?

Filters zoals today, this week of last month zijn gedefinieerd relatief aan de huidige datum, precies zoals in Excel. Zet ReferenceDate in TXLSXAutoFilterExecutionOptions op een vaste datum wanneer u herhaalbare resultaten nodig heeft; een waarde van nul betekent de datum waarop de operatie begint

Kan HotXLS filteren op kleuren die uit een table style komen?

Nee, en Excel ook niet. HotXLS-kleurfilters matchen alleen directe celopmaak en ondersteunde overrides van conditionele opmaak. Kleuren die alleen door een ingebouwde of custom table style zijn aangebracht doen niet mee, dus een kleurfilterresultaat stemt overeen met wat de Excel-dropdown zou selecteren

Geavanceerde AutoFilter-uitvoering wordt geleverd met de XLSX-werkblad-API van HotXLS, de Delphi-spreadsheetcomponent voor het lezen en schrijven van Excel-bestanden zonder Excel, vanaf v2.384.136, met gradient-, rich-text- en conditionele rich-text-kleurafhandeling toegevoegd tot v2.384.150