Créez une carte thermique à l’aide d’une macro ONLYOFFICE

Dans l’analyse des données, il est essentiel de reconnaître les variations des valeurs numériques d’un seul coup d’œil pour prendre des décisions. Le formatage manuel de chaque cellule peut être long et source d’erreurs. Cette macro ONLYOFFICE résout ce problème en ajustant dynamiquement les couleurs des cellules, facilitant ainsi l’interprétation efficace de données complexes.

Construction de la macro

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

Pour commencer, nous initialisons la feuille active, la sélection et la plage dans les variables feuille, plage et données, respectivement.

Traitement de l’erreur « Aucune donnée sélectionnée »

Immédiatement après, nous vérifions si des données sont effectivement sélectionnées. Si aucune donnée n’est sélectionnée, nous mettons fin à la fonction macro avec un message approprié : « Aucune donnée sélectionnée ».

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

Extraction des index de lignes et de colonnes d’une sélection

Si des données sont présentes, nous procédons à l’extraction de leurs paramètres – début et fin de colonne, index de début et de fin de ligne.

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

Il existe de nombreuses façons d’obtenir les index, mais l’une d’entre elles est illustrée dans l’extrait de code ci-dessus :

• range.GetCells() – nous accédons aux cellules de notre sélection, et en ajoutant .Row() ou .Col(), nous obtenons l’index de la première ligne et de la première colonne.
• Une fois que nous l’avons obtenu, nous pouvons utiliser data.length, qui nous donnera le nombre de lignes dans nos données. Nous l’ajoutons ensuite à la variable firstRowIndex pour obtenir l’indice de la dernière ligne. Nous procédons de la même manière pour les colonnes, en utilisant data[0].length pour obtenir le nombre de colonnes, puis en ajoutant la variable firstColIndex.

Pourquoi data[0] ?

Étant donné que nous ne savons pas combien de lignes nous avons, et que data[0], data[1], etc. représentent chacun une ligne, la seule chose dont nous pouvons être certains est que nos données auront toujours au moins une ligne. Cela signifie que data[0] sera toujours valide si la variable data contient des valeurs.

Collecte de chiffres dans des cellules sélectionnées d’une feuille de calcul

Ensuite, nous créons le tableau des valeurs et le remplissons avec les chiffres de notre sélection.

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

Après avoir initialisé le tableau des valeurs, nous parcourons chaque cellule de notre sélection.

Par défaut, la valeur de chaque cellule est traitée comme une chaîne de caractères. Lorsque nous vérifions si la valeur est un nombre, nous devons d’abord utiliser la méthode parseFloat() pour la convertir en nombre.

Si la chaîne de caractères contenue dans la cellule représente un nombre, parseFloat la transformera en un nombre. Dans le cas contraire, elle sera transformée en NaN (Not a Number).

La fonction isNaN(…) vérifie si la valeur entre parenthèses n’est pas un nombre. Si nous ajoutons un « ! » avant isNaN(…), nous vérifions si la valeur est un nombre.

Si la valeur est un nombre, nous initialisons la variable value et y stockons le nombre. Nous ajoutons ensuite cette valeur au tableau des valeurs.

Après l’itération, nous obtenons le tableau des valeurs, qui contient tous les nombres des cellules sélectionnées.

La raison pour laquelle nous avons besoin de ce tableau est que nous pouvons trouver les nombres minimum et maximum des cellules sélectionnées à l’aide des méthodes JavaScript Math.min() et Math.max().

Recherche des valeurs minimales et maximales

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

Application des couleurs

Maintenant que nous disposons de toutes les informations nécessaires, nous pouvons appliquer des couleurs personnalisées aux cellules contenant des nombres.

Nous disposons des nombres minimum et maximum des cellules sélectionnées, ce qui nous permet d’itérer une fois de plus dans les cellules.

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
      }
    }
  }

Lors de la création d’une couleur à l’aide du système RVB, nous avons besoin des paramètres rouge, vert et bleu pour générer la couleur souhaitée. Dans ce cas, la couleur doit aller du vert au rouge, ce qui signifie que le paramètre bleu reste à 0 tandis que les paramètres rouge et vert varient en fonction de la nuance souhaitée.

Si le nombre est petit – plus proche de la valeur minimale – la couleur sera plus verte (la valeur minimale correspond au vert pur). Inversement, si le nombre est grand, la couleur se déplace vers le rouge (la valeur maximale correspond à un rouge pur).

Pour déterminer la taille du nombre, nous utilisons la méthode Min-Max Normalization afin d’obtenir un « ratio » entre 0 et 1. Un nombre plus grand donne un ratio plus proche de 1, tandis qu’un nombre plus petit donne un ratio plus proche de 0. Ce ratio est calculé à l’aide des variables minValue et maxValue.

Nous pouvons ensuite utiliser ce rapport pour déterminer les paramètres rouge et vert. Contrairement au rapport, qui va de 0 à 1, les valeurs du rouge et du vert vont de 0 à 255, et nous multiplions donc le rapport en conséquence.

Comme le rouge devrait être plus dominant pour les grands nombres, nous le calculons en multipliant le ratio par 225 et en l’arrondissant au nombre entier le plus proche.

Pour le vert, le calcul est différent. Le vert devrait être plus dominant pour les petits nombres, nous utilisons donc le rapport 1, en le multipliant par 225. Lorsque le nombre est plus grand, le rapport se rapproche de 1, ce qui réduit le rapport de 1, et donc la valeur du vert.

Une fois les paramètres rouge et vert obtenus, nous utilisons Api.CreateColorFromRGB(r, g, b) pour créer la couleur et .SetFillColor(color) pour l’appliquer à la cellule.

Code complet de la 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
      }
    }
  }
})();

Exécutons la macro et voyons comment elle fonctionne !

Avant d’exécuter la macro :

Après avoir exécuté la macro :

Désormais, vous pouvez facilement visualiser la distribution des données dans votre feuille de calcul, ce qui rend l’analyse plus intuitive. Que vous travailliez sur la version desktop ou web d’ONLYOFFICE, cette macro s’intègre parfaitement à votre flux de travail.

Ne manquez pas l’occasion d’explorer tout le potentiel de l’API ONLYOFFICE. Grâce à un large éventail de méthodes, vous pouvez donner vie à vos idées d’automatisation. Si vous avez des questions ou des concepts innovants, n’hésitez pas à les partager avec nous. Nous apprécions votre contribution et nous nous réjouissons de collaborer avec vous. Nous vous souhaitons bonne chance dans vos projets d’exploration !

À propos de l’auteur