Eseguire AutoFilter Excel senza Excel in Delphi: HotXLS

HotXLS valuta i criteri AutoFilter già memorizzati in un file XLSX senza avviare Excel. Dalla v2.384.136, TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter e AutoFilterRowVisible eseguono elenchi di valori, gruppi di date, filtri Top/Bottom-n e percentuale, filtri per data relativa e media, filtri per colore di riempimento e font e filtri per icone della formattazione condizionale, sotto una data di riferimento congelata e limiti espliciti di risorse

Il grilletto usuale è una cartella di lavoro che una persona ha filtrato in Excel e un server che deve agire di conseguenza. Qualcuno imposta "Top 10 by revenue", "This month" sulla data d'ordine e un riempimento rosso sulle righe scadute, salva, e carica il file. Il vostro servizio Delphi ora ha bisogno dell'insieme di righe che Excel mostrerebbe: per contarle, esportarle, o riscrivere il file con le righe giuste nascoste. Il file contiene solo la definizione. Prima di questa release HotXLS sapeva valutare i criteri custom ordinari, ma i tipi avanzati restavano nell'XML memorizzato senza nulla che li eseguisse, così la risposta onesta era "apritelo in Excel"

Cosa esegue davvero HotXLS da un AutoFilter salvato?

HotXLS esegue un tipo di filtro per colonna, preso dall'elemento filterColumn che Excel ha scritto, e combina le colonne con AND, esattamente come i menu a tendina di Excel. Gli elementi vengono dal gruppo AutoFilter in ECMA-376 Part 1 §18.3.2, e ciascuno mappa a un percorso di valutazione distinto:

• filters con figli filter e dateGroupItem: un elenco di valori spuntati, una voce vuota facoltativa e gruppi di date del calendario dall'anno al secondo, in OR tra loro
• customFilters: uno o due criteri di confronto uniti da AND o OR, il percorso che esisteva già
• top10: Top o Bottom di n elementi, o n percento, con val limitato a 1-500 elementi o 1-100 percento
• dynamicFilter: today, yesterday, tomorrow, this/last/next week, month, quarter and year, year to date, M1-M12, Q1-Q4, above average and below average
• colorFilter e iconFilter: colore effettivo di riempimento o font risolto attraverso lo stile differenziale, e icone della formattazione condizionale per set di icone e icon id in base zero

Tutto ciò che sta fuori da quell'elenco fallisce con EXLSXAutoFilterUnsupported invece di produrre un insieme di righe indovinato. Ciò include i tipi above e below standard deviation di ST_DynamicFilterType, i valori calendarType non gregoriani, gli elementi di estensione sconosciuti, i riferimenti dxfId non risolti e una colonna che per qualche motivo porta due tipi di filtro insieme. Un motore di filtri che restituisce silenziosamente righe "quasi giuste" è peggiore di uno che rifiuta, perché nessuno se ne accorge finché un report non esce con i clienti sbagliati dentro

Prima preview, poi apply

HotXLS divide il lavoro in una preview di sola lettura e un apply transazionale, ed entrambi prendono lo stesso record TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows restituisce il numero di righe dati combacianti e non tocca mai la visibilità delle righe. ApplyAutoFilter(AResetVisible, Options) prepara prima ogni decisione di riga e solo dopo pubblica il nuovo insieme di righe nascoste in un solo passo, così un'eccezione durante la preparazione lascia il foglio esattamente com'era

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Options: TXLSXAutoFilterExecutionOptions;
  Matching: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders-filtered-by-sales.xlsx');
    Sheet := Book.Sheets[1];   // l'indicizzatore Sheets è in base 1

    // Partite sempre dai valori predefiniti, poi cambiate i singoli campi
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // solo conteggio
    WriteLn('Rows Excel would show: ', Matching);

    // True: le righe combacianti diventano visibili anche se erano nascoste prima
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Inizializzate il record con XLSDefaultAutoFilterExecutionOptions ogni volta. La funzione azzera il record e poi imposta ogni limite, e il contesto di esecuzione rifiuta con EXLSXAutoFilterLimit un record i cui limiti non sono tutti positivi. Un record dichiarato sullo stack e riempito campo per campo porterà di solito spazzatura o zeri nei campi dimenticati. Gli overload senza parametri (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) usano gli stessi valori predefiniti, con la data corrente come riferimento

