Come inserire formule e funzioni Excel in JavaScript (React)

Un foglio di calcolo generato pieno di numeri precalcolati è un'istantanea. Sembra corretto nel momento in cui viene prodotto e inizia a invecchiare immediatamente: i dati che ne stanno alla base evolvono, i numeri al suo interno no, e una volta che il file ha lasciato la tua applicazione nessuno può dire quali celle sia consentito modificare. Una cartella di lavoro che invece contiene le sue formule rimane un documento vivo — modifica un input e i totali si aggiornano.

Spire.XLS for JavaScript è un motore per fogli di calcolo compilato in WebAssembly, così un'app React può creare cartelle di lavoro nel browser senza un server. I file vengono letti e scritti tramite un file system virtuale (VFS), e le formule vengono scritte allo stesso modo dei valori: tramite l'oggetto Range di una cella. Cambia solo il nome della proprietà.

Quest'ultimo punto è l'intero trucco. La domanda interessante non è come scrivere una formula, ma con quale delle quattro proprietà disponibili scriverla, perché tre di esse memorizzeranno silenziosamente la tua formula come semplice testo.

Per la configurazione del progetto, vedi Integrare Spire.XLS for JavaScript in un progetto React. Gli esempi seguenti presuppongono che il pacchetto sia installato e che il modulo WebAssembly sia stato inizializzato.

Perché le cartelle di lavoro generate dovrebbero contenere formule

Generare un file con le risposte già inserite è più facile da scrivere e peggiore da ricevere. I casi in cui si rompe davvero:

• Modelli con segnaposto. Ci si aspetta che il destinatario sostituisca gli input. Se i totali sono codificati in modo fisso, sostituire un input lascia i totali errati e nulla lo avverte.
• Modelli consegnati a un analista. Vorrà testare un'ipotesi diversa. Un foglio che non può essere ricalcolato è un foglio che deve ricostruire da zero.
• Report che devono essere tracciabili. Un numero senza una regola visibile alle spalle non può essere verificato. Una formula sì.
• Fogli di lavoro che alimentano altri fogli di lavoro. Altre celle fanno riferimento a queste; se il valore non viene mai ricalcolato, tutto ciò che sta a valle eredita l'obsolescenza.

In tutti e quattro i casi, la formula è il punto del file. I valori sono un sottoprodotto.

Prerequisiti

Ti serve un progetto React con Spire.XLS for JavaScript installato e il modulo WebAssembly inizializzato, raggiungibile all'indirizzo window.wasmModule.spirexls. L'esempio seguente carica anche un font nel VFS prima di formattare qualsiasi testo, e salva con il flag della versione Excel 2010 in modo che l'output si apra correttamente sia in Excel attuale sia nelle versioni precedenti.

Scegliere la proprietà che scrive una formula

Ogni cella in cui scrivi è un oggetto Range, e questo espone quattro proprietà che accettano qualcosa. Non sono intercambiabili:

Text è quella con cui bisogna stare attenti, e vale la pena capire perché prima del codice seguente. Assegna =SUM(B1:F1) a Text e la cella memorizzerà quei caratteri — visualizzerà la formula per sempre, perché nulla la valuterà mai.

Questo comportamento non è un difetto. È esattamente ciò che l'esempio utilizza deliberatamente, in modo che ogni riga possa mostrare la formula a sinistra e il suo risultato a destra: la cella di sinistra usa Text perché è pensata per visualizzare la regola, e la cella di destra usa Formula perché è pensata per applicarla.

Scrivere formule nelle celle

Il flusso è breve:

1. Crea un oggetto Workbook.
2. Ottieni un foglio di lavoro con il metodo Workbook.Worksheets.get().
3. Scrivi i dati di input nelle celle e imposta la formattazione delle celle.
4. Assegna le formule alle celle che devono calcolare, tramite la proprietà Range.Formula.
5. Salva la cartella di lavoro con Workbook.SaveToFile().

