So fügen Sie Excel-Formeln und -Funktionen in JavaScript (React) ein

Eine generierte Tabellenkalkulation voller vorberechneter Zahlen ist eine Momentaufnahme. Sie sieht in dem Moment richtig aus, in dem sie erzeugt wird, und beginnt sofort zu veralten: Die Daten dahinter verändern sich weiter, die Zahlen darin nicht, und sobald die Datei Ihre Anwendung verlassen hat, kann niemand mehr sagen, welche Zellen geändert werden dürfen. Eine Arbeitsmappe, die ihre Formeln mitführt, bleibt dagegen ein lebendiges Dokument – ändern Sie eine Eingabe, und die Summen folgen.

Spire.XLS für JavaScript ist eine nach WebAssembly kompilierte Tabellenkalkulations-Engine, sodass eine React-App Arbeitsmappen im Browser ohne Server erstellen kann. Dateien werden über ein virtuelles Dateisystem (VFS) gelesen und geschrieben, und Formeln werden genauso geschrieben wie Werte: über das Range-Objekt einer Zelle. Nur der Name der Eigenschaft ändert sich.

Genau dieser letzte Punkt ist der ganze Trick. Die interessante Frage ist nicht, wie man eine Formel schreibt, sondern mit welcher der vier verfügbaren Eigenschaften man sie schreibt, denn drei davon speichern Ihre Formel still und leise als reinen Text.

Zur Projekteinrichtung siehe Integrating Spire.XLS for JavaScript in a React Project. Die folgenden Beispiele setzen voraus, dass das Paket installiert und das WebAssembly-Modul initialisiert ist.

Warum generierte Arbeitsmappen Formeln enthalten sollten

Eine Datei zu generieren, in der die Antworten bereits eingetragen sind, ist einfacher zu schreiben und schlechter zu erhalten. Die Fälle, in denen das tatsächlich zum Problem wird:

• Vorlagen mit Platzhaltern. Vom Empfänger wird erwartet, dass er die Eingaben ersetzt. Sind die Summen fest einprogrammiert, bleiben nach dem Ersetzen einer Eingabe die Summen falsch, und nichts warnt ihn davor.
• Modelle, die einem Analysten übergeben werden. Er möchte eine andere Annahme testen. Eine Tabelle, die sich nicht neu herleiten lässt, muss neu aufgebaut werden.
• Berichte, die nachvollziehbar sein müssen. Eine Zahl ohne sichtbare Regel dahinter lässt sich nicht prüfen. Eine Formel schon.
• Arbeitsblätter, die andere Arbeitsblätter speisen. Andere Zellen verweisen darauf; wenn der Wert nie neu berechnet wird, erbt alles Nachgelagerte die Veraltung.

In allen vier Fällen ist die Formel der Zweck der Datei. Die Werte sind ein Nebenprodukt.

Voraussetzungen

Sie benötigen ein React-Projekt mit installiertem Spire.XLS für JavaScript und initialisiertem WebAssembly-Modul, erreichbar unter window.wasmModule.spirexls. Das folgende Beispiel lädt außerdem eine Schriftart in das VFS, bevor Text formatiert wird, und speichert mit dem Versions-Flag für Excel 2010, damit die Ausgabe sowohl in aktuellem Excel als auch in älteren Versionen sauber geöffnet wird.

Die passende Eigenschaft zum Schreiben einer Formel wählen

Jede Zelle, in die Sie schreiben, ist ein Range-Objekt, und dieses bietet vier Eigenschaften, die etwas entgegennehmen. Sie sind nicht austauschbar:

Text ist diejenige, bei der man aufpassen muss, und es lohnt sich zu verstehen, warum, noch bevor man sich den folgenden Code ansieht. Weisen Sie =SUM(B1:F1) Text zu, speichert die Zelle genau diese Zeichen – sie zeigt die Formel für immer an, weil nichts sie jemals auswerten wird.

