Motore di formule e funzioni personalizzate in HotXLS

Una libreria per fogli di calcolo che si limita a memorizzare stringhe di formula e una libreria con un motore di formule funzionante sono due prodotti diversi che sembrano identici fino al momento in cui a uno dei due chiedete un numero. Gran parte del codice Delphi per fogli di calcolo non nota mai lo scarto, perché Excel lo maschera: scrivete SUM(B2:B501) in una cella, salvate ed Excel ricalcola il totale nell'istante in cui una persona apre il file. Togliete la persona dal giro, fate passare la stessa cartella di lavoro in una pipeline server che esporta direttamente in CSV, e la differenza smette di essere accademica. Il CSV porta il testo letterale =SUM(B2:B501) dove doveva esserci un numero, perché in nessun momento qualcosa ha davvero valutato la formula

È la linea su cui HotXLS sta dal lato giusto. Tratta una formula come fanno i formati di file, come testo memorizzato più un risultato in cache facoltativo, quindi una semplice esportazione CSV riproduce la ricetta invece del piatto. Ma porta anche un motore di calcolo che potete chiamare direttamente, lo stesso motore in entrambe le facciate XLS e XLSX, più un gancio per risolvere nomi di funzione di cui il motore non ha mai sentito parlare. HotXLS è una libreria Object Pascal nativa che legge e scrive XLS e XLSX da Delphi e C++Builder senza automazione di Excel, e la sua metà dedicata al calcolo è quella che riporta le formule memorizzate a valori su richiesta

Le formule sono memorizzate, non valutate subito

Scrivere una formula in una cella non calcola nulla. Al momento del salvataggio la cartella di lavoro registra il testo della formula. Sul lato XLS registra anche i flag governati da RecalcOnSave, che vale True per impostazione predefinita e dice a Excel di ricalcolare all'apertura. Quel modello è corretto per i file destinati a Excel e sbagliato per le pipeline che consumano direttamente i valori delle celle, che si tratti di esportazione CSV, esportazione HTML o del vostro codice che rilegge le celle. Per quei casi valutate in modo esplicito con Calculate. Esiste in quattro punti di ingresso: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook e TXLSXWorksheet espongono tutti function Calculate(const Formula: WideString): Variant

// valutate nel processo, poi consegnate il valore invece della ricetta
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // ora il CSV porta il numero

L'espressione passata a Calculate è normale testo di formula Excel. Riferimenti fra fogli, nomi definiti e funzioni annidate si risolvono tutti contro la cartella di lavoro corrente in memoria, il che rende la chiamata utile ben oltre il rattoppo delle esportazioni CSV. Trattatela come un meccanismo di asserzione. Un generatore che ha appena scritto cinquecento righe di dettaglio può chiedere alla cartella di lavoro il proprio totale generale e confrontarlo con la cifra calcolata indipendentemente in Pascal, intercettando un errore di intervallo di una unità prima che lo faccia il revisore di un cliente

Inquadra anche la giusta strategia di test per l'output ricco di formule. Excel resta l'implementazione di riferimento del linguaggio delle formule, quindi per la manciata di formule che portano conseguenze di business tenete un file di riferimento approvato i cui valori attesi sono stati prodotti da Excel stesso, e fate valutare alla pipeline di build le formule della cartella generata con Calculate contro quei riferimenti. Le differenze emergono così come test falliti in Delphi invece che come discrepanze scoperte da un cliente che confronta due report

Aggiungere funzioni di business con OnUserFunction

Quando il motore incontra un nome di funzione che non riconosce, solleva un evento invece di fallire di netto. Assegnate OnUserFunction su una delle due classi di cartella di lavoro e potete risolvere voi stessi la chiamata:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arriva come array di Variant
    Handled := True;
  end;
end;