Il flag AResetVisible decide cosa succede alle righe che passano il filtro ma erano già nascoste. True rende visibile ogni riga combaciante, che è ciò che Excel fa quando riapplicate. False conserva il nascondimento manuale precedente sulle righe combacianti e aggiunge solo il nascondimento per le righe che falliscono. Le righe fuori dall'intervallo AutoFilter conservano il loro stato in entrambi i casi. Se la cartella di lavoro è cambiata tra preparazione e pubblicazione, ApplyAutoFilter solleva un'eccezione invece di pubblicare decisioni calcolate su dati stanti

Perché gli elenchi di valori ignorano asterischi e punti di domanda?

Le voci di un elenco di valori in un elemento filters sono token di visualizzazione letterali, quindi HotXLS confronta * e ? come caratteri ordinari. Una voce spuntata A* nel menu a tendina significa che la cella mostra esattamente A*, non "inizia con A". I caratteri jolly appartengono a customFilters, dove "begins with" e "contains" sono davvero pattern, e quelli conservano le regole separate trattate in come HotXLS confronta i criteri jolly di Excel e gli escape con tilde. Confondere le due semantiche è il modo più rapido per filtrare una colonna di codici articolo in un pasticcio

Il confronto è contro il valore mostrato, il che solleva la domanda di quale visualizzazione. HotXLS formatta la cella con il proprio formato numerico di base, overlay di stile memorizzati compresi, e ignora deliberatamente un formato numerico che la formattazione condizionale applica sopra. I controlli nativi di Excel nel fixture di HotXLS mostrano perché il token conta: l'1 numerico e il testo "1" combaciano entrambi con una selezione di 1, un 1 numerico formattato come 0.0 combacia invece con 1.0 insieme al testo "1.0", e il testo "01" resta distinto da entrambi. Il testo dall'aspetto di data non viene mai analizzato come data, quindi una cella di testo che contiene "2026-10-19" non cadrà in un gruppo di date

Come decidono i filtri Top 10, media e date dinamiche?

HotXLS calcola le soglie di classifica e media una volta sola sull'intera colonna sorgente, non sulle righe che un altro filtro ha lasciato visibili. Solo i numeri finiti entrano nella popolazione, il che significa che il testo dall'aspetto numerico come "100" è escluso, e la popolazione viene raccolta prima che qualunque decisione di riga sia presa. Nella cartella di controllo nativa un filtro sopra media mantiene una media di 27 su tutta la colonna anche dopo che una seconda colonna nasconde il valore 50; non commuta sulla media di 21,25 delle righe rimanenti

I pari sono inclusi alla soglia, come in Excel. Un filtro Top 3 su valori con due voci pari a 20 seleziona quattro righe. La modalità percentuale prende la quota troncata della popolazione numerica, con un minimo di un elemento, così il Top 50 percento di cinque valori seleziona due valori con una soglia di 30. Le varianti Bottom specchiano il confronto. Questi numeri vengono dagli insiemi di righe di Excel stesso registrati nei fixture di test di HotXLS, non dalla lettura della specifica, che su molti di loro tace

Le date dinamiche richiedono una data di riferimento congelata

TXLSXAutoFilterExecutionOptions.ReferenceDate congela il significato di "today" per un'intera operazione. I filtri relativi come questa settimana o il trimestre scorso, e le regole di data della formattazione condizionale che alimentano i filtri colore, vengono tutti valutati contro quella sola data, così una preview alle 23:59:59 e un apply un secondo dopo concordano. Un valore zero seleziona la data corrente una volta all'avvio dell'operazione; i valori seriali rispettano il sistema di date 1900 o 1904 della cartella di lavoro, compreso il seriale fittizio 60 di Excel nel sistema 1900 per i filtri M2 e Q1

