Haz que los datos de Excel sean visuales con formato condicional en JavaScript

Una tabla de ventas con veinte columnas de números es precisa e ilegible. El ojo no puede comparar 43.210 con 38.900 a lo largo de una fila con la suficiente rapidez para encontrar el trimestre débil, y quien lee el informe lo sabe, por eso pide un gráfico. Pero un gráfico por columna significa veinte gráficos, y ahora la hoja de cálculo es una galería en lugar de una tabla.

Las barras de datos, las escalas de color y los conjuntos de iconos resuelven esto dentro de las propias celdas. Una barra crece en proporción al valor. Un color pasa de pálido a saturado a medida que el número aumenta. Un icono cambia de forma cuando el valor cruza un umbral. Ninguno de ellos añade filas, columnas u objetos flotantes: la visualización se asienta en la celda que ya contiene el número. Los tres son formas de formato condicional de Excel, y Spire.XLS for JavaScript los aplica a través de una única API en el navegador sobre WebAssembly, con los archivos circulando por un sistema de archivos virtual (VFS) y sin necesidad de backend.

Para la configuración del proyecto, consulte Integrar Spire.XLS for JavaScript en un proyecto de React. Los ejemplos siguientes presuponen que el paquete está instalado y que el módulo de WebAssembly se ha inicializado.

Por qué no basta con añadir un gráfico

Los gráficos y la visualización dentro de la celda responden a la misma pregunta —"¿cómo se comparan estos valores?"—, pero encajan en momentos diferentes:

Cuando el objetivo es hacer que una tabla de números se pueda escanear sin rehacer el diseño, la visualización dentro de la celda es la herramienta más ligera. Las tres secciones siguientes abordan cada tipo, y comparten más API de lo que difieren, que es lo primero que conviene saber.

Requisitos previos

Necesita un proyecto de React con Spire.XLS for JavaScript instalado y el módulo de WebAssembly inicializado, accesible en window.wasmModule.spirexls. El ejemplo carga una fuente y un archivo de datos de ventas en el VFS, y guarda con la marca de versión de Excel 2010, que es la versión más antigua que admite estos tipos de formato condicional.

Una API, tres visualizaciones

Los tres tipos siguen la misma cadena de llamadas. La única línea que cambia es la asignación de FormatType:

sheet.ConditionalFormats.Add()  →  xcfs.AddRange(range)  →  format = xcfs.AddCondition()  →  format.FormatType = ???

La cadena compartida es la razón por la que los tres ejemplos de código siguientes se parecen: son la misma operación con un tipo de formato distinto. Las diferencias están en lo que produce cada tipo y en cuándo recurriríamos a él, que es lo que aborda la tabla comparativa que aparece más adelante en este artículo.

Barras de datos: la magnitud de un vistazo

Una barra de datos dibuja una banda horizontal de color dentro de cada celda, y la longitud de la banda es proporcional al valor de la celda en relación con el resto del rango seleccionado. El valor más alto llena la celda; el más bajo llena apenas una franja. Escanear una fila de barras de datos es la misma operación mental que escanear un gráfico de barras, salvo que los números permanecen visibles debajo.

Los pasos son:

1. Cargue la fuente y el archivo de datos de prueba en el VFS.
2. Cargue el libro y obtenga la hoja de cálculo.
3. Llame a ConditionalFormats.Add para crear un formato condicional y vincule el rango de datos con AddRange.
4. Llame a AddCondition para añadir una condición, establezca FormatType en DataBar y defina el color de la barra.
5. Guarde el libro.

function App() {
  const applyDataBars = 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 the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the data bars
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a data bar condition and set the bar color
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.DataBar;
    format.DataBar.BarColor = xlsModule.Color.get_CadetBlue();

    // Save the workbook
    const outputFileName = "ApplyDataBars.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the 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>Apply Data Bars</h1>
      <button onClick={applyDataBars}>Start</button>
    </div>
  );
}

export default App;

Barras de datos aplicadas a una tabla de cifras de ventas, con la longitud de la barra proporcional al valor de la celda

