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:
| Eigenschaft | Was Sie erhalten | Greifen Sie darauf zurück, wenn |
|---|---|---|
HasFormula |
Ob die Zelle eine Formel enthält | Sie einen Bereich prüfen, bevor Sie irgendetwas lesen |
Formula |
Die gespeicherte Formelzeichenkette – =SUM(B1:F1)
|
Sie die Regel brauchen |
FormulaNumberValue |
Das numerische Ergebnis der Auswertung dieser Formel | Sie die Zahl brauchen, die die Regel erzeugt hat |
NumberValue |
Die in einer Datenzelle enthaltene Zahl | Die Zelle Daten und keine Regel enthält |
Text |
Der in die Zelle geschriebene Text | Sie die Anzeigezeichenkette möchten |
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.