Comment insérer des formules et fonctions Excel en JavaScript (React)

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