Rendi visivi i dati di Excel con la formattazione condizionale in JavaScript

Una tabella di vendite con venti colonne di numeri è accurata e illeggibile. L'occhio non riesce a confrontare 43.210 con 38.900 lungo una riga abbastanza velocemente da individuare il trimestre debole, e chi legge il report lo sa — ed è per questo che chiede un grafico. Ma un grafico per colonna significa venti grafici, e ora il foglio di lavoro è una galleria invece di una tabella.

Le barre dei dati, le scale di colori e i set di icone risolvono il problema all'interno delle celle stesse. Una barra cresce in proporzione al valore. Un colore passa dal tenue al saturo man mano che il numero aumenta. Un'icona cambia forma quando il valore supera una soglia. Nessuno di essi aggiunge righe, colonne o oggetti fluttuanti — la visualizzazione risiede nella cella che già contiene il numero. Tutti e tre sono forme di formattazione condizionale di Excel, e Spire.XLS for JavaScript le applica tramite un'unica API nel browser su WebAssembly, con i file che passano attraverso un file system virtuale (VFS) e senza necessità di un backend.

Per la configurazione del progetto, vedi Integrare Spire.XLS for JavaScript in un progetto React. Gli esempi seguenti presuppongono che il pacchetto sia installato e che il modulo WebAssembly sia stato inizializzato.

Perché non aggiungere semplicemente un grafico

I grafici e la visualizzazione all'interno delle celle rispondono alla stessa domanda — "come si confrontano questi valori?" — ma si adattano a momenti diversi:

Quando l'obiettivo è rendere leggibile a colpo d'occhio una tabella di numeri senza ricostruire il layout, la visualizzazione nelle celle è lo strumento più leggero. Le tre sezioni seguenti trattano ciascun tipo, e condividono più API di quante ne differenzino — ed è la prima cosa che vale la pena sapere.

Prerequisiti

Serve un progetto React con Spire.XLS for JavaScript installato e il modulo WebAssembly inizializzato, accessibile all'indirizzo window.wasmModule.spirexls. L'esempio carica un font e un file di dati di vendita nel VFS e salva con il flag della versione Excel 2010, che è la prima versione a supportare questi tipi di formato condizionale.

Un'unica API, tre visualizzazioni

Tutti e tre i tipi seguono la stessa catena di chiamate. L'unica riga che cambia è l'assegnazione di FormatType:

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

La catena condivisa è il motivo per cui i tre esempi di codice seguenti si assomigliano — sono la stessa operazione con un tipo di formato diverso. Le differenze stanno in ciò che ciascun tipo produce e in quando lo si utilizzerebbe, ed è proprio questo che affronta la tabella di confronto più avanti nell'articolo.

Barre dei dati: la grandezza a colpo d'occhio

Una barra dei dati disegna una fascia colorata orizzontale all'interno di ogni cella, e la lunghezza della fascia è proporzionale al valore della cella rispetto al resto dell'intervallo selezionato. Il valore più grande riempie la cella; il più piccolo riempie uno spicchio sottile. Scansionare una riga di barre dei dati è la stessa operazione mentale che scansionare un grafico a barre, con la differenza che i numeri restano visibili sotto.

I passaggi sono:

1. Caricare il font e il file di dati di test nel VFS.
2. Caricare la cartella di lavoro e ottenere il foglio di lavoro.
3. Chiamare ConditionalFormats.Add per creare un formato condizionale e associare l'intervallo di dati con AddRange.
4. Chiamare AddCondition per aggiungere una condizione, impostare FormatType su DataBar e impostare il colore della barra.
5. Salvare la cartella di lavoro.

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;

Barre dei dati applicate a una tabella di cifre di vendita, lunghezza della barra proporzionale al valore della cella

Solo le celle numeriche ricevono le barre — le celle di testo all'interno dell'intervallo vengono ignorate. È il comportamento previsto: una barra dei dati esprime una grandezza relativa, e il testo non ha alcuna grandezza da esprimere. Mantieni l'intervallo delimitato all'area numerica; includere una colonna con i nomi dei prodotti o una riga di intestazione non causa un errore, ma quelle celle non mostreranno nulla.

Scale di colori: mappe di calore senza grafico

Una scala di colori ombreggia ogni cella in base alla posizione del suo valore tra il minimo e il massimo dell'intervallo. Non sono richiesti argomenti di colore — quando non ne viene specificato nessuno, il risultato è una scala a due colori che assume l'arancione in corrispondenza del minimo e il giallo pallido in corrispondenza del massimo, con i valori intermedi ombreggiati in proporzione. L'effetto è una mappa di calore incorporata nella tabella di dati: i punti caldi e quelli freddi sono visibili senza ordinare né creare grafici.

I passaggi sono gli stessi delle barre dei dati, con FormatType impostato su ColorScale e nessuna proprietà aggiuntiva:

1. Caricare il font e il file di dati di test nel VFS.
2. Caricare la cartella di lavoro e ottenere il foglio di lavoro.
3. Chiamare ConditionalFormats.Add per creare un formato condizionale e associare l'intervallo di dati con AddRange.
4. Chiamare AddCondition per aggiungere una condizione e impostare FormatType su ColorScale.
5. Salvare la cartella di lavoro.

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;

Scale di colori applicate a una tabella di cifre di vendita, con ombreggiatura dall'arancione al giallo pallido

Mentre le barre dei dati mostrano la grandezza assoluta attraverso la lunghezza della barra, le scale di colori mostrano la posizione relativa attraverso la tonalità. Un valore al centro dell'intervallo ottiene una tonalità intermedia indipendentemente dal fatto che l'intervallo vada da 1 a 100 o da 10.000 a 50.000 — l'ombreggiatura è posizionale, non assoluta.

