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 bytecch, con i caratteri spinti nella coda del record$08è un valore Bes, un Boolean o un codice di errore compresso in due byte$0Ce$0Enon 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
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;
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;
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