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 :
| Propriété | Ce que vous obtenez | Utilisez-la quand |
|---|---|---|
HasFormula |
Si la cellule contient une formule | Vous filtrez une plage avant toute lecture |
Formula |
La chaîne de formule telle qu'elle est stockée — =SUM(B1:F1)
|
Vous avez besoin de la règle |
FormulaNumberValue |
Le résultat numérique de l'évaluation de cette formule | Vous avez besoin du nombre produit par la règle |
NumberValue |
Le nombre contenu dans une cellule de données | La cellule est une donnée plutôt qu'une règle |
Text |
Le texte tel qu'il a été écrit dans la cellule | Vous voulez la chaîne affichée |
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.