Cómo insertar fórmulas y funciones de Excel en JavaScript (React)

Una hoja de cálculo generada llena de números ya calculados es una instantánea. Se ve bien en el momento en que se produce y empieza a quedar obsoleta de inmediato: los datos que hay detrás avanzan, los números que contiene no, y una vez que el archivo ha salido de su aplicación nadie puede saber qué celdas tiene permitido modificar. Un libro que en cambio lleva sus fórmulas sigue siendo un documento vivo: edite una entrada y los totales se actualizan.

Spire.XLS for JavaScript es un motor de hojas de cálculo compilado a WebAssembly, por lo que una aplicación React puede crear libros en el navegador sin necesidad de un servidor. Los archivos se leen y se escriben a través de un sistema de archivos virtual (VFS), y las fórmulas se escriben igual que los valores: a través del objeto Range de una celda. Solo cambia el nombre de la propiedad.

Ese último punto es todo el truco. La pregunta interesante no es cómo escribir una fórmula, sino con cuál de las cuatro propiedades disponibles escribirla, porque tres de ellas almacenarán su fórmula silenciosamente como texto sin formato.

Para la configuración del proyecto, consulte Integrating Spire.XLS for JavaScript in a React Project. Los ejemplos a continuación suponen que el paquete está instalado y que el módulo WebAssembly se ha inicializado.

Por qué los libros generados deben incluir fórmulas

Generar un archivo con las respuestas ya rellenadas es más fácil de escribir y peor de recibir. Los casos en los que realmente falla:

• Plantillas con marcadores de posición. Se espera que el destinatario reemplace las entradas. Si los totales están codificados de forma fija, reemplazar una entrada deja los totales incorrectos y nada se lo advierte.
• Modelos entregados a un analista. Querrán probar un supuesto diferente. Una hoja que no se puede volver a derivar es una hoja que tienen que reconstruir.
• Informes que deben ser trazables. Un número sin una regla visible detrás no se puede comprobar. Una fórmula sí.
• Hojas de cálculo que alimentan a otras hojas. Otras celdas hacen referencia a estas; si el valor nunca se recalcula, todo lo que está aguas abajo hereda la obsolescencia.

En los cuatro casos, la fórmula es el objetivo del archivo. Los valores son un subproducto.

Requisitos previos

Necesita un proyecto React con Spire.XLS for JavaScript instalado y el módulo WebAssembly inicializado, accesible en window.wasmModule.spirexls. El ejemplo siguiente también carga una fuente en el VFS antes de dar formato a cualquier texto, y guarda con la marca de versión de Excel 2010 para que la salida se abra sin problemas tanto en el Excel actual como en versiones anteriores.

Elegir la propiedad que escribe una fórmula

Cada celda en la que escribe es un objeto Range, y este expone cuatro propiedades que aceptan algo. No son intercambiables:

Text es la que hay que manejar con cuidado, y vale la pena entender por qué antes del código que aparece a continuación. Asigne =SUM(B1:F1) a Text y la celda almacenará esos caracteres: mostrará la fórmula para siempre, porque nada la va a evaluar nunca.

Ese comportamiento no es un defecto. Es exactamente lo que el ejemplo utiliza de forma deliberada, para que cada fila pueda mostrar la fórmula a la izquierda y su resultado a la derecha: la celda izquierda usa Text porque está pensada para mostrar la regla, y la celda derecha usa Formula porque está pensada para aplicarla.

Escribir fórmulas en las celdas

El flujo es breve:

1. Cree un objeto Workbook.
2. Obtenga una hoja de cálculo con el método Workbook.Worksheets.get().
3. Escriba los datos de entrada en las celdas y establezca el formato de las celdas.
4. Asigne fórmulas a las celdas que deben calcular, a través de la propiedad Range.Formula.
5. Guarde el libro con Workbook.SaveToFile().

El ejemplo crea una pequeña hoja con una fila de números de entrada y luego escribe cinco fórmulas debajo: una expresión aritmética, una función de fecha, una función trigonométrica, un promedio y una suma:

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;

Inserte fórmulas y resultados de funciones en hojas de cálculo de Excel

Observe la llamada de formato antes de las fórmulas. Range.get() acepta lastRow y lastColumn, por lo que se puede dar estilo a un bloque de encabezado en una sola llamada en lugar de celda por celda: el mismo objeto que usa para escribir una fórmula también lleva el estilo.

Funciones por categoría

Las cinco fórmulas del ejemplo no son cinco técnicas diferentes. Son una técnica aplicada a cinco tipos de expresión:

