HotXLS: data validation, AutoFilter, and worksheet tables

Tre funzionalità di HotXLS condividono un foglio di lavoro ma operano su oggetti completamente diversi, e i problemi iniziano quando presumi che facciano cose simili. La validazione dei dati collega una regola a un intervallo che vincola cosa un utente può digitarvi. Un AutoFilter collega una definizione di criteri memorizzata a una regione e cambia quali righe mostra un visualizzatore. Una tabella avvolge un intervallo in una struttura con nome e tipizzata con stile a bande. Una vincola l'input, una registra una vista, una impone uno schema. Nessuna delle tre sposta da sola un singolo valore di cella, e l'AutoFilter in particolare inganna le persone, perché la parola suggerisce un'azione mentre memorizza solo una definizione. Sapere quale oggetto tocca ciascuna chiamata, e quando l'effetto si materializza davvero, è ciò che separa una cartella di lavoro che si comporta allo stesso modo in Excel come nei tuoi test da una che diverge silenziosamente

AutoFilter memorizza una definizione, non ritaglia le righe

Un AutoFilter in un file salvato è un record di criteri. Il nascondimento delle righe avviene dopo, quando Excel apre la cartella di lavoro e valuta i criteri sui dati. HotXLS scrive quel record fedelmente e non ritaglia nulla: ogni riga che hai filtrato è ancora fisicamente presente nel file. Una pipeline che applica un filtro per scartare ordini respinti e poi rilegge la cartella di lavoro li vedrà tutti, respinti inclusi, e il codice è corretto secondo l'API mentre è sbagliato secondo il modello mentale dell'autore. Sul foglio di lavoro XLSX, SetAutoFilter dichiara la regione filtrata e AddAutoFilterColumn collega i criteri a una sua colonna. Quando il codice lato server ha bisogno del risultato effettivo, per un conteggio di righe in un riepilogo o per inoltrare solo le righe corrispondenti, la libreria valuta i criteri al posto tuo invece di fingere che il file sia cambiato:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Column id 3 = quarta colonna DENTRO l'intervallo del filtro (offset a base 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible ora corrisponde a ciò che Excel mostrerà dopo aver aperto il file

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible risponde riga per riga, e PreviewAutoFilterRows percorre l'intera regione tramite un callback quando ti serve l'insieme corrispondente in un unico passaggio. C'è un caso in cui nessuna delle due è la risposta giusta: se il requisito è che le righe escluse non debbano esistere affatto nel file, un taglio per la privacy piuttosto che una vista, elimina le righe direttamente. Un filtro è lo strumento sbagliato in quel caso, perché qualsiasi destinatario lo rimuove con un clic e i dati che intendevi nascondere tornano a schermo

L'id di colonna è un offset, non un numero di colonna

Il commento nello snippet sopra segnala la trappola che costa più tempo di debug in questa API. AddAutoFilterColumn identifica il suo bersaglio tramite la posizione a base 0 all'interno dell'intervallo del filtro, non tramite la colonna del foglio di lavoro. Per un filtro su A1:E500 i due sistemi di numerazione differiscono per l'appunto di uno, il che è esattamente il tipo di quasi-errore che sopravvive a un test rapido e si rompe nel momento in cui un collega filtra una colonna diversa. Per un filtro che inizia alla colonna C, l'id 0 significa colonna C, e la discrepanza diventa evidente in fretta. Quando l'intervallo del filtro viene calcolato a runtime, deriva l'id di colonna dalla stessa variabile che ha costruito la stringa dell'intervallo, mai da una costante di colonna del foglio di lavoro. Ogni colonna accetta una seconda condizione tramite l'overload che prende due operatori, due criteri e un connettore and/or, che rispecchia la finestra di dialogo del filtro personalizzato di Excel. La facciata XLS copre lo stesso terreno con SetAutoFilter più ApplyAutoFilter, i cui parametri di criteri e operatore seguono le convenzioni più vecchie in stile COM e numerano il campo a partire da 1. Cambiare facciata significa cambiare base di indicizzazione, quindi il punto di chiamata merita un commento che dica quale delle due è in gioco

Le regole di validazione sono il contratto sotto cui editano i tuoi utenti

Delle tre funzionalità, la validazione è l'unica che vincola attivamente l'input futuro, e merita la maggior attenzione progettuale nelle cartelle di lavoro che escono per essere compilate e tornano per l'elaborazione. La variante a elenco copre la maggior parte di quel lavoro:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Quantità: numeri interi, zero o più
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Oltre a elenchi e numeri interi, la stessa famiglia copre decimali, date, orari, lunghezza del testo e formule libere tramite AddCustomValidation, e la generica AddDataValidation espone l'intera matrice di tipo e operatore per generatori di regole guidati da configurazione. Lo stile di errore conta più di quanto suggerisca il suo nome. xlsxDvErrStop rifiuta del tutto l'input non valido; gli stili di avviso e informazione lasciano passare il valore dopo un singolo clic. Scegli per colonna in base al fatto che il codice che rilegge la cartella di lavoro possa tollerare un valore fuori dalla regola. Due limiti vanno inclusi nel testo del prompt o nel README che spedisci con il file. La validazione in Excel protegge la digitazione, ma incollare un blocco sopra un intervallo validato aggira la regola, quindi qualsiasi codice che rilegge i dati deve validare di nuovo invece di fidarsi delle celle. E una regola copre l'intervallo letterale che le hai passato, il che significa che collegare la validazione prima di conoscere il conteggio finale delle righe lascia la coda aggiunta senza protezione. Scrivi prima i dati, poi dimensiona le regole sull'estensione effettiva

