Kør Excel AutoFilters uden Excel i Delphi: HotXLS

HotXLS evaluerer de AutoFilter-kriterier, der allerede er gemt i en XLSX-fil, uden at starte Excel. Siden v2.384.136 udfører TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter og AutoFilterRowVisible værdilister, datogrupper, Top/Bottom-n- og procentfiltre, relative dato- og gennemsnitsfiltre, fyld- og skriftfarvefiltre og conditional formatting-ikonfiltre under en frossen referencedato og eksplicitte ressourcegrænser

Den sædvanlige udløser er en projektmappe, som en person har filtreret i Excel, og en server, der skal handle på den. En person sætter "Top 10 efter omsætning", "Denne måned" på ordredatoen og en rød fyld på forfaldne rækker, gemmer og uploader filen. Din Delphi-tjeneste har nu brug for det rækkesæt, Excel ville vise: for at tælle det, eksportere det eller skrive filen tilbage med de rigtige rækker skjult. Filen rummer kun definitionen. Før denne release kunne HotXLS evaluere almindelige custom-kriterier, men de avancerede slags lå i den gemte XML uden noget, der udførte dem, så det ærlige svar var "åbn den i Excel"

Hvad udfører HotXLS egentlig fra en gemt AutoFilter?

HotXLS udfører én filterkind pr. kolonne, taget fra det filterColumn-element, Excel skrev, og kombinerer kolonner med AND, præcis som Excel-dropdowns. Elementerne kommer fra AutoFilter-gruppen i ECMA-376 Part 1 §18.3.2, og hver mapper til en distinkt evalueringssti:

• filters med filter- og dateGroupItem-børn: en afkrydset værdiliste, en valgfri tom post og kalenderdatogrupper fra år ned til sekund, OR'et sammen
• customFilters: ét eller to sammenligningskriterier forbundet med AND eller OR, stien der allerede eksisterede
• top10: Top eller Bottom n elementer, eller n procent, med val begrænset til 1-500 elementer eller 1-100 procent
• dynamicFilter: i dag, i går, i morgen, denne/sidste/næste uge, måned, kvartal og år, year to date, M1-M12, Q1-Q4, over gennemsnit og under gennemsnit
• colorFilter og iconFilter: effektiv fyld- eller skriftfarve resolveret gennem differentialstilen og conditional formatting-ikoner efter ikonsæt og nul-baseret icon id

Alt uden for den liste fejler med EXLSXAutoFilterUnsupported i stedet for at producere et gættet rækkesæt. Det inkluderer above- og below-standardafvigelsestyperne af ST_DynamicFilterType, ikke-gregorianske calendarType-værdier, ukendte extension-elementer, uopløste dxfId-referencer og en kolonne, der på en eller anden måde bærer to filterkinds på én gang. En filtermotor, der stille returnerer "tæt nok på"-rækker, er værre end en, der nægter, fordi ingen opdager det, før en rapport går ud med de forkerte kunder i

Først preview, så apply

HotXLS splitter arbejdet i en read-only preview og en transaktionel apply, og begge tager den samme TXLSXAutoFilterExecutionOptions-post. PreviewAutoFilterRows returnerer antallet af matchende datarækker og rører aldrig rækkevisibility. ApplyAutoFilter(AResetVisible, Options) forbereder hver rækkebeslutning først og publicerer først derefter det nye skjul-rækker-sæt i ét trin, så en exception under forberedelsen efterlader arket præcis, som det var

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];   // Sheets-indekseren er 1-baseret

    // Start altid fra defaults, ændr så enkelte felter
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // kun tælling
    WriteLn('Rows Excel would show: ', Matching);

    // True: matchende rækker bliver synlige, selv hvis de var skjult før
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Initialisér posten med XLSDefaultAutoFilterExecutionOptions hver gang. Funktionen nulfylder posten og sætter derefter hver grænse, og execution context'en afviser en post, hvis grænser ikke alle er positive, med EXLSXAutoFilterLimit. En post erklæret på stakken og fyldt felt for felt vil typisk bære skrald eller nuller i de felter, du glemte. De parameterløse overloads (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) bruger samme defaults med den aktuelle dato som reference

Flaget AResetVisible beslutter, hvad der sker med rækker, der består filteret, men allerede var skjult. True gør hver matchende række synlig, hvilket er det, Excel gør, når du anvender igen. False beholder tidligere manuel skjulning på matchende rækker og tilføjer kun skjulning for rækker, der fejler. Rækker uden for AutoFilter-området beholder deres tilstand begge veje. Har projektmappen ændret sig mellem forberedelse og publicering, rejser ApplyAutoFilter i stedet for at publicere beslutninger beregnet af forældede data

Hvorfor ignorerer værdilister stjerner og spørgsmålstegn?