Solo las celdas numéricas reciben barras; las celdas de texto dentro del rango se omiten. Esto es lo esperado: una barra de datos expresa una magnitud relativa, y el texto no tiene magnitud que expresar. Mantenga el rango limitado al área numérica; incluir una columna de nombres de producto o una fila de encabezados no provoca ningún error, pero esas celdas no mostrarán nada.

Escalas de color: mapas de calor sin un gráfico

Una escala de color sombrea cada celda según dónde se sitúe su valor entre el mínimo y el máximo del rango. No se requieren argumentos de color: cuando no se especifica ninguno, el resultado es una escala de dos colores que toma naranja en el mínimo y amarillo pálido en el máximo, con los valores intermedios sombreados de forma proporcional. El efecto es un mapa de calor integrado en la tabla de datos: los puntos calientes y fríos se ven sin necesidad de ordenar ni de crear un gráfico.

Los pasos son los mismos que para las barras de datos, con FormatType establecido en ColorScale y sin propiedades adicionales:

1. Cargue la fuente y el archivo de datos de prueba en el VFS.
2. Cargue el libro y obtenga la hoja de cálculo.
3. Llame a ConditionalFormats.Add para crear un formato condicional y vincule el rango de datos con AddRange.
4. Llame a AddCondition para añadir una condición y establezca FormatType en ColorScale.
5. Guarde el libro.

function App() {
  const applyColorScales = 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 the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the color scales
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a color scale condition; colors transition with the values
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.ColorScale;

    // Save the workbook
    const outputFileName = "ApplyColorScales.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the 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>Apply Color Scales</h1>
      <button onClick={applyColorScales}>Start</button>
    </div>
  );
}

export default App;

Escalas de color aplicadas a una tabla de cifras de ventas, con un sombreado de naranja a amarillo pálido

Mientras que las barras de datos muestran la magnitud absoluta mediante la longitud de la barra, las escalas de color muestran la posición relativa mediante el tono. Un valor situado en la mitad del rango recibe un tono medio independientemente de si el rango abarca de 1 a 100 o de 10.000 a 50.000: el sombreado es posicional, no absoluto.

Conjuntos de iconos: bandas de estado

Un conjunto de iconos coloca un icono distinto en cada celda según la banda en la que se sitúe el valor. El ejemplo utiliza tres semáforos: rojo para el tercio más bajo, amarillo para el intermedio y verde para el más alto. A diferencia de las barras de datos y las escalas de color, que comunican un degradado continuo, los conjuntos de iconos comunican una categoría discreta —"esto es bajo", "esto es medio", "esto es alto"—, lo que se acerca más a un indicador de estado que a una medición.

Los pasos solo se diferencian en el FormatType y en la selección del estilo de icono:

1. Cargue la fuente y el archivo de datos de prueba en el VFS.
2. Cargue el libro y obtenga la hoja de cálculo.
3. Llame a ConditionalFormats.Add para crear un formato condicional y vincule el rango de datos con AddRange.
4. Llame a AddCondition para añadir una condición, establezca FormatType en IconSet y especifique el tipo de conjunto de iconos.
5. Guarde el libro.

function App() {
  const applyIconSets = 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 the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the icon sets
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add an icon set condition and set the icon style to three traffic lights
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.IconSet;
    format.IconSet.IconSetType = xlsModule.IconSetType.ThreeTrafficLights1;

    // Save the workbook
    const outputFileName = "ApplyIconSets.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the 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>Apply Icon Sets</h1>
      <button onClick={applyIconSets}>Start</button>
    </div>
  );
}

export default App;

Conjuntos de iconos aplicados a una tabla de cifras de ventas, con iconos de semáforo según las bandas de valores

Un conjunto de iconos divide el rango en bandas, por lo que el mismo icono abarca un intervalo distinto de valores en rangos diferentes. En un rango de 10 a 90, el icono verde abarca aproximadamente de 60 a 90; en un rango de 10 a 900, abarca aproximadamente de 600 a 900. Las bandas son relativas, no absolutas, lo cual es el valor predeterminado adecuado para una tabla en la que cada columna tiene su propia escala, pero conviene saberlo si espera un umbral fijo.

Elegir entre las tres opciones

