Kör Excel AutoFilter utan Excel i Delphi: HotXLS

HotXLS utvärderar de AutoFilter-kriterier som redan lagrats i en XLSX-fil utan att starta Excel. Sedan v2.384.136 kör TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter och AutoFilterRowVisible vallistor, datumgrupper, Top/Bottom-n och procentfilter, relativa datum- och medelfilter, fyllnings- och teckenfärgfilter samt villkorsformateringens ikonfilter, under ett fruset referensdatum och explicita resursgränser

Den vanliga utlösaren är en arbetsbok som en person filtrerat i Excel och en server som måste agera på den. Någon sätter "Top 10 by revenue", "This month" på orderdatum och en röd fyllning på försenade rader, sparar, och laddar upp filen. Din Delphi-tjänst behöver nu den raduppsättning Excel skulle visa: för att räkna den, exportera den, eller skriva tillbaka filen med rätt rader dolda. Filen håller bara definitionen. Före den här releasen kunde HotXLS utvärdera vanliga anpassade kriterier, men de avancerade slagen satt i den lagrade XML:en utan något som körde dem, så det ärliga svaret var "öppna den i Excel"

Vad kör HotXLS egentligen från ett sparat AutoFilter?

HotXLS kör ett filterslag per kolumn, taget från det filterColumn-element Excel skrev, och kombinerar kolumner med AND, precis som Excel-rullgardinerna. Elementen kommer från AutoFilter-gruppen i ECMA-376 Part 1 §18.3.2, och vart och ett mappar till en egen utvärderingsväg:

• filters med filter- och dateGroupItem-barn: en ikryssad vallista, ett valbart tomt val och kalenderdatumgrupper från år ner till sekund, OR:ade ihop
• customFilters: ett eller två jämförelsekriterier sammanfogade med AND eller OR, vägen som redan fanns
• top10: Top eller Bottom n poster, eller n procent, med val begränsad till 1–500 poster eller 1–100 procent
• dynamicFilter: today, yesterday, tomorrow, this/last/next week, month, quarter och year, year to date, M1–M12, Q1–Q4, above average och below average
• colorFilter och iconFilter: effektiv fyllnings- eller teckenfärg löst genom differentialstilen, och villkorsformateringens ikoner efter ikonuppsättning och nollbaserat ikon-id

Allt utanför listan misslyckas med EXLSXAutoFilterUnsupported i stället för att producera en gissad raduppsättning. Det inkluderar above- och below-standardavvikelse-lägena i ST_DynamicFilterType, icke-gregorianska calendarType-värden, okända extension-element, olösta dxfId-referenser och en kolumn som av någon anledning bär två filterslag på en gång. En filtermotor som tyst returnerar "nära nog"-rader är värre än en som vägrar, för ingen märker det förrän en rapport går ut med fel kunder i

Först förhandsvisning, sedan applicering

HotXLS delar arbetet i en läsningsförhandsvisning och en transaktionell applicering, och båda tar samma TXLSXAutoFilterExecutionOptions-post. PreviewAutoFilterRows returnerar antalet matchande datarader och rör aldrig radsynlighet. ApplyAutoFilter(AResetVisible, Options) förbereder varje radsbeslut först och publicerar först därefter den nya dolda-rad-uppsättningen i ett steg, så ett undantag under förberedelsen lämnar bladet exakt 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-indexeraren är 1-baserad

    // Börja alltid från standardvärdena, ändra sedan enstaka fält
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // bara räkna
    WriteLn('Rows Excel would show: ', Matching);

    // True: matchande rader blir synliga även om de var dolda tidigare
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Initiera posten med XLSDefaultAutoFilterExecutionOptions varje gång. Funktionen nollfyller posten och sätter sedan varje gräns, och exekveringskontexten avvisar en post vars gränser inte alla är positiva med EXLSXAutoFilterLimit. En post deklarerad på stacken och fylld fält för fält bär oftast skräp eller nollor i de fält du glömde. De parameterlösa overloaderna (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) använder samma standardvärden, med aktuellt datum som referens

Flaggan AResetVisible avgör vad som händer med rader som passerar filtret men redan var dolda. True gör varje matchande rad synlig, vilket är vad Excel gör när du applicerar igen. False behåller tidigare manuell döljning på matchande rader och lägger bara till döljning för rader som faller. Rader utanför AutoFilter-området behåller sitt tillstånd oavsett. Har arbetsboken ändrats mellan förberedelse och publicering kastar ApplyAutoFilter i stället för att publicera beslut beräknade ur inaktuell data

Varför ignorerar vallistor asterisker och frågetecken?