Værdilisteposter i et filters-element er literale display-tokens, så HotXLS sammenligner * og ? som almindelige tegn. En afkrydset post A* i dropdownen betyder, at cellen viser præcis A*, ikke "starter med A". Wildcards hører hjemme i customFilters, hvor "begynder med" og "indeholder" reelt er mønstre, og dem følger de separate regler dækket i hvordan HotXLS matcher Excel wildcard-kriterier og tilde-escapes. At blande de to semantikker sammen er den hurtigste måde at filtrere en varenummerkolonne til volapyk på

Sammenligningen er mod den viste værdi, hvilket rejser spørgsmålet om hvilken visning. HotXLS formatterer cellen med dens basis-talformat, inklusive gemte style-overlays, og ignorerer bevidst et talformat, som conditional formatting lægger ovenpå. De native Excel-kontroller i HotXLS-fixturen viser, hvorfor tokenet betyder noget: numerisk 1 og tekst "1" matcher begge et valg af 1, en numerisk 1 formatteret som 0.0 matcher i stedet 1.0 sammen med teksten "1.0", og teksten "01" forbliver adskilt fra begge. Dato-lignende tekst parses aldrig til en dato, så en tekstcelle med "2026-10-19" falder ikke i en datogruppe

Hvordan beslutter Top 10-, gennemsnits- og dynamiske datofiltre?

HotXLS beregner rangerings- og gennemsnitsgrænser én gang over hele kildekolonnen, ikke over de rækker, et andet filter efterlod synlige. Kun endelige tal kommer ind i populationen, hvilket betyder, at tal-lignende tekst som "100" ekskluderes, og populationen indsamles, før nogen rækkebeslutning træffes. I den native kontrolprojektmappe beholder et above-average-filter et gennemsnit på 27 hen over hele kolonnen, selv efter en anden kolonne skjuler værdien 50; det skifter ikke til 21,25-gennemsnittet af de resterende rækker

Lige værdier medregnes ved grænsen, som i Excel. Et Top 3-filter over værdier med to poster lige ved 20 vælger fire rækker. Procenttilstand tager den afkortede andel af den numeriske population, med et gulv på ét element, så Top 50 procent af fem værdier vælger to værdier med en tærskel på 30. Bottom-varianter spejler sammenligningen. Disse tal kommer fra Excels egne rækkesæt, optaget i HotXLS-testfixturerne, ikke fra at læse specifikationen, som er tavs om de fleste af dem

Dynamiske datoer behøver en frossen referencedato

TXLSXAutoFilterExecutionOptions.ReferenceDate fryser betydningen af "i dag" for en hel operation. Relative filtre som denne uge eller sidste kvartal, og conditional formatting-datoregler, der fodrer farvefiltre, evalueres alle mod den ene dato, så et preview kl. 23:59:59 og en apply et sekund senere er enige. En nulværdi vælger den aktuelle dato én gang, når operationen starter; serialværdier respekterer projektmappens 1900- eller 1904-datosystem, inklusive Excels fiktive serial 60 i 1900-systemet for M2- og Q1-filtrene

Vinduerne er halvåbne kalenderintervaller: denne uge løber fra søndag til følgende søndag, dette kvartal fra kvartalets første dag til den næstes første dag, og year to date fra 1. januar op til referencedagens slut. Fastlæg ReferenceDate eksplicit i alt, der kører i en batch eller en test. Et natligt job, der krydser midnat, eller en unittest skrevet i oktober, der skal bestå i januar, producerer ellers rækkesæt, der adskiller sig uden synlig grund

Formelceller filtreres på deres cachede værdier. Filtermotoren genberegner ikke stille projektmappen, så en fil gemt af en writer, der efterlod ingen cachede resultater, behøver et eksplicit beregningspass, før filteret kan se meningsfulde tal eller datoer

Hvilke farver ser et farvefilter?

Et HotXLS farvefilter ser den effektive farve fra direkte celleformattering plus enhver matchende conditional formatting-override, og intet, der kun kommer fra en tabelstil. Excel opfører sig på samme måde: en banderet række malet lyseblå af en indbygget tabelstil er ikke "lyseblå" for filter-dropdownen. I de native kontroller vælges en celle malet rød af conditional formatting af et rødt fyldfilter, mens en celle formatteret rød direkte men overtrumfet af en aktiv gul regel ekskluderes

Tre forfinelser ankom i opfølgningsreleases. Version 2.384.138 behandler lineære og path gradient-fyld som adskilt fra både solide farver og ingen fyld, så en gradient aldrig matcher et ensfarvet kriterium, selv når et af dens stops har præcis den farve. Version 2.384.140 tilføjede skriftfarvefiltrering for rich text, hvor en celle matcher, hvis et vilkårligt ikke-tomt run bærer den ønskede RGB-, automatic- eller nedarvede farve. Version 2.384.150 fuldendte conditional rich-text-beslutninger, hvilket viste sig at være det mærkelige tilfælde

