Technischer Artikel

BIFF8-AutoFilter-DOPER-Kriterien in Delphi mit HotXLS

Eine Betragsspalte enthält 250, 730 und 1.040. Eine erzeugte .xls-Datei legt auf diese Spalte einen AutoFilter von >=100, Excel öffnet die Datei ohne Reparatur-Prompt, das Kopfzeilen-Dropdown zeigt, dass ein Filter gesetzt ist, und jede Datenzeile verschwindet. Die Bytes sind gültiges BIFF8. Das ganze Problem ist ein Typ-Byte: Der Operand 100 wurde als String gespeichert, Excel vergleicht einen String-Operanden als Text, und keine numerische Zelle besteht einen Textvergleich gegen "100". Speichert man den Operanden als IEEE-Double, behält derselbe Filter alle drei Zeilen

Wo ein BIFF8-AutoFilter seine Kriterien ablegt

Ein BIFF8-AutoFilter verteilt sich auf drei Record-Typen, und nur einer davon trägt Kriterien. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) vermerkt, wie viele Spalten der Filterbereich umspannt. FILTERMODE ($009B) ist ein Marker ohne Body, und HotXLS schreibt ihn nur, wenn mindestens ein Feld ein aktives Kriterium hat. Jedes aktive Feld bekommt dann seinen eigenen AUTOFILTER-Record ($009E, §2.4.6): einen Feldindex, ein grbit-Wort, dessen untere zwei Bits wJoin sind, zwei DOPER-Strukturen von je exakt 10 Bytes und einen optionalen Tail, der die Zeichen eines String-Operanden hält. Der Feldindex auf dem Datenträger ist nullbasiert, während TXLSWorksheet.ApplyAutoFilter Felder ab 1 nummeriert, also erscheint die dritte Spalte Ihres Bereichs im Hex-Report als 02 00 am Anfang des Record-Bodys

Das erste Byte jedes DOPER, vt, sagt, welche Art Operand folgt:

  • $04 ist ein IEEE-754-Double in den restlichen 8 Bytes, die Form, die Excel für einen numerischen Vergleich verwendet
  • $06 ist ein String, dessen Länge in einem einzigen cch-Byte sitzt, mit den Zeichen im Record-Tail
  • $08 ist ein Bes-Wert, ein Boolean oder Fehlercode, gepackt in zwei Bytes
  • $0C und $0E tragen gar keinen Operanden und bedeuten: alle Leerzellen treffen bzw. alle Nicht-Leerzellen treffen

Das zweite Byte, grbitSgn, ist der Vergleich: 1 bis 6 stehen für <, =, <=, >, <> und >=. In HotXLS bleiben beide Bytes nach dem Setzen eines Kriteriums inspizierbar. TXLSWorksheet.AutoFilterColumns liefert ein TXLSAutofilter, seine Find-Methode liefert das Element zu einem 1-basierten Feld, und dieses Element exponiert Criteria1 und Criteria2 als TXLSAutofilterDOPER-Objekte mit schreibgeschützten DataType-, grbitSgn- und Value-Properties. Das ist es, was beim Speichern der Arbeitsmappe Byte für Byte geschrieben wird

HotXLS-AUTOFILTER-Record-Anatomie mit dem nullbasierten Feldindex, dem grbit-Wort, dessen untere zwei Bits wJoin tragen, und zwei 10-Byte-DOPER-Strukturen, deren vt-Byte einen IEEE-Zahl-, String-, Bes-Boolean-, Leer- oder Nicht-Leer-Operanden wählt, während grbitSgn den Vergleichsoperator kodiert, den Excel anwendet
Jeder AUTOFILTER-Record trägt zwei 10-Byte-DOPERs, und das vt-Byte entscheidet, ob Excel ein Kriterium als Zahl-, Text-, Boolean- oder Leer-Test vergleicht — beide über AutoFilterColumns zurücklesen, bevor Sie speichern

Warum trifft ein '>=100'-Filter in Excel keine Zeilen?