L'esempio crea un piccolo foglio con una riga di numeri di input, poi scrive cinque formule sotto di essa — un'espressione aritmetica, una funzione di data, una funzione trigonometrica, una media e una somma:

function App() {
  const insertFormulasAndFunctions = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a Workbook object
    const workbook = new xlsModule.Workbook();

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Declare two variables: currentRow and currentFormula
    let currentRow = 1;
    let currentFormula = "";

    // Set the column width
    sheet.SetColumnWidth(1, 32);
    sheet.SetColumnWidth(2, 16);

    // Write data into cells
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Test Data";
    sheet.Range.get({ row: currentRow, column: 2 }).NumberValue = 1;
    sheet.Range.get({ row: currentRow, column: 3 }).NumberValue = 2;
    sheet.Range.get({ row: currentRow, column: 4 }).NumberValue = 3;
    sheet.Range.get({ row: currentRow, column: 5 }).NumberValue = 4;
    sheet.Range.get({ row: currentRow, column: 6 }).NumberValue = 5;
    currentRow += 2;
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Formula or Function";
    sheet.Range.get({ row: currentRow, column: 2 }).Value = "Result";

    // Set the cell formatting
    let range = sheet.Range.get({ row: currentRow, column: 1, lastRow: currentRow, lastColumn: 2 });
    range.Style.Font.FontName = "Arial";
    range.Style.KnownColor = xlsModule.ExcelColors.LightGreen;
    range.Style.FillPattern = xlsModule.ExcelPatternType.Solid;
    range.Style.Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Medium;
    range.Style.Font.IsBold = true;

    // Mathematical operation
    currentFormula = "=1/2+3*4";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Date function
    currentFormula = "=TODAY()";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Style.NumberFormat = "YYYY/MM/DD";

    // Trigonometric function
    currentFormula = "=SIN(PI()/6)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Average function
    currentFormula = "=AVERAGE(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Sum function
    currentFormula = "=SUM(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Save the workbook
    const outputFileName = 'InsertFormulasAndFunctions_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release resources
    workbook.Dispose();

    // Read the converted file from the VFS and trigger a download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Insert Formulas and Functions</h1>
      <button onClick={insertFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Inserisci formule e risultati di funzioni nei fogli di lavoro Excel

Nota la chiamata di formattazione prima delle formule. Range.get() accetta lastRow e lastColumn, quindi un blocco di intestazione può essere formattato con una sola chiamata invece che cella per cella — lo stesso oggetto che usi per scrivere una formula porta anche lo stile.

Funzioni per categoria

Le cinque formule dell'esempio non sono cinque tecniche diverse. Sono un'unica tecnica applicata a cinque tipi di espressione:

Non esiste un'API separata per le "funzioni". Una funzione è una formula — Range.Formula riceve la stringa, e il motore decide cosa farne. Ecco perché il catalogo delle cose che puoi scrivere è ampio quanto l'elenco di funzioni del motore per fogli di calcolo, senza alcun wrapper da mantenere per funzione.

Mostrare il testo della formula accanto al suo risultato

Una delle abitudini più utili in un foglio di lavoro generato è mantenere la regola visibile accanto al suo output. L'esempio lo fa inserendo la stringa della formula nella colonna A come testo letterale e il valore valutato nella colonna B:

// Column A displays the rule; column B applies it
sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

Assegnare prima "@" come formato numerico è ciò che impedisce alla colonna delle etichette di provare a interpretare la stringa — la cella viene dichiarata testo prima che vi si scriva qualcosa. La colonna dei risultati non necessita di tale accortezza, ma potrebbe aver bisogno di un proprio formato di visualizzazione: la riga della data imposta .Style.NumberFormat = "YYYY/MM/DD", senza il quale il valore viene visualizzato come numero seriale anziché come data.

Un foglio che porta con sé le proprie regole in questo modo sopravvive a ogni passaggio, perché le etichette sono testo semplice che nessun motore toccherà.

Una formula su un intervallo

I fogli di lavoro reali raramente necessitano di una sola formula; necessitano della stessa regola lungo una colonna. Poiché sei tu a costruire la stringa, controlli i riferimenti esplicitamente:

// One rule, many rows: the row number in the reference shifts with each cell
for (let row = 2; row <= 11; row += 1) {
  sheet.Range.get({ row: row, column: 3 }).Formula = `=A${row}*B${row}`;
}

È lo stesso comportamento di riferimento relativo che otterresti trascinando una formula verso il basso in Excel, scritto per esteso. Se invece la regola dovesse puntare sempre a un unico input fisso, fissalo — $A$1 non si sposta quando la formula si sposta, mentre A1 sì.

Sintassi delle formule che trae in inganno

• Il segno di uguale iniziale. Una stringa di formula senza = non è una formula. Verrà memorizzata come testo e mai valutata.
• Riferimenti relativi e assoluti. A1 si sposta; $A$1 no. Scegli deliberatamente quando generi formule in un ciclo.
• Riferimenti tra fogli. Nomina il foglio all'interno della stringa — Sheet2!A1. Se il nome del foglio contiene spazi, racchiudilo tra virgolette: 'Q1 Sales'!A1.
• Separatori di argomenti tra impostazioni locali. La stringa viene memorizzata così come la scrivi. Mantieni la forma separata da virgole usata sopra se il file verrà aperto in una combinazione di impostazioni locali, dove alcune visualizzano invece il punto e virgola.
• Funzioni volatili. TODAY() e NOW() cambiano ogni volta che la cartella di lavoro viene ricalcolata, quindi un valore letto in seguito non corrisponderà a quello che hai visto. Quel divario tra una regola e il suo ultimo valore calcolato vale la pena di essere conosciuto di per sé — è ciò di cui si occupa Reading and Extracting Excel Formulas in JavaScript (React).

Problemi comuni

La cella mostra la formula invece di un risultato. È stata scritta tramite Text anziché Formula. RIassegnala con Formula — la cella necessita della regola, non dei caratteri.

Una data appare come un numero a cinque cifre. È il valore seriale senza alcun formato data applicato. Imposta .Style.NumberFormat sulla cella, come fa l'esempio per la riga TODAY().

La formattazione finisce su celle che non intendevo toccare. Controlla l'intervallo che hai passato a Range.get(). Fornire lastRow e lastColumn applica la modifica a un blocco, il che è comodo per un'intestazione e facile da delimitare in modo errato.

La formula è memorizzata ma la cella appare vuota quando viene riletta. I risultati compaiono una volta che la cartella di lavoro è stata calcolata. Salva dopo aver scritto le formule in modo che i valori calcolati viaggino con il file.

Domande frequenti

Ho bisogno di Excel o Office installato per scrivere formule?

No. Il motore per fogli di calcolo è fornito con il pacchetto e viene eseguito come WebAssembly nel browser. Nulla viene automatizzato e nulla è richiesto sulla macchina dell'utente.

Una formula può fare riferimento a un foglio di lavoro diverso nella stessa cartella di lavoro?

Sì, e la scrivi esattamente come faresti in Excel — includi il nome del foglio nella stringa della formula.

Posso mescolare formule e valori semplici in un unico foglio?

Sì, e di solito lo farai. Le proprietà sono indipendenti: alcune celle ricevono dati tramite NumberValue o Value, altre ricevono regole tramite Formula.

Cosa succede ai risultati quando il destinatario apre il file?

Le formule vengono memorizzate, ed Excel ricalcola all'apertura della cartella di lavoro. Questo è il punto di scrivere regole anziché risultati — il file rimane corretto anche se gli input vengono modificati in seguito.

La scrittura di formule richiede un backend?

No. La cartella di lavoro viene creata nel browser e restituita come byte che trasformi in un Blob per il download. Nulla viene caricato.

Vedi anche

• Reading and Extracting Excel Formulas in JavaScript (React)
• Integrare Spire.XLS for JavaScript in un progetto React
• Creare intervalli denominati in React con JavaScript
• Pagina del prodotto Spire.XLS for JavaScript