Native Excel kan genbruge en farvebeslutning mellem en rich-text-celle og en almindelig celle med samme tekst, samme basisstil og samme matchende conditional regelsti, så det synlige rækkesæt kan afhænge af rækkefølgen af de celler. HotXLS gengiver det ved at forberede det komplette dataområde i stigende rækkefølge, selv når du kun spørger AutoFilterRowVisible om én række. At inspicere række 40 koster derfor et pass hen over området, men rækkeinspektion, preview og apply returnerer altid samme svar for samme række

Hver ressource har en grænse, og hvad man skal fange

HotXLS afgrænser hver slags arbejde i en AutoFilter-operation med sit eget felt i TXLSXAutoFilterExecutionOptions, fordi en filtreret projektmappe fra en uploadformular er utroværdigt input. Defaults er 1048576 for MaxXmlCharacters, 16384 for MaxXmlNodes, 4096 for MaxFilterItems, 4000000 for MaxExaminedCells, 1000000 hver for MaxEffectiveFormatCells og MaxPopulationValues, 67108864 bytes for MaxContextBytes og 16000000 for MaxConditionalSteps. MaxContextBytes måler tilbageholdt logisk tilstand og reserverede midlertidige konvolutter, ikke proceshukommelse

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
  // Kaldes for hver datarække; Visible er filterbeslutningen
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted tæller undersøgte celler og kan stå stille under formattering
  ACancel := Now > Deadline;
end;

Række-callback'en i PreviewAutoFilterRows modtager hver datarække med sin beslutning, ikke kun de matchende, og funktionen returnerer stadig det matchende antal. At sætte dens Cancel-parameter stopper besøget tidligt uden exception. OnWork er annulleringshagen: at sætte ACancel til True rejser EXLSXAutoFilterCancelled. Mens operationen forbereder beslutninger, holder den en read lease på projektmappen, så ingen af callback'ne må ændre den projektmappe

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // strammere loft for uploadede filer
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 tre specifikke exceptions stammer fra EXLSXAutoFilterExecution, som selv dækker ugyldig tilstand såsom en referencedato uden for den understøttede kalender eller en projektmappe, der ændrede sig før publicering, så behold basisklassen sidst. I alle tilfælde er arkets rækkevisibility uændret, hvilket gør "log, spring over, flag filen til manuel gennemgang" til en sikker default-politik

Hvor grænserne går

Dækningen er bred, men et par kanter er værd at kende, før du lover paritet til en kunde:

• Dynamiske standardafvigelsesfiltre afvises, indtil en verificeret politik findes, selvom enumerationen har værdier til dem
• Ugevinduer starter på søndag; projektmapper, der læner sig op ad en anden ugestart, modelleres ikke
• Formelresultater kommer kun fra den gemte cache, uden genberegning inde i filterkaldet
• Tabelstilfarver matcher aldrig et farvefilter, af design og i overensstemmelse med Excel
• Ukendt rich-text-struktur eller ukendt farvemetadata fejler eksplicit, mens kendte ikke-farveegenskaber såsom fontfamilie ikke blokerer et farvematch
• XLS-facaden og dens BIFF8-lager er en separat sti, dækket i hvordan HotXLS skriver BIFF8 AutoFilter DOPER-kriterier

Er du stadig i gang med at beslutte, hvordan AutoFilter-definitioner, datavalidering og tabeller forholder sig i en projektmappe, du selv genererer, så start med HotXLS datavalidering, AutoFilter og tabeller i Delphi, som dækker SetAutoFilter, kolonne-id'er og hvornår et filter reelt træder i kraft

FAQ

Ændrer PreviewAutoFilterRows, hvilke rækker der er skjulte?

Nej. PreviewAutoFilterRows evaluerer kun de gemte kriterier og rapporterer beslutninger gennem sin returværdi og valgfrie callback. Kun ApplyAutoFilter publicerer et nyt skjul-rækker-sæt, og det gør den, efter alle beslutninger er forberedt, så en fiasko efterlader den oprindelige visibility intakt

Hvorfor giver et relativt datofilter forskellige rækker på forskellige dage?

Filtre som i dag, denne uge eller sidste måned er defineret relativt til den aktuelle dato, præcis som i Excel. Sæt ReferenceDate i TXLSXAutoFilterExecutionOptions til en fast dato, når du har brug for gentagelige resultater; en nulværdi betyder datoen, hvor operationen starter

Kan HotXLS filtrere på farver, der kommer fra en tabelstil?

Nej, og det kan Excel heller ikke. HotXLS farvefiltre matcher kun direkte celleformattering og understøttede conditional formatting-overrides. Farver malet udelukkende af en indbygget eller custom tabelstil deltager ikke, så et farvefilterresultat stemmer med det, Excel-dropdownen ville vælge

Avanceret AutoFilter-udførelse følger med XLSX worksheet-API'en i HotXLS, Delphi-regnearkskomponenten til at læse og skrive Excel-filer uden Excel, fra v2.384.136 og frem, med gradient-, rich-text- og conditional rich-text-farvehåndtering tilføjet frem til v2.384.150