Articolo tecnico

Criteri DOPER dell'AutoFilter BIFF8 in Delphi con HotXLS

Una colonna importi contiene 250, 730 e 1.040. Un .xls generato applica alla colonna un AutoFilter di >=100, Excel apre il file senza prompt di riparazione, il menu a tendina dell'intestazione mostra che un filtro è impostato, e ogni riga di dati sparisce. I byte sono BIFF8 validi. Il problema è tutto in un byte di tipo: l'operando 100 è stato memorizzato come stringa, Excel confronta un operando stringa come testo, e nessuna cella numerica passa un confronto testuale contro "100". Memoriazzate l'operando come double IEEE e lo stesso filtro si tiene tutte e tre le righe

Dove un AutoFilter BIFF8 conserva i suoi criteri

Un AutoFilter BIFF8 è sparso su tre tipi di record, e solo uno di questi trasporta criteri. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) registra quante colonne copre l'intervallo del filtro. FILTERMODE ($009B) è un marcatore senza corpo, e HotXLS lo emette solo quando almeno un campo ha un criterio attivo. Ogni campo attivo riceve poi il suo record AUTOFILTER ($009E, §2.4.6): un indice di campo, una parola grbit i cui due bit bassi sono wJoin, due strutture DOPER da esattamente 10 byte l'una, e una coda opzionale che contiene i caratteri di un eventuale operando stringa. L'indice di campo su disco è in base zero, mentre TXLSWorksheet.ApplyAutoFilter numera i campi da 1, quindi la terza colonna del vostro intervallo compare come 02 00 all'inizio del corpo del record in un dump esadecimale

Il primo byte di ogni DOPER, vt, dice che tipo di operando segue:

  • $04 è un double IEEE 754 negli 8 byte rimanenti, la forma che Excel usa per un confronto numerico
  • $06 è una stringa la cui lunghezza sta in un solo byte cch, con i caratteri 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 proprio alcun operando e significano trova tutte le celle vuote e trova tutte le non vuote

Il secondo byte, grbitSgn, è il confronto: da 1 a 6 stanno per <, =, <=, >, <> e >=. In HotXLS entrambi i byte restano ispezionabili dopo aver impostato un criterio. TXLSWorksheet.AutoFilterColumns restituisce un TXLSAutofilter, il suo metodo Find restituisce la voce di un campo in base 1, e quella voce espone Criteria1 e Criteria2 come oggetti TXLSAutofilterDOPER con proprietà DataType, grbitSgn e Value in sola lettura. È ciò che verrà scritto, byte per byte, al salvataggio del workbook

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 trova nulla perché un operando testuale impone un confronto testuale. Il primo scrittore veniale rimuove correttamente il prefisso >=, imposta il segno a 6, e poi emette un DOPER vtString con i caratteri 100, dato che ogni criterio arriva comunque come stringa, sia nella finestra di dialogo di Excel sia nella chiamata AutoFilter di VBA. Excel accetta quel record e lo valuta esattamente come scritto. Una cella numerica con 250 non è mai "maggiore o uguale" al testo "100" in quel confronto, così la colonna si svuota

TXLSWorksheet.ApplyAutoFilter e TXLSAutofilter.SetFieldCriteria convertono i criteri allo stesso modo. Una stringa che inizia con uno dei sei operatori, seguita da testo che si lascia leggere come numero, diventa un DOPER vtIEEENumber con quell'operatore come segno. Un Variant numerico (Integer, Int64, Double, Currency e gli altri tipi ordinali e floating point) diventa un DOPER numero con segno 2, un test di uguaglianza. Un Variant Boolean diventa un DOPER Bes. Una stringa nuda senza operatore resta un DOPER stringa, perché è così che Excel stesso memorizza una voce scelta dall'elenco 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 (i campi sono in base 1 nell'API)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // Atteso: DataType = 4 (numero IEEE), grbitSgn = 6 (>=)
  // L'Assert è un controllo che aggiungete voi, non fa parte della libreria
  Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));

  Wb.SaveAs('orders.xls');