Dieses Verhalten ist kein Defekt. Es ist genau das, was das Beispiel bewusst nutzt, damit jede Zeile links die Formel und rechts ihr Ergebnis anzeigen kann: Die linke Zelle verwendet Text, weil sie die Regel anzeigen soll, und die rechte Zelle verwendet Formula, weil sie sie anwenden soll.

Formeln in Zellen schreiben

Der Ablauf ist kurz:

1. Ein Workbook-Objekt erstellen.
2. Ein Arbeitsblatt mit der Methode Workbook.Worksheets.get() abrufen.
3. Die Eingabedaten in Zellen schreiben und die Zellformatierung festlegen.
4. Formeln über die Eigenschaft Range.Formula den Zellen zuweisen, die berechnen sollen.
5. Die Arbeitsmappe mit Workbook.SaveToFile() speichern.

Das Beispiel erstellt ein kleines Blatt mit einer Zeile aus Eingabezahlen und schreibt darunter fünf Formeln – einen arithmetischen Ausdruck, eine Datumsfunktion, eine trigonometrische Funktion, einen Mittelwert und eine Summe:

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;

Formeln und Funktionsergebnisse in Excel-Arbeitsblätter einfügen

Beachten Sie den Formatierungsaufruf vor den Formeln. Range.get() akzeptiert lastRow und lastColumn, sodass ein Kopfzeilenblock mit einem einzigen Aufruf formatiert werden kann statt Zelle für Zelle – dasselbe Objekt, mit dem Sie eine Formel schreiben, trägt auch das Format.

Funktionen nach Kategorie

Die fünf Formeln im Beispiel sind nicht fünf verschiedene Techniken. Sie sind eine Technik, angewandt auf fünf Arten von Ausdrücken:

Es gibt keine separate API für "Funktionen". Eine Funktion ist eine Formel – Range.Formula nimmt die Zeichenkette entgegen, und die Engine entscheidet, was damit geschieht. Deshalb ist der Katalog der Dinge, die Sie schreiben können, so groß wie die Funktionsliste der Tabellenkalkulations-Engine, ohne dass pro Funktion ein Wrapper gepflegt werden muss.

Den Formeltext neben seinem Ergebnis anzeigen

Eine der nützlicheren Gewohnheiten in einem generierten Arbeitsblatt ist, die Regel neben ihrer Ausgabe sichtbar zu halten. Das Beispiel tut dies, indem es die Formelzeichenkette in Spalte A als wörtlichen Text und den ausgewerteten Wert in Spalte B platziert:

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

Dass zuerst "@" als Zahlenformat zugewiesen wird, verhindert, dass die Beschriftungsspalte versucht, die Zeichenkette zu interpretieren – die Zelle wird als Text deklariert, bevor etwas hineingeschrieben wird. Die Ergebnisspalte braucht diese Vorsicht nicht, aber möglicherweise ein eigenes Anzeigeformat: Die Datumszeile setzt .Style.NumberFormat = "YYYY/MM/DD", ohne das der Wert als Seriennummer statt als Datum dargestellt wird.

Ein Blatt, das seine eigenen Regeln auf diese Weise mitführt, übersteht jeden Roundtrip, denn die Beschriftungen sind reiner Text, den keine Engine anfassen wird.

Eine Formel über einen Bereich

Echte Arbeitsblätter brauchen selten nur eine Formel; sie brauchen dieselbe Regel über eine ganze Spalte. Da Sie die Zeichenkette selbst aufbauen, steuern Sie die Bezüge explizit:

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

Das ist dasselbe Verhalten relativer Bezüge, das Sie erhalten würden, wenn Sie in Excel eine Formel nach unten ziehen – nur ausgeschrieben. Soll die Regel immer auf eine feste Eingabe verweisen, binden Sie sie: $A$1 verschiebt sich nicht, wenn die Formel wandert, A1 hingegen schon.

Formelsyntax, über die Menschen stolpern