Vallistposter i ett filters-element är bokstavliga visningstoken, så HotXLS jämför * och ? som vanliga tecken. En ikryssad post A* i rullgardinen betyder att cellen visar exakt A*, inte "börjar med A". Jokertecken hör hemma i customFilters, där "börjar med" och "innehåller" verkligen är mönster, och de behåller de separata regler som täcks i hur HotXLS matchar Excel-jokerteckenkriterier och tilde-escape. Att blanda ihop de två semantikerna är snabbaste sättet att filtrera en artikelnr-kolumn till nonsens

Jämförelsen sker mot det visade värdet, vilket väcker frågan vilken visning. HotXLS formaterar cellen med dess basnummerformat, inklusive lagrade stilöverdrag, och ignorerar medvetet ett nummerformat som villkorsformateringen lägger ovanpå. De nativa Excel-kontrollerna i HotXLS-fixturen visar varför tokenen spelar roll: numerisk 1 och text "1" matchar båda ett urval av 1, en numerisk 1 formaterad som 0.0 matchar i stället 1.0 tillsammans med texten "1.0", och text "01" förblir åtskild från båda. Datumliknande text parsas aldrig till ett datum, så en textcell med "2026-10-19" faller inte in i en datumgrupp

Hur avgör Top 10-, medel- och dynamiska datumfilter?

HotXLS beräknar ranknings- och medelgränser en gång över hela källkolumnen, inte över raderna ett annat filter lämnat synliga. Bara ändliga tal går in i populationen, vilket betyder att numeriskt seende text som "100" utesluts, och populationen samlas in innan något radsbeslut fattas. I den nativa kontrollarbetsboken håller ett above-average-filter ett medel på 27 över hela kolumnen även efter att en andra kolumn döljer värdet 50; den växlar inte till 21,25-medelvärdet av de kvarvarande raderna

Lika värden inkluderas vid gränsen, som i Excel. Ett Top 3-filter över värden med två poster lika på 20 väljer fyra rader. Procentläget tar den avkapade andelen av den numeriska populationen, med ett golv på en post, så Top 50 procent av fem värden väljer två värden med en tröskel på 30. Bottom-varianterna speglar jämförelsen. Dessa tal kommer från Excels egna raduppsättningar registrerade i HotXLS-testfixturerna, inte från att läsa specifikationen, som är tyst om de flesta av dem

Dynamiska datum behöver ett fruset referensdatum

TXLSXAutoFilterExecutionOptions.ReferenceDate fryser betydelsen av "idag" för en hel operation. Relativa filter som denna vecka eller förra kvartalet, och villkorsformateringens datumregler som matar färgfilter, utvärderas alla mot det datumet, så en förhandsvisning 23:59:59 och en applicering en sekund senare stämmer överens. Ett nollvärde väljer aktuellt datum en gång när operationen startar; serialvärden respekterar arbetsbokens 1900- eller 1904-datorsystem, inklusive Excels fiktiva serial 60 i 1900-systemet för M2- och Q1-filtren

Fönstren är halvöppna kalenderintervall: denna vecka löper från söndag till följande söndag, detta kvartal från kvartalets första dag till nästa kvartalets första dag, och year to date från 1 januari upp till slutet av referensdagen. Fäst ReferenceDate explicit i allt som körs i en batch eller ett test. Ett nattligt jobb som passerar midnatt, eller ett enhetstest skrivet i oktober som ska passera i januari, producerar annars raduppsättningar som skiljer sig utan synlig anledning

Formelceller filtreras på deras cachade värden. Filtermotorn räknar inte om arbetsboken i tysthet, så en fil sparad av en skrivare som lämnade inga cachade resultat behöver ett explicit kalkyleringsvarv innan filtret kan se meningsfulla tal eller datum

Vilka färger ser ett färgfilter?

Ett HotXLS-färgfilter ser den effektiva färgen från direkt cellformatering plus eventuell matchande villkorsformateringsöverdrag, och ingenting som bara kommer från en tabellstil. Excel beter sig på samma sätt: en randig rad målad ljusblå av en inbyggd tabellstil är inte "ljusblå" för filterrullgardinen. I de nativa kontrollerna väljs en cell målad röd av villkorsformatering av ett rött fyllnadsfilter, medan en cell formaterad röd direkt men överdragen av en aktiv gul regel utesluts

Tre förfiningar kom i uppföljande releaser. Version 2.384.138 behandlar linjära och sökvägs-gradientfyllningar som åtskilda både från solida färger och ingen fyllning, så en gradient matchar aldrig ett solidfärgkriterium även när ett av dess stopp har exakt den färgen. Version 2.384.140 lade till teckenfärgfiltrering för rich text, där en cell matchar om något icke-tomt run bär den begärda RGB-, automatiska eller ärvda färgen. Version 2.384.150 fullbordade villkorliga rich-text-beslut, vilket visade sig vara det märkliga fallet

