BIFF8 AutoFilter DOPER-kriterier i Delphi med HotXLS

HotXLS lagrer hvert BIFF8 AutoFilter-kriterium som en AUTOFILTER-record som bærer to 10-byte DOPER-strukturer, og DOPER-typen avgjør hvordan Excel sammenligner. Fra og med v2.384.45 skriver TXLSWorksheet.ApplyAutoFilter en sammenligning som '>=100' som en IEEE number-DOPER, slik at Excel treffer celler med tall i stedet for å sammenligne tekst. Feilrapporten som utløste endringen var kort og frustrerende: en nattlig eksport satte et filter på en beløpskolonne, filen åpnet uten klager, rullegardinpilen viste kriteriet, og filteret traff null rader. Ingenting var ødelagt. Bytene var gyldig BIFF8, bare feil slags gyldig, og det er den feilklassen denne artikkelen går gjennom, sammen med to eldre bytenivåfeil fikset i v2.384.18

Hva lagrer en BIFF8 AutoFilter egentlig?

En BIFF8 AutoFilter er et sett med tre record-typer, ikke én, og bare per-felt-recorden holder kriterier. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) registrerer hvor mange kolonner filterområdet dekker. FILTERMODE ($009B) er en kroppsløs markør som HotXLS bare skriver ut når minst ett felt har et aktivt kriterium. Så får hvert aktive felt sin egen AUTOFILTER-record ($009E, §2.4.6): en nullbasert feltindeks, et grbit-ord hvis to laveste biter er wJoin, to DOPER-er på nøyaktig 10 byte hver, og en valgfri hale som holder tegnene i en eventuell streng-DOPER. Feltindeksen er nullbasert på disk selv om ApplyAutoFilter nummererer feltene fra 1, noe som betyr noe første gang du jakter på en record i en hex dump. Den første byten i hver DOPER, vt, sier hva slags operand som følger:

• $04 er en IEEE 754 double lagret i de gjenværende 8 bytene, slik Excel lagrer en numerisk sammenligning
• $06 er en streng hvis lengde bor i en enkelt cch-byte, med tegnene selv skjøvet inn i record-halen
• $08 er en Bes-verdi, en Boolean eller feilkode pakket inn i to byte
• $0C og $0E bærer ingen operand og betyr treff på alle tomme og treff på alle ikke-tomme

Den andre byten, grbitSgn, holder sammenligningen: 1 til 6 mapper til <, =, <=, >, <> og >=. HotXLS holder begge bytene synlige etterpå gjennom AutoFilterColumns, hvis elementer eksponerer Criteria1 og Criteria2 som TXLSAutofilterDOPER-objekter med DataType, grbitSgn og Value, så du kan asserte på det som faktisk skal skrives i stedet for å gjette

Hvorfor traff et «>=100»-filter ingen rader i Excel?

Filteret traff ingenting fordi operanden var lagret som tekst, og Excel sammenligner en streng-DOPER mot cellen som tekst. Før v2.384.45 strippet CreateFilterDoper i lxFilter.pas korrekt >=-prefikset og satte sign-verdien til 6, men bygget alltid en vtString-DOPER som holdt tegnene 100. En tallcelle med 250 tilfredsstiller aldri en tekstsammenligning mot "100", så hver rad falt ut. Ikke noe unntak, ingen diagnostikk, ingen reparasjonsmelding fra Excel. Regelen siden v2.384.45 er bevisst smal: starter kriteriet med en sammenligningsoperator og resten lar seg lese som et tall uansett locale, skriver HotXLS en vtIEEENumber-DOPER med samme sign. En naken verdi uten operator beholder strengformen, for det er slik Excel selv lagrer et element plukket fra rullegardinlisten

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 = andre kolonnen i A1:B100 (1-basert på API-siden)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE-tall), grbitSgn = 6 (>=)
  // Før fiksen: DataType = 6 (streng), som traff ingenting
  Assert(Doper.DataType = 4);

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

Tolkingen er der de gjenværende skarpe kantene bor. Operanden går gjennom TryStrToFloat med punktum som desimaltegn, så '>=1.5' blir et tall mens '>=1,5' forblir en streng-DOPER og nok en gang stille ikke treffer noe, uansett hva Windows-locale sier. Datoer er den samme fellen i en annen drakt: '>=2026-01-01' er ikke et tall, så det skrives som tekst, mens Excel holder datoceller som serienumre. For likhet på et tall gir både '=100' og en numerisk Variant som 100 en IEEE-DOPER med sign 2, mens den nakne strengen '100' gir en tekstmatching. Bygg numeriske operander i kode i stedet for å formatere dem for mennesker:

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

  // Terskel med desimaler: formater alltid med punktum
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Datoer: sammenlign mot serienummeret Excel lagrer i cellen.
  // En Delphi TDateTime tilsvarer 1900-systemets serie for datoer etter mars 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Hvordan kombinerer AND og OR to betingelser?