La facciata legacy offre le stesse famiglie di regole con una differenza ergonomica. I costruttori lato XLS, ossia AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation e AddCustomValidation, restituiscono direttamente l'oggetto TDataValidation invece di un indice, così la configurazione di prompt ed errore si concatena a partire dal riferimento restituito invece che tramite una ricerca. L'enumerazione degli operatori (xlsDvBetween, xlsDvGreaterThan e gli altri) rispecchia l'insieme XLSX, quindi il codice che costruisce regole si porta tra le facciate a parte questa differenza di stile nel valore restituito. Il testo del prompt in sé merita tanta cura quanto la regola. Un menu a discesa che rifiuta l'input con una casella di errore vuota insegna agli utenti a scrivere una email all'IT; uno che nomina gli stati validi insegna loro a correggere la cella e andare avanti

Un'inversione di polarità che la libreria assorbe per te

Chiunque abbia letto a mano l'XML di validazione OOXML ha incontrato l'attributo invertito showDropDown: in ISO/IEC 29500 un valore true significa "sopprimi la freccia del menu a discesa", l'opposto di quanto suggerisce il nome. HotXLS inverte questo internamente, così la proprietà ShowDropDown su una regola di validazione significa esattamente ciò che dice, con true che mostra il menu a discesa. L'unico modo per scottarsi è mescolare livelli di verità, impostando la proprietà da codice mentre un collega verifica l'XML salvato e "corregge" l'attributo che a lui sembra al contrario. Decidi se la proprietà o l'XML grezzo sia autorevole per gli strumenti di revisione, e annota l'inversione dove vive quella decisione

Le tabelle danno a un intervallo uno schema e un nome

Una tabella di foglio di lavoro, il ListObject nel linguaggio di Excel, avvolge un intervallo con un nome, colonne tipizzate, stile a bande e supporto ai riferimenti strutturati. È la funzionalità che fa sembrare finita una cartella di lavoro generata una volta che gli utenti iniziano a ordinarla ed estenderla. La creazione è simmetrica tra le facciate, con AddTable che accetta un nome, un intervallo e un elenco di colonne:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Sul lato XLSX l'oggetto tabella risultante espone StyleName (la famiglia integrata TableStyleMedium2 e le sue sorelle), interruttori per le righe a bande e un flag per la riga dei totali, così applicare lo stile aziendale è un'assegnazione di proprietà invece di un passaggio di formattazione manuale. Nei file .xls legacy la stessa chiamata scrive i record di tabella BIFF8, e la facciata offre anche AddPivotTable per viste di riepilogo costruite da campi di riga, colonna e dati, un promemoria che le "tabelle" nel formato più vecchio arrivano più lontano del ListObject OOXML. Dai nomi alle tabelle come daresti nomi alle viste di un database. Il codice a valle che legge Orders[Amount] tramite riferimento strutturato sopravvive al riordino delle colonne che rompe il codice posizionale

Due convenzioni risparmiano pulizia in seguito. Excel richiede che i nomi delle tabelle siano univoci nell'intera cartella di lavoro, quindi un generatore che emette un foglio per regione ha bisogno di uno schema come Orders_EMEA invece di riusare Orders. Un duplicato non fallisce al momento della scrittura; emerge come una finestra di dialogo di riparazione quando l'utente apre il file, che è il posto peggiore per scoprirlo. L'altra convenzione riguarda la riga dei totali: quando abilitata, si trova subito sotto l'intervallo dati, quindi qualsiasi codice che in seguito aggiunge righe con "ultima riga usata più uno" scrive nella banda dei totali invece che dopo di essa. Tieni traccia dell'estensione dei dati separatamente dall'estensione della tabella e le aggiunte atterreranno dove ti aspetti

Le tre funzionalità si compongono naturalmente nei deliverable per l'inserimento dati. Una tabella definisce la regione modificabile, la validazione vincola le colonne in cui gli utenti digitano, e un filtro preimpostato risparmia al destinatario i primi clic. C'è un argomento ragionevole per spedire un filtro già applicato così che la cartella di lavoro si apra focalizzata sulle righe che contano, purché tu ricordi che le righe escluse sono ancora nel file e un destinatario curioso può rivelarle. Portare in modo efficiente i risultati di una query nel foglio, la metà a monte di questa pipeline, è trattato in l'esportazione di risultati da database a Excel da Delphi, e le cartelle di lavoro dove le formule riepilogano i dati validati traggono beneficio dai nomi definiti per riferimenti stabili tra fogli

Validazione, filtri e tabelle sono la differenza tra spedire una griglia di valori e spedire una piccola applicazione. Il riferimento completo per regole, filtri e tabelle si trova sulla pagina prodotto HotXLS Delphi Component