No hay una API separada para las «funciones». Una función es una fórmula: Range.Formula recibe la cadena y el motor decide qué hacer con ella. Por eso el catálogo de cosas que puede escribir es tan amplio como la lista de funciones del motor de hojas de cálculo, sin ningún contenedor que mantener por función.

Mostrar el texto de la fórmula junto a su resultado

Uno de los hábitos más útiles en una hoja de cálculo generada es mantener la regla visible junto a su resultado. El ejemplo lo hace colocando la cadena de la fórmula en la columna A como texto literal y el valor evaluado en la columna 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;

Asignar "@" como formato de número en primer lugar es lo que evita que la columna de etiquetas intente interpretar la cadena: la celda se declara como texto antes de que se escriba nada en ella. La columna de resultados no necesita ese cuidado, pero puede necesitar un formato de visualización propio: la fila de la fecha establece .Style.NumberFormat = "YYYY/MM/DD", sin lo cual el valor se muestra como un número de serie en lugar de una fecha.

Una hoja que lleva sus propias reglas de esta manera sobrevive a todos los traslados, porque las etiquetas son texto sin formato que ningún motor tocará.

Una fórmula en todo un rango

Las hojas de cálculo reales rara vez necesitan una sola fórmula; necesitan la misma regla a lo largo de una columna. Como usted construye la cadena, controla las referencias de forma explícita:

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

Ese es el mismo comportamiento de referencia relativa que obtendría al arrastrar una fórmula hacia abajo en Excel, escrito de forma desarrollada. Si la regla debe apuntar siempre a una entrada fija, fíjela: $A$1 no se desplaza cuando la fórmula se mueve, mientras que A1 sí lo hace.

Sintaxis de fórmulas que suele causar problemas

• El signo igual inicial. Una cadena de fórmula sin = no es una fórmula. Se almacenará como texto y nunca se evaluará.
• Referencias relativas frente a absolutas. A1 se desplaza; $A$1 no. Elija de forma deliberada cuando genere fórmulas en un bucle.
• Referencias entre hojas. Nombre la hoja dentro de la cadena: Sheet2!A1. Si el nombre de la hoja contiene espacios, entrecomíllelo: 'Q1 Sales'!A1.
• Separadores de argumentos según la configuración regional. La cadena se almacena tal como usted la escribe. Mantenga la forma separada por comas usada arriba si el archivo se abrirá en una combinación de configuraciones regionales, donde algunas muestran puntos y coma en su lugar.
• Funciones volátiles. TODAY() y NOW() cambian cada vez que el libro se recalcula, por lo que un valor leído más tarde no coincidirá con el que vio. Esa diferencia entre una regla y su último valor calculado merece conocerse por sí misma: es de lo que trata Reading and Extracting Excel Formulas in JavaScript (React).

Problemas comunes

La celda muestra la fórmula en lugar de un resultado. Se escribió a través de Text en lugar de Formula. Reasígnela con Formula: la celda necesita la regla, no los caracteres.

Una fecha aparece como un número de cinco dígitos. Ese es el valor de serie sin ningún formato de fecha aplicado. Establezca .Style.NumberFormat en la celda, como hace el ejemplo para la fila de TODAY().

El formato se aplica a celdas que no pretendía tocar. Compruebe el rango que pasó a Range.get(). Suministrar lastRow y lastColumn aplica el cambio a un bloque, lo cual es cómodo para un encabezado y fácil de delimitar mal.

La fórmula se almacena pero la celda parece vacía al volver a leerla. Los resultados aparecen una vez que el libro se ha calculado. Guarde después de escribir las fórmulas para que los valores calculados viajen con el archivo.

Preguntas frecuentes

¿Necesito tener Excel u Office instalado para escribir fórmulas?

No. El motor de hojas de cálculo viene incluido con el paquete y se ejecuta como WebAssembly en el navegador. No se automatiza nada ni se requiere nada en el equipo del usuario.

¿Puede una fórmula hacer referencia a una hoja de cálculo diferente del mismo libro?

Sí, y la escribe exactamente como lo haría en Excel: incluya el nombre de la hoja en la cadena de la fórmula.

¿Puedo mezclar fórmulas y valores simples en una misma hoja?

Sí, y normalmente lo hará. Las propiedades son independientes: algunas celdas reciben datos a través de NumberValue o Value, y otras reciben reglas a través de Formula.

¿Qué ocurre con los resultados cuando el destinatario abre el archivo?

Las fórmulas se almacenan y Excel recalcula cuando se abre el libro. Ese es el objetivo de escribir reglas en lugar de resultados: el archivo sigue siendo correcto aunque las entradas se editen después.

¿Escribir fórmulas requiere un backend?

No. El libro se crea en el navegador y se devuelve como bytes que usted convierte en un Blob para su descarga. No se sube nada.

Ver también

• 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