Las tres se aplican a un rango, las tres viven dentro de las celdas y las tres son formato condicional. La elección depende de lo que el lector necesite hacer con los números:

Los tres no son mutuamente excluyentes. Una hoja de cálculo puede llevar barras de datos en las columnas de ingresos y conjuntos de iconos en la columna de tasa de crecimiento en la misma operación de guardado, porque cada llamada a ConditionalFormats.Add crea un formato independiente vinculado a su propio rango.

Personalizar la apariencia de las barras de datos

El color de relleno de una barra de datos proviene de DataBar.BarColor. Establecer solo FormatType sin BarColor da como resultado el azul predeterminado. También hay disponible un borde, pero tiene una dependencia: el tipo de borde debe establecerse antes de que el color del borde surta efecto.

// Set the border type first so that the border color takes effect
format.DataBar.BarBorder.Type = xlsModule.DataBarBorderType.DataBarBorderSolid;
format.DataBar.BarBorder.Color = xlsModule.Color.get_Red();

// Fill color of the bar
format.DataBar.BarColor = xlsModule.Color.get_GreenYellow();

Establecer BarBorder.Color por sí solo, sin definir antes BarBorder.Type, no tiene ningún efecto: el borde no se dibuja porque no se ha declarado ningún tipo de borde. Las escalas de color y los conjuntos de iconos no tienen propiedades de apariencia equivalentes; su estilo viene determinado por el tipo de formato y, en el caso de los conjuntos de iconos, por la enumeración IconSetType.

Problemas comunes

Las celdas de texto del rango de destino no muestran barras de datos. Esto es lo esperado. Una barra de datos expresa una magnitud relativa, y solo las celdas numéricas tienen magnitud. Las celdas de texto se omiten de forma silenciosa: sin error y sin barra. Mantenga el rango limitado al área numérica.

Todas las barras de datos son del azul predeterminado. No se estableció DataBar.BarColor después de asignar FormatType. Establézcalo con cualquier valor de xlsModule.Color para cambiar el relleno.

No se muestra el color del borde de la barra de datos. No se estableció primero el tipo de borde. Asigne DataBar.BarBorder.Type antes de DataBar.BarBorder.Color: el color solo surte efecto una vez declarado un tipo de borde sólido.

No hay ningún cambio visible tras aplicar un formato condicional. Compruebe que el rango pasado a AddRange coincide con el lugar donde están realmente los datos. Un rango que apunta a celdas vacías no produce ningún error ni ningún resultado visible.

Preguntas frecuentes

¿Puedo aplicar más de un formato condicional al mismo rango?

Sí. Cada llamada a ConditionalFormats.Add crea un formato independiente. Dos formatos pueden tener como destino el mismo rango, aunque el resultado visual de superponer una barra de datos y una escala de color en las mismas celdas puede resultar confuso; normalmente es más claro aplicar tipos distintos a rangos distintos.

¿Qué versiones de Excel admiten estos tipos de formato condicional?

Las barras de datos, las escalas de color y los conjuntos de iconos se introdujeron en Excel 2007. El ejemplo guarda con ExcelVersion.Version2010 para garantizar la compatibilidad tanto con Excel 2010 como con versiones posteriores.

¿Necesito tener Excel instalado para aplicar formato condicional?

No. El motor de hojas de cálculo viene incluido en el paquete y se ejecuta como WebAssembly en el navegador. El formato condicional se escribe como XML estándar dentro del archivo .xlsx, y Excel lo representa al abrir el archivo.

¿Puedo establecer umbrales personalizados para los conjuntos de iconos?

La enumeración IconSetType selecciona un estilo de icono predefinido con límites de banda predefinidos. El ejemplo utiliza ThreeTrafficLights1, que divide el rango en tres bandas iguales.

¿Sobrevive el formato condicional si el archivo se abre y se vuelve a guardar en Excel?

Sí. El formato condicional forma parte de las reglas de formato almacenadas en la hoja de cálculo, no es un artefacto de representación. Excel lee, conserva y vuelve a aplicar las mismas reglas al recalcular.

Véase también

• Integrar Spire.XLS for JavaScript en un proyecto de React
• Página de producto de Spire.XLS for JavaScript