Nativ Excel kan återanvända ett färgbeslut mellan en rich-text-cell och en vanlig cell med samma text, samma basstil och samma matchande villkorsregelväg, så den synliga raduppsättningen kan bero på ordningen av de cellerna. HotXLS återskapar det genom att förbereda hela dataområdet i stigande radordning, även när du bara frågar AutoFilterRowVisible om en rad. Att granska rad 40 kostar därför ett varv över området, men radgranskning, förhandsvisning och applicering returnerar alltid samma svar för samma rad

Varje resurs har en gräns, och vad du ska fånga

HotXLS avgränsar varje slags arbete i en AutoFilter-operation med sitt eget fält i TXLSXAutoFilterExecutionOptions, eftersom en filtrerad arbetsbok från ett uppladdningsformulär är obetrodd indata. Standardvärdena är 1048576 för MaxXmlCharacters, 16384 för MaxXmlNodes, 4096 för MaxFilterItems, 4000000 för MaxExaminedCells, 1000000 vardera för MaxEffectiveFormatCells och MaxPopulationValues, 67108864 byte för MaxContextBytes och 16000000 för MaxConditionalSteps. MaxContextBytes mäter kvarhållet logiskt tillstånd och reserverade temporära kuvert, inte processminne

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
  // Anropas för varje datarad; Visible är filterbeslutet
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted räknar granskade celler och kan stå still under formatering
  ACancel := Now > Deadline;
end;

Rad-callbacken hos PreviewAutoFilterRows tar emot varje datarad med sitt beslut, inte bara de matchande, och funktionen returnerar ändå det matchande antalet. Att sätta dess Cancel-parameter stoppar besöket tidigt utan undantag. OnWork är avbrottshaken: att sätta ACancel till True kastar EXLSXAutoFilterCancelled. Medan operationen förbereder beslut håller den en läslease på arbetsboken, så ingen av callbackerna får modifiera den arbetsboken

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // snävare tak för uppladdade 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;

Alla tre specifika undantagen härstammar från EXLSXAutoFilterExecution, som själv täcker ogiltigt tillstånd som ett referensdatum utanför den stödda kalendern eller en arbetsbok som ändrats före publicering, så behåll basklassen sist. I vartje fall är bladets radsynlighet oförändrad, vilket gör "logga, hoppa över, flagga filen för manuell granskning" till en säker standardpolicy

Där gränserna går

Täckningen är bred, men några kanter är värda att känna innan du lovar paritet till en kund:

• Standardavvikelsens dynamiska filter avvisas tills en verifierad policy finns, fastän uppräkningen har värden för dem
• Veckofönster börjar på söndag; arbetsböcker som förlitar sig på en annan veckostart modelleras inte
• Formelresultat kommer från den lagrade cachen enbart, utan omkalkylering inne i filteranropet
• Tabellstilfärger matchar aldrig ett färgfilter, av design och i överensstämmelse med Excel
• Okänd rich-text-struktur eller okänd färgmetadata misslyckas explicit, medan kända icke-färgegenskaper som typsnittsfamilj inte blockerar en färgmatchning
• XLS-fasaden och dess BIFF8-lagring är en separat väg, täckt i hur HotXLS skriver BIFF8 AutoFilter DOPER-kriterier

Funderar du fortfarande på hur AutoFilter-definitioner, datavalidering och tabeller förhåller sig i en arbetsbok du själv genererar, börja med HotXLS datavalidering, AutoFilter och tabeller i Delphi, som täcker SetAutoFilter, kolumn-id:n och när ett filter faktiskt träder i kraft

FAQ

Ändrar PreviewAutoFilterRows vilka rader som är dolda?

Nej. PreviewAutoFilterRows utvärderar bara de lagrade kriterierna och rapporterar beslut genom sitt returvärde och valfri callback. Bara ApplyAutoFilter publicerar en ny dolda-rad-uppsättning, och det gör den efter att alla beslut förberetts, så ett misslyckande lämnar den ursprungliga synligheten intakt

Varför ger ett relativt datumfilter olika rader olika dagar?

Filter som today, this week eller last month är definierade relativt aktuellt datum, precis som i Excel. Sätt ReferenceDate i TXLSXAutoFilterExecutionOptions till ett fast datum när du behöver repeterbara resultat; ett nollvärde betyder datumet då operationen startar

Kan HotXLS filtrera på färger som kommer från en tabellstil?

Nej, och inte heller kan Excel. HotXLS-färgfilter matchar bara direkt cellformatering och stödda villkorsformateringsöverdrag. Färger målade enbart av en inbyggd eller anpassad tabellstil deltar inte, så ett färgfilterresultat överensstämmer med vad Excel-rullgardinen skulle välja

Avancerad AutoFilter-exekvering levereras med XLSX-blad-API:et i HotXLS, Delphi-kalkylarkskomponenten för att läsa och skriva Excel-filer utan Excel, från v2.384.136 och framåt, med gradient-, rich-text- och villkorlig rich-text-färghantering tillagd genom v2.384.150