wJoin-bitene i AUTOFILTER grbit er 0 for AND og 1 for OR, og HotXLS hadde de to konstantene byttet om helt frem til v2.384.18. Et between-aktig filter som minst 100 og under 500 ble lagret som minst 100 eller under 500, noe som i praksis treffer alle tall og ser ut som om filteret rett og slett ikke ble anvendt. De offentlige operatorkonstantene legger til en ny portingsfare. I HotXLS er xlAnd 0 og xlOr 1, mens Excel automation nummererer dem 1 og 2. XlAutoFilterOperator er en ren Byte, så kode oversatt fra en VBA-makro med literale tall kompilerer pent, og en literal 1 som betydde AND i COM betyr nå OR. Bruk de navngitte konstantene, og problemet kan ikke oppstå:

// Beløp mellom 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å disk
  Assert(Criteria2.grbitSgn = 1);      // 1 = mindre enn
end;

Boolean, tomme celler og taket på 255 tegn

Et boolsk kriterium lagres som en Bes-verdi ([MS-XLS] §2.5.10), og Bes setter verdi-byten bBoolErr først og fError-flagget sist. HotXLS skrev dem i motsatt rekkefølge før v2.384.18, så et filter etter TRUE la 1 inn i feilflagget, og Excel leste kriteriet som en feilkode. Skriver og leser var byttet om sammen, og det er derfor HotXLS tok egne filer gjennom en rundtur uten klager mens Excel var uenig — en påminnelse om at en selvkonsistent rundtur beviser ingenting om spesifikasjonssamsvar. Tomme celler trenger ingen operand i det hele tatt: å gi '=' alene gir en treff-alle-tomme-DOPER ($0C), og '<>' alene en treff-alle-ikke-tomme-DOPER ($0E)

Strengkriterier treffer en hard grense i DOPER-oppsettet. Lengdefeltet cch er én enkelt byte, så en strengoperand kan ikke overstige 255 tegn, og CreateFilterDoper trunkerer lengre tekst etter å ha strippet operatoren i stedet for å la lengdebyten wrappe og kaste record-halen ut av synk. Trunkeringen er stille, og et filter på en lang beskrivelseskolonne kan treffe annerledes enn hele teksten du ga inn. I BIFF8 lagrer halen hver streng som ett flaggbyte fulgt av UTF-16 kodeenheter, og den deklarerte record-størrelsen må telle disse bytene nøyaktig — samme bokføringsdisiplin som dekkes i hvordan BIFF record-lengdedeklarasjoner driver i en Delphi XLS-writer

Hvorfor utsletter et nytt ApplyAutoFilter-kall det første?

Hvert ApplyAutoFilter-kall redefinerer hele filterområdet, så bare kriteriet fra siste kall overlever. Internt kaller det SetAutoFilter, som tømmer hvert felt før området bygges opp igjen, og det er korrekt for én kolonne og overraskende for to. Skal du filtrere flere kolonner, kaller du ApplyAutoFilter én gang for å etablere området og det første kriteriet, og legger så til de andre gjennom AutoFilterColumns.SetFieldCriteria, som lar området og de andre feltene være i fred. Begge veier ignorerer et feltnummer utenfor området uten å reise noe, så verifiser ved å lese tilbake, helst etter å ha åpnet den lagrede filen på nytt:

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 lagret definisjon: HotXLS skriver kriteriene og evaluerer dem ikke på det klassiske XLS-regnearket, så en pipeline som trenger de matchende radene på serveren må beregne dem selv der, mens XLSX-fasaden tilbyr evaluering på radnivå som vist i HotXLS datavalidering, AutoFilter og tabeller i Delphi. Når Excel først skjuler rader, avhenger totalsummer under området av hvordan SUBTOTAL og AGGREGATE behandler skjulte og filtrerte rader, som er neste sted et numerisk filter som stille ikke treffer noe, viser seg som et feil tall

HotXLS leser og skriver BIFF8 XLS- og XLSX-arbeidsbøker direkte fra Delphi og C++Builder, inkludert AutoFilter-kriterier med numeriske, boolske og AND/OR-DOPER-er som Excel evaluerer slik det er ment. Se HotXLS Delphi regnearkkomponent for funksjoner, utgaver og en prøveversjon