• Das führende Gleichheitszeichen. Eine Formelzeichenkette ohne = ist keine Formel. Sie wird als Text gespeichert und nie ausgewertet.
• Relative versus absolute Bezüge. A1 verschiebt sich; $A$1 nicht. Wählen Sie bewusst, wenn Sie Formeln in einer Schleife generieren.
• Blattübergreifende Bezüge. Nennen Sie das Blatt innerhalb der Zeichenkette – Sheet2!A1. Enthält der Blattname Leerzeichen, setzen Sie ihn in Anführungszeichen: 'Q1 Sales'!A1.
• Argumenttrennzeichen je nach Locale. Die Zeichenkette wird genau so gespeichert, wie Sie sie schreiben. Behalten Sie die oben verwendete kommagetrennte Form bei, wenn die Datei in verschiedenen Locales geöffnet wird, von denen einige stattdessen Semikolons anzeigen.
• Flüchtige Funktionen. TODAY() und NOW() ändern sich, wann immer die Arbeitsmappe neu berechnet wird, sodass ein später ausgelesener Wert nicht mit dem übereinstimmt, den Sie gesehen haben. Diese Lücke zwischen einer Regel und ihrem zuletzt berechneten Wert ist für sich genommen wissenswert – damit befasst sich Reading and Extracting Excel Formulas in JavaScript (React).

Häufige Probleme

Die Zelle zeigt die Formel statt eines Ergebnisses an. Sie wurde über Text statt über Formula geschrieben. Weisen Sie sie erneut mit Formula zu – die Zelle braucht die Regel, nicht die Zeichen.

Ein Datum erscheint als fünfstellige Zahl. Das ist der Serienwert ohne angewandtes Datumsformat. Setzen Sie .Style.NumberFormat für die Zelle, wie es das Beispiel für die TODAY()-Zeile tut.

Die Formatierung landet auf Zellen, die ich gar nicht ändern wollte. Prüfen Sie den Bereich, den Sie an Range.get() übergeben haben. Die Angabe von lastRow und lastColumn wendet die Änderung auf einen Block an, was für eine Kopfzeile praktisch und leicht falsch abzugrenzen ist.

Die Formel wird gespeichert, aber die Zelle wirkt beim Auslesen leer. Ergebnisse erscheinen, sobald die Arbeitsmappe berechnet wurde. Speichern Sie nach dem Schreiben der Formeln, damit die berechneten Werte mit der Datei mitreisen.

FAQ

Brauche ich Excel oder Office, um Formeln zu schreiben?

Nein. Die Tabellenkalkulations-Engine ist im Paket enthalten und läuft als WebAssembly im Browser. Es wird nichts automatisiert und nichts auf dem Rechner des Anwenders vorausgesetzt.

Kann eine Formel auf ein anderes Arbeitsblatt in derselben Arbeitsmappe verweisen?

Ja, und Sie schreiben sie genau so, wie Sie es in Excel tun würden – nehmen Sie den Blattnamen in die Formelzeichenkette auf.

Kann ich Formeln und einfache Werte in einem Blatt mischen?

Ja, und das werden Sie in der Regel auch tun. Die Eigenschaften sind unabhängig: Einige Zellen erhalten Daten über NumberValue oder Value, andere erhalten Regeln über Formula.

Was geschieht mit den Ergebnissen, wenn der Empfänger die Datei öffnet?

Die Formeln werden gespeichert, und Excel berechnet beim Öffnen der Arbeitsmappe neu. Das ist der Sinn davon, Regeln statt Ergebnisse zu schreiben – die Datei bleibt korrekt, selbst wenn die Eingaben anschließend bearbeitet werden.

Erfordert das Schreiben von Formeln ein Backend?

Nein. Die Arbeitsmappe wird im Browser erstellt und als Bytes zurückgegeben, aus denen Sie einen Blob zum Herunterladen machen. Es wird nichts hochgeladen.

Siehe auch

• 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