Qualcuno ha creato questa cartella di lavoro anni fa. Ricalcola quando i dati cambiano, i totali si spostano in modi che nessuno prevede più e non esiste documentazione — perché le formule sono la documentazione. Leggere i numeri non ti dirà come sono stati prodotti. Leggere le regole sì.

Spire.XLS per JavaScript compila un motore per fogli di calcolo in WebAssembly, così un'app React può aprire un .xlsx esistente nel browser, scorrerne le celle ed estrarre la regola dietro ognuna di esse. La cartella di lavoro passa attraverso un file system virtuale (VFS), quindi nulla viene caricato e nessun backend è coinvolto.

A ogni cella vengono poste due domande: contiene una formula e, se sì, cosa dice quella formula? La prima è una verifica di proprietà. La seconda è una lettura. Quasi tutto in questo articolo deriva dal tenere separate queste due cose.

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

Quando servono le formule, non i numeri

Il motivo per leggere le regole anziché i valori è quasi sempre uno di questi:

• Subentrare in un modello che nessuno ha documentato. Le regole sono l'unica descrizione sopravvissuta di ciò che fa la cartella di lavoro.
• Spostare i calcoli fuori dal foglio di calcolo. Reimplementare un calcolo nel codice dell'applicazione richiede di conoscere l'espressione esatta, non solo il suo ultimo risultato.
• Verificare la coerenza. Una riga che usa silenziosamente una regola diversa da quelle intorno a sé è invisibile nei valori e ovvia nelle formule.
• Produrre una richiesta di modifica. Un elenco di celle e delle regole che contengono è qualcosa che un utente business può esaminare e correggere.
• Verificare una cartella di lavoro generata dal tuo stesso codice. Confermare che ciò che è stato scritto è ciò che è stato memorizzato — vedi Come inserire formule e funzioni di Excel in JavaScript (React) per il lato di scrittura di questa coppia.

Prerequisiti

Ti serve un progetto React con Spire.XLS per JavaScript installato e il modulo WebAssembly inizializzato, raggiungibile all'indirizzo window.wasmModule.spirexls. La cartella di lavoro che vuoi ispezionare dovrebbe già trovarsi nel VFS — caricata dalla cartella public della tua applicazione con FetchFileToVFS, oppure scritta lì come byte se è arrivata da altrove.

Se il risultato dovrà essere formattato — larghezze di colonna e simili — carica nel VFS anche un font, come fa l'esempio.

Le due domande da porre a ogni cella

Inizia chiedendo al foglio di lavoro la regione che effettivamente utilizza:

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange è la metà di quel frammento che ti protegge. Ciclare su A1:Z1000 in un foglio con dodici righe utilizzate passa la maggior parte del tempo su celle vuote e ti lascia a filtrarle in seguito. Chiedere al foglio la sua regione allocata mantiene il ciclo proporzionato al contenuto, cosa che conta non appena la cartella di lavoro è reale.

Poi HasFormula decide cosa vale la pena leggere. È un semplice booleano e risponde esattamente a una domanda — se la cella contiene una formula — che si rivela una domanda più ristretta di quanto sembri.

Un esempio completo

Il componente seguente carica una cartella di lavoro esistente, scorre il suo intervallo utilizzato e scrive ogni formula trovata in un nuovo foglio come riga leggibile — l'indirizzo della cella e la regola in essa memorizzata:

