Crea una heat map di dati in un foglio di calcolo utilizzando una macro ONLYOFFICE

Nell’analisi dei dati, riconoscere a colpo d’occhio le variazioni nei valori numerici è fondamentale per prendere decisioni. Formattare manualmente ogni cella può richiedere molto tempo ed essere soggetto a errori, ma questa macro di ONLYOFFICE risolve il problema regolando dinamicamente i colori delle celle, rendendo più semplice interpretare i dati complessi in modo efficiente.

Costruzione della macro

var sheet = Api.GetActiveSheet();
var range = sheet.GetSelection();
var data = range.GetValue();

Per cominciare, inizializziamo il foglio attivo, la selezione e l’intervallo all’interno delle variabili sheet, range e data.

Gestione dell’errore “Nessun dato selezionato”

Subito dopo, verifichiamo se sono stati selezionati dei dati. In caso contrario, la funzione macro viene interrotta con un messaggio appropriato: : “No data selected.”

//We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }

Estrazione degli indici di riga e colonna dalla selezione

Se sono presenti dati, procediamo recuperando i parametri dell’intervallo: indice iniziale e finale di righe e colonne.

//Indexes indicating where rows and columns start and end
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

Esistono diversi modi per ottenere gli indici, ma uno è illustrato nello snippet di codice qui sopra:

• range.GetCells() – accediamo alle celle della selezione, e aggiungendo .Row() o .Col() otteniamo l’indice della prima riga o della prima colonna.
• Una volta ottenuti, possiamo usare data.length per conoscere il numero di righe, e aggiungerlo alla variabile firstRowIndex per ottenere l’indice dell’ultima riga. Lo stesso vale per le colonne, utilizzando data[0].length e sommando firstColIndex.

Perché data[0]?

Poiché non sappiamo quante righe ci siano, e ciascuno di data[0], data[1], ecc. rappresenta una riga, l’unica certezza è che ci sarà sempre almeno una riga. Ciò significa che data[0] sarà sempre valido se la variabile data contiene valori.

Raccolta dei numeri dalle celle selezionate

Ora creiamo l’array values e lo riempiamo con i numeri prelevati dalla selezione.

var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

Dopo aver inizializzato l’array values, eseguiamo un ciclo su ogni cella della selezione.

Di default, ogni valore viene trattato come stringa. Quindi, per verificare se è un numero, usiamo il metodo parseFloat() per convertirlo.

Se la stringa rappresenta un numero, parseFloat lo convertirà correttamente. In caso contrario, restituirà NaN (Not a Number).

La funzione isNaN(…) controlla se il valore non è un numero. Aggiungendo un “!” davanti, verifichiamo se il valore È un numero.

Se lo è, lo salviamo nella variabile value e lo aggiungiamo all’array values.

Alla fine del ciclo, l’array conterrà tutti i numeri selezionati.

Questo array è utile per trovare i valori minimo e massimo tramite i metodi JavaScript Math.min() e Math.max().

Trovare i valori minimo e massimo

//Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

Applicazione dei colori

Ora che abbiamo tutte le informazioni necessarie, possiamo applicare i colori personalizzati alle celle contenenti numeri.

Conoscendo i valori minimo e massimo, iteriamo nuovamente sulle celle:

for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //We want colors to go from green to red
      }
    }
  }

Quando si crea un colore utilizzando il sistema RGB, è necessario specificare i parametri rosso, verde e blu per generare qualsiasi tonalità desiderata. In questo caso, il colore deve variare dal verde al rosso, il che significa che il parametro blu rimane a 0, mentre i parametri rosso e verde variano in base alla tonalità desiderata.

Se il numero è basso — cioè più vicino al valore minimo — il colore sarà più tendente al verde (il valore minimo corrisponde al verde puro). Al contrario, se il numero è alto, il colore si sposterà verso il rosso (il valore massimo corrisponde al rosso puro).

Per determinare quanto un numero sia grande o piccolo, utilizziamo il metodo della normalizzazione Min-Max per ottenere un “rapporto” compreso tra 0 e 1. Un numero maggiore genera un rapporto più vicino a 1, mentre un numero minore produce un rapporto più vicino a 0. Questo rapporto viene calcolato utilizzando le variabili minValue e maxValue.

Possiamo quindi usare questo rapporto per determinare i parametri rosso e verde. A differenza del rapporto, che varia tra 0 e 1, sia il valore del rosso che quello del verde vanno da 0 a 255, quindi moltiplichiamo il rapporto di conseguenza.

Poiché il rosso dovrebbe essere più dominante per i numeri più grandi, lo calcoliamo moltiplicando il rapporto per 225 e arrotondandolo al numero intero più vicino.

Per il verde, il calcolo è diverso. Il verde dovrebbe essere più dominante per i numeri più piccoli, quindi utilizziamo 1 − rapporto, moltiplicandolo per 225. Quando il numero è più grande, il rapporto si avvicina a 1, rendendo 1 − rapporto più piccolo, il che a sua volta riduce il valore del verde.

Una volta ottenuti i parametri rosso e verde, utilizziamo Api.CreateColorFromRGB(r, g, b) per creare il colore e .SetFillColor(color) per applicarlo alla cella.

L’intero codice della macro

(function () {
  var sheet = Api.GetActiveSheet();
  var range = sheet.GetSelection();
  var data = range.GetValue();


  //We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }


  //Indexes indicating where rows and columns start and end
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;


  console.log(firstColIndex + " " + lastColIndex); //Testing if we got the right column indexes, first should be on spot, last should be higher by 1
  console.log(firstRowIndex + " " + lastRowIndex); //Testing if we got the right row indexes, first should be on spot, last should be higher by 1


  var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }


  //Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //We want colors to go from green to red
      }
    }
  }
})();

Ora eseguiamo la macro e vediamo come funziona!

Prima del lancio della macro:

Dopo il lancio della macro:

 

Ora puoi visualizzare facilmente la distribuzione dei dati nel tuo foglio di calcolo, rendendo l’analisi più intuitiva. Che tu stia lavorando nella versione desktop o web di ONLYOFFICE, questa macro si integra perfettamente nel tuo flusso di lavoro.

Non perdere l’occasione di esplorare tutto il potenziale delle API di ONLYOFFICE. Con un’ampia gamma di metodi a disposizione, puoi dare vita alle tue idee di automazione. Se hai domande o concetti innovativi, sentiti libero di condividerli con noi. Apprezziamo il tuo contributo e non vediamo l’ora di collaborare con te. Buon lavoro!

Sull’autore