Der Filter trifft nichts, weil ein Text-Operand einen Textvergleich erzwingt. Der naheliegende erste Writer strippt das >=-Präfix korrekt, setzt das Vorzeichen auf 6 und schreibt dann einen vtString-DOPER mit den Zeichen 100, denn jedes Kriterium kommt ohnehin als String an, sowohl in Excels eigenem Dialog als auch im AutoFilter-Aufruf von VBA. Excel akzeptiert diesen Record und wertet ihn exakt so aus, wie er dasteht. Eine Zahlenzelle mit 250 ist in diesem Vergleich nie größer oder gleich dem Text "100", also leert sich die Spalte

TXLSWorksheet.ApplyAutoFilter und TXLSAutofilter.SetFieldCriteria konvertieren Kriterien auf dieselbe Weise. Ein String, der mit einem der sechs Operatoren beginnt, gefolgt von Text, der als Zahl parst, wird ein vtIEEENumber-DOPER mit diesem Operator als Vorzeichen. Ein numerischer Variant (Integer, Int64, Double, Currency und die anderen Ordinal- und Float-Typen) wird ein Zahlen-DOPER mit Vorzeichen 2, ein Gleichheitstest. Ein Boolean-Variant wird ein Bes-DOPER. Ein nackter String ohne Operator bleibt ein String-DOPER, denn genau so speichert Excel selbst einen aus der Dropdown-Liste gewählten Eintrag

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;

  // Feld 2 = zweite Spalte von A1:B100 (Felder sind in der API 1-basiert)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // Erwartet: DataType = 4 (IEEE-Zahl), grbitSgn = 6 (>=)
  // Das Assert ist eine Prüfung, die Sie selbst ergänzen, kein Teil der Bibliothek
  Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));

  Wb.SaveAs('orders.xls');
end;
Dasselbe >=100-AutoFilter-Kriterium als vtString-DOPER gespeichert trifft keine numerischen Zellen, während der vtIEEENumber-DOPER mit grbitSgn 6, den HotXLS schreibt, numerisch gegen den Betrag 250 ausgewertet wird
Die Bytes sind beide Male gültiges BIFF8 — nur das Operand-Typ-Byte unterscheidet sich, weshalb Excel die String-Version öffnet, das Kriterium im Dropdown zeigt und trotzdem null Zeilen trifft

Die Nackter-String-Regel ist die, die Leute erwischt. In Delphi sehen '100' und 100 als Variant-Argumente austauschbar aus, doch das erste erzeugt einen Textvergleich und das zweite eine numerische Gleichheit. Raten wäre schlimmer. Eine Teilenummern-Spalte, die 00100 als Text hält, bekäme einen Zahlen-DOPER für 100 und träfe keine ihrer eigenen Zellen. Literale nackte Strings spiegeln, was Excel für einen Listeneintrag schreibt, und wenn Sie eine Zahl meinen, übergeben Sie eine Zahl oder schreiben '=100', was einen IEEE-DOPER mit Vorzeichen 2 ergibt

Dezimaltrennzeichen, Datumsangaben und das 1904-Datumssystem

Der Zahltest nach dem Operator verwendet immer den Punkt als Dezimaltrennzeichen, egal was die Windows-Regionaleinstellungen sagen. '>=1.5' wird ein Zahlen-DOPER; '>=1,5' parst nicht, bleibt ein String-DOPER und leert eine numerische Spalte auf einer deutschen oder französischen Maschine genauso zuverlässig wie auf einer amerikanischen. Gruppentrennzeichen scheitern genauso, also ist '>=1,000' Text. Whitespace zwischen Operator und Ziffern wird getrimmt, '>= 100' ist also in Ordnung. Das Trennzeichen an einer Stelle festzulegen verhindert, dass sich ein Report je nach erzeugendem Server anders verhält, und das ist der richtige Default für ein Dateiformat

Datumswerte sind dieselbe Falle im anderen Kostüm. '>=2026-01-01' ist keine Zahl, wird also als Text geschrieben, während Excel Datumzellen als serielle Zahlen hält und numerisch vergleicht. Übergeben Sie die Serienzahl. Ein Delphi-TDateTime hat ab dem 1. März 1900 denselben Ganzzahlanteil wie die Excel-1900-System-Serienzahl. Eine auf das 1904-Datumssystem umgeschaltete Arbeitsmappe zählt ab dem 1. Januar 1904, ihre Serienzahlen laufen also 1462 niedriger, und IXLSWorkbook.Date1904 verrät, welches System die Datei nutzt. Die Details zu diesem Offset stehen in wie 1900er- und 1904er-Datumsserien mit Zahlenformaten interagieren

var
  Fmt: TFormatSettings;
  Serial: Integer;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // Schwelle mit Nachkommastellen: mit Punkt formatieren, nie mit dem Locale
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Datum: gegen die Serienzahl vergleichen, die Excel in der Zelle speichert
  Serial := Trunc(EncodeDate(2026, 1, 1));
  if Wb.Date1904 then
    Dec(Serial, 1462);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
    xlAnd, Unassigned);
end;

Ein numerischer Variant wäre einfacher als String-Konkatenation, aber eine Variant-Zahl bedeutet immer Gleichheit. Jeder andere Vergleich muss als Operator plus Text reisen, daher lässt sich der Formatierungsschritt nicht überspringen

Wie verknüpfen AND und OR zwei Bedingungen?

Die wJoin-Bits des AUTOFILTER-grbit sind 0 für AND und 1 für OR, und sie zu vertauschen ist leicht und teuer. Vertauscht man sie, wird ein Bereichsfilter wie mindestens 100 und unter 500 als mindestens 100 oder unter 500 gespeichert, was jede Zahl trifft und so aussieht, als hätte der Filter nie gegriffen. HotXLS schreibt 0 für xlAnd und 1 für xlOr, und wenn Gleichheitskriterien im Spiel sind, setzt es daneben auch die Bits fSimple1 und fSimple2 neben wJoin, die einen DOPER mit Vorzeichen 2 markieren

Die öffentlichen Konstanten bringen eine eigene Portierungs-Falle mit. In HotXLS ist xlAnd 0 und xlOr ist 1, während Excel-Automation sie mit 1 und 2 nummeriert. XlAutoFilterOperator ist als schlichtes Byte deklariert, also kompiliert ein mit Literalen übersetztes VBA-Makro ohne Warnung. Ein literal 1, das unter COM für AND stand, bedeutet jetzt OR. Ein literal 2, das für OR stand, kollidiert mit xlBottom10Items, und weil HotXLS beim Bau der beiden DOPERs jeden Operatorwert außer xlOr als AND behandelt, wird dieses Kriterium mit AND verknüpft. Beide Literale kippen. Benannte Konstanten vermeiden das ganze Problem:

// Betrag von 100 (inklusive) bis unter 500 (exklusiv)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  // Prüfungen, die Sie selbst ergänzen: bestätigen, was geschrieben wird
  Assert(Operator = xlAnd);            // wJoin = 0 in der Datei
  Assert(Criteria2.DataType = 4);      // IEEE-Zahl
  Assert(Criteria2.grbitSgn = 1);      // 1 = kleiner als
end;
HotXLS-wJoin-Bitlayout für AutoFilter-Kriterien, bei dem 0 zwei DOPERs mit AND und 1 mit OR verknüpft, eine Zahlenlinie, die zeigt, wie vertauschte Konstanten einen Bereichsfilter zu einem alles-treffenden OR weiten, und der xlAnd-xlOr-Nummerierungsclash mit der Excel-Automation
Vertauschte wJoin-Konstanten machen aus einem Bereichsfilter einen, der jede Zahl trifft, und übersetztes VBA kompiliert trotzdem, weil XlAutoFilterOperator ein schlichtes Byte ist — ein literal 1, das unter COM-Automation AND bedeutete, bedeutet hier OR

Booleans, Leerzellen und die 255-Zeichen-Grenze

Ein Boolean-Kriterium wird als Bes-Wert gespeichert ([MS-XLS] §2.5.10), und Bes stellt das Wert-Byte bBoolErr voran und das fError-Flag dahinter. In umgekehrter Reihenfolge geschrieben setzt ein Filter für TRUE die 1 ins Fehler-Flag, und Excel liest das Kriterium als Fehlercode. Das Fiesliche: Ein Reader mit demselben Tausch round-tript seine eigenen Dateien perfekt. Eine Bibliothek, die nur liest, was sie selbst geschrieben hat, beweist hier nichts über Konformität; die Datei in Excel öffnen und den Filter-Dialog prüfen schon. HotXLS schreibt bBoolErr zuerst und fError als 0, also erzeugt True einen DOPER, dessen DataType 8 ist und dessen Value 1 ist

Leer-Tests brauchen keinen Operanden. Der String '=' für sich erzeugt einen Match-All-Blanks-DOPER ($0C), und '<>' für sich einen Match-All-Non-Blanks-DOPER ($0E). Beides sind vollwertige Kriterien, das Feld zählt also als aktiv, und FILTERMODE wird geschrieben

String-Operanden stoßen im DOPER-Layout an eine harte Grenze. Das cch-Feld ist ein Byte, ein String-Operand fasst also höchstens 255 Zeichen, dieselbe Deckelung, die Excels eigener Filter-Dialog erzwingt. HotXLS strippt zuerst den Operator und kürzt dann alles Längere auf 255 Zeichen, damit das Längen-Byte nicht überläuft und den Record-Tail aus dem Takt wirft. Ein Filter auf einer langen Beschreibungsspalte trifft also unter Umständen auf ein Präfix des Übergebenen. Groß- und Kleinschreibung bleibt unangetastet: Excel vergleicht Filtertext beim Anwenden ohne Rücksicht auf Groß- und Kleinschreibung, und das gespeicherte Kriterium umzuschreiben würde nur die Daten des Nutzers verändern. In BIFF8 legt der Tail jeden String als Ein-Byte-Flag gefolgt von UTF-16-Code-Einheiten ab, und die deklarierte Record-Größe muss exakt diese Bytes zählen, dieselbe Buchführung, die in wie BIFF-Record-Längendeklarationen in einem Delphi-XLS-Writer driften behandelt wird

Mehrere Spalten filtern und die gespeicherte Datei prüfen

Jeder ApplyAutoFilter-Aufruf definiert den ganzen Filterbereich neu, nach zwei Aufrufen überlebt also nur das Kriterium des zweiten. Die Methode läuft über das öffentliche TXLSWorksheet.SetAutoFilter, das vor dem Neuaufbau des Bereichs jedes Feld löscht. Für eine einzelne Spalte ist das korrekt, und beim ersten Filtern zweier Spalten überraschend. Für mehrere Spalten gilt: ApplyAutoFilter einmal aufrufen, um Bereich und erstes Kriterium zu setzen, dann den Rest über AutoFilterColumns.SetFieldCriteria ergänzen, das ein Feld anfasst und Bereich und andere Felder in Ruhe lässt. Beide Wege ignorieren eine Feldnummer außerhalb des Bereichs, ohne eine Exception zu werfen, also besteht die lohnende Prüfung darin, die Kriterien aus der wieder geöffneten Datei zurückzulesen

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                       // Bereich + Feld 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
  // Prüfungen, die Sie selbst ergänzen, nach dem Wiederöffnen der Datei
  Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6));   // Text 'North'
  Assert(Find(3).Criteria1.DataType = 4);                          // Zahl 100
  Assert(Find(4).Criteria1.DataType = 8);                          // Boolean TRUE
end;

Der AUTOFILTER-Record ist eine gespeicherte Definition. Auf einem klassischen XLS-Arbeitsblatt schreibt HotXLS die Kriterien, damit Excel sie beim Öffnen der Datei auswertet, und die zeilenweise Auswertung auf der Server-Seite lebt im XLSX-Objektmodell, wie in HotXLS-Datenvalidierung, AutoFilter und Tabellen in Delphi gezeigt. Sobald Excel Zeilen ausblendet, hängen Summen unterhalb des Bereichs davon ab, wie SUBTOTAL und AGGREGATE ausgeblendete und gefilterte Zeilen behandeln, und ein als Text gespeicherter Zahlenfilter fällt dort als Summe null auf

Die Kriterien-Konvertierung, das Bes-Layout und die Join-Bits von oben sind das, was HotXLS in jede BIFF8-Arbeitsmappe schreibt, die es speichert, neben nativer XLSX-Unterstützung für Delphi und C++Builder. Editionen, die volle Featureliste und ein Trial-Download finden sich auf der Seite HotXLS Delphi spreadsheet component