BIFF8 AutoFilter DOPER-kriterier i Delphi med HotXLS

HotXLS gemmer hvert BIFF8 AutoFilter-kriterium som en AUTOFILTER-record, der bærer to 10-byte DOPER-strukturer, og DOPER-typen afgør, hvordan Excel sammenligner. Siden v2.384.45 skriver TXLSWorksheet.ApplyAutoFilter en sammenligning som '>=100' som en IEEE number-DOPER, så Excel matcher numeriske celler i stedet for at sammenligne tekst. Fejlrapporten, der udløste ændringen, var kort og rasende: en natlig eksport satte et filter på en beløbskolonne, filen åbnede uden brok, dropdown-pilen viste kriteriet, og filteret matchede nul rækker. Intet var korrupt. Bytene var gyldig BIFF8, bare den forkerte slags gyldig, og det er den fejlklasse, artiklen går igennem, sammen med to ældre fejl på byteniveau, der blev rettet i v2.384.18

Hvad gemmer en BIFF8 AutoFilter egentlig?

En BIFF8 AutoFilter er et sæt af tre record-typer, ikke én, og kun per-field-recorden bærer kriterier. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) gemmer, hvor mange kolonner filterområdet dækker. FILTERMODE ($009B) er en kropsløs markør, som HotXLS kun udsender, når mindst ét felt har et aktivt kriterium. Derefter får hvert aktivt felt sin egen AUTOFILTER-record ($009E, §2.4.6): et nul-baseret feltindeks, et grbit-ord, hvis laveste to bit er wJoin, to DOPER'er på præcis 10 byte hver og en valgfri hale, der rummer tegnene i enhver string-DOPER. Feltindekset er nul-baseret på disken, selvom ApplyAutoFilter nummererer felter fra 1, hvilket betyder noget, første gang du går på jagt efter en record i et hex-dump. Den første byte i hver DOPER, vt, siger, hvilken slags operand der følger:

• $04 er en IEEE 754 double gemt i de resterende 8 byte, hvilket er sådan, Excel gemmer en numerisk sammenligning
• $06 er en streng, hvis længde ligger i en enkelt cch-byte, med tegnene selv skubbet ud i record-halen
• $08 er en Bes-værdi, en Boolean eller fejlkode pakket i to byte
• $0C og $0E bærer ingen operand og betyder match alle tomme celler henholdsvis match alle ikke-tomme celler

Den anden byte, grbitSgn, holder sammenligningen: 1 til 6 mapper til <, =, <=, >, <> og >=. HotXLS holder begge bytes synlige bagefter gennem AutoFilterColumns, hvis elementer eksponerer Criteria1 og Criteria2 som TXLSAutofilterDOPER-objekter med DataType, grbitSgn og Value, så du kan asserte på, hvad der bliver skrevet, i stedet for at gætte

Hvorfor matchede et '>=100'-filter ingen rækker i Excel?

Filteret matchede ingenting, fordi operanden var gemt som tekst, og Excel sammenligner en string-DOPER med cellen som tekst. Før v2.384.45 strippede CreateFilterDoper i lxFilter.pas korrekt >=-præfikset og satte tegnet til 6, men byggede derefter altid en vtString-DOPER med tegnene 100. En talcelle med 250 tilfredsstiller aldrig en tekstsammenligning mod "100", så alle rækker faldt ud. Ingen exception, ingen diagnostik, ingen reparationsdialog fra Excel. Reglen siden v2.384.45 er med vilje snæver: starter kriteriet med en sammenligningsoperator, og resten kan parses som et tal under invariant-culture-regler, skriver HotXLS en vtIEEENumber-DOPER med samme tegn. En bar værdi uden operator beholder strengformen, for det er sådan, Excel selv gemmer et element valgt fra dropdown-listen

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;

  // Felt 2 = anden kolonne i A1:B100 (1-baseret på API-siden)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE-tal), grbitSgn = 6 (>=)
  // Før fixen: DataType = 6 (string), som matchede ingenting
  Assert(Doper.DataType = 4);

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

Parsingen er der, hvor de resterende skarpe kanter gemmer sig. Operanden går gennem TryStrToFloat med et punktum som decimalseparator, så '>=1.5' bliver et tal, mens '>=1,5' forbliver en string-DOPER og igen lydløst matcher ingenting, uanset hvad Windows-lokalen siger. Datoer er den samme fælde i et andet kostume: '>=2026-01-01' er ikke et tal, så det skrives som tekst, mens Excel gemmer datoceller som serienumre. For lighed på et tal giver både '=100' og en numerisk Variant som 100 en IEEE-DOPER med tegn 2, mens den bare streng '100' giver et tekstmatch. Byg de numeriske operander i kode frem for at formatere dem til mennesker:

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

  // Tærskel med decimaldel: formatér altid med punktum
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Datoer: sammenlign med det serienummer, Excel gemmer i cellen.
  // En Delphi TDateTime svarer til 1900-systemets serial for datoer efter marts 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Hvordan forbinder AND og OR to betingelser?

