BIFF8 AutoFilter DOPER-criteria in Delphi met HotXLS

HotXLS slaat elk BIFF8 AutoFilter-criterium op als een AUTOFILTER-record met twee 10-bytes DOPER-structuren, en het DOPER-type bepaalt hoe Excel vergelijkt. Sinds v2.384.45 schrijft TXLSWorksheet.ApplyAutoFilter een vergelijking als '>=100' als een IEEE-getal-DOPER, zodat Excel numerieke cellen matcht in plaats van tekst. Het bugrapport dat de wijziging afdwong was kort en verbijsterend: een nightly-export zette een filter op een bedragkolom, het bestand opende zonder mopperen, de dropdownpijl toonde het criterium, en het filter matchte nul rijen. Niets was corrupt. De bytes waren geldige BIFF8, alleen van de verkeerde soort geldig, en dat is precies de soort fout die dit artikel doorloopt, samen met twee oudere fouten op byteniveau die al in v2.384.18 waren verholpen

Wat slaat een BIFF8 AutoFilter nu eigenlijk op?

Een BIFF8 AutoFilter is een set van drie recordtypes, niet één, en alleen het per-veld-record bevat criteria. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) legt vast over hoeveel kolommen het filterbereik loopt. FILTERMODE ($009B) is een record zonder body dat HotXLS alleen uitzendt wanneer minstens één veld een actief criterium heeft. Daarna krijgt elk actief veld zijn eigen AUTOFILTER-record ($009E, §2.4.6): een zero-based veldindex, een grbit-woord waarvan de laagste twee bits wJoin vormen, twee DOPERs van precies 10 bytes elk, en een optionele staart die de tekens van elke string-DOPER bevat. Op schijf is de veldindex zero-based terwijl ApplyAutoFilter velden vanaf 1 telt, wat ertoe doet zodra u in een hexdump naar een record gaat zoeken. De eerste byte van elke DOPER, vt, zegt wat voor operand volgt:

• $04 is een IEEE 754-double in de resterende 8 bytes, de manier waarop Excel een numerieke vergelijking opslaat
• $06 is een string waarvan de lengte in één cch-byte staat, met de tekens zelf in de recordstaart
• $08 is een Bes-waarde, een Boolean of foutcode in twee bytes gepakt
• $0C en $0E dragen geen operand en betekenen match alle lege cellen respectievelijk match alle niet-lege cellen

De tweede byte, grbitSgn, bevat de vergelijking: 1 tot en met 6 staan voor <, =, <=, >, <> en >=. HotXLS houdt beide bytes achteraf zichtbaar via AutoFilterColumns, waarvan de items Criteria1 en Criteria2 blootleggen als TXLSAutofilterDOPER-objecten met DataType, grbitSgn en Value, zodat u kunt asserten op wat er wordt weggeschreven in plaats van te gokken

Waarom matchte een '>=100'-filter nul rijen in Excel?

Het filter matchte niets omdat de operand als tekst was opgeslagen, en Excel vergelijkt een string-DOPER met de cel als tekst. Vóór v2.384.45 strepte CreateFilterDoper in lxFilter.pas het >=-voorvoegsel correct af en zette het teken op 6, maar bouwde toen altijd een vtString-DOPER met de tekens 100. Een getalcel met 250 voldoet nooit aan een tekstvergelijking tegen "100", dus elke rij viel af. Geen exception, geen diagnose, geen herstelvraag van Excel. De regel sinds v2.384.45 is met opzet smal: begint het criterium met een vergelijkingsoperator en parseert de rest als een getal volgens invariant-culture-regels, dan schrijft HotXLS een vtIEEENumber-DOPER met hetzelfde teken. Een kale waarde zonder operator houdt de stringvorm, want zo slaat Excel zelf een uit de dropdownlijst gekozen item 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 (1-based aan de API-kant)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE-getal), grbitSgn = 6 (>=)
  // Vóór de fix: DataType = 6 (string), wat niets matchte
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;

In het parsen zitten de resterende scherpe randjes. De operand gaat door TryStrToFloat met een punt als decimaalteken, dus '>=1.5' wordt een getal en '>=1,5' blijft een string-DOPER die weer geruisloos niets matcht, wat de Windows-locale ook zegt. Data zijn dezelfde val in een ander kostuum: '>=2026-01-01' is geen getal, dus het wordt als tekst weggeschreven, terwijl Excel datumcellen als serienummers bewaart. Voor gelijkheid op een getal leveren zowel '=100' als een numerieke Variant zoals 100 een IEEE-DOPER met teken 2 op, terwijl de kale string '100' een tekstmatch oplevert. Bouw numerieke operanden in code op in plaats van ze voor mensen op te maken:

var
  Fmt: TFormatSettings;
  Since: TDateTime;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // Drempel met een kommacijfer: altijd met een punt opmaken
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Data: vergelijk met het serienummer dat Excel in de cel bewaart.
  // Een Delphi-TDateTime is het serienummer uit het 1900-systeem voor data na maart 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Hoe verbinden AND en OR twee voorwaarden?