end;
Lo stesso criterio AutoFilter >=100 memorizzato come DOPER vtString non trova celle numeriche, mentre il DOPER vtIEEENumber con grbitSgn 6 che HotXLS scrive viene valutato numericamente contro l'importo 250
I byte sono BIFF8 validi in entrambi i casi — cambia solo il byte del tipo di operando, ed è per questo che Excel apre la versione stringa, mostra il criterio nel menu a tendina e trova comunque zero righe

La regola della stringa nuda è quella che becca la gente. In Delphi '100' e 100 sembrano interscambiabili come argomenti Variant, eppure il primo produce un confronto testuale e il secondo un'uguaglianza numerica. Lasciare indovinare sarebbe peggio. Una colonna di part number che tiene 00100 come testo riceverebbe un DOPER numero per 100 e non troverebbe nessuna delle sue celle. Le stringhe nude letterali rispecchiano ciò che Excel scrive per una voce di elenco, e quando intendete un numero passate un numero o scrivete '=100', che dà un DOPER IEEE con segno 2

Separatori decimali, date e sistema di date 1904

Il test numerico dopo l'operatore usa sempre il punto come separatore decimale, qualunque cosa dicano le impostazioni regionali di Windows. '>=1.5' diventa un DOPER numero; '>=1,5' non si interpreta, resta un DOPER stringa e svuota una colonna numerica su una macchina tedesca o francese esattamente come su una americana. I separatori di raggruppamento falliscono allo stesso modo, quindi '>=1,000' è testo. Gli spazi fra l'operatore e le cifre vengono trimmati, quindi '>= 100' va bene. Fissare il separatore in un punto unico evita che un report si comporti diversamente a seconda di quale server l'ha generato, ed è il predefinito giusto per un formato di file

Le date sono la stessa trappola in un costume diverso. '>=2026-01-01' non è un numero, quindi viene scritto come testo, mentre Excel tiene le celle data come numeri seriali e le confronta numericamente. Passate il seriale. Un TDateTime di Delphi ha la stessa parte intera del seriale Excel del sistema 1900 per qualunque data dal 1 marzo 1900 in avanti. Un workbook passato al sistema di date 1904 conta dal 1 gennaio 1904, quindi i suoi seriali restano 1462 più in basso, e IXLSWorkbook.Date1904 dice che sistema usa il file. I dettagli di quell'offset sono in come i seriali data 1900 e 1904 interagiscono con i formati numerici

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

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

  // Date: confrontate col numero seriale che Excel memorizza nella cella
  Serial := Trunc(EncodeDate(2026, 1, 1));
  if Wb.Date1904 then
    Dec(Serial, 1462);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
    xlAnd, Unassigned);
end;

Un Variant numerico sarebbe più semplice della concatenazione di stringhe, ma un Variant numero significa sempre uguaglianza. Qualunque altro confronto deve viaggiare come operatore più testo, quindi il passo di formattazione non si può saltare

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 invertirli è facile e costoso. Scambiateli e un filtro a intervallo come almeno 100 e sotto 500 viene salvato come almeno 100 o sotto 500, che trova ogni numero e sembra che il filtro non sia mai stato applicato. HotXLS scrive 0 per xlAnd e 1 per xlOr, e quando ci sono criteri di uguaglianza imposta anche i bit fSimple1 e fSimple2 accanto a wJoin, che marcano un DOPER con segno 2

Le costanti pubbliche portano un pericolo di porting tutto loro. In HotXLS xlAnd vale 0 e xlOr vale 1, mentre l'automazione di Excel le numera 1 e 2. XlAutoFilterOperator è dichiarato come semplice Byte, quindi una macro VBA tradotta con numeri letterali compila senza avvertimenti. Un letterale 1 che significava AND sotto COM ora significa OR. Un letterale 2 che significava OR collide con xlBottom10Items, e poiché HotXLS tratta ogni valore di operatore diverso da xlOr come AND quando costruisce i due DOPER, quel criterio viene unito con AND. Entrambi i letterali si capovolgono. Le costanti nominali evitano tutta la questione:

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