Set di icone: fasce di stato

Un set di icone colloca un'icona diversa in ogni cella in base alla fascia in cui ricade il valore. L'esempio utilizza i tre semafori: rosso per il terzo più basso, giallo per quello intermedio, verde per il più alto. A differenza delle barre dei dati e delle scale di colori, che comunicano un gradiente continuo, i set di icone comunicano una categoria discreta — "questo è basso", "questo è medio", "questo è alto" — il che è più vicino a un indicatore di stato che a una misurazione.

I passaggi differiscono solo per FormatType e per la selezione dello stile dell'icona:

1. Caricare il font e il file di dati di test nel VFS.
2. Caricare la cartella di lavoro e ottenere il foglio di lavoro.
3. Chiamare ConditionalFormats.Add per creare un formato condizionale e associare l'intervallo di dati con AddRange.
4. Chiamare AddCondition per aggiungere una condizione, impostare FormatType su IconSet e specificare il tipo di set di icone.
5. Salvare la cartella di lavoro.

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;

Set di icone applicati a una tabella di cifre di vendita, icone a semaforo basate sulle fasce di valore

Un set di icone divide l'intervallo in fasce, quindi la stessa icona copre un intervallo di valori diverso in intervalli diversi. In un intervallo da 10 a 90, l'icona verde copre all'incirca da 60 a 90; in un intervallo da 10 a 900, copre all'incirca da 600 a 900. Le fasce sono relative, non assolute — il che è l'impostazione predefinita corretta per una tabella in cui ogni colonna ha la propria scala, ma vale la pena saperlo se ci si aspetta una soglia fissa.

Scegliere tra i tre

Tutti e tre vengono applicati a un intervallo, tutti e tre risiedono all'interno delle celle e tutti e tre sono formattazione condizionale. La scelta riguarda ciò che il lettore deve fare con i numeri:

I tre non si escludono a vicenda. Un foglio di lavoro può contenere barre dei dati sulle colonne dei ricavi e set di icone sulla colonna del tasso di crescita nello stesso salvataggio, perché ogni chiamata a ConditionalFormats.Add crea un formato indipendente associato al proprio intervallo.

Personalizzare l'aspetto delle barre dei dati

Il colore di riempimento di una barra dei dati proviene da DataBar.BarColor. Impostando solo FormatType senza BarColor si ottiene il blu predefinito. È disponibile anche un bordo, ma ha una dipendenza: il tipo di bordo deve essere impostato prima che il colore del bordo abbia effetto.

// 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();

Impostare BarBorder.Color da solo, senza impostare prima BarBorder.Type, non ha alcun effetto — il bordo non viene disegnato perché non è stato dichiarato alcun tipo di bordo. Le scale di colori e i set di icone non hanno proprietà equivalenti di aspetto; il loro stile è determinato dal tipo di formato e, per i set di icone, dall'enumerazione IconSetType.

Problemi comuni

Le celle di testo nell'intervallo di destinazione non mostrano barre dei dati. Questo è il comportamento previsto. Una barra dei dati esprime una grandezza relativa, e solo le celle numeriche hanno una grandezza. Le celle di testo vengono ignorate silenziosamente — nessun errore, nessuna barra. Mantieni l'intervallo limitato all'area numerica.

Le barre dei dati sono tutte del blu predefinito. DataBar.BarColor non è stato impostato dopo l'assegnazione di FormatType. Impostalo su un qualsiasi valore di xlsModule.Color per cambiare il riempimento.

Il colore del bordo delle barre dei dati non viene visualizzato. Il tipo di bordo non è stato impostato per primo. Assegna DataBar.BarBorder.Type prima di DataBar.BarBorder.Color — il colore ha effetto solo dopo che è stato dichiarato un tipo di bordo pieno.

Nessun cambiamento visibile dopo l'applicazione di un formato condizionale. Verifica che l'intervallo passato a AddRange corrisponda a dove si trovano effettivamente i dati. Un intervallo che punta a celle vuote non produce errori né risultati visibili.

FAQ

Posso applicare più di un formato condizionale allo stesso intervallo?

Sì. Ogni chiamata a ConditionalFormats.Add crea un formato indipendente. Due formati possono avere come destinazione lo stesso intervallo, anche se il risultato visivo di sovrapporre una barra dei dati e una scala di colori sulle stesse celle può risultare confuso — di solito è più chiaro applicare tipi diversi a intervalli diversi.

Quali versioni di Excel supportano questi tipi di formato condizionale?

Le barre dei dati, le scale di colori e i set di icone sono stati introdotti in Excel 2007. L'esempio salva con ExcelVersion.Version2010 per garantire la compatibilità sia con Excel 2010 sia con le versioni successive.

Serve Excel installato per applicare la formattazione condizionale?

No. Il motore per fogli di calcolo è incluso nel pacchetto e viene eseguito come WebAssembly nel browser. La formattazione condizionale viene scritta come XML standard all'interno del file .xlsx, ed Excel la renderizza all'apertura del file.

Posso impostare soglie personalizzate per i set di icone?

L'enumerazione IconSetType seleziona uno stile di icona predefinito con confini di fascia predefiniti. L'esempio usa ThreeTrafficLights1, che divide l'intervallo in tre fasce uguali.

La formattazione condizionale sopravvive se il file viene aperto e salvato di nuovo in Excel?

Sì. La formattazione condizionale fa parte delle regole di formato memorizzate nel foglio di lavoro, non è un artefatto di rendering. Excel legge, preserva e riapplica le stesse regole al ricalcolo.

Vedi anche

• Integrare Spire.XLS for JavaScript in un progetto React
• Pagina del prodotto Spire.XLS for JavaScript