Een bedragkolom bevat 250, 730 en 1.040. Een gegenereerd .xls legt een AutoFilter van >=100 over die kolom, Excel opent het bestand zonder reparatieprompt, de header-dropdown toont dat er een filter staat, en elke datarij verdwijnt. De bytes zijn geldige BIFF8. Het hele probleem zit in één typebyte: de operand 100 was opgeslagen als string, Excel vergelijkt een string-operand als tekst, en geen enkele numerieke cel overleeft een tekstvergelijking tegen "100". Slaat u de operand op als IEEE double, dan houdt hetzelfde filter alle drie de rijen over
Waar een BIFF8-AutoFilter zijn criteria bewaart
Een BIFF8-AutoFilter is verspreid over drie recordtypes, en maar één daarvan draagt criteria. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) legt vast over hoeveel kolommen het filterbereik loopt. FILTERMODE ($009B) is een marker zonder body, en HotXLS zendt die alleen uit wanneer minstens één veld een actief criterium heeft. Elk actief veld krijgt dan zijn eigen AUTOFILTER-record ($009E, §2.4.6): een veldindex, een grbit-woord waarvan de laagste twee bits wJoin zijn, twee DOPER-structuren van precies 10 bytes elk, en een optionele staart met de tekens van een eventuele string-operand. De veldindex op schijf is zero-based, terwijl TXLSWorksheet.ApplyAutoFilter velden vanaf 1 telt, dus de derde kolom van uw bereik verschijnt in een hexdump als 02 00 aan het begin van de recordbody
De eerste byte van elke DOPER, vt, zegt wat voor soort operand volgt:
$04is een IEEE 754-double in de resterende 8 bytes, de vorm die Excel gebruikt voor een numerieke vergelijking$06is een string waarvan de lengte in ééncch-byte zit, met de tekens naar de recordstaart geschoven$08is een Bes-waarde, een Boolean of foutcode gepakt in twee bytes$0Cen$0Edragen helemaal geen operand en betekenen alle lege cellen matchen en alle niet-lege cellen matchen
De tweede byte, grbitSgn, is de vergelijking: 1 tot en met 6 staan voor <, =, <=, >, <> en >=. In HotXLS blijven beide bytes inspecteerbaar nadat u een criterium zet. TXLSWorksheet.AutoFilterColumns geeft een TXLSAutofilter terug, zijn Find-methode geeft het item voor een 1-based veld, en dat item legt Criteria1 en Criteria2 bloot als TXLSAutofilterDOPER-objecten met read-only properties DataType, grbitSgn en Value. Dat is wat er byte voor byte wordt weggeschreven wanneer u de werkmap opslaat
Waarom matcht een filter '>=100' geen rijen in Excel?
Het filter matcht niets omdat een tekst-operand een tekstvergelijking afdwingt. De voor de hand liggende eerste implementatie stript de prefix >= correct, zet het teken op 6, en zendt dan een vtString-DOPER uit met de tekens 100, want elk criterium komt toch als string binnen, zowel in Excels eigen dialoog als in de AutoFilter-aanroep van VBA. Excel accepteert dat record en evalueert het precies zoals geschreven. Een getalcel met 250 is in die vergelijking nooit "groter dan of gelijk aan" de tekst "100", dus de kolom loopt leeg
TXLSWorksheet.ApplyAutoFilter en TXLSAutofilter.SetFieldCriteria converteren criteria op dezelfde manier. Een string die met een van de zes operatoren begint, gevolgd door tekst die als getal parseert, wordt een vtIEEENumber-DOPER met die operator als teken. Een numerieke Variant (Integer, Int64, Double, Currency en de andere ordinale en float-types) wordt een getal-DOPER met teken 2, een gelijkheidstest. Een Boolean-Variant wordt een Bes-DOPER. Een kale string zonder operator blijft een string-DOPER, want zo slaat Excel zelf een item uit de dropdownlijst op
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
Doper: TXLSAutofilterDOPER;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Cells[1, 1].Value := 'Region';
Sh.Cells[1, 2].Value := 'Amount';
Sh.Cells[2, 1].Value := 'North';
Sh.Cells[2, 2].Value := 250;
// Veld 2 = tweede kolom van A1:B100 (velden zijn 1-based in de API)
Sh.ApplyAutoFilter('A1:B100', 2, '>=100');
Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
// Verwacht: DataType = 4 (IEEE-getal), grbitSgn = 6 (>=)
// De Assert is een controle die u zelf toevoegt, geen deel van de bibliotheek
Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));
Wb.SaveAs('orders.xls');
end;
De regel voor kale strings is degene die mensen vangt. In Delphi ogen '100' en 100 als uitwisselbare Variant-argumenten, maar de eerste produceert een tekstmatch en de tweede een numerieke gelijkheid. Gokken zou erger zijn. Een artikelnummerkolom die 00100 als tekst bevat zou een getal-DOPER voor 100 krijgen en geen enkele eigen cel matchen. Letterlijke kale strings spiegelen wat Excel schrijft bij een keuze uit de lijst, en als u een getal bedoelt geeft u een getal door of schrijft u '=100', wat een IEEE-DOPER met teken 2 oplevert
Decimaalteken, datums en het 1904-datumsysteem
De getaltoets na de operator gebruikt altijd een punt als decimaalteken, wat de Windows-regio-instellingen ook zeggen. '>=1.5' wordt een getal-DOPER; '>=1,5' parseert niet, blijft een string-DOPER en legt een numerieke kolom op een Duitse of Franse machine even betrouwbaar stil als op een Amerikaanse. Groepsscheidingstekens falen op dezelfde manier, dus '>=1,000' is tekst. Witruimte tussen de operator en de cijfers wordt getrimd, dus '>= 100' is prima. Het scheidingsteken op één plaats vastleggen voorkomt dat een rapport zich anders gedraagt naargelang welke server het genereerde, en dat is de juiste default voor een bestandsformaat
Datums zijn dezelfde valstrik in ander kostuum. '>=2026-01-01' is geen getal, dus het wordt als tekst weggeschreven, terwijl Excel datumcellen als serienummers bewaart en numeriek vergelijkt. Geef in plaats daarvan het serienummer door. Een Delphi-TDateTime heeft voor elke datum vanaf 1 maart 1900 hetzelfde gehele deel als het serienummer van het Excel-1900-systeem. Een werkmap die op het 1904-datumsysteem staat telt vanaf 1 januari 1904, dus de serienummers lopen er 1462 lager, en IXLSWorkbook.Date1904 vertelt u welk systeem het bestand gebruikt. De details van die verschuiving staan in hoe 1900- en 1904-datumserienummers samenspelen met getalnotaties
var
Fmt: TFormatSettings;
Serial: Integer;
begin
Fmt := TFormatSettings.Create;
Fmt.DecimalSeparator := '.';
// Drempel met een breuk: formatteer met een punt, nooit met de locale
Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));
// Datums: vergelijk met het serienummer dat Excel in de cel bewaart
Serial := Trunc(EncodeDate(2026, 1, 1));
if Wb.Date1904 then
Dec(Serial, 1462);
Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
xlAnd, Unassigned);
end;
Een numerieke Variant zou eenvouder zijn dan stringconcatenatie, maar een Variant-getal betekent altijd gelijkheid. Elke andere vergelijking moet reizen als operator plus tekst, dus de formatteerstap kan niet worden overgeslagen
Hoe verbinden AND en OR twee voorwaarden?
De wJoin-bits van het AUTOFILTER-grbit zijn 0 voor AND en 1 voor OR, en ze omdraaien is gemakkelijk en duur. Wisselt u ze om, dan wordt een bereikfilter als ten minste 100 en onder de 500 opgeslagen als ten minste 100 of onder de 500, wat elk getal matcht en lijkt alsof het filter nooit heeft gewerkt. HotXLS schrijft 0 voor xlAnd en 1 voor xlOr, en wanneer gelijkheidscriteria meespelen zet het ook de bits fSimple1 en fSimple2 naast wJoin, die een DOPER met teken 2 markeren
De publieke constanten brengen hun eigen porteergevaar mee. In HotXLS is xlAnd 0 en xlOr 1, terwijl Excel-automatisering ze 1 en 2 nummert. XlAutoFilterOperator is gedeclareerd als een gewone Byte, dus een VBA-macro die met letterlijke getallen is vertaald compileert zonder waarschuwing. Een letterlijke 1 die onder COM AND betekende betekent hier OR. Een letterlijke 2 die OR betekende botst met xlBottom10Items, en omdat HotXLS bij het bouwen van de twee DOPERs elke operatorwaarde anders dan xlOr als AND behandelt, wordt dat criterium met AND verbonden. Beide letterlijke waarden draaien om. Benoemde constanten vermijden het hele probleem:
// Bedrag van 100 (inclusief) tot 500 (exclusief)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');
with Sh.AutoFilterColumns.Find(3) do
begin
// Controles die u zelf toevoegt: bevestig wat er wordt weggeschreven
Assert(Operator = xlAnd); // wJoin = 0 op schijf
Assert(Criteria2.DataType = 4); // IEEE-getal
Assert(Criteria2.grbitSgn = 1); // 1 = kleiner dan
end;
Booleans, lege cellen en het plafond van 255 tekens
Een Boolean-criterium wordt opgeslagen als Bes-waarde ([MS-XLS] §2.5.10), en Bes zet de waardebyte bBoolErr eerst en de vlag fError tweede. Schrijft u ze in omgekeerde volgorde, dan stopt een filter op TRUE de 1 in de foutvlag, en leest Excel het criterium als foutcode. Het vervelende is dat een reader met dezelfde verwisseling zijn eigen bestanden perfect round-tript. Een bibliotheek die alleen leest wat hij zelf schreef bewijst hier niets over conformiteit; het bestand in Excel openen en het filterdialoog controleren wel. HotXLS schrijft bBoolErr eerst en fError als 0, dus het doorgeven van True levert een DOPER op waarvan DataType 8 is en Value 1
Legetests hebben geen operand nodig. De string '=' alleen produceert een match-alle-legen-DOPER ($0C), en '<>' alleen een match-alle-niet-legen-DOPER ($0E). Beide zijn volwaardige criteria, dus het veld telt als actief en FILTERMODE wordt geschreven
String-operanden stoten in de DOPER-lay-out tegen een harde grens aan. Het veld cch is één byte, dus een string-operand kan hooguit 255 tekens bevatten, hetzelfde plafond dat het filterdialoog van Excel zelf handhaaft. HotXLS stript eerst de operator en kapt daarna alles wat langer is af op 255 tekens, zodat de lengtebyte niet omslaat en de recordstaart uit de pas raakt. Een filter op een lange omschrijvingskolom kan daardoor matchen op een prefix van wat u doorgaf. Hoofdlettergebruik wordt met rust gelaten: Excel matcht filtertekst zonder onderscheid in hoofdletters wanneer het filter wordt toegepast, en het herschrijven van het opgeslagen criterium zou alleen de data van de gebruiker veranderen. In BIFF8 bewaart de staart elke string als één-byte-vlag gevolgd door UTF-16-code-eenheden, en de gedeclareerde recordgrootte moet precies die bytes tellen, dezelfde boekhouding die wordt behandeld in hoe BIFF-recordlengtedeclaraties afdwalen in een Delphi XLS-schrijver
Meerdere kolommen filteren en het opgeslagen bestand controleren
Elke ApplyAutoFilter-aanroep herdefinieert het hele filterbereik, dus na twee aanroepen overleeft alleen het criterium van de tweede. De methode loopt via de publieke TXLSWorksheet.SetAutoFilter, die elk veld wist voordat het bereik wordt herbouwd. Dat is correct voor één kolom en verrassend de eerste keer dat u er twee filtert. Voor meerdere kolommen: roep ApplyAutoFilter één keer aan om het bereik en het eerste criterium vast te leggen, en voeg de rest toe met AutoFilterColumns.SetFieldCriteria, dat één veld raakt en het bereik en de andere velden met rust laat. Beide paden negeren een veldnummer buiten het bereik zonder exception, dus de controle die de moeite waard is leest de criteria terug uit het heropende bestand
Sh.ApplyAutoFilter('A1:D500', 1, 'North'); // bereik + veld 1
Sh.AutoFilterColumns.SetFieldCriteria(3, '>=100', xlAnd, Unassigned);
Sh.AutoFilterColumns.SetFieldCriteria(4, True, xlAnd, Unassigned);
Wb.SaveAs('orders.xls');
Wb := TXLSWorkbook.Create;
Wb.Open('orders.xls');
with Wb.Sheets[1].AutoFilterColumns do
begin
// Controles die u zelf toevoegt na het heropenen van het bestand
Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6)); // tekst 'North'
Assert(Find(3).Criteria1.DataType = 4); // getal 100
Assert(Find(4).Criteria1.DataType = 8); // Boolean TRUE
end;
Het AUTOFILTER-record is een opgeslagen definitie. Op een klassiek XLS-werkblad schrijft HotXLS de criteria om door Excel te laten evalueren zodra het bestand opent, en evaluatie op rijniveau aan de serverkant zit in het XLSX-objectmodel, zoals getoond in HotXLS data validation, AutoFilter en tabellen in Delphi. Zodra Excel rijen echt verbergt, hangen totalen onder het bereik af van hoe SUBTOTAL en AGGREGATE met verborgen en gefilterde rijen omgaan, en een als tekst opgeslagen numeriek filter verschijnt daar als een totaal van nul
De hierboven beschreven criteriumconversie, Bes-lay-out en join-bits schrijft HotXLS in elke BIFF8-werkmap die het opslaat, naast native XLSX-ondersteuning voor Delphi en C++Builder. Edities, de volledige functielijst en een proefdownload staan op de pagina HotXLS Delphi spreadsheet component