wJoin-bitene i AUTOFILTER-grbit er 0 for AND og 1 for OR, og HotXLS havde de to konstanter byttet om helt ind til v2.384.18. Et between-agtigt filter som mindst 100 og under 500 blev gemt som mindst 100 eller under 500, hvilket i praksis matcher hvert tal og ser ud som om, filteret slet ikke blev anvendt. De offentlige operator-konstanter tilføjer endnu en porteringfælde. I HotXLS er xlAnd 0 og xlOr 1, mens Excel-automation nummererer dem 1 og 2. XlAutoFilterOperator er en simpel Byte, så kode oversat fra en VBA-makro med literale tal kompilerer uden brok, og en literal 1, der betød AND i COM, betyder nu OR. Brug de navngivne konstanter, så problemet slet ikke kan opstå:

// Beløb mellem 100 (inklusive) og 500 (eksklusive)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 på disken
  Assert(Criteria2.grbitSgn = 1);      // 1 = mindre end
end;

Booleans, tomme celler og 255-tegn-grænsen

Et Boolean-kriterium gemmes som en Bes-værdi ([MS-XLS] §2.5.10), og Bes sætter værdibyten bBoolErr først og fError-flagget andet. HotXLS skrev dem i omvendt rækkefølge før v2.384.18, så et filter for TRUE lagde 1 i fejlflagget, og Excel læste kriteriet som en fejlkode. Writer og reader var byttet om sammen, og derfor round-trippede HotXLS sine egne filer uden brok, mens Excel var uenig — en påmindelse om, at en selvkonsistent round-trip beviser ingenting om spec-overholdelse. Tomme celler behøver slet ingen operand: at give '=' alene giver en match-alle-tomme-DOPER ($0C), og '<>' alene en match-alle-ikke-tomme-DOPER ($0E)

Strengkriterier rammer en hård grænse i DOPER-layoutet. cch-længdefeltet er en enkelt byte, så en strengoperand kan ikke overstige 255 tegn, og CreateFilterDoper trunkerer længere tekst, efter operatoren er strippet, frem for at lade længdebyten wrappe og desynkronisere record-halen. Trunkeringen er lydløs, og et filter på en lang beskrivelseskolonne kan matche anderledes end den fulde tekst, du gav med. I BIFF8 gemmer halen hver streng som et én-byte-flag efterfulgt af UTF-16-kodeenheder, og den deklarerede record-størrelse skal tælle de byte præcist — samme bogholderidisciplin, som artiklen om hvordan BIFF-record-længdedeklarationer driver i en Delphi XLS-writer gennemgår

Hvorfor sletter et andet ApplyAutoFilter-kald det første?

Hvert ApplyAutoFilter-kald redefinerer hele filterområdet, så kun kriteriet fra det sidste kald overlever. Internt kalder det SetAutoFilter, som rydder hvert felt, før området bygges op igen, og det er korrekt for én kolonne og overraskende for to. Vil du filtrere flere kolonner, så kald ApplyAutoFilter én gang for at etablere området og det første kriterium, og tilføj derefter de andre gennem AutoFilterColumns.SetFieldCriteria, som lader området og de andre felter være. Begge veje ignorerer et feltnummer uden for området uden at raise, så verificér ved at læse tilbage, ideelt efter at have genåbnet den gemte fil:

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                     // område + felt 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);

Husk, at AUTOFILTER-recorden er en gemt definition: HotXLS skriver kriterierne og evaluerer dem ikke på det klassiske XLS-regneark, så en pipeline, der skal bruge de matchende rækker på serveren, må beregne dem selv dér, mens XLSX-facaden tilbyder evaluering på rækkeniveau som vist i HotXLS-datavalidering, AutoFilter og tabeller i Delphi. Så snart Excel skjuler rækker, afhænger totaler under området af hvordan SUBTOTAL og AGGREGATE behandler skjulte og filtrerede rækker, hvilket er det næste sted, et numerisk filter, der lydløst matcher ingenting, viser sig som et forkert tal

HotXLS læser og skriver BIFF8 XLS- og XLSX-arbejdsbøger nativt fra Delphi og C++Builder, inklusive AutoFilter-kriterier med numeriske, Boolean- og AND/OR-DOPER'er, som Excel evaluerer, som det var tiltænkt. Se HotXLS Delphi spreadsheet component for features, editioner og en prøvedownload