// collegamento e uso
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Tre dettagli meritano attenzione. Primo, impostate Handled := True solo quando avete davvero riconosciuto il nome. Lasciarlo a False permette al motore di proseguire con la sua normale gestione delle funzioni sconosciute, così un solo gestore può servire più cartelle di lavoro senza rivendicare tutto ciò che gli passa davanti. Secondo, confrontate i nomi senza distinzione fra maiuscole e minuscole con SameText, dato che gli autori delle formule scrivono discount( e DISCOUNT( indifferentemente. Terzo, gli argomenti arrivano già valutati: DISCOUNT(A1) vi consegna il valore di A1, non il riferimento, quindi una funzione non può sapere da dove vengano i suoi input. Quest'ultimo punto prepara il limite di cui parla la sezione seguente

Trattate il corpo del gestore con la stessa diffidenza di qualsiasi punto di ingresso esterno. L'array Args riflette qualunque cosa abbia scritto l'autore della formula, quindi convalidate numero e tipi degli argomenti prima di indicizzarlo, e decidete in anticipo cosa restituisce una chiamata non valida: un valore di errore Variant oppure un'eccezione sollevata. La scelta conta perché un'eccezione lanciata dentro il gestore si propaga fuori attraverso la chiamata a Calculate che ha innescato la valutazione. Questo è accettabile in un generatore strettamente controllato e sgarbato in un servizio che valuta cartelle di lavoro scritte dagli utenti, dove una sola formula sbagliata farebbe cadere la richiesta. In quel contesto, catturate dentro il gestore e restituite un valore sentinella che il flusso circostante possa riconoscere e registrare

Le funzioni che dipendono dalla posizione richiedono la variante Ex

Alcune funzioni dipendono legittimamente dal punto in cui vengono valutate. Un tasso che cambia per foglio, una ricerca relativa alla riga, un moltiplicatore per area geografica che si applica solo sui fogli regionali: nessuno di questi casi può essere risolto dai soli valori degli argomenti. L'evento semplice non riesce a esprimerlo, quindi il motore offre OnUserFunctionEx, identico tranne che per un parametro in più:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // la stessa formula produce un tasso diverso su ogni foglio regionale
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext porta SheetIndex, Row e Col della cella in valutazione. Se il risultato di una funzione dipende anche solo un poco dalla sua posizione, collegate l'evento Ex fin dall'inizio. Innestare il contesto in un gestore che trenta formule già richiamano è molto più scomodo che scegliere la firma giusta il primo giorno, e i due eventi sono per il resto così simili che c'è poco motivo di partire da quello più ristretto

Le funzioni personalizzate non viaggiano fino a Excel

Una funzione personalizzata vive interamente dentro il vostro processo. Il nome DISCOUNT significa qualcosa solo mentre girano il vostro codice Delphi e il suo gestore di eventi. Aprite il file salvato in Excel e DISCOUNT è solo un nome non riconosciuto; la cella mostra #NAME? a meno che sulla macchina dell'utente non esista per caso una funzione VBA o un componente aggiuntivo corrispondente. È il dato di progetto che separa una dimostrazione da un prodotto distribuibile, e impone una scelta da fare di proposito anziché da scoprire dopo

Decidete, cella per cella, quale dei due contratti state consegnando. Le celle che l'utente deve vedere ricalcolare dentro Excel vanno costruite con il vocabolario di funzioni di Excel e con nient'altro. Le celle la cui logica è proprietaria vanno valutate nel processo con Calculate e salvate come semplici valori, così che la funzione personalizzata si comporti da regola di calcolo interna anziché da contenuto del file. La modalità di guasto che genera in modo affidabile ticket di supporto è la via di mezzo: salvare una formula con funzione personalizzata e aspettarsi che Excel la onori

Il contratto a soli valori ha un vantaggio silenzioso: protegge la proprietà intellettuale. Una regola di prezzo valutata nel vostro processo Delphi e consegnata come numero non può essere ricostruita a ritroso dalla cartella di lavoro come può esserlo una formula visibile, e un utente non può romperla modificando una cella intermedia. Generatori di fatture, prospetti di provvigioni e listini appartengono quasi sempre a questa categoria. Il caso che ha davvero bisogno di formule vive è il modello interattivo di simulazione, dove ci si aspetta che il cliente cambi gli input e veda muoversi i totali, e quelli vanno costruiti con il vocabolario di Excel più i nomi definiti

Modalità di calcolo, iterazione e R1C1: le manopole della facciata XLS

La facciata XLS espone le impostazioni di calcolo a livello BIFF che Excel legge dal file. CalculationMode accetta xlCalcManual, xlCalcAutomatic (il valore predefinito) o xlCalcAutomaticExceptTables, e determina come Excel si comporta una volta aperto il file. Una cartella di lavoro modello con migliaia di formule è spesso più cortese consegnata in modalità manuale, così che sia il destinatario a decidere quando scatenare la tempesta di ricalcolo. EnableIteration (predefinito False), insieme a MaxIterations (predefinito 100) e MaxIterationChange (predefinito 0.001), sblocca i riferimenti circolari deliberati del tipo a convergenza iterativa che compaiono in alcuni modelli finanziari. ReferenceStyle commuta fra visualizzazione A1 e R1C1, e UseFullPrecision rispecchia l'opzione di Excel che usa la precisione come visualizzata

Queste proprietà vivono sulla facciata XLS perché corrispondono a record BIFF; quando generate .xlsx, progettate le formule in modo che non dipendano dalle impostazioni iterative, oppure calcolate in Delphi i valori convergenti e scrivete i risultati

Formule di matrice: il punto di ingresso pubblico è XLSX

Le formule di matrice legacy in stile CSE si creano tramite TXLSXRange.SetArrayFormula:

// una sola formula di matrice estesa su A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Il metodo equivalente esiste nella gerarchia di classi XLS ma sta in una sezione privata, quindi non esiste un modo supportato per creare nuove formule di matrice dentro i file .xls. Quelle già presenti nei file aperti sopravvivono intatte a un ciclo di andata e ritorno; quello che non potete fare è crearle. La regola che ne consegue è abbastanza semplice: quando la semantica di matrice fa parte del requisito, puntate su .xlsx. Se un deliverable .xls legacy ha davvero bisogno del comportamento di matrice, la via pragmatica è calcolare il risultato della matrice in Delphi e scrivere i singoli valori nelle celle

Due letture collegate su questo sito: nomi definiti e formule fra fogli tratta la risoluzione dei nomi che il motore esegue, e l'articolo sull'esportazione CSV e TSV dettaglia il comportamento di esportazione che rende necessario il calcolo esplicito. Il riferimento completo del motore, incluso l'insieme di funzioni supportate, è distribuito con HotXLS Delphi Component