Le finestre sono intervalli di calendario semiaperti: questa settimana va dalla domenica alla domenica successiva, questo trimestre dal primo giorno del trimestre al primo giorno del successivo, e year to date dal 1 gennaio fino alla fine del giorno di riferimento. Bloccate ReferenceDate esplicitamente in tutto ciò che gira in batch o in un test. Un job notturno che attraversa la mezzanotte, o uno unit test scritto a ottobre che deve passare a gennaio, produrrebbero altrimenti insiemi di righe diversi senza motivo visibile

Le celle formula vengono filtrate sui valori in cache. Il motore di filtri non ricalcola silenziosamente la cartella di lavoro, quindi un file salvato da un writer che non ha lasciato risultati in cache richiede un passaggio di calcolo esplicito prima che il filtro possa vedere numeri o date sensati

Quali colori vede un filtro colore?

Un filtro colore di HotXLS vede il colore effettivo dalla formattazione diretta della cella più qualunque override di formattazione condizionale combaciante, e nulla di ciò che viene solo da uno stile tabella. Excel si comporta allo stesso modo: una riga a fasce dipinta azzurra da uno stile tabella integrato non è "azzurra" per il menu a tendina del filtro. Nei controlli nativi, una cella dipinta di rosso dalla formattazione condizionale viene selezionata da un filtro riempimento rosso, mentre una cella formattata rossa direttamente ma scavalcata da una regola gialla attiva è esclusa

Tre rifiniture sono arrivate nelle release successive. La versione 2.384.138 tratta i riempimenti a gradiente lineare e path come distinti sia dai colori pieni sia dal nessun riempimento, così un gradiente non combacia mai con un criterio di colore pieno nemmeno quando uno dei suoi stop ha proprio quel colore. La versione 2.384.140 ha aggiunto il filtro sul colore del font per il rich text, dove una cella combacia se qualunque run non vuoto porta il RGB richiesto, il colore automatico o ereditato. La versione 2.384.150 ha completato le decisioni condizionali sul rich text, che si sono rivelate la parte strana

Excel nativo può riusare una decisione di colore tra una cella rich text e una cella semplice con lo stesso testo, lo stesso stile base e lo stesso percorso di regola condizionale combaciante, così l'insieme di righe visibili può dipendere dall'ordine di quelle celle. HotXLS lo riproduce preparando l'intero intervallo dati in ordine di riga crescente, anche quando chiedete a AutoFilterRowVisible di una sola riga. Ispezionare la riga 40 costa quindi un passaggio sull'intervallo, ma ispezione di riga, preview e apply restituiscono sempre la stessa risposta per la stessa riga

Ogni risorsa ha un limite, e cosa intercettare

HotXLS limita ogni tipo di lavoro in un'operazione AutoFilter con il proprio campo in TXLSXAutoFilterExecutionOptions, perché una cartella di lavoro filtrata proveniente da un form di upload è input non fidato. I valori predefiniti sono 1048576 per MaxXmlCharacters, 16384 per MaxXmlNodes, 4096 per MaxFilterItems, 4000000 per MaxExaminedCells, 1000000 ciascuno per MaxEffectiveFormatCells e MaxPopulationValues, 67108864 byte per MaxContextBytes e 16000000 per MaxConditionalSteps. MaxContextBytes misura stato logico trattenuto e envelope temporanee riservate, non memoria di processo

type
  TFilterJob = class
  public
    Deadline: TDateTime;
    VisibleRows: TList<Integer>;
    procedure HandleRow(Sender: TObject; SheetIndex, Row: Integer;
      Visible: Boolean; var Cancel: Boolean);
    procedure HandleWork(Sender: TObject; ACompleted: Int64;
      var ACancel: Boolean);
  end;

procedure TFilterJob.HandleRow(Sender: TObject; SheetIndex, Row: Integer;
  Visible: Boolean; var Cancel: Boolean);
begin
  // Chiamata per ogni riga dati; Visible è la decisione del filtro
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted conta le celle esaminate e può restare fermo durante la formattazione
  ACancel := Now > Deadline;
end;