De wJoin-bits van de AUTOFILTER-grbit zijn 0 voor AND en 1 voor OR, en HotXLS had die twee constanten omgekeerd tot v2.384.18. Een tussen-filter zoals ten minste 100 en onder 500 werd opgeslagen als ten minste 100 of onder 500, wat in de praktijk elk getal matcht en eruitziet alsof het filter gewoon niet werkte. De publieke operatorconstanten voegen een tweede porteergevaar toe. In HotXLS is xlAnd 0 en xlOr 1, terwijl Excel-automatisering ze 1 en 2 nummert. XlAutoFilterOperator is een doodgewone Byte, dus vanuit een VBA-macro vertaalde code met literale getallen compileert vlekkeloos, en een letterlijke 1 die in COM voor AND stond betekent hier OR. Gebruik de benoemde constanten en het probleem kan niet ontstaan:

// Bedrag tussen 100 (inclusief) en 500 (exclusief)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 op schijf
  Assert(Criteria2.grbitSgn = 1);      // 1 = kleiner dan
end;

Booleans, lege cellen en het plafond van 255 tekens

Een Boolean-criterium wordt als Bes-waarde opgeslagen ([MS-XLS] §2.5.10), en Bes zet de waardebyte bBoolErr eerst en de fError-vlag tweede. HotXLS schreef ze vóór v2.384.18 in omgekeerde volgorde, dus een filter op TRUE zette 1 in de foutvlag en Excel las het criterium als foutcode. Writer en lezer waren samen omgekeerd, wat verklaart waarom HotXLS zijn eigen bestanden mopperloos teruglas terwijl Excel bezwaar maakte — een herinnering dat een zelf-consistente round trip niets bewijst over spec-conformiteit. Lege cellen hebben helemaal geen operand nodig: alleen '=' meegeven levert een match-all-blanks-DOPER op ($0C) en alleen '<>' een match-all-non-blanks-DOPER ($0E)

Stringcriteria botsen op een harde grens in de DOPER-lay-out. Het cch-lengteveld is één byte, dus een string-operand kan niet boven 255 tekens uitkomen, en CreateFilterDoper kapt langere tekst af na het strippen van de operator in plaats van de lengtebyte te laten omslaan en de recordstaart te desynchroniseren. De afkapping is geruisloos, en een filter op een lange omschrijvingskolom kan anders matchen dan de volledige tekst die u doorgaf. In BIFF8 bewaart de staart elke string als een een-byte-vlag gevolgd door UTF-16-code-eenheden, en de opgegeven recordgrootte moet die bytes exact meetellen, dezelfde boekhoudkundige discipline als in hoe BIFF-recordlengteaanduidingen uit de pas lopen in een Delphi-XLS-writer

Waarom wist een tweede ApplyAutoFilter-aanroep de eerste uit?

Elke ApplyAutoFilter-aanroep herdefinieert het hele filterbereik, dus alleen het criterium uit de laatste aanroep overleeft. Intern roept hij SetAutoFilter aan, dat elk veld wist voordat het bereik opnieuw wordt opgebouwd, en dat is correct voor één kolom en verrassend voor twee. Wilt u meerdere kolommen filteren, roep ApplyAutoFilter dan één keer aan om het bereik en het eerste criterium vast te leggen, en voeg de rest daarna toe via AutoFilterColumns.SetFieldCriteria, dat het bereik en de andere velden met rust laat. Beide paden negeren een veldnummer buiten het bereik zonder te klagen, dus verifieer door terug te lezen, bij voorkeur na het heropenen van het opgeslagen 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');
Assert(Wb.Sheets[1].AutoFilterColumns.Find(1).Active);
Assert(Wb.Sheets[1].AutoFilterColumns.Find(3).Criteria1.DataType = 4);

Houd er rekening mee dat het AUTOFILTER-record een opgeslagen definitie is: HotXLS schrijft de criteria weg maar evalueert ze niet op het klassieke XLS-werkblad, dus een pipeline die op de server de matchende rijen nodig heeft moet ze daar zelf berekenen, terwijl de XLSX-facade evaluatie op rijniveau biedt zoals in HotXLS-gegevensvalidatie, AutoFilter en tabellen in Delphi. Zodra Excel rijen verbergt, hangen totalen onder het bereik af van hoe SUBTOTAL en AGGREGATE met verborgen en gefilterde rijen omgaan, de volgende plek waar een numeriek filter dat geruisloos niets matcht opduikt als een verkeerd getal

HotXLS leest en schrijft BIFF8-XLS- en XLSX-werkmappen native vanuit Delphi en C++Builder, inclusief AutoFiltercriteria met numerieke, Boolean- en AND/OR-DOPERs die Excel precies evalueert zoals bedoeld. Zie de HotXLS Delphi-spreadsheetcomponent voor functies, edities en een proefdownload