with Sh.AutoFilterColumns.Find(3) do
begin
  // Controlli che aggiungete voi: confermate ciò che verrà scritto
  Assert(Operator = xlAnd);            // wJoin = 0 su disco
  Assert(Criteria2.DataType = 4);      // numero IEEE
  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 costanti scambiate allarghino 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 capovolte trasformano 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. Scriverli nell'ordine opposto fa sì che un filtro per TRUE metta 1 nel flag di errore, ed Excel legge il criterio come codice di errore. La parte cattiva è che un reader con lo stesso scambio fa il round trip dei propri file alla perfezione. Una libreria che legge solo ciò che ha scritto non prova nulla sulla conformità qui; aprire il file in Excel e controllare la finestra del filtro sì. HotXLS scrive bBoolErr per primo e fError a 0, quindi passare True produce un DOPER il cui DataType è 8 e il cui Value è 1

I test di vuoto non hanno bisogno di operando. La stringa '=' da sola produce un DOPER match-all-blanks ($0C), e '<>' da sola un DOPER match-all-non-blanks ($0E). Entrambi sono criteri completi, quindi il campo conta come attivo e FILTERMODE viene scritto

Gli operandi stringa sbattono contro un limite fisso del layout DOPER. Il campo cch è un byte, quindi un operando stringa tiene al massimo 255 caratteri, lo stesso tetto che la finestra del filtro di Excel impone. HotXLS rimuove prima l'operatore e poi tronca a 255 caratteri tutto ciò che è più lungo, così il byte di lunghezza non va in wrap e la coda del record non esce di passo. Un filtro su una colonna di descrizioni lunghe può quindi trovare su un prefisso di ciò che avete passato. Il maiuscolo/minuscolo viene lasciato stare: Excel confronta il testo del filtro senza riguardo per il case quando applica il filtro, e riscrivere il criterio memorizzato cambierebbe solo i dati dell'utente. In BIFF8 la coda memorizza ogni stringa come un flag di un byte seguito da code unit UTF-16, e la dimensione dichiarata del record deve contare esattamente quei byte, la stessa contabilità trattata in come le dichiarazioni di lunghezza dei record BIFF derivano in un writer XLS Delphi

Filtrare più colonne e controllare il file salvato

Ogni chiamata a ApplyAutoFilter ridefinisce l'intero intervallo del filtro, quindi dopo due chiamate sopravvive solo il criterio della seconda. Il metodo passa per il pubblico TXLSWorksheet.SetAutoFilter, che ripulisce ogni campo prima di ricostruire l'intervallo. Per una colonna è corretto, e la prima volta che filtrate due colonne è sorprendente. Per più colonne, chiamate ApplyAutoFilter una volta per stabilire intervallo e primo criterio, poi aggiungete il resto con AutoFilterColumns.SetFieldCriteria, che tocca un campo e lascia in pace intervallo e altri campi. Entrambe le vie ignorano un numero di campo fuori intervallo senza sollevare eccezioni, quindi il controllo che vale la pena avere rilegge i criteri dal file riaperto

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');
with Wb.Sheets[1].AutoFilterColumns do
begin
  // Controlli che aggiungete voi dopo aver riaperto il file
  Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6));   // testo 'North'
  Assert(Find(3).Criteria1.DataType = 4);                          // numero 100
  Assert(Find(4).Criteria1.DataType = 8);                          // Boolean TRUE
end;

Il record AUTOFILTER è una definizione memorizzata. Su un foglio XLS classico HotXLS scrive i criteri perché Excel li valuti quando il file si apre, e la valutazione a livello di riga sul server vive nel modello a oggetti XLSX, come mostrato in convalida dati, AutoFilter e tabelle in HotXLS per Delphi. Una volta che Excel nasconde le righe, qualunque totale sotto l'intervallo dipende da come SUBTOTAL e AGGREGATE trattano le righe nascoste e filtrate, e un filtro numerico memorizzato come testo lì compare come un totale di zero

La conversione dei criteri, il layout Bes e i bit di join descritti sopra sono ciò che HotXLS scrive in ogni workbook BIFF8 che salva, accanto al supporto XLSX nativo per Delphi e C++Builder. Edizioni, lista completa delle funzionalità e download di prova sono sulla pagina del componente foglio di calcolo HotXLS per Delphi