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:
| Propiedad | Qué obtiene | Úsela cuando |
|---|---|---|
HasFormula |
Si la celda contiene una fórmula | Filtre un rango antes de leer cualquier cosa |
Formula |
La cadena de fórmula tal como se almacena: =SUM(B1:F1)
|
Necesita la regla |
FormulaNumberValue |
El resultado numérico de evaluar esa fórmula | Necesita el número que produjo la regla |
NumberValue |
El número contenido en una celda de datos | La celda es un dato en lugar de una regla |
Text |
El texto tal como se escribió en la celda | Quiere la cadena de visualización |
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.