La callback di riga di PreviewAutoFilterRows riceve ogni riga dati con la propria decisione, non solo quelle combacianti, e la funzione restituisce comunque il conteggio delle combacianti. Impostare il parametro Cancel ferma in anticipo la visita senza eccezioni. OnWork è l'hook di cancellazione: impostare ACancel a True solleva EXLSXAutoFilterCancelled. Mentre l'operazione prepara le decisioni detiene un lease di lettura sulla cartella di lavoro, quindi nessuna delle due callback può modificarla

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // tetto più stretto per i file caricati
Options.OnWork := Job.HandleWork;
try
  Sheet.PreviewAutoFilterRows(Job.HandleRow, Options);
  Sheet.ApplyAutoFilter(True, Options);
except
  on E: EXLSXAutoFilterUnsupported do
    Log('Filter needs Excel: ' + E.Message);
  on E: EXLSXAutoFilterLimit do
    Log('Filter exceeded a budget: ' + E.Message);
  on E: EXLSXAutoFilterCancelled do
    Log('Filter cancelled at deadline');
  on E: EXLSXAutoFilterExecution do
    Log('Filter state invalid: ' + E.Message);
end;

Tutte e tre le eccezioni specifiche derivano da EXLSXAutoFilterExecution, che a sua volta copre stato non valido come una data di riferimento fuori dal calendario supportato o una cartella di lavoro cambiata prima della pubblicazione, quindi tenete la classe base per ultima. In ogni caso la visibilità delle righe del foglio resta invariata, il che rende "log, salta, segnala il file per revisione manuale" una politica predefinita sicura

Dove sono i confini

La copertura è ampia, ma qualcuno dei bordi vale la pena conoscerlo prima di promettere parità a un cliente:

• I filtri dinamici per deviazione standard vengono rifiutati finché non esiste una policy verificata, anche se l'enumerazione ha valori per loro
• Le finestre settimanali cominciano la domenica; le cartelle di lavoro che fanno affidamento su un diverso inizio settimana non sono modellate
• I risultati delle formula vengono dalla cache memorizzata soltanto, senza ricalcolo dentro la chiamata del filtro
• I colori da stile tabella non combaciano mai con un filtro colore, di proposito e in accordo con Excel
• Una struttura rich text sconosciuta o metadati colore sconosciuti falliscono esplicitamente, mentre le proprietà note non relative al colore come la famiglia del font non bloccano una combacianza di colore
• La facciata XLS e il suo storage BIFF8 sono un percorso separato, trattato in come HotXLS scrive i criteri DOPER dell'AutoFilter BIFF8

Se state ancora decidendo come definizioni AutoFilter, validazione dei dati e tabelle si rapportano in una cartella di lavoro che generate voi, partite da validazione dei dati, AutoFilter e tabelle HotXLS in Delphi, che copre SetAutoFilter, gli id di colonna e quando un filtro ha davvero effetto

FAQ

PreviewAutoFilterRows cambia quali righe sono nascoste?

No. PreviewAutoFilterRows si limita a valutare i criteri memorizzati e riporta le decisioni attraverso il valore di ritorno e la callback facoltativa. Solo ApplyAutoFilter pubblica un nuovo insieme di righe nascoste, e lo fa dopo che tutte le decisioni sono state preparate, così un fallimento lascia intatta la visibilità originale

Perché un filtro per data relativa dà righe diverse in giorni diversi?

Filtri come today, questa settimana o il mese scorso sono definiti rispetto alla data corrente, esattamente come in Excel. Impostate ReferenceDate in TXLSXAutoFilterExecutionOptions a una data fissa quando vi servono risultati ripetibili; un valore zero significa la data di avvio dell'operazione

HotXLS può filtrare sui colori che vengono da uno stile tabella?

No, e nemmeno Excel può. I filtri colore di HotXLS combaciano solo con la formattazione diretta della cella e con gli override di formattazione condizionale supportati. I colori dipinti soltanto da uno stile tabella integrato o custom non partecipano, così il risultato di un filtro colore concorda con ciò che il menu a tendina di Excel selezionerebbe

L'esecuzione avanzata dell'AutoFilter arriva con l'API del foglio di lavoro XLSX di HotXLS, il componente spreadsheet Delphi per leggere e scrivere file Excel senza Excel, dalla v2.384.136 in poi, con gestione del colore per gradienti, rich text e rich text condizionale aggiunta fino alla v2.384.150