function App() {
  const readFormulasAndFunctions = 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 and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Leggi formule e risultati delle funzioni dai fogli di lavoro Excel

Nota cosa fa il codice con la cartella di lavoro di origine: la legge e nulla più. Per l'output viene creato un secondo Workbook, quindi il file ispezionato non viene mai modificato. Questo conta quando stai esaminando il documento di qualcun altro — l'ispezione dovrebbe essere non distruttiva per costruzione, non perché ti ricordi di non salvare.

Formula o valore

Qui è dove la lettura ristretta di HasFormula ripaga, perché le proprietà che puoi leggere da una cella non restituiscono tutte la stessa cosa:

La coppia che causa più confusione è Formula rispetto a FormulaNumberValue: la stessa cella, due risposte completamente diverse. Una è la regola; l'altra è ciò che la regola ha prodotto. Chiedi quella sbagliata e otterrai un valore tecnicamente valido che non è ciò che stavi cercando — un audit delle formule che restituisce numeri, o un'estrazione di valori che restituisce formule.

Comporre un inventario delle formule

L'esempio scrive ogni occorrenza in una seconda cartella di lavoro e la scarica. Questa è la forma giusta quando l'inventario è esso stesso un documento — qualcosa da consegnare a un revisore o da allegare a un ticket.

Quando invece l'inventario è destinato allo schermo, raccogli prima gli stessi dati e decidi dopo come presentarli:

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

RangeAddressLocal è ciò che rende utilizzabile il risultato. Restituisce l'indirizzo nella notazione propria del foglio — il nome che una persona userebbe parlando della cella — anziché una coppia riga-colonna, che è tecnicamente equivalente e praticamente illeggibile. Una voce che dice B7 può essere gestita; una voce che dice riga 7, colonna 2 deve prima essere tradotta.

Più di un foglio di lavoro

Il ciclo sopra copre un solo foglio. Un inventario a livello di cartella di lavoro significa ripeterlo per ogni foglio di lavoro, recuperando ciascuno nello stesso modo in cui viene recuperato il primo, con Workbook.Worksheets.get(i) che prende l'indice.

Due dettagli valgono la pena di essere gestiti correttamente prima di scalare il tutto. Registra da quale foglio di lavoro proviene ogni voce, perché B7 su due fogli sono due celle diverse e un elenco che non le distingue è ambiguo esattamente nel momento in cui conta. E mantieni la colonna di output abbastanza larga — gli indirizzi e le stringhe delle regole sono lunghi, e un inventario troncato è peggio di uno stretto.

Perché una cella con formula può non essere rilevata

Una cella che visualizza =SUM(B1:F1) non necessariamente contiene una formula. Se è stata scritta tramite Text o Value invece che tramite Formula, oppure digitata in una cella già formattata come testo, allora i caratteri sono memorizzati come stringa. Il foglio mostra una formula; la cella contiene un'etichetta.

HasFormula lo segnala correttamente come false, e una scansione che si aspetta di trovare quella cella risulta vuota. Questa è la trappola in questo flusso di lavoro perché non sembra un errore: la cartella di lavoro contiene visibilmente delle formule, il codice viene eseguito senza errori e l'inventario è più corto di tutte le celle che sono state digitate come testo.

Quando una formula sembra mancare da un inventario, controlla come è stata scritta prima di controllare il codice di lettura. Se la cartella di lavoro è generata dalla tua stessa applicazione, questa è la stessa distinzione di proprietà che l'inserimento di formule copre dal lato della scrittura.

Problemi comuni

La scansione non trova nulla, ma il foglio è pieno di formule. Sono memorizzate come testo. Vedi la sezione precedente — HasFormula segnala solo le formule reali.

Il risultato è un numero quando volevo la formula, o viceversa. Hai letto la proprietà sbagliata. Formula fornisce la regola, FormulaNumberValue fornisce il numero calcolato.

Il ciclo è lento o produce centinaia di voci vuote. Sta scorrendo un intervallo rettangolare fisso invece della regione allocata del foglio. Usa AllocatedRange come origine dell'iterazione.

Mancano le celle di un secondo foglio. Il ciclo viene eseguito su un solo foglio di lavoro. Ripetilo per ogni foglio e mantieni il nome del foglio accanto a ogni voce.

La cartella di lavoro di origine è cambiata dopo l'esecuzione. Non dovrebbe esserlo — l'esempio legge una cartella di lavoro e ne scrive un'altra. Verifica che l'output venga salvato in un oggetto Workbook diverso, come nel codice sopra.

Domande frequenti

Devo avere Excel installato per leggere le formule da una cartella di lavoro?

No. Il motore è incluso nel pacchetto e funziona come WebAssembly all'interno del browser. L'applicazione originale per fogli di calcolo non è coinvolta in nessun momento.

Posso leggere il valore calcolato invece della formula?

Sì. Leggi FormulaNumberValue anziché Formula dalla stessa cella. Usa prima HasFormula in modo da porre quella domanda solo alle celle in cui ha senso.

Leggere una cartella di lavoro la modifica?

La lettura no. L'esempio apre l'input, crea una cartella di lavoro di output separata per i risultati e salva solo quella — così il file ispezionato resta com'era.

Quali formati Excel posso leggere?

Sia il formato legacy .xls sia i file moderni .xlsx sono supportati dalla stessa API, quindi una cartella di lavoro non deve essere convertita prima di poter essere ispezionata.

Funziona per cartelle di lavoro archiviate su un server?

Sì, se riesci a portare i byte nel browser. Scrivili nel VFS e carica da lì — la lettura stessa è interamente lato client, e la cartella di lavoro viene caricata solo se la tua applicazione sceglie di caricarla.

Vedi anche

• Come inserire formule e funzioni di Excel in JavaScript (React)
• Integrare Spire.XLS per JavaScript in un progetto React
• Creare intervalli denominati in React con JavaScript
• Pagina del prodotto Spire.XLS per JavaScript

Quelqu'un a créé ce classeur il y a des années. Il se recalcule lorsque les données changent, les totaux évoluent d'une manière que plus personne ne prévoit, et il n'existe aucune documentation — parce que les formules sont la documentation. Lire les chiffres ne vous dira pas comment ils ont été produits. Lire les règles, si.

Spire.XLS for JavaScript compile un moteur de tableur en WebAssembly, ce qui permet à une application React d'ouvrir un fichier .xlsx existant dans le navigateur, de parcourir ses cellules et d'extraire la règle qui se cache derrière chacune d'elles. Le classeur transite par un système de fichiers virtuel (VFS), donc rien n'est téléversé et aucun backend n'intervient.

Deux questions sont posées à chaque cellule : contient-elle une formule et, si oui, que dit cette formule ? La première est une vérification de propriété. La seconde est une lecture. Presque tout dans cet article découle du fait de garder ces deux aspects distincts.

Pour la configuration du projet, consultez Intégrer Spire.XLS for JavaScript dans un projet React. Les exemples ci-dessous supposent que le package est installé et que le module WebAssembly a été initialisé.

Quand vous avez besoin des formules, pas des chiffres

La raison de lire les règles plutôt que les valeurs est presque toujours l'une des suivantes :

• Reprendre un modèle que personne n'a documenté. Les règles sont la seule description subsistante de ce que fait le classeur.
• Sortir les calculs du tableur. Réimplémenter un calcul dans le code de l'application exige de connaître l'expression exacte, et pas seulement son dernier résultat.
• Vérifier la cohérence. Une ligne qui utilise discrètement une règle différente de celle des lignes voisines est invisible dans les valeurs et évidente dans les formules.
• Produire une demande de modification. Une liste de cellules et des règles qu'elles contiennent est quelque chose qu'un utilisateur métier peut examiner et corriger.
• Vérifier un classeur généré par votre propre code. Confirmer que ce qui a été écrit est bien ce qui a été enregistré — voir Comment insérer des formules et fonctions Excel en JavaScript (React) pour le volet écriture de cette paire.

Prérequis

Vous avez besoin d'un projet React avec Spire.XLS for JavaScript installé et le module WebAssembly initialisé, accessible à l'adresse window.wasmModule.spirexls. Le classeur que vous souhaitez inspecter doit déjà se trouver dans le VFS — chargé depuis le dossier public de votre application avec FetchFileToVFS, ou écrit à cet endroit sous forme d'octets s'il provient d'ailleurs.

Si le résultat doit être mis en forme — largeurs de colonnes et autres — chargez également une police dans le VFS, comme le fait l'exemple.

Les deux questions à poser à chaque cellule

Commencez par demander à la feuille de calcul la région qu'elle utilise réellement :

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange est la moitié de cet extrait qui vous protège. Parcourir A1:Z1000 sur une feuille comptant douze lignes utilisées passe l'essentiel de son temps sur des cellules vides, et vous laisse les filtrer ensuite. Demander à la feuille sa région allouée maintient la boucle proportionnelle au contenu, ce qui compte dès que le classeur est réel.

Ensuite, HasFormula détermine ce qui vaut la peine d'être lu. C'est un simple booléen, et il répond exactement à une question — si la cellule contient une formule — ce qui s'avère être une question plus étroite qu'il n'y paraît.

Un exemple complet

Le composant ci-dessous charge un classeur existant, parcourt sa plage utilisée et écrit chaque formule qu'il trouve dans une nouvelle feuille sous forme d'une ligne lisible — l'adresse de la cellule et la règle qu'elle contient :

function App() {
  const readFormulasAndFunctions = 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 and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Lire les formules et les résultats de fonctions depuis des feuilles de calcul Excel

Notez ce que le code fait avec le classeur source : il le lit et rien d'autre. Un second Workbook est créé pour la sortie, de sorte que le fichier inspecté n'est jamais modifié. Cela compte lorsque vous examinez le document de quelqu'un d'autre — l'inspection doit être non destructive par construction, et non parce que l'on se souvient de ne pas enregistrer.

Formule ou valeur

C'est ici que la lecture étroite de HasFormula porte ses fruits, car les propriétés que vous pouvez lire depuis une cellule ne renvoient pas toutes la même chose :

La paire qui prête le plus à confusion est Formula face à FormulaNumberValue : la même cellule, deux réponses complètement différentes. L'une est la règle ; l'autre est ce que la règle a produit. Demandez la mauvaise et vous obtiendrez une valeur techniquement valide qui n'est pas ce que vous cherchiez — un audit de formules qui renvoie des nombres, ou une extraction de valeurs qui renvoie des formules.

Constituer un inventaire de formules

L'exemple écrit chaque correspondance dans un second classeur et le télécharge. C'est la bonne forme lorsque l'inventaire est lui-même un document — quelque chose à remettre à un réviseur ou à joindre à un ticket.

Lorsque l'inventaire est destiné à l'écran, en revanche, collectez d'abord les mêmes données et décidez ensuite comment les présenter :

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

RangeAddressLocal est ce qui rend le résultat exploitable. Il renvoie l'adresse dans la notation propre à la feuille — le nom qu'une personne utiliserait en parlant de la cellule — plutôt qu'une paire ligne-colonne, qui est techniquement équivalente et pratiquement illisible. Une entrée qui indique B7 peut être traitée ; une entrée qui indique ligne 7, colonne 2 doit d'abord être traduite.

Plusieurs feuilles de calcul

La boucle ci-dessus couvre une seule feuille. Un inventaire au niveau du classeur implique de la répéter pour chaque feuille de calcul à tour de rôle, en récupérant chacune de la même manière que la première, avec Workbook.Worksheets.get(i) qui prend l'index.

Deux détails méritent d'être bien traités avant de passer à l'échelle supérieure. Enregistrez la feuille de calcul d'où provient chaque entrée, car B7 sur deux feuilles correspond à deux cellules différentes et une liste qui ne les distingue pas est ambiguë exactement au moment où cela compte. Et gardez la colonne de sortie suffisamment large — les adresses et les chaînes de règles sont longues, et un inventaire tronqué est pire qu'un inventaire étroit.

Pourquoi une cellule de formule peut passer inaperçue

Une cellule qui affiche =SUM(B1:F1) ne contient pas nécessairement une formule. Si elle a été écrite via Text ou Value au lieu de Formula, ou saisie dans une cellule déjà formatée en texte, alors les caractères sont stockés sous forme de chaîne. La feuille affiche une formule ; la cellule contient une étiquette.

HasFormula le signale correctement comme false, et une analyse qui s'attend à trouver cette cellule revient vide. C'est le piège de ce flux de travail, car cela ne ressemble pas à un échec : le classeur contient visiblement des formules, le code s'exécute sans erreur, et l'inventaire est incomplet du nombre de cellules saisies comme texte.

Lorsqu'une formule semble manquer dans un inventaire, vérifiez comment elle a été écrite avant de vérifier le code de lecture. Si le classeur est généré par votre propre application, il s'agit de la même distinction de propriété que l'insertion de formules aborde du côté de l'écriture.

Problèmes courants

L'analyse ne trouve rien, mais la feuille est pleine de formules. Elles sont stockées sous forme de texte. Voir la section ci-dessus — HasFormula ne signale que les véritables formules.

Le résultat est un nombre alors que je voulais la formule, ou l'inverse. Vous avez lu la mauvaise propriété. Formula donne la règle, FormulaNumberValue donne le nombre calculé.

La boucle est lente ou produit des centaines d'entrées vides. Elle parcourt une plage rectangulaire fixe au lieu de la région allouée de la feuille. Utilisez AllocatedRange comme source de l'itération.

Les cellules d'une seconde feuille sont manquantes. La boucle s'exécute sur une seule feuille de calcul. Répétez-la pour chaque feuille de calcul, et conservez la feuille aux côtés de chaque entrée.

Le classeur source a changé après l'exécution. Il n'aurait pas dû — l'exemple lit un classeur et écrit dans un autre. Vérifiez que la sortie est enregistrée dans un objet Workbook différent, comme dans le code ci-dessus.

FAQ

Ai-je besoin d'Excel installé pour lire les formules d'un classeur ?

Non. Le moteur est fourni avec le package et s'exécute en WebAssembly dans le navigateur. L'application de tableur d'origine n'intervient à aucun moment.

Puis-je lire la valeur calculée au lieu de la formule ?

Oui. Lisez FormulaNumberValue plutôt que Formula depuis la même cellule. Utilisez d'abord HasFormula afin de ne poser cette question qu'aux cellules où elle a un sens.

La lecture d'un classeur le modifie-t-elle ?

Non. L'exemple ouvre l'entrée, crée un classeur de sortie distinct pour les résultats et n'enregistre que celui-ci — le fichier inspecté reste donc tel qu'il était.

Quels formats Excel puis-je lire ?

Aussi bien l'ancien format .xls que les fichiers modernes .xlsx sont pris en charge par la même API, si bien qu'un classeur n'a pas besoin d'être converti avant de pouvoir être inspecté.

Cela fonctionne-t-il pour des classeurs stockés sur un serveur ?

Oui, si vous pouvez amener les octets dans le navigateur. Écrivez-les dans le VFS et chargez-les depuis là — la lecture elle-même est entièrement côté client, et le classeur n'est téléversé que si votre propre application choisit de le faire.

Voir aussi

• Comment insérer des formules et fonctions Excel en JavaScript (React)
• Intégrer Spire.XLS for JavaScript dans un projet React
• Créer des plages nommées en React avec JavaScript
• Page produit Spire.XLS for JavaScript

Alguien creó este libro de trabajo hace años. Se recalcula cuando cambian los datos, los totales se mueven de formas que ya nadie predice y no hay documentación, porque las fórmulas son la documentación. Leer los números no le dirá cómo se produjeron. Leer las reglas sí.

Spire.XLS for JavaScript compila un motor de hojas de cálculo a WebAssembly, de modo que una aplicación React puede abrir un .xlsx existente en el navegador, recorrer sus celdas y extraer la regla que hay detrás de cada una. El libro de trabajo viaja a través de un sistema de archivos virtual (VFS), por lo que no se sube nada y no interviene ningún backend.

A cada celda se le hacen dos preguntas: ¿contiene una fórmula? Y, si es así, ¿qué dice esa fórmula? La primera es una comprobación de propiedad. La segunda es una lectura. Casi todo en este artículo se deriva de mantener esas dos cosas separadas.

Para la configuración del proyecto, consulte Integrating Spire.XLS for JavaScript in a React Project. Los ejemplos siguientes asumen que el paquete está instalado y que el módulo WebAssembly se ha inicializado.

Cuando necesita las fórmulas, no los números

La razón para leer reglas en lugar de valores casi siempre es una de estas:

• Hacerse cargo de un modelo que nadie documentó. Las reglas son la única descripción que queda de lo que hace el libro de trabajo.
• Sacar los cálculos de la hoja de cálculo. Reimplementar un cálculo en el código de la aplicación requiere conocer la expresión exacta, no solo su último resultado.
• Comprobar la coherencia. Una fila que usa en silencio una regla distinta de las filas que la rodean es invisible en los valores y obvia en las fórmulas.
• Producir una solicitud de cambio. Una lista de celdas y las reglas que contienen es algo que un usuario de negocio puede revisar y corregir.
• Verificar un libro de trabajo generado por su propio código. Confirmar que lo que se escribió es lo que se almacenó; consulte How to Insert Excel Formulas and Functions in JavaScript (React) para la parte de escritura de ese par.

Requisitos previos

Necesita un proyecto React con Spire.XLS for JavaScript instalado y el módulo WebAssembly inicializado, accesible en window.wasmModule.spirexls. El libro de trabajo que desea inspeccionar ya debe estar en el VFS: cargado desde la carpeta pública de su aplicación con FetchFileToVFS, o escrito allí como bytes si llegó desde otro lugar.

Si el resultado se va a formatear —anchos de columna y similares—, cargue también una fuente en el VFS, como hace el ejemplo.

Las dos preguntas que hay que hacer a cada celda

Empiece por pedir a la hoja de cálculo la región que realmente usa:

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange es la mitad de ese fragmento que le protege. Recorrer A1:Z1000 en una hoja con doce filas usadas dedica la mayor parte del tiempo a celdas vacías y luego le obliga a filtrarlas. Pedir a la hoja su región asignada mantiene el bucle proporcional al contenido, lo cual importa en cuanto el libro de trabajo es real.

Después, HasFormula decide qué merece la pena leer. Es un booleano simple y responde exactamente a una pregunta —si la celda contiene una fórmula—, que resulta ser una pregunta más limitada de lo que parece.

Un ejemplo completo

El componente siguiente carga un libro de trabajo existente, recorre su rango usado y escribe cada fórmula que encuentra en una hoja nueva como una línea legible: la dirección de la celda y la regla almacenada en ella:

function App() {
  const readFormulasAndFunctions = 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 and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Lea fórmulas y resultados de funciones desde hojas de cálculo de Excel

Observe lo que hace el código con el libro de trabajo de origen: lo lee y nada más. Se crea un segundo Workbook para la salida, por lo que el archivo que se inspecciona nunca se modifica. Eso importa cuando examina el documento de otra persona: la inspección debe ser no destructiva por construcción, no porque recuerde no guardar.

Fórmula o valor

Aquí es donde compensa la lectura limitada de HasFormula, porque las propiedades que puede leer de una celda no todas devuelven lo mismo:

El par que causa más confusión es Formula frente a FormulaNumberValue: la misma celda, dos respuestas completamente diferentes. Una es la regla; la otra es lo que produjo la regla. Pida la equivocada y obtendrá un valor técnicamente válido que no es lo que estaba buscando: una auditoría de fórmulas que devuelve números, o una extracción de valores que devuelve fórmulas.

Cómo elaborar un inventario de fórmulas

El ejemplo escribe cada coincidencia en un segundo libro de trabajo y lo descarga. Esa es la forma correcta cuando el inventario es en sí mismo un documento: algo que entregar a un revisor o adjuntar a un ticket.

Cuando el inventario es para la pantalla, recopile primero los mismos datos y decida después cómo presentarlos:

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

RangeAddressLocal es lo que hace utilizable el resultado. Devuelve la dirección en la propia notación de la hoja —el nombre que usaría una persona al hablar de la celda— en lugar de un par fila-columna, que es técnicamente equivalente y prácticamente ilegible. Se puede actuar sobre una entrada que dice B7; una entrada que dice fila 7, columna 2 tiene que traducirse primero.

Más de una hoja de cálculo

El bucle anterior cubre una hoja. Un inventario a nivel de libro de trabajo implica repetirlo para cada hoja de cálculo por turno, obteniendo cada una de la misma manera que la primera, con Workbook.Worksheets.get(i) tomando el índice.

Merece la pena acertar con dos detalles antes de ampliarlo. Registre de qué hoja de cálculo proviene cada entrada, porque B7 en dos hojas son dos celdas diferentes y una lista que no las distingue es ambigua justo en el momento en que importa. Y mantenga la columna de salida lo bastante ancha: las direcciones y las cadenas de reglas son largas, y un inventario truncado es peor que uno estrecho.

Por qué una celda con fórmula puede pasar desapercibida

Una celda que muestra =SUM(B1:F1) no necesariamente contiene una fórmula. Si se escribió mediante Text o Value en lugar de Formula, o se escribió en una celda que ya estaba formateada como texto, los caracteres se almacenan como una cadena. La hoja muestra una fórmula; la celda contiene una etiqueta.

HasFormula lo informa correctamente como false, y un escaneo que espera encontrar esa celda no devuelve nada. Esta es la trampa de este flujo de trabajo porque no parece un fallo: el libro de trabajo contiene fórmulas visiblemente, el código se ejecuta sin error y el inventario se queda corto en todas las celdas que se escribieron como texto.

Cuando una fórmula parece faltar en un inventario, compruebe cómo se escribió antes de comprobar el código de lectura. Si el libro de trabajo lo genera su propia aplicación, esta es la misma distinción de propiedades que inserting formulas cubre desde el lado de la escritura.

Problemas comunes

El escaneo no encuentra nada, pero la hoja está llena de fórmulas. Están almacenadas como texto. Consulte la sección anterior: HasFormula solo informa de fórmulas reales.

El resultado es un número cuando quería la fórmula, o al revés. Leyó la propiedad equivocada. Formula da la regla, FormulaNumberValue da el número calculado.

El bucle es lento o produce cientos de entradas vacías. Está recorriendo un rango rectangular fijo en lugar de la región asignada de la hoja. Use AllocatedRange como origen de la iteración.

Faltan celdas de una segunda hoja. El bucle se ejecuta en una sola hoja de cálculo. Repítalo para cada hoja y conserve la hoja junto a cada entrada.

El libro de trabajo de origen cambió después de ejecutarlo. No debería haber cambiado: el ejemplo lee un libro de trabajo y escribe en otro. Compruebe que la salida se guarda en un objeto Workbook distinto, como en el código anterior.

Preguntas frecuentes

¿Necesito tener Excel instalado para leer fórmulas de un libro de trabajo?

No. El motor viene incluido con el paquete y se ejecuta como WebAssembly dentro del navegador. La aplicación original de hojas de cálculo no interviene en ningún momento.

¿Puedo leer el valor calculado en lugar de la fórmula?

Sí. Lea FormulaNumberValue en lugar de Formula de la misma celda. Use HasFormula primero para hacer esa pregunta solo a las celdas donde significa algo.

¿Leer un libro de trabajo lo modifica?

Leerlo no lo modifica. El ejemplo abre la entrada, crea un libro de trabajo de salida separado para los resultados y guarda solo ese, por lo que el archivo que se inspecciona queda como estaba.

¿Qué formatos de Excel puedo leer?

Tanto el formato heredado .xls como los archivos modernos .xlsx son compatibles con la misma API, por lo que un libro de trabajo no necesita convertirse antes de poder inspeccionarlo.

¿Esto funciona para libros de trabajo almacenados en un servidor?

Sí, si puede llevar los bytes al navegador. Escríbalos en el VFS y cargue desde allí: la lectura en sí es completamente del lado del cliente, y el libro de trabajo solo se sube si su propia aplicación decide subirlo.

Véase también

• How to Insert Excel Formulas and Functions in JavaScript (React)
• Integrating Spire.XLS for JavaScript in a React Project
• Create Named Ranges in React with JavaScript
• Spire.XLS for JavaScript Product Page

Jemand hat diese Arbeitsmappe vor Jahren erstellt. Sie berechnet sich neu, wenn sich die Daten ändern, die Summen verschieben sich auf eine Weise, die niemand mehr vorhersagt, und es gibt keine Dokumentation – denn die Formeln sind die Dokumentation. Die Zahlen zu lesen, verrät Ihnen nicht, wie sie entstanden sind. Die Regeln zu lesen, schon.

Spire.XLS für JavaScript kompiliert eine Tabellenkalkulations-Engine zu WebAssembly, sodass eine React-App eine vorhandene .xlsx im Browser öffnen, ihre Zellen durchlaufen und die Regel hinter jeder einzelnen herausziehen kann. Die Arbeitsmappe wandert durch ein virtuelles Dateisystem (VFS), sodass nichts hochgeladen wird und kein Backend beteiligt ist.

An jede Zelle werden zwei Fragen gestellt: Enthält sie eine Formel, und wenn ja, was besagt diese Formel? Die erste ist eine Eigenschaftsprüfung. Die zweite ist ein Lesevorgang. Fast alles in diesem Artikel folgt daraus, diese beiden auseinanderzuhalten.

Zur Projekteinrichtung siehe Integration von Spire.XLS für JavaScript in ein React-Projekt. Die folgenden Beispiele gehen davon aus, dass das Paket installiert und das WebAssembly-Modul initialisiert wurde.

Wenn Sie die Formeln brauchen, nicht die Zahlen

Der Grund, Regeln statt Werte zu lesen, ist fast immer einer der folgenden:

• Übernahme eines Modells, das niemand dokumentiert hat. Die Regeln sind die einzige erhaltene Beschreibung dessen, was die Arbeitsmappe tut.
• Verlagerung von Berechnungen aus der Tabellenkalkulation. Um eine Berechnung im Anwendungscode neu zu implementieren, muss man den genauen Ausdruck kennen, nicht nur sein letztes Ergebnis.
• Konsistenzprüfung. Eine Zeile, die stillschweigend eine andere Regel verwendet als die Zeilen um sie herum, ist in den Werten unsichtbar und in den Formeln offensichtlich.
• Erstellung einer Änderungsanforderung. Eine Liste von Zellen und den darin enthaltenen Regeln ist etwas, das ein Fachanwender prüfen und korrigieren kann.
• Überprüfung einer Arbeitsmappe, die Ihr eigener Code erzeugt hat. Bestätigung, dass das Geschriebene auch das Gespeicherte ist – siehe Wie man Excel-Formeln und -Funktionen in JavaScript (React) einfügt für die schreibende Seite dieses Paares.

Voraussetzungen

Sie brauchen ein React-Projekt mit installiertem Spire.XLS für JavaScript und initialisiertem WebAssembly-Modul, erreichbar unter window.wasmModule.spirexls. Die Arbeitsmappe, die Sie untersuchen möchten, sollte sich bereits im VFS befinden – geladen aus dem public-Ordner Ihrer Anwendung mit FetchFileToVFS oder als Bytes dorthin geschrieben, wenn sie von woanders gekommen ist.

Wenn das Ergebnis formatiert werden soll – Spaltenbreiten und dergleichen –, laden Sie zusätzlich eine Schriftart in das VFS, wie es das Beispiel tut.

Die zwei Fragen, die Sie an jede Zelle stellen sollten

Fragen Sie zunächst das Arbeitsblatt nach dem Bereich, den es tatsächlich verwendet:

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange ist die Hälfte dieses Snippets, die Sie schützt. Eine Schleife über A1:Z1000 in einem Blatt mit zwölf verwendeten Zeilen verbringt die meiste Zeit mit leeren Zellen und lässt Sie diese anschließend herausfiltern. Das Arbeitsblatt nach seinem zugewiesenen Bereich zu fragen, hält die Schleife proportional zum Inhalt – was zählt, sobald die Arbeitsmappe echt ist.

Dann entscheidet HasFormula, was es wert ist, gelesen zu werden. Es ist ein einfacher boolescher Wert und beantwortet genau eine Frage – ob die Zelle eine Formel enthält –, was sich als eine engere Frage entpuppt, als es klingt.

Ein vollständiges Beispiel

Die folgende Komponente lädt eine vorhandene Arbeitsmappe, durchläuft ihren verwendeten Bereich und schreibt jede gefundene Formel als lesbare Zeile in ein neues Blatt – die Zelladresse und die darin gespeicherte Regel:

function App() {
  const readFormulasAndFunctions = 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 and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Formeln und Funktionsergebnisse aus Excel-Arbeitsblättern lesen

Beachten Sie, was der Code mit der Quellarbeitsmappe macht: Er liest sie und sonst nichts. Für die Ausgabe wird eine zweite Workbook erstellt, sodass die untersuchte Datei nie verändert wird. Das ist wichtig, wenn Sie das Dokument einer anderen Person prüfen – die Untersuchung sollte konstruktionsbedingt nicht destruktiv sein und nicht davon abhängen, dass man daran denkt, nicht zu speichern.

Formel oder Wert

Hier zahlt sich die enge Auslegung von HasFormula aus, denn die Eigenschaften, die Sie aus einer Zelle lesen können, liefern nicht alle dasselbe:

Das Paar, das die meiste Verwirrung stiftet, ist Formula versus FormulaNumberValue: dieselbe Zelle, zwei völlig unterschiedliche Antworten. Die eine ist die Regel; die andere ist das, was die Regel erzeugt hat. Fragen Sie nach der falschen, erhalten Sie einen technisch gültigen Wert, der nicht das ist, wonach Sie gesucht haben – ein Formel-Audit, das Zahlen zurückgibt, oder eine Wertextraktion, die Formeln zurückgibt.

Ein Formelverzeichnis zusammenstellen

Das Beispiel schreibt jeden Treffer in eine zweite Arbeitsmappe und lädt sie herunter. Das ist die richtige Form, wenn das Verzeichnis selbst ein Dokument ist – etwas, das man einem Prüfer aushändigt oder an ein Ticket anhängt.

Wenn das Verzeichnis stattdessen für den Bildschirm bestimmt ist, sammeln Sie zuerst dieselben Daten und entscheiden Sie danach, wie Sie sie präsentieren:

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

RangeAddressLocal ist es, was das Ergebnis brauchbar macht. Es gibt die Adresse in der eigenen Notation des Blatts zurück – den Namen, den eine Person verwenden würde, wenn sie über die Zelle spricht – statt eines Zeilen-und-Spalten-Paares, das technisch äquivalent und praktisch unlesbar ist. Ein Eintrag, der B7 sagt, kann umgesetzt werden; ein Eintrag, der Zeile 7, Spalte 2 sagt, muss zuerst übersetzt werden.

Mehr als ein Arbeitsblatt

Die obige Schleife deckt ein Blatt ab. Ein Verzeichnis auf Arbeitsmappenebene bedeutet, sie der Reihe nach für jedes Arbeitsblatt zu wiederholen und jedes davon genauso abzurufen, wie das erste abgerufen wird, wobei Workbook.Worksheets.get(i) den Index annimmt.

Zwei Details sollten Sie richtig hinbekommen, bevor Sie es skalieren. Halten Sie fest, von welchem Arbeitsblatt jeder Eintrag stammt, denn B7 auf zwei Blättern sind zwei verschiedene Zellen, und eine Liste, die sie nicht unterscheidet, ist genau in dem Moment mehrdeutig, in dem es darauf ankommt. Und halten Sie die Ausgabespalte breit genug – die Adressen und Regelzeichenketten sind lang, und ein abgeschnittenes Verzeichnis ist schlimmer als ein schmales.

Warum eine Formelzelle unentdeckt bleiben kann

Eine Zelle, die =SUM(B1:F1) anzeigt, enthält nicht unbedingt eine Formel. Wenn sie über Text oder Value statt über Formula geschrieben oder in eine bereits als Text formatierte Zelle eingegeben wurde, dann werden die Zeichen als Zeichenkette gespeichert. Das Blatt zeigt eine Formel; die Zelle enthält ein Label.

HasFormula meldet dies korrekt als false, und ein Scan, der diese Zelle zu finden erwartet, liefert nichts. Das ist die Falle in diesem Workflow, weil es nicht wie ein Fehler aussieht: Die Arbeitsmappe enthält sichtbar Formeln, der Code läuft fehlerfrei, und dem Verzeichnis fehlen so viele Zellen, wie als Text eingegeben wurden.

Wenn in einem Verzeichnis eine Formel zu fehlen scheint, prüfen Sie, wie sie geschrieben wurde, bevor Sie den Lesecode prüfen. Wenn die Arbeitsmappe von Ihrer eigenen Anwendung erzeugt wird, ist dies dieselbe Eigenschaftsunterscheidung, die das Einfügen von Formeln von der schreibenden Seite her behandelt.

Häufige Probleme

Der Scan findet nichts, aber das Blatt ist voller Formeln. Sie sind als Text gespeichert. Siehe den Abschnitt oben – HasFormula meldet nur echte Formeln.

Das Ergebnis ist eine Zahl, obwohl ich die Formel wollte, oder umgekehrt. Sie haben die falsche Eigenschaft gelesen. Formula liefert die Regel, FormulaNumberValue liefert die berechnete Zahl.

Die Schleife ist langsam oder erzeugt Hunderte leerer Einträge. Sie durchläuft einen festen rechteckigen Bereich statt des zugewiesenen Bereichs des Blatts. Verwenden Sie AllocatedRange als Quelle der Iteration.

Zellen aus einem zweiten Blatt fehlen. Die Schleife läuft auf einem Arbeitsblatt. Wiederholen Sie sie für jedes Arbeitsblatt und behalten Sie das Blatt neben jedem Eintrag bei.

Die Quellarbeitsmappe hat sich nach dem Ausführen geändert. Das sollte sie nicht – das Beispiel liest eine Arbeitsmappe und schreibt in eine andere. Prüfen Sie, dass die Ausgabe in ein anderes Workbook-Objekt gespeichert wird, wie im Code oben.

FAQ

Muss Excel installiert sein, um Formeln aus einer Arbeitsmappe zu lesen?

Nein. Die Engine ist im Paket enthalten und läuft als WebAssembly im Browser. Die ursprüngliche Tabellenkalkulationsanwendung ist an keiner Stelle beteiligt.

Kann ich statt der Formel den berechneten Wert lesen?

Ja. Lesen Sie FormulaNumberValue statt Formula aus derselben Zelle. Verwenden Sie zuerst HasFormula, damit Sie diese Frage nur bei Zellen stellen, bei denen sie etwas bedeutet.

Verändert das Lesen einer Arbeitsmappe diese?

Das Lesen nicht. Das Beispiel öffnet die Eingabe, erstellt eine separate Ausgabearbeitsmappe für die Ergebnisse und speichert nur diese – die untersuchte Datei bleibt also so, wie sie war.

Welche Excel-Formate kann ich lesen?

Sowohl das ältere .xls-Format als auch moderne .xlsx-Dateien werden von derselben API unterstützt, sodass eine Arbeitsmappe nicht konvertiert werden muss, bevor sie untersucht werden kann.

Funktioniert das auch für Arbeitsmappen, die auf einem Server gespeichert sind?

Ja, wenn Sie die Bytes in den Browser bekommen. Schreiben Sie sie in das VFS und laden Sie von dort – das Lesen selbst findet vollständig clientseitig statt, und die Arbeitsmappe wird nur hochgeladen, wenn Ihre eigene Anwendung sich dafür entscheidet.

Siehe auch

• Wie man Excel-Formeln und -Funktionen in JavaScript (React) einfügt
• Integration von Spire.XLS für JavaScript in ein React-Projekt
• Benannte Bereiche in React mit JavaScript erstellen
• Spire.XLS für JavaScript – Produktseite

Кто-то создал эту книгу много лет назад. Она пересчитывается при изменении данных, итоги меняются так, как уже никто не предскажет, и никакой документации нет — потому что формулы и есть документация. Чтение чисел не расскажет, как они были получены. Чтение правил — расскажет.

Spire.XLS for JavaScript компилирует движок электронных таблиц в WebAssembly, поэтому приложение на React может открыть существующий .xlsx в браузере, обойти его ячейки и извлечь правило, стоящее за каждой из них. Книга перемещается через виртуальную файловую систему (VFS), поэтому ничего не загружается на сервер и бэкенд не задействован.

К каждой ячейке задаются два вопроса: содержит ли она формулу и, если да, что эта формула говорит? Первый — это проверка свойства. Второй — это чтение. Практически всё в этой статье вытекает из того, чтобы не смешивать эти два действия.

О настройке проекта см. Интеграция Spire.XLS for JavaScript в проект на React. Примеры ниже предполагают, что пакет установлен и модуль WebAssembly инициализирован.

Когда нужны формулы, а не числа

Причина читать правила, а не значения, почти всегда одна из следующих:

• Принять на себя модель, которую никто не документировал. Правила — единственное сохранившееся описание того, что делает книга.
• Перенос вычислений из электронной таблицы. Чтобы реализовать вычисление в коде приложения заново, нужно знать точное выражение, а не только его последний результат.
• Проверка согласованности. Одна строка, тихо использующая иное правило, чем окружающие её строки, незаметна в значениях и очевидна в формулах.
• Подготовка запроса на изменение. Список ячеек и содержащихся в них правил — это то, что бизнес-пользователь может просмотреть и исправить.
• Проверка книги, созданной вашим собственным кодом. Подтверждение того, что записано именно то, что сохранилось — о стороне записи этой пары см. Как вставлять формулы и функции Excel в JavaScript (React).

Предварительные требования

Вам нужен проект на React с установленным Spire.XLS for JavaScript и инициализированным модулем WebAssembly, доступным по адресу window.wasmModule.spirexls. Книга, которую вы хотите проверить, уже должна находиться в VFS — загружена из общей папки вашего приложения с помощью FetchFileToVFS или записана туда в виде байтов, если она пришла откуда-то ещё.

Если результат будет форматироваться — ширины столбцов и тому подобное — загрузите в VFS также шрифт, как это делает пример.

Два вопроса к каждой ячейке

Начните с того, что запросите у листа область, которую он действительно использует:

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange — это та половина фрагмента, которая вас защищает. Перебор A1:Z1000 на листе с двенадцатью используемыми строками тратит большую часть времени на пустые ячейки и оставляет вам их фильтрацию после. Запрос у листа его выделенной области делает цикл пропорциональным содержимому, что важно, как только книга становится реальной.

Затем HasFormula решает, что стоит читать. Это обычное логическое значение, и оно отвечает ровно на один вопрос — содержит ли ячейка формулу — который, как выясняется, является более узким вопросом, чем кажется.

Полный пример

Компонент ниже загружает существующую книгу, обходит её используемый диапазон и записывает каждую найденную формулу в новый лист в виде удобочитаемой строки — адрес ячейки и хранящееся в ней правило:

function App() {
  const readFormulasAndFunctions = 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 and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Чтение формул и результатов функций из листов Excel

Обратите внимание, что код делает с исходной книгой: он только читает её, и ничего больше. Для вывода создаётся второй Workbook, поэтому проверяемый файл никогда не изменяется. Это важно, когда вы изучаете чужой документ — проверка должна быть неразрушающей по своей конструкции, а не за счёт того, что вы помните о необходимости не сохранять.

Формула или значение

Именно здесь узкое понимание HasFormula окупается, потому что свойства, которые можно прочитать из ячейки, возвращают не одно и то же:

Наибольшую путаницу вызывает пара Formula и FormulaNumberValue: одна и та же ячейка, два совершенно разных ответа. Один — это правило; другой — то, что правило произвело. Запросите не тот — и получите технически корректное значение, которое не является тем, что вы искали: аудит формул, возвращающий числа, или извлечение значений, возвращающее формулы.

Составление описи формул

В примере каждое найденное совпадение записывается во вторую книгу и скачивается. Это правильная форма, когда сама опись является документом — тем, что можно передать проверяющему или приложить к заявке.

Когда же опись предназначена для отображения на экране, сначала соберите те же данные, а затем решите, как их представить:

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

Именно RangeAddressLocal делает результат пригодным для использования. Он возвращает адрес в собственной нотации листа — имя, которое человек использовал бы при обсуждении ячейки, — а не пару «строка и столбец», которая технически эквивалентна и практически нечитаема. С записью, которая говорит B7, можно работать; запись, которая говорит «строка 7, столбец 2», нужно сначала перевести.

Более одного листа

Приведённый выше цикл охватывает один лист. Опись на уровне всей книги означает повторение его по очереди для каждого листа, при этом каждый лист извлекается так же, как первый, с помощью Workbook.Worksheets.get(i), принимающего индекс.

Прежде чем масштабировать это, стоит правильно сделать две вещи. Записывайте, с какого листа пришла каждая запись, потому что B7 на двух листах — это две разные ячейки, а список, который их не различает, становится неоднозначным именно в тот момент, когда это важно. И держите столбец вывода достаточно широким — адреса и строки правил длинные, а обрезанная опись хуже узкой.

Почему ячейка с формулой может остаться незамеченной

Ячейка, отображающая =SUM(B1:F1), не обязательно содержит формулу. Если она была записана через Text или Value вместо Formula, или введена в ячейку, уже отформатированную как текст, то символы хранятся как строка. Лист показывает формулу; ячейка содержит метку.

HasFormula корректно сообщает об этом как false, и сканирование, ожидающее найти эту ячейку, ничего не находит. В этом и заключается ловушка данного рабочего процесса: это не выглядит как сбой — книга наглядно содержит формулы, код выполняется без ошибок, а в описи не хватает ровно столько ячеек, сколько было введено как текст.

Когда формула, по-видимому, отсутствует в описи, проверьте, как она была записана, прежде чем проверять код чтения. Если книга создаётся вашим собственным приложением, это то же самое различие свойств, которое вставка формул рассматривает со стороны записи.

Типичные проблемы

Сканирование ничего не находит, но лист полон формул. Они хранятся как текст. См. раздел выше — HasFormula сообщает только о настоящих формулах.

Результат — число, хотя мне нужна была формула, или наоборот. Вы прочитали не то свойство. Formula даёт правило, FormulaNumberValue даёт вычисленное число.

Цикл работает медленно или создаёт сотни пустых записей. Он обходит фиксированный прямоугольный диапазон вместо выделенной области листа. Используйте AllocatedRange в качестве источника перебора.

Отсутствуют ячейки со второго листа. Цикл выполняется на одном листе. Повторите его для каждого листа и сохраняйте имя листа рядом с каждой записью.

Исходная книга изменилась после выполнения. Этого не должно было произойти — пример читает одну книгу, а записывает в другую. Убедитесь, что вывод сохраняется в другой объект Workbook, как в приведённом выше коде.

Часто задаваемые вопросы

Нужен ли установленный Excel, чтобы читать формулы из книги?

Нет. Движок входит в состав пакета и работает как WebAssembly внутри браузера. Исходное приложение для работы с электронными таблицами нигде не задействовано.

Можно ли прочитать вычисленное значение вместо формулы?

Да. Читайте FormulaNumberValue вместо Formula из той же ячейки. Сначала используйте HasFormula, чтобы задавать этот вопрос только тем ячейкам, где он имеет смысл.

Изменяет ли чтение книгу?

Чтение — нет. Пример открывает входной файл, создаёт отдельную выходную книгу для результатов и сохраняет только её, поэтому проверяемый файл остаётся таким, каким был.

Какие форматы Excel я могу читать?

Как устаревший формат .xls, так и современные файлы .xlsx поддерживаются одним и тем же API, поэтому книгу не нужно преобразовывать перед проверкой.

Работает ли это с книгами, хранящимися на сервере?

Да, если вы можете доставить байты в браузер. Запишите их в VFS и загружайте оттуда — само чтение полностью выполняется на стороне клиента, и книга загружается на сервер только если ваше приложение решит это сделать.

См. также

• Как вставлять формулы и функции Excel в JavaScript (React)
• Интеграция Spire.XLS for JavaScript в проект на React
• Создание именованных диапазонов в React с помощью JavaScript
• Страница продукта Spire.XLS for JavaScript

Uma planilha gerada cheia de números pré-calculados é um instantâneo. Ela parece correta no momento em que é produzida e começa a envelhecer imediatamente: os dados por trás dela avançam, os números dentro dela não, e depois que o arquivo saiu da sua aplicação ninguém consegue dizer quais células podem ser alteradas. Uma pasta de trabalho que carrega suas fórmulas, por outro lado, permanece um documento vivo — edite uma entrada e os totais acompanham.

O Spire.XLS for JavaScript é um mecanismo de planilha compilado para WebAssembly, de modo que um aplicativo React pode criar pastas de trabalho no navegador sem um servidor. Os arquivos são lidos e gravados por meio de um sistema de arquivos virtual (VFS), e as fórmulas são escritas da mesma forma que os valores: através do objeto Range de uma célula. Só o nome da propriedade muda.

Esse último ponto é todo o truque. A pergunta interessante não é como escrever uma fórmula, mas com qual das quatro propriedades disponíveis escrevê-la, porque três delas armazenarão silenciosamente sua fórmula como texto simples.

Para a configuração do projeto, consulte Integrating Spire.XLS for JavaScript in a React Project. Os exemplos abaixo pressupõem que o pacote está instalado e que o módulo WebAssembly foi inicializado.

Por que pastas de trabalho geradas devem conter fórmulas

Gerar um arquivo com as respostas já preenchidas é mais fácil de escrever e pior de receber. Os casos em que isso realmente quebra:

• Modelos com espaços reservados. Espera-se que o destinatário substitua as entradas. Se os totais estiverem codificados diretamente, substituir uma entrada deixa os totais errados e nada o avisa.
• Modelos entregues a um analista. Ele vai querer testar uma premissa diferente. Uma planilha que não pode ser recalculada é uma planilha que ele terá de reconstruir.
• Relatórios que precisam ser rastreáveis. Um número sem uma regra visível por trás dele não pode ser verificado. Uma fórmula pode.
• Planilhas que alimentam outras planilhas. Outras células fazem referência a estas; se o valor nunca for recalculado, tudo o que está a jusante herda a desatualização.

Em todos os quatro casos, a fórmula é o ponto central do arquivo. Os valores são um subproduto.

Pré-requisitos

Você precisa de um projeto React com o Spire.XLS for JavaScript instalado e o módulo WebAssembly inicializado, acessível em window.wasmModule.spirexls. O exemplo abaixo também carrega uma fonte no VFS antes de formatar qualquer texto e salva com o sinalizador da versão Excel 2010 para que a saída abra sem problemas tanto no Excel atual quanto em versões mais antigas.

Escolhendo a propriedade que escreve uma fórmula

Cada célula em que você escreve é um objeto Range, e ele expõe quatro propriedades que aceitam algo. Elas não são intercambiáveis:

Text é a propriedade com que se deve ter cuidado, e vale a pena entender por quê antes do código abaixo. Atribua =SUM(B1:F1) a Text e a célula armazenará esses caracteres — ela exibirá a fórmula para sempre, porque nada jamais a avaliará.

Esse comportamento não é um defeito. É exatamente o que o exemplo usa deliberadamente, para que cada linha possa mostrar a fórmula à esquerda e seu resultado à direita: a célula da esquerda usa Text porque sua finalidade é exibir a regra, e a célula da direita usa Formula porque sua finalidade é aplicá-la.

Escrevendo fórmulas nas células

O fluxo é curto:

1. Crie um objeto Workbook.
2. Obtenha uma planilha com o método Workbook.Worksheets.get().
3. Escreva os dados de entrada nas células e defina a formatação das células.
4. Atribua fórmulas às células que devem calcular, por meio da propriedade Range.Formula.
5. Salve a pasta de trabalho com Workbook.SaveToFile().

O exemplo cria uma pequena planilha com uma linha de números de entrada e, em seguida, escreve cinco fórmulas abaixo dela — uma expressão aritmética, uma função de data, uma função trigonométrica, uma média e uma soma:

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;

Insira fórmulas e resultados de funções em planilhas do Excel

Observe a chamada de formatação antes das fórmulas. Range.get() aceita lastRow e lastColumn, então um bloco de cabeçalho pode ser estilizado em uma única chamada em vez de célula por célula — o mesmo objeto que você usa para escrever uma fórmula também carrega o estilo.

Funções por categoria

As cinco fórmulas do exemplo não são cinco técnicas diferentes. São uma única técnica aplicada a cinco tipos de expressão:

Não existe uma API separada para "funções". Uma função é uma fórmula — Range.Formula recebe a string, e o mecanismo decide o que fazer com ela. É por isso que o catálogo de coisas que você pode escrever é tão grande quanto a lista de funções do mecanismo de planilha, sem nenhum wrapper a manter por função.

Exibindo o texto da fórmula ao lado do seu resultado

Um dos hábitos mais úteis em uma planilha gerada é manter a regra visível ao lado da sua saída. O exemplo faz isso colocando a string da fórmula na coluna A como texto literal e o valor avaliado na coluna 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;

Atribuir "@" como formato de número primeiro é o que impede a coluna de rótulos de tentar interpretar a string — a célula é declarada como texto antes que qualquer coisa seja escrita nela. A coluna de resultados não precisa desse cuidado, mas pode precisar de um formato de exibição próprio: a linha da data define .Style.NumberFormat = "YYYY/MM/DD", sem o qual o valor é renderizado como um número de série em vez de uma data.

Uma planilha que carrega suas próprias regras assim sobrevive a cada ida e volta, porque os rótulos são texto simples que nenhum mecanismo vai tocar.

Uma fórmula em todo um intervalo

Planilhas reais raramente precisam de uma única fórmula; elas precisam da mesma regra ao longo de uma coluna. Como você é quem monta a string, você controla as referências explicitamente:

// 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}`;
}

Esse é o mesmo comportamento de referência relativa que você obteria arrastando uma fórmula para baixo no Excel, escrito por extenso. Se a regra deve sempre apontar para uma entrada fixa, fixe-a — $A$1 não muda quando a fórmula se move, enquanto A1 muda.

Sintaxe de fórmulas que costuma causar problemas

• O sinal de igual no início. Uma string de fórmula sem = não é uma fórmula. Ela será armazenada como texto e nunca avaliada.
• Referências relativas versus absolutas. A1 muda; $A$1 não. Escolha deliberadamente ao gerar fórmulas em um laço.
• Referências entre planilhas. Nomeie a planilha dentro da string — Sheet2!A1. Se o nome da planilha contiver espaços, coloque-o entre aspas: 'Q1 Sales'!A1.
• Separadores de argumentos entre localidades. A string é armazenada como você a escreve. Mantenha a forma separada por vírgulas usada acima se o arquivo for aberto em uma combinação de localidades, nas quais algumas exibem ponto e vírgula.
• Funções voláteis. TODAY() e NOW() mudam sempre que a pasta de trabalho é recalculada, então um valor lido depois não corresponderá ao que você viu. Essa lacuna entre uma regra e seu último valor calculado merece ser conhecida por si só — é disso que trata Reading and Extracting Excel Formulas in JavaScript (React).

Problemas comuns

A célula exibe a fórmula em vez do resultado. Ela foi escrita por meio de Text em vez de Formula. Reatribua-a com Formula — a célula precisa da regra, não dos caracteres.

Uma data aparece como um número de cinco dígitos. Esse é o valor de série sem nenhum formato de data aplicado. Defina .Style.NumberFormat na célula, como o exemplo faz para a linha de TODAY().

A formatação recai sobre células que eu não pretendia tocar. Verifique o intervalo que você passou para Range.get(). Informar lastRow e lastColumn aplica a alteração a um bloco, o que é conveniente para um cabeçalho e fácil de delimitar incorretamente.

A fórmula é armazenada, mas a célula parece vazia quando lida de volta. Os resultados aparecem depois que a pasta de trabalho é calculada. Salve após escrever as fórmulas para que os valores calculados viajem junto com o arquivo.

Perguntas frequentes

Preciso do Excel ou do Office instalado para escrever fórmulas?

Não. O mecanismo de planilha é fornecido com o pacote e é executado como WebAssembly no navegador. Nada é automatizado e nada é necessário na máquina do usuário.

Uma fórmula pode fazer referência a uma planilha diferente na mesma pasta de trabalho?

Sim, e você a escreve exatamente como faria no Excel — inclua o nome da planilha na string da fórmula.

Posso misturar fórmulas e valores simples em uma mesma planilha?

Sim, e normalmente você fará isso. As propriedades são independentes: algumas células recebem dados por meio de NumberValue ou Value, outras recebem regras por meio de Formula.

O que acontece com os resultados quando o destinatário abre o arquivo?

As fórmulas são armazenadas, e o Excel recalcula quando a pasta de trabalho é aberta. Esse é o objetivo de escrever regras em vez de resultados — o arquivo permanece correto mesmo que as entradas sejam editadas depois.

Escrever fórmulas exige um backend?

Não. A pasta de trabalho é criada no navegador e retornada como bytes que você transforma em um Blob para download. Nada é enviado.

Veja também

• Reading and Extracting Excel Formulas in JavaScript (React)
• Integrating Spire.XLS for JavaScript in a React Project
• Create Named Ranges in React with JavaScript
• Spire.XLS for JavaScript Product Page

미리 계산된 숫자로 가득 찬 생성된 스프레드시트는 스냅샷에 불과합니다. 생성되는 순간에는 올바르게 보이지만 즉시 낡아가기 시작합니다. 그 뒤의 데이터는 계속 변하는데 그 안의 숫자는 변하지 않으며, 파일이 애플리케이션을 떠난 뒤에는 어떤 셀을 변경해도 되는지 아무도 알 수 없습니다. 대신 수식을 함께 담은 통합 문서는 살아 있는 문서로 남습니다. 입력값을 편집하면 합계가 따라옵니다.

Spire.XLS for JavaScript는 WebAssembly로 컴파일된 스프레드시트 엔진이므로 React 앱이 서버 없이 브라우저에서 통합 문서를 만들 수 있습니다. 파일은 가상 파일 시스템(VFS)을 통해 읽고 쓰며, 수식은 값과 같은 방식으로, 즉 셀의 Range 객체를 통해 작성됩니다. 달라지는 것은 속성 이름뿐입니다.

마지막 요점이 바로 핵심입니다. 흥미로운 질문은 수식을 작성하는 방법이 아니라 사용 가능한 네 가지 속성 중 어느 것으로 작성할지입니다. 그중 세 가지는 여러분의 수식을 조용히 일반 텍스트로 저장하기 때문입니다.

프로젝트 설정은 React 프로젝트에 Spire.XLS for JavaScript 통합하기를 참조하세요. 아래 예제는 패키지가 설치되어 있고 WebAssembly 모듈이 초기화되었다고 가정합니다.

생성된 통합 문서에 수식이 포함되어야 하는 이유

정답이 이미 채워진 파일을 생성하는 것이 작성하기는 더 쉽지만, 받는 입장에서는 더 나쁩니다. 실제로 문제가 되는 경우는 다음과 같습니다.

• 자리 표시자가 있는 템플릿. 받는 사람이 입력값을 교체할 것으로 예상됩니다. 합계가 하드코딩되어 있으면 입력값을 교체해도 합계는 잘못된 상태로 남고, 아무런 경고도 표시되지 않습니다.
• 분석가에게 전달되는 모델. 그들은 다른 가정을 테스트하고 싶어 할 것입니다. 다시 도출할 수 없는 시트는 다시 만들어야 하는 시트입니다.
• 추적 가능해야 하는 보고서. 뒤에 보이는 규칙이 없는 숫자는 검증할 수 없습니다. 수식은 검증할 수 있습니다.
• 다른 워크시트에 데이터를 공급하는 워크시트. 다른 셀들이 이를 참조합니다. 값이 다시 계산되지 않으면 다운스트림의 모든 것이 그 오래된 값을 물려받습니다.

네 가지 경우 모두에서 수식이 파일의 핵심입니다. 값은 부산물일 뿐입니다.

사전 요구 사항

Spire.XLS for JavaScript가 설치되어 있고 WebAssembly 모듈이 초기화되어 window.wasmModule.spirexls에서 접근 가능한 React 프로젝트가 필요합니다. 아래 샘플은 텍스트 서식을 지정하기 전에 VFS에 글꼴을 로드하고, 최신 Excel과 이전 버전 모두에서 문제없이 열리도록 Excel 2010 버전 플래그로 저장합니다.

수식을 작성하는 속성 선택

작성하는 모든 셀은 Range 객체이며, 값을 받는 네 가지 속성을 노출합니다. 이들은 서로 호환되지 않습니다.

Text는 주의해야 할 속성이며, 아래 코드를 보기 전에 그 이유를 이해할 가치가 있습니다. =SUM(B1:F1)을 Text에 할당하면 셀은 그 문자들을 저장합니다. 아무것도 이를 평가하지 않으므로 수식이 영원히 표시됩니다.

이 동작은 결함이 아닙니다. 샘플이 의도적으로 사용하는 바로 그 동작으로, 각 행이 왼쪽에 수식을, 오른쪽에 결과를 표시할 수 있게 합니다. 왼쪽 셀은 규칙을 표시하기 위한 것이므로 Text를 사용하고, 오른쪽 셀은 규칙을 적용하기 위한 것이므로 Formula를 사용합니다.

셀에 수식 작성하기

흐름은 짧습니다.

1. Workbook 객체를 생성합니다.
2. Workbook.Worksheets.get() 메서드로 워크시트를 가져옵니다.
3. 입력 데이터를 셀에 쓰고 셀 서식을 설정합니다.
4. 계산해야 하는 셀에 Range.Formula 속성을 통해 수식을 할당합니다.
5. Workbook.SaveToFile()로 통합 문서를 저장합니다.

이 예제는 입력 숫자 행이 있는 작은 시트를 만들고 그 아래에 다섯 개의 수식(산술 식, 날짜 함수, 삼각 함수, 평균, 합계)을 작성합니다.

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;

Excel 워크시트에 수식과 함수 결과 삽입

수식 앞의 서식 지정 호출에 주목하세요. Range.get()은 lastRow와 lastColumn을 받으므로, 헤더 블록을 셀 단위가 아니라 한 번의 호출로 스타일링할 수 있습니다. 수식을 작성하는 데 사용하는 동일한 객체가 스타일도 함께 갖습니다.

범주별 함수

샘플의 다섯 수식은 다섯 가지 기법이 아닙니다. 하나의 기법을 다섯 종류의 식에 적용한 것입니다.

"함수"를 위한 별도의 API는 없습니다. 함수는 곧 수식입니다. Range.Formula가 문자열을 받고 엔진이 이를 어떻게 처리할지 결정합니다. 그래서 작성할 수 있는 것들의 목록은 스프레드시트 엔진의 함수 목록만큼 방대하며, 함수마다 유지 관리할 래퍼가 없습니다.

결과 옆에 수식 텍스트 표시하기

생성된 워크시트에서 유용한 습관 중 하나는 규칙을 그 결과 옆에 보이게 유지하는 것입니다. 샘플은 수식 문자열을 A열에 리터럴 텍스트로, 평가된 값을 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;

먼저 숫자 서식으로 "@"를 할당하는 것이 레이블 열이 문자열을 해석하려 들지 않게 하는 방법입니다. 즉, 아무것도 쓰기 전에 셀이 텍스트로 선언됩니다. 결과 열에는 그런 주의가 필요 없지만, 자체 표시 서식이 필요할 수 있습니다. 날짜 행은 .Style.NumberFormat = "YYYY/MM/DD"를 설정하는데, 이것이 없으면 값이 날짜가 아닌 일련 번호로 표시됩니다.

이렇게 자체 규칙을 포함한 시트는 레이블이 어떤 엔진도 건드리지 않는 일반 텍스트이므로 모든 왕복 과정을 견뎌냅니다.

범위 전체에 하나의 수식 적용하기

실제 워크시트에는 하나의 수식이 필요한 경우가 드물고, 열을 따라 내려가는 동일한 규칙이 필요합니다. 문자열을 직접 만들기 때문에 참조를 명시적으로 제어할 수 있습니다.

// 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}`;
}

이는 Excel에서 수식을 아래로 끌어 복사할 때 얻는 것과 동일한 상대 참조 동작을 풀어 쓴 것입니다. 규칙이 항상 하나의 고정된 입력을 가리켜야 한다면 고정하세요. 수식이 이동해도 $A$1은 변하지 않지만 A1은 변합니다.

사람들이 자주 실수하는 수식 구문

• 선행 등호. 수식 문자열에 =가 없으면 수식이 아닙니다. 텍스트로 저장되고 절대 평가되지 않습니다.
• 상대 참조와 절대 참조. A1은 이동하고 $A$1은 이동하지 않습니다. 루프에서 수식을 생성할 때는 의도적으로 선택하세요.
• 시트 간 참조. 문자열 안에 시트 이름을 지정하세요 — Sheet2!A1. 시트 이름에 공백이 있으면 따옴표로 묶으세요: 'Q1 Sales'!A1.
• 로캘별 인수 구분 기호. 문자열은 작성한 그대로 저장됩니다. 일부 로캘에서 세미콜론으로 표시되는 경우가 있으므로, 파일이 여러 로캘에서 열릴 가능성이 있다면 위에서 사용한 쉼표 구분 형식을 유지하세요.
• 휘발성 함수. TODAY()와 NOW()는 통합 문서가 재계산될 때마다 변경되므로, 나중에 다시 읽은 값이 앞서 본 값과 일치하지 않습니다. 규칙과 마지막 계산 값 사이의 이 간극은 그 자체로 알아둘 가치가 있습니다. 바로 JavaScript(React)에서 Excel 수식 읽기 및 추출에서 다루는 내용입니다.

일반적인 문제

셀에 결과 대신 수식이 표시됩니다. Formula가 아닌 Text를 통해 작성된 것입니다. Formula로 다시 할당하세요. 셀에는 문자가 아니라 규칙이 필요합니다.

날짜가 다섯 자리 숫자로 표시됩니다. 날짜 서식이 적용되지 않은 일련 번호 값입니다. 샘플이 TODAY() 행에 하는 것처럼 셀에 .Style.NumberFormat을 설정하세요.

서식이 의도하지 않은 셀에 적용됩니다. Range.get()에 전달한 범위를 확인하세요. lastRow와 lastColumn을 지정하면 변경 사항이 블록 전체에 적용되는데, 헤더에는 편리하지만 범위를 잘못 잡기 쉽습니다.

수식은 저장되었지만 다시 읽을 때 셀이 비어 있는 것처럼 보입니다. 결과는 통합 문서가 계산된 후에 나타납니다. 계산된 값이 파일과 함께 저장되도록 수식을 작성한 뒤 저장하세요.

자주 묻는 질문

수식을 작성하려면 Excel이나 Office가 설치되어 있어야 하나요?

아니요. 스프레드시트 엔진은 패키지에 포함되어 있으며 브라우저에서 WebAssembly로 실행됩니다. 자동화되는 것도 없고 사용자 컴퓨터에 필요한 것도 없습니다.

수식이 같은 통합 문서의 다른 워크시트를 참조할 수 있나요?

예, Excel에서 하는 것과 정확히 동일하게 작성하면 됩니다. 수식 문자열에 시트 이름을 포함하세요.

하나의 시트에서 수식과 일반 값을 섞어 쓸 수 있나요?

예, 보통 그렇게 하게 됩니다. 속성들은 서로 독립적입니다. 일부 셀은 NumberValue나 Value를 통해 데이터를 받고, 다른 셀은 Formula를 통해 규칙을 받습니다.

받는 사람이 파일을 열면 결과는 어떻게 되나요?

수식이 저장되어 있고, 통합 문서를 열 때 Excel이 다시 계산합니다. 결과가 아닌 규칙을 작성하는 이유가 바로 이것입니다. 나중에 입력값을 편집해도 파일은 올바른 상태로 유지됩니다.

수식을 작성하려면 백엔드가 필요한가요?

아니요. 통합 문서는 브라우저에서 만들어지고, 다운로드용 Blob으로 변환하는 바이트로 반환됩니다. 아무것도 업로드되지 않습니다.

참고 항목

• JavaScript(React)에서 Excel 수식 읽기 및 추출
• React 프로젝트에 Spire.XLS for JavaScript 통합하기
• JavaScript로 React에서 명명된 범위 만들기
• Spire.XLS for JavaScript 제품 페이지

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

Une feuille de calcul générée remplie de nombres pré-calculés n'est qu'un instantané. Elle a l'air correcte au moment où elle est produite et commence à vieillir immédiatement : les données qui la sous-tendent évoluent, les nombres qu'elle contient non, et une fois que le fichier a quitté votre application, personne ne peut dire quelles cellules il est autorisé à modifier. Un classeur qui contient ses formules reste au contraire un document vivant — modifiez une entrée, et les totaux suivent.

Spire.XLS for JavaScript est un moteur de feuille de calcul compilé en WebAssembly, ce qui permet à une application React de créer des classeurs dans le navigateur sans serveur. Les fichiers sont lus et écrits via un système de fichiers virtuel (VFS), et les formules s'écrivent de la même manière que les valeurs : via l'objet Range d'une cellule. Seul le nom de la propriété change.

Ce dernier point est toute l'astuce. La question intéressante n'est pas comment écrire une formule, mais avec laquelle des quatre propriétés disponibles l'écrire, car trois d'entre elles stockeront discrètement votre formule sous forme de texte brut.

Pour la configuration du projet, consultez Intégrer Spire.XLS for JavaScript dans un projet React. Les exemples ci-dessous supposent que le package est installé et que le module WebAssembly a été initialisé.

Pourquoi les classeurs générés doivent contenir des formules

Générer un fichier avec les réponses déjà remplies est plus facile à écrire et pire à recevoir. Les cas où cela pose réellement problème :

• Modèles avec espaces réservés. Le destinataire est censé remplacer les entrées. Si les totaux sont codés en dur, remplacer une entrée laisse les totaux erronés sans que rien ne l'avertisse.
• Modèles remis à un analyste. Il voudra tester une hypothèse différente. Une feuille qui ne peut pas être recalculée est une feuille qu'il devra reconstruire.
• Rapports qui doivent être traçables. Un nombre sans règle visible derrière lui ne peut pas être vérifié. Une formule, si.
• Feuilles de calcul qui alimentent d'autres feuilles. D'autres cellules s'y réfèrent ; si la valeur n'est jamais recalculée, tout ce qui en dépend hérite de l'obsolescence.

Dans ces quatre cas, la formule est l'objet même du fichier. Les valeurs ne sont qu'un sous-produit.

Prérequis

Vous avez besoin d'un projet React avec Spire.XLS for JavaScript installé et le module WebAssembly initialisé, accessible à window.wasmModule.spirexls. L'exemple ci-dessous charge également une police dans le VFS avant de formater le moindre texte, et enregistre avec l'indicateur de version Excel 2010 afin que le résultat s'ouvre proprement dans Excel actuel comme dans les versions plus anciennes.

Choisir la propriété qui écrit une formule

Chaque cellule dans laquelle vous écrivez est un objet Range, et il expose quatre propriétés qui acceptent quelque chose. Elles ne sont pas interchangeables :

Text est celle avec laquelle il faut être prudent, et il vaut la peine de comprendre pourquoi avant le code ci-dessous. Affectez =SUM(B1:F1) à Text et la cellule stocke ces caractères — elle affichera la formule pour toujours, car rien ne l'évaluera jamais.

Ce comportement n'est pas un défaut. C'est exactement ce que l'exemple utilise délibérément, afin que chaque ligne puisse afficher la formule à gauche et son résultat à droite : la cellule de gauche utilise Text parce qu'elle est destinée à afficher la règle, et la cellule de droite utilise Formula parce qu'elle est destinée à l'appliquer.

Écrire des formules dans des cellules

Le déroulement est court :

1. Créer un objet Workbook.
2. Obtenir une feuille de calcul avec la méthode Workbook.Worksheets.get().
3. Écrire les données d'entrée dans les cellules et définir la mise en forme des cellules.
4. Affecter des formules aux cellules qui doivent calculer, via la propriété Range.Formula.
5. Enregistrer le classeur avec Workbook.SaveToFile().

L'exemple construit une petite feuille avec une ligne de nombres d'entrée, puis écrit cinq formules en dessous — une expression arithmétique, une fonction de date, une fonction trigonométrique, une moyenne et une somme :

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;

Insérer des formules et des résultats de fonctions dans des feuilles de calcul Excel

Notez l'appel de mise en forme avant les formules. Range.get() accepte lastRow et lastColumn, de sorte qu'un bloc d'en-tête peut être mis en forme en un seul appel plutôt que cellule par cellule — le même objet que vous utilisez pour écrire une formule porte aussi le style.

Fonctions par catégorie

Les cinq formules de l'exemple ne sont pas cinq techniques différentes. C'est une seule technique appliquée à cinq types d'expression :

Il n'existe pas d'API distincte pour les « fonctions ». Une fonction est une formule — Range.Formula reçoit la chaîne, et le moteur décide quoi en faire. C'est pourquoi le catalogue de ce que vous pouvez écrire est aussi vaste que la liste de fonctions du moteur de feuille de calcul, sans aucun wrapper à maintenir par fonction.

Afficher le texte de la formule à côté de son résultat

L'une des habitudes les plus utiles dans une feuille de calcul générée est de garder la règle visible à côté de son résultat. L'exemple le fait en plaçant la chaîne de formule dans la colonne A sous forme de texte littéral et la valeur évaluée dans la colonne 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;

Affecter d'abord "@" comme format de nombre est ce qui empêche la colonne d'étiquettes d'essayer d'interpréter la chaîne — la cellule est déclarée comme texte avant que quoi que ce soit y soit écrit. La colonne de résultats n'a pas besoin d'un tel soin, mais elle peut avoir besoin de son propre format d'affichage : la ligne de date définit .Style.NumberFormat = "YYYY/MM/DD", sans quoi la valeur s'affiche comme un numéro de série plutôt que comme une date.

Une feuille qui porte ainsi ses propres règles survit à chaque aller-retour, car les étiquettes sont du texte brut que nul moteur ne touchera.

Une seule formule sur toute une plage

Les vraies feuilles de calcul ont rarement besoin d'une seule formule ; elles ont besoin de la même règle sur toute une colonne. Comme vous construisez la chaîne, vous contrôlez explicitement les références :

// 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}`;
}

C'est le même comportement de référence relative que celui obtenu en faisant glisser une formule vers le bas dans Excel, écrit en toutes lettres. Si la règle doit toujours pointer vers une entrée fixe unique, figez-la — $A$1 ne se décale pas lorsque la formule se déplace, tandis que A1 le fait.

La syntaxe des formules qui piège les gens

• Le signe égal initial. Une chaîne de formule sans = n'est pas une formule. Elle sera stockée comme texte et jamais évaluée.
• Références relatives ou absolues. A1 se décale ; $A$1 non. Choisissez délibérément lorsque vous générez des formules en boucle.
• Références inter-feuilles. Nommez la feuille dans la chaîne — Sheet2!A1. Si le nom de la feuille contient des espaces, mettez-le entre guillemets : 'Q1 Sales'!A1.
• Séparateurs d'arguments selon les paramètres régionaux. La chaîne est stockée telle que vous l'écrivez. Conservez la forme séparée par des virgules utilisée ci-dessus si le fichier doit être ouvert dans des paramètres régionaux variés, où certains affichent des points-virgules à la place.
• Fonctions volatiles. TODAY() et NOW() changent à chaque recalcul du classeur, de sorte qu'une valeur relue plus tard ne correspondra pas à celle que vous avez vue. Cet écart entre une règle et sa dernière valeur calculée mérite d'être connu en soi — c'est ce que traite Lire et extraire des formules Excel en JavaScript (React).

Problèmes courants

La cellule affiche la formule au lieu d'un résultat. Elle a été écrite via Text plutôt que Formula. Réaffectez-la avec Formula — la cellule a besoin de la règle, pas des caractères.

Une date s'affiche comme un nombre à cinq chiffres. C'est la valeur de série sans format de date appliqué. Définissez .Style.NumberFormat sur la cellule, comme le fait l'exemple pour la ligne TODAY().

La mise en forme s'applique à des cellules que je ne voulais pas toucher. Vérifiez la plage que vous avez passée à Range.get(). Fournir lastRow et lastColumn applique la modification à un bloc, ce qui est pratique pour un en-tête et facile à mal délimiter.

La formule est stockée mais la cellule semble vide à la relecture. Les résultats apparaissent une fois le classeur calculé. Enregistrez après avoir écrit les formules afin que les valeurs calculées accompagnent le fichier.

FAQ

Ai-je besoin d'Excel ou d'Office installé pour écrire des formules ?

Non. Le moteur de feuille de calcul est fourni avec le package et s'exécute en WebAssembly dans le navigateur. Rien n'est automatisé et rien n'est requis sur la machine de l'utilisateur.

Une formule peut-elle référencer une autre feuille du même classeur ?

Oui, et vous l'écrivez exactement comme dans Excel — incluez le nom de la feuille dans la chaîne de formule.

Puis-je mélanger formules et valeurs simples dans une même feuille ?

Oui, et vous le ferez généralement. Les propriétés sont indépendantes : certaines cellules reçoivent des données via NumberValue ou Value, d'autres reçoivent des règles via Formula.

Qu'advient-il des résultats lorsque le destinataire ouvre le fichier ?

Les formules sont stockées, et Excel recalcule à l'ouverture du classeur. C'est tout l'intérêt d'écrire des règles plutôt que des résultats — le fichier reste correct même si les entrées sont modifiées par la suite.

L'écriture de formules nécessite-t-elle un backend ?

Non. Le classeur est construit dans le navigateur et renvoyé sous forme d'octets que vous transformez en Blob pour le téléchargement. Rien n'est téléversé.

Voir aussi

• Lire et extraire des formules Excel en JavaScript (React)
• Intégrer Spire.XLS for JavaScript dans un projet React
• Créer des plages nommées en React avec JavaScript
• Page produit de Spire.XLS for JavaScript

Una hoja de cálculo generada llena de números ya calculados es una instantánea. Se ve bien en el momento en que se produce y empieza a quedar obsoleta de inmediato: los datos que hay detrás avanzan, los números que contiene no, y una vez que el archivo ha salido de su aplicación nadie puede saber qué celdas tiene permitido modificar. Un libro que en cambio lleva sus fórmulas sigue siendo un documento vivo: edite una entrada y los totales se actualizan.

Spire.XLS for JavaScript es un motor de hojas de cálculo compilado a WebAssembly, por lo que una aplicación React puede crear libros en el navegador sin necesidad de un servidor. Los archivos se leen y se escriben a través de un sistema de archivos virtual (VFS), y las fórmulas se escriben igual que los valores: a través del objeto Range de una celda. Solo cambia el nombre de la propiedad.

Ese último punto es todo el truco. La pregunta interesante no es cómo escribir una fórmula, sino con cuál de las cuatro propiedades disponibles escribirla, porque tres de ellas almacenarán su fórmula silenciosamente como texto sin formato.

Para la configuración del proyecto, consulte Integrating Spire.XLS for JavaScript in a React Project. Los ejemplos a continuación suponen que el paquete está instalado y que el módulo WebAssembly se ha inicializado.

Por qué los libros generados deben incluir fórmulas

Generar un archivo con las respuestas ya rellenadas es más fácil de escribir y peor de recibir. Los casos en los que realmente falla:

• Plantillas con marcadores de posición. Se espera que el destinatario reemplace las entradas. Si los totales están codificados de forma fija, reemplazar una entrada deja los totales incorrectos y nada se lo advierte.
• Modelos entregados a un analista. Querrán probar un supuesto diferente. Una hoja que no se puede volver a derivar es una hoja que tienen que reconstruir.
• Informes que deben ser trazables. Un número sin una regla visible detrás no se puede comprobar. Una fórmula sí.
• Hojas de cálculo que alimentan a otras hojas. Otras celdas hacen referencia a estas; si el valor nunca se recalcula, todo lo que está aguas abajo hereda la obsolescencia.

En los cuatro casos, la fórmula es el objetivo del archivo. Los valores son un subproducto.

Requisitos previos

Necesita un proyecto React con Spire.XLS for JavaScript instalado y el módulo WebAssembly inicializado, accesible en window.wasmModule.spirexls. El ejemplo siguiente también carga una fuente en el VFS antes de dar formato a cualquier texto, y guarda con la marca de versión de Excel 2010 para que la salida se abra sin problemas tanto en el Excel actual como en versiones anteriores.

Elegir la propiedad que escribe una fórmula

Cada celda en la que escribe es un objeto Range, y este expone cuatro propiedades que aceptan algo. No son intercambiables:

Text es la que hay que manejar con cuidado, y vale la pena entender por qué antes del código que aparece a continuación. Asigne =SUM(B1:F1) a Text y la celda almacenará esos caracteres: mostrará la fórmula para siempre, porque nada la va a evaluar nunca.

Ese comportamiento no es un defecto. Es exactamente lo que el ejemplo utiliza de forma deliberada, para que cada fila pueda mostrar la fórmula a la izquierda y su resultado a la derecha: la celda izquierda usa Text porque está pensada para mostrar la regla, y la celda derecha usa Formula porque está pensada para aplicarla.

Escribir fórmulas en las celdas

El flujo es breve:

1. Cree un objeto Workbook.
2. Obtenga una hoja de cálculo con el método Workbook.Worksheets.get().
3. Escriba los datos de entrada en las celdas y establezca el formato de las celdas.
4. Asigne fórmulas a las celdas que deben calcular, a través de la propiedad Range.Formula.
5. Guarde el libro con Workbook.SaveToFile().

El ejemplo crea una pequeña hoja con una fila de números de entrada y luego escribe cinco fórmulas debajo: una expresión aritmética, una función de fecha, una función trigonométrica, un promedio y una suma:

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;

Inserte fórmulas y resultados de funciones en hojas de cálculo de Excel

Observe la llamada de formato antes de las fórmulas. Range.get() acepta lastRow y lastColumn, por lo que se puede dar estilo a un bloque de encabezado en una sola llamada en lugar de celda por celda: el mismo objeto que usa para escribir una fórmula también lleva el estilo.

Funciones por categoría

Las cinco fórmulas del ejemplo no son cinco técnicas diferentes. Son una técnica aplicada a cinco tipos de expresión:

No hay una API separada para las «funciones». Una función es una fórmula: Range.Formula recibe la cadena y el motor decide qué hacer con ella. Por eso el catálogo de cosas que puede escribir es tan amplio como la lista de funciones del motor de hojas de cálculo, sin ningún contenedor que mantener por función.

Mostrar el texto de la fórmula junto a su resultado

Uno de los hábitos más útiles en una hoja de cálculo generada es mantener la regla visible junto a su resultado. El ejemplo lo hace colocando la cadena de la fórmula en la columna A como texto literal y el valor evaluado en la columna 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;

Asignar "@" como formato de número en primer lugar es lo que evita que la columna de etiquetas intente interpretar la cadena: la celda se declara como texto antes de que se escriba nada en ella. La columna de resultados no necesita ese cuidado, pero puede necesitar un formato de visualización propio: la fila de la fecha establece .Style.NumberFormat = "YYYY/MM/DD", sin lo cual el valor se muestra como un número de serie en lugar de una fecha.

Una hoja que lleva sus propias reglas de esta manera sobrevive a todos los traslados, porque las etiquetas son texto sin formato que ningún motor tocará.

Una fórmula en todo un rango

Las hojas de cálculo reales rara vez necesitan una sola fórmula; necesitan la misma regla a lo largo de una columna. Como usted construye la cadena, controla las referencias de forma explícita:

// 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}`;
}

Ese es el mismo comportamiento de referencia relativa que obtendría al arrastrar una fórmula hacia abajo en Excel, escrito de forma desarrollada. Si la regla debe apuntar siempre a una entrada fija, fíjela: $A$1 no se desplaza cuando la fórmula se mueve, mientras que A1 sí lo hace.

Sintaxis de fórmulas que suele causar problemas

• El signo igual inicial. Una cadena de fórmula sin = no es una fórmula. Se almacenará como texto y nunca se evaluará.
• Referencias relativas frente a absolutas. A1 se desplaza; $A$1 no. Elija de forma deliberada cuando genere fórmulas en un bucle.
• Referencias entre hojas. Nombre la hoja dentro de la cadena: Sheet2!A1. Si el nombre de la hoja contiene espacios, entrecomíllelo: 'Q1 Sales'!A1.
• Separadores de argumentos según la configuración regional. La cadena se almacena tal como usted la escribe. Mantenga la forma separada por comas usada arriba si el archivo se abrirá en una combinación de configuraciones regionales, donde algunas muestran puntos y coma en su lugar.
• Funciones volátiles. TODAY() y NOW() cambian cada vez que el libro se recalcula, por lo que un valor leído más tarde no coincidirá con el que vio. Esa diferencia entre una regla y su último valor calculado merece conocerse por sí misma: es de lo que trata Reading and Extracting Excel Formulas in JavaScript (React).

Problemas comunes

La celda muestra la fórmula en lugar de un resultado. Se escribió a través de Text en lugar de Formula. Reasígnela con Formula: la celda necesita la regla, no los caracteres.

Una fecha aparece como un número de cinco dígitos. Ese es el valor de serie sin ningún formato de fecha aplicado. Establezca .Style.NumberFormat en la celda, como hace el ejemplo para la fila de TODAY().

El formato se aplica a celdas que no pretendía tocar. Compruebe el rango que pasó a Range.get(). Suministrar lastRow y lastColumn aplica el cambio a un bloque, lo cual es cómodo para un encabezado y fácil de delimitar mal.

La fórmula se almacena pero la celda parece vacía al volver a leerla. Los resultados aparecen una vez que el libro se ha calculado. Guarde después de escribir las fórmulas para que los valores calculados viajen con el archivo.

Preguntas frecuentes

¿Necesito tener Excel u Office instalado para escribir fórmulas?

No. El motor de hojas de cálculo viene incluido con el paquete y se ejecuta como WebAssembly en el navegador. No se automatiza nada ni se requiere nada en el equipo del usuario.

¿Puede una fórmula hacer referencia a una hoja de cálculo diferente del mismo libro?

Sí, y la escribe exactamente como lo haría en Excel: incluya el nombre de la hoja en la cadena de la fórmula.

¿Puedo mezclar fórmulas y valores simples en una misma hoja?

Sí, y normalmente lo hará. Las propiedades son independientes: algunas celdas reciben datos a través de NumberValue o Value, y otras reciben reglas a través de Formula.

¿Qué ocurre con los resultados cuando el destinatario abre el archivo?

Las fórmulas se almacenan y Excel recalcula cuando se abre el libro. Ese es el objetivo de escribir reglas en lugar de resultados: el archivo sigue siendo correcto aunque las entradas se editen después.

¿Escribir fórmulas requiere un backend?

No. El libro se crea en el navegador y se devuelve como bytes que usted convierte en un Blob para su descarga. No se sube nada.

Ver también

• Reading and Extracting Excel Formulas in JavaScript (React)
• Integrating Spire.XLS for JavaScript in a React Project
• Create Named Ranges in React with JavaScript
• Spire.XLS for JavaScript Product Page