Article technique

Critères DOPER AutoFilter BIFF8 en Delphi avec HotXLS

Une colonne de montants contient 250, 730 et 1 040. Un .xls généré applique un AutoFilter de >=100 à cette colonne, Excel ouvre le fichier sans invite de réparation, le menu déroulant d'en-tête montre qu'un filtre est posé, et chaque ligne de données disparaît. Les octets sont du BIFF8 valide. Tout le problème tient en un octet de type : l'opérande 100 a été stocké comme chaîne, Excel compare un opérande chaîne comme du texte, et aucune cellule numérique ne passe une comparaison textuelle contre "100". Stockez l'opérande comme un double IEEE et le même filtre garde les trois lignes

Où un AutoFilter BIFF8 range ses critères

Un AutoFilter BIFF8 s'étale sur trois types d'enregistrements, et un seul porte des critères. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) note sur combien de colonnes s'étend la plage du filtre. FILTERMODE ($009B) est un marqueur sans corps, et HotXLS ne l'émet que lorsqu'au moins un champ porte un critère actif. Chaque champ actif reçoit ensuite son propre enregistrement AUTOFILTER ($009E, §2.4.6) : un index de champ, un mot grbit dont les deux bits bas sont wJoin, deux structures DOPER d'exactement 10 octets chacune, et une queue optionnelle portant les caractères d'un éventuel opérande chaîne. L'index de champ sur disque part de zéro, tandis que TXLSWorksheet.ApplyAutoFilter numérote les champs à partir de 1, donc la troisième colonne de votre plage apparaît comme 02 00 au début du corps d'enregistrement dans un dump hexadécimal

Le premier octet de chaque DOPER, vt, dit quel type d'opérande suit :

  • $04 est un double IEEE 754 dans les 8 octets restants, la forme qu'Excel utilise pour une comparaison numérique
  • $06 est une chaîne dont la longueur tient dans un seul octet cch, les caractères étant poussés vers la queue de l'enregistrement
  • $08 est une valeur Bes, un booléen ou un code d'erreur empaqueté dans deux octets
  • $0C et $0E ne portent aucun opérande et signifient correspondre à tous les vides et correspondre à tous les non-vides

Le deuxième octet, grbitSgn, est la comparaison : 1 à 6 représentent <, =, <=, >, <> et >=. Dans HotXLS les deux octets restent inspectables après avoir posé un critère. TXLSWorksheet.AutoFilterColumns renvoie un TXLSAutofilter, sa méthode Find renvoie l'élément d'un champ numéroté à partir de 1, et cet élément expose Criteria1 et Criteria2 comme objets TXLSAutofilterDOPER avec des propriétés DataType, grbitSgn et Value en lecture seule. C'est ce qui sera écrit, octet par octet, quand le classeur sera sauvegardé

Anatomie de l'enregistrement AUTOFILTER HotXLS montrant l'index de champ à base zéro, le mot grbit dont les deux bits bas portent wJoin, et deux structures DOPER de 10 octets dont l'octet vt choisit un opérande nombre IEEE, chaîne, booléen Bes, vide ou non-vide tandis que grbitSgn encode l'opérateur de comparaison qu'Excel applique
Chaque enregistrement AUTOFILTER transporte deux DOPER de 10 octets, et l'octet vt décide si Excel compare un critère comme nombre, texte, booléen ou test de vide — relisez les deux via AutoFilterColumns avant de sauvegarder

Pourquoi un filtre '>=100' ne trouve aucune ligne dans Excel ?

Le filtre ne trouve rien parce qu'un opérande texte force une comparaison textuelle. Le premier rédacteur venu retire correctement le préfixe >=, pose le signe à 6, puis émet un DOPER vtString portant les caractères 100, puisque tout critère arrive de toute façon sous forme de chaîne, dans le dialogue d'Excel comme dans l'appel AutoFilter de VBA. Excel accepte cet enregistrement et l'évalue exactement comme écrit. Une cellule nombre valant 250 n'est jamais « supérieure ou égale » au texte "100" dans cette comparaison, donc la colonne se vide

TXLSWorksheet.ApplyAutoFilter et TXLSAutofilter.SetFieldCriteria convertissent les critères de la même façon. Une chaîne qui commence par l'un des six opérateurs, suivie d'un texte qui se parse comme un nombre, devient un DOPER vtIEEENumber avec cet opérateur comme signe. Un Variant numérique (Integer, Int64, Double, Currency et les autres types ordinaux et flottants) devient un DOPER nombre avec le signe 2, un test d'égalité. Un Variant booléen devient un DOPER Bes. Une chaîne nue sans opérateur reste un DOPER chaîne, parce que c'est ainsi qu'Excel stocke lui-même un élément choisi dans la liste déroulante

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;

  // Champ 2 = deuxième colonne de A1:B100 (champs à base 1 dans l'API)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // Attendu : DataType = 4 (nombre IEEE), grbitSgn = 6 (>=)
  // L'Assert est une vérification à ajouter vous-même, hors de la bibliothèque
  Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));

  Wb.SaveAs('orders.xls');
end;
Le même critère AutoFilter >=100 stocké en DOPER vtString ne trouve aucune cellule numérique, tandis que le DOPER vtIEEENumber avec grbitSgn 6 qu'écrit HotXLS est évalué numériquement contre le montant 250
Les octets sont du BIFF8 valide dans les deux cas — seul l'octet de type d'opérande diffère, voilà pourquoi Excel ouvre la version chaîne, montre le critère dans le menu déroulant et ne trouve quand même aucune ligne

La règle de la chaîne nue est celle qui piège. En Delphi, '100' et 100 paraissent interchangeables comme arguments Variant, pourtant le premier produit une correspondance textuelle et le second une égalité numérique. Deviner serait pire. Une colonne de références pièces qui contient 00100 en texte recevrait un DOPER nombre pour 100 et ne trouverait aucune de ses propres cellules. Les chaînes nues littérales reflètent ce qu'Excel écrit pour un choix dans une liste, et quand vous pensez à un nombre vous passez un nombre ou vous écrivez '=100', ce qui donne un DOPER IEEE avec le signe 2

Séparateurs décimaux, dates et système de dates 1904

Le test numérique après l'opérateur utilise toujours le point comme séparateur décimal, quoi qu'en disent les paramètres régionaux de Windows. '>=1.5' devient un DOPER nombre ; '>=1,5' ne se parse pas, reste un DOPER chaîne et vide une colonne numérique sur une machine allemande ou française aussi sûrement que sur une machine américaine. Les séparateurs de groupement échouent pareillement, donc '>=1,000' est du texte. Les espaces entre l'opérateur et les chiffres sont rognées, donc '>= 100' passe. Corriger le séparateur à un seul endroit évite qu'un rapport se comporte différemment selon le serveur qui l'a généré, et c'est le bon défaut pour un format de fichier

Les dates sont le même piège sous un autre costume. '>=2026-01-01' n'est pas un nombre, donc il part en texte, tandis qu'Excel garde les cellules de dates comme numéros de série et les compare numériquement. Passez le numéro de série. Un TDateTime Delphi a la même partie entière que le numéro de série du système 1900 d'Excel pour toute date à partir du 1er mars 1900. Un classeur basculé sur le système de dates 1904 compte à partir du 1er janvier 1904, donc ses numéros de série courent 1462 plus bas, et IXLSWorkbook.Date1904 dit quel système le fichier utilise. Le détail de ce décalage se trouve dans l'interaction des numéros de série de dates 1900 et 1904 avec les formats de nombres

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

  // Seuil avec fraction : formatez-le avec un point, jamais selon la locale
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Dates : comparez au numéro de série qu'Excel stocke dans la cellule
  Serial := Trunc(EncodeDate(2026, 1, 1));
  if Wb.Date1904 then
    Dec(Serial, 1462);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
    xlAnd, Unassigned);
end;

Un Variant numérique serait plus simple que la concaténation de chaînes, mais un Variant nombre signifie toujours l'égalité. Toute autre comparaison doit voyager en opérateur plus texte, donc l'étape de formatage est incontournable

Comment AND et OR joignent-ils deux conditions ?

Les bits wJoin du grbit d'AUTOFILTER valent 0 pour AND et 1 pour OR, et les inverser est facile et coûteux. Intervertissez-les et un filtre de plage comme au moins 100 et en dessous de 500 se sauvegarde en au moins 100 ou en dessous de 500, ce qui trouve tous les nombres et donne l'impression que le filtre ne s'est jamais appliqué. HotXLS écrit 0 pour xlAnd et 1 pour xlOr, et quand des critères d'égalité sont en jeu il pose aussi les bits fSimple1 et fSimple2 à côté de wJoin, qui marquent un DOPER de signe 2

Les constantes publiques apportent leur propre danger de portage. Dans HotXLS, xlAnd vaut 0 et xlOr vaut 1, tandis que l'automation Excel les numérote 1 et 2. XlAutoFilterOperator est déclaré comme un simple Byte, donc une macro VBA traduite avec des nombres littéraux compile sans avertissement. Un littéral 1 qui voulait dire AND sous COM veut maintenant dire OR. Un littéral 2 qui voulait dire OR entre en collision avec xlBottom10Items, et comme HotXLS traite toute valeur d'opérateur autre que xlOr comme AND quand il construit les deux DOPER, ce critère est joint avec AND. Les deux littéraux basculent. Des constantes nommées évitent tout le problème :

// Montant de 100 (inclus) à 500 (exclu)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  // Vérifications à ajouter vous-même : confirmez ce qui sera écrit
  Assert(Operator = xlAnd);            // wJoin = 0 sur disque
  Assert(Criteria2.DataType = 4);      // nombre IEEE
  Assert(Criteria2.grbitSgn = 1);      // 1 = inférieur à
end;
Disposition des bits wJoin HotXLS pour les critères AutoFilter où 0 joint deux DOPER avec AND et 1 avec OR, une droite numérique montrant comment des constantes inversées élargissent un filtre entre deux bornes en un OR qui trouve tout, et le conflit de numérotation xlAnd xlOr avec l'automation Excel
Des constantes wJoin inversées transforment un filtre entre deux bornes en un filtre qui trouve tous les nombres, et le VBA traduit compile quand même parce que XlAutoFilterOperator est un simple Byte — un littéral 1 qui voulait dire AND sous l'automation COM veut dire OR ici

Booléens, vides et plafond des 255 caractères

Un critère booléen est stocké comme valeur Bes ([MS-XLS] §2.5.10), et Bes met l'octet de valeur bBoolErr en premier et le drapeau fError en second. Les écrire dans l'ordre inverse et un filtre pour TRUE met 1 dans le drapeau d'erreur, donc Excel lit le critère comme un code d'erreur. Le pénible, c'est qu'un lecteur avec le même échange fait un aller-retour parfait de ses propres fichiers. Une bibliothèque qui ne lit jamais que ce qu'elle a écrit ne prouve rien ici sur la conformité ; ouvrir le fichier dans Excel et vérifier le dialogue du filtre, si. HotXLS écrit bBoolErr en premier et fError à 0, donc passer True produit un DOPER dont DataType vaut 8 et Value vaut 1

Les tests de vide n'ont pas besoin d'opérande. La chaîne '=' seule produit un DOPER tout-vide ($0C), et '<>' seule un DOPER tout-non-vide ($0E). Ce sont des critères à part entière, donc le champ compte comme actif et FILTERMODE est écrit

Les opérandes chaînes butent sur une limite dure de la disposition DOPER. Le champ cch fait un octet, donc un opérande chaîne peut porter au plus 255 caractères, le même plafond qu'impose le dialogue de filtre d'Excel. HotXLS rogne d'abord l'opérateur puis tronque tout ce qui dépasse à 255 caractères, ce qui empêche l'octet de longueur de boucler et de désynchroniser la queue de l'enregistrement. Un filtre sur une colonne de descriptions longues peut donc correspondre sur un préfixe de ce que vous avez passé. La casse reste tranquille : Excel apparie le texte du filtre sans égard à la casse au moment d'appliquer le filtre, et réécrire le critère stocké ne ferait que changer les données de l'utilisateur. En BIFF8 la queue stocke chaque chaîne comme un drapeau d'un octet suivi d'unités de code UTF-16, et la taille déclarée de l'enregistrement doit compter exactement ces octets, la même comptabilité que couvre la dérive des déclarations de longueur d'enregistrement BIFF dans un rédacteur XLS Delphi

Filtrer plusieurs colonnes et vérifier le fichier sauvegardé

Chaque appel ApplyAutoFilter redéfinit toute la plage du filtre, donc après deux appels seul le critère du second survit. La méthode passe par le TXLSWorksheet.SetAutoFilter public, qui efface chaque champ avant de reconstruire la plage. C'est correct pour une seule colonne et surprenant la première fois que vous filtrez deux colonnes. Pour plusieurs colonnes, appelez ApplyAutoFilter une fois pour établir la plage et le premier critère, puis ajoutez les autres avec AutoFilterColumns.SetFieldCriteria, qui touche un champ et laisse la plage et les autres champs tranquilles. Les deux voies ignorent un numéro de champ hors plage sans lever d'exception, donc la vérification qui vaut la peine relit les critères depuis le fichier rouvert

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                       // plage + champ 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
  // Vérifications à ajouter vous-même après réouverture du fichier
  Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6));   // texte 'North'
  Assert(Find(3).Criteria1.DataType = 4);                          // nombre 100
  Assert(Find(4).Criteria1.DataType = 8);                          // booléen TRUE
end;

L'enregistrement AUTOFILTER est une définition stockée. Sur une feuille XLS classique HotXLS écrit les critères pour qu'Excel les évalue à l'ouverture du fichier, et l'évaluation au niveau des lignes côté serveur vit dans le modèle objet XLSX, comme montré dans validation de données HotXLS, AutoFilter et tables en Delphi. Une fois qu'Excel cache des lignes, les totaux sous la plage dépendent de la façon dont SUBTOTAL et AGGREGATE traitent les lignes cachées et filtrées, et un filtre numérique stocké en texte s'y voit comme un total de zéro

La conversion des critères, la disposition Bes et les bits de jointure décrits plus haut sont ce que HotXLS écrit dans chaque classeur BIFF8 qu'il sauvegarde, aux côtés du support XLSX natif pour Delphi et C++Builder. Les éditions, la liste complète des fonctionnalités et un téléchargement d'essai sont sur la page du composant tableur Delphi HotXLS