Criteri DOPER dell'AutoFilter BIFF8 in Delphi con HotXLS

HotXLS salva ogni criterio di AutoFilter BIFF8 come record AUTOFILTER che trasporta due strutture DOPER da 10 byte, e il tipo di DOPER decide come Excel confronta. Dalla v2.384.45, TXLSWorksheet.ApplyAutoFilter scrive un confronto come '>=100' come DOPER numerico IEEE, così Excel trova le celle numeriche invece di confrontare testo. La segnalazione che ha motivato la modifica era breve e esasperante: un export notturno applicava un filtro su una colonna di importi, il file si apriva senza protestare, la freccia del menu a tendina mostrava il criterio, e il filtro trovava zero righe. Nulla era corrotto. I byte erano BIFF8 validi, solo validi del tipo sbagliato, ed è proprio quella famiglia di guai che questo articolo ripercorre, insieme a due vecchi errori a livello di byte corretti nella v2.384.18

Che cosa memorizza davvero un AutoFilter BIFF8?

Un AutoFilter BIFF8 è un insieme di tre tipi di record, non uno, e solo il record per campo contiene criteri. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) registra quante colonne copre l'intervallo del filtro. FILTERMODE ($009B) è un marcatore senza corpo che HotXLS emette solo quando almeno un campo ha un criterio attivo. Poi ogni campo attivo riceve il suo record AUTOFILTER ($009E, §2.4.6): un indice di campo in base zero, una parola grbit i cui due bit bassi sono wJoin, due DOPER da esattamente 10 byte ciascuno e una coda opzionale che custodisce i caratteri di un eventuale DOPER stringa. L'indice di campo è in base zero su disco anche se ApplyAutoFilter numera i campi da 1, cosa che conta la prima volta che andate a caccia di un record in un dump esadecimale. Il primo byte di ogni DOPER, vt, dice che tipo di operando segue:

$04 è un double IEEE 754 memorizzato negli 8 byte rimanenti, ed è il modo in cui Excel conserva un confronto numerico
$06 è una stringa la cui lunghezza sta in un solo byte cch, con i caratteri stessi spinti nella coda del record
$08 è un valore Bes, un Boolean o un codice di errore compresso in due byte
$0C e $0E non portano alcun operando e significano trova tutte le celle vuote e trova tutte le non vuote

Il secondo byte, grbitSgn, contiene il confronto: da 1 a 6 corrispondono a <, =, <=, >, <> e >=. HotXLS tiene visibili entrambi i byte dopo la scrittura tramite AutoFilterColumns, i cui elementi espongono Criteria1 e Criteria2 come oggetti TXLSAutofilterDOPER con DataType, grbitSgn e Value, così potete fare assert su ciò che verrà scritto invece di tirare a indovinare

Anatomia del record AUTOFILTER di HotXLS con l'indice di campo in base zero, la parola grbit i cui due bit bassi contengono wJoin, e due strutture DOPER da 10 byte il cui byte vt seleziona un operando numero IEEE, stringa, Boolean Bes, vuoto o non vuoto mentre grbitSgn codifica l'operatore di confronto che Excel applica — Ogni record AUTOFILTER trasporta due DOPER da 10 byte, e il byte vt decide se Excel confronta il criterio come numero, testo, Boolean o test di vuoto — rileggete entrambi tramite AutoFilterColumns prima di salvare

Perché un filtro '>=100' non trovava righe in Excel?

Il filtro non trovava nulla perché l'operando era memorizzato come testo, ed Excel confronta un DOPER stringa con la cella come testo. Prima della v2.384.45, CreateFilterDoper in lxFilter.pas rimuoveva correttamente il prefisso >= e impostava il segno a 6, poi costruiva sempre un DOPER vtString con i caratteri 100. Una cella numerica che contiene 250 non soddisfa mai un confronto testuale contro "100", quindi ogni riga usciva dal set. Nessuna eccezione, nessun diagnostico, nessuna richiesta di riparazione da Excel. La regola dalla v2.384.45 è volutamente stretta: se il criterio inizia con un operatore di confronto e il resto è interpretabile come numero con regole di cultura invariante, HotXLS scrive un DOPER vtIEEENumber con lo stesso segno. Un valore nudo senza operatore resta in forma stringa, perché è così che Excel stesso memorizza una voce scelta dal menu a tendina

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;

  // Campo 2 = seconda colonna di A1:B100 (lato API in base 1)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (numero IEEE), grbitSgn = 6 (>=)
  // Prima della correzione: DataType = 6 (stringa), che non trovava nulla
  Assert(Doper.DataType = 4);

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

HotXLS scrive lo stesso criterio AutoFilter >=100 come DOPER vtString che non trova celle numeriche o come DOPER vtIEEENumber con grbitSgn 6 che Excel valuta numericamente contro l'importo 250, il guasto silenzioso a zero righe corretto da CreateFilterDoper nella v2.384.45 — I byte erano BIFF8 validi in entrambi i casi — è cambiato solo il byte del tipo di operando, ed ecco perché Excel apriva il file, mostrava il criterio nel menu a tendina e trovava comunque zero righe

Il parsing è dove restano gli spigoli vivi. L'operando passa per TryStrToFloat con il punto come separatore decimale, quindi '>=1.5' diventa un numero e '>=1,5' resta un DOPER stringa che di nuovo non trova nulla, qualunque cosa dica il locale di Windows. Le date sono la stessa trappola in un costume diverso: '>=2026-01-01' non è un numero, quindi viene scritto come testo, mentre Excel conserva le celle data come numeri seriali. Per l'uguaglianza su un numero, sia '=100' sia un Variant numerico come 100 producono un DOPER IEEE con segno 2, mentre la stringa nuda '100' produce un confronto testuale. Costruite gli operandi numerici nel codice invece di formattarli per gli umani:

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

  // Soglia con decimali: formattare sempre col punto
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Date: confrontate col numero seriale che Excel memorizza nella cella.
  // Un TDateTime Delphi coincide col seriale del sistema 1900 per date dopo marzo 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Come fanno AND e OR a unire due condizioni?

I bit wJoin del grbit dell'AUTOFILTER valgono 0 per AND e 1 per OR, e fino alla v2.384.18 HotXLS aveva quelle due costanti invertite. Un filtro in stile between come almeno 100 e sotto 500 veniva salvato come almeno 100 o sotto 500, che in pratica trova ogni numero e sembra che il filtro semplicemente non sia stato applicato. Le costanti pubbliche degli operatori aggiungono un secondo pericolo di porting. In HotXLS, xlAnd vale 0 e xlOr vale 1, mentre l'automazione di Excel li numera 1 e 2. XlAutoFilterOperator è un semplice Byte, quindi codice tradotto da una macro VBA con numeri letterali compila pulito, e un letterale 1 che significava AND in COM ora significa OR. Usate le costanti nominali e il problema non può sorgere:

// Importo tra 100 (incluso) e 500 (escluso)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 su disco
  Assert(Criteria2.grbitSgn = 1);      // 1 = minore di
end;

Layout dei bit wJoin di HotXLS per i criteri AutoFilter dove 0 unisce due DOPER con AND e 1 con OR, una linea dei numeri che mostra come le costanti invertite prima della v2.384.18 allargassero un filtro between in un OR che trova tutto, e il conflitto di numerazione tra xlAnd e xlOr con l'automazione di Excel — Costanti wJoin invertite trasformavano un filtro between in uno che trova ogni numero, e il VBA tradotto compila ancora perché XlAutoFilterOperator è un semplice Byte — un letterale 1 che significava AND sotto l'automazione COM qui significa OR

Boolean, celle vuote e il tetto dei 255 caratteri

Un criterio Boolean viene memorizzato come valore Bes ([MS-XLS] §2.5.10), e Bes mette prima il byte di valore bBoolErr e dopo il flag fError. HotXLS li scriveva in ordine opposto prima della v2.384.18, quindi un filtro per TRUE metteva 1 nel flag di errore ed Excel leggeva il criterio come codice di errore. Writer e reader erano invertiti insieme, ed ecco perché HotXLS riliceva i propri file senza proteste mentre Excel era in disaccordo: promemoria che un round trip auto-coerente non prova nulla sulla conformità alle specifiche. Per le celle vuote l'operando non serve affatto: passare '=' da solo produce un DOPER trova-tutte-le-vuote ($0C) e '<>' da solo un DOPER trova-tutte-le-non-vuote ($0E)

I criteri stringa sbattono contro un limite fisso del layout DOPER. Il campo di lunghezza cch è un solo byte, quindi un operando stringa non può superare i 255 caratteri, e CreateFilterDoper tronca il testo più lungo dopo aver rimosso l'operatore invece di lasciare che il byte di lunghezza vada in wrap e desincronizzi la coda del record. Il troncamento è silenzioso, e un filtro su una colonna di descrizioni lunghe può trovare in modo diverso dal testo completo che avete passato. In BIFF8 la coda memorizza ogni stringa come flag di un byte seguito da code unit UTF-16, e la dimensione dichiarata del record deve contare quei byte esattamente, la stessa disciplina di contabilità trattata in come le dichiarazioni di lunghezza dei record BIFF vanno in deriva in un writer XLS per Delphi

Perché una seconda chiamata ad ApplyAutoFilter cancella la prima?

Ogni chiamata a ApplyAutoFilter ridefinisce l'intero intervallo del filtro, quindi sopravvive solo il criterio dell'ultima chiamata. Internamente chiama SetAutoFilter, che ripulisce ogni campo prima di ricostruire l'intervallo, e per una colonna è corretto mentre per due è sorprendente. Per filtrare più colonne, chiamate ApplyAutoFilter una volta per stabilire intervallo e primo criterio, poi aggiungete gli altri tramite AutoFilterColumns.SetFieldCriteria, che lascia in pace intervallo e altri campi. Entrambe le vie ignorano un numero di campo fuori intervallo senza sollevare nulla, quindi verificate rileggendo, idealmente dopo aver riaperto il file salvato:

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                     // intervallo + campo 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);

Tenete presente che il record AUTOFILTER è una definizione memorizzata: HotXLS scrive i criteri e non li valuta sul foglio XLS classico, quindi una pipeline che ha bisogno delle righe corrispondenti sul server deve calcolarsele lì per conto suo, mentre la facciata XLSX offre valutazione a livello di riga come mostrato in convalida dati, AutoFilter e tabelle con HotXLS in Delphi. Quando Excel nasconde le righe, qualsiasi totale sotto l'intervallo dipende da come SUBTOTAL e AGGREGATE trattano righe nascoste e filtrate, che è il posto successivo dove un filtro numerico che in silenzio non trova nulla si manifesta come numero sbagliato

HotXLS legge e scrive workbook BIFF8 XLS e XLSX in modo nativo da Delphi e C++Builder, compresi criteri AutoFilter con DOPER numerici, Boolean e AND/OR che Excel valuta come previsto. Vedete il componente foglio di calcolo HotXLS per Delphi per funzionalità, edizioni e download della trial