Erstellen Sie eine Heatmap für Tabellendaten mit einem ONLYOFFICE-Makro

Im Bereich der Datenanalyse ist es entscheidend, Abweichungen in Zahlenwerten auf einen Blick zu erkennen, um fundierte Entscheidungen treffen zu können. Das manuelle Formatieren jeder Zelle ist jedoch zeitaufwendig und fehleranfällig. Dieses ONLYOFFICE-Makro löst dieses Problem, indem es die Zellfarben dynamisch anpasst und so die Interpretation komplexer Daten deutlich erleicht.

Erstellung des Makros

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

Zunächst initialisieren wir das aktive Tabellenblatt, die Auswahl sowie den Bereich innerhalb des Tabellenblatts und speichern sie jeweils in den Variablen sheet, range und data.

Fehlerbehandlung: „Keine Daten ausgewählt“

Unmittelbar danach überprüfen wir, ob tatsächlich Daten ausgewählt wurden. Falls keine Auswahl erfolgt ist, beendet das Makro seine Ausführung mit einer entsprechenden Meldung: „Keine Daten ausgewählt.“

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

Extrahieren der Zeilen- und Spaltenindizes aus einer Auswahl

Wenn Daten vorhanden sind, fahren wir fort, indem wir ihre Parameter abrufen – Start- und Endindex der Spalten sowie Start- und Endindex der Zeilen.

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

Es gibt viele Möglichkeiten, die Indizes zu ermitteln, aber eine davon wird im obigen Codeausschnitt gezeigt:

• range.GetCells ()– wir greifen auf die Zellen unserer Auswahl zu, und durch Anhängen von .Row () oder .Col () erhalten wir den Index der ersten Zeile bzw. der ersten Spalte.
• Sobald wir diese Werte haben, können wir data.length verwenden, was uns die Anzahl der Zeilen in unseren Daten liefert. Anschließend addieren wir diesen Wert zur Variable firstRowIndex, um den Index der letzten Zeile zu erhalten. Dasselbe machen wir für die Spalten, wobei wir data[0].length verwenden, um die Anzahl der Spalten zu ermitteln, und diesen Wert zur Variable firstColIndex addieren.

Warum data[0]?

Da wir nicht wissen, wie viele Zeilen wir haben – und data[0], data[1] usw. jeweils eine Zeile repräsentieren – können wir uns nur sicher sein, dass unsere Daten mindestens eine Zeile enthalten. Das bedeutet, dass data[0] immer gültig ist, sofern die Variable data überhaupt Werte enthält.

Sammeln von Zahlen aus den ausgewählten Tabellenzellen

Als Nächstes erstellen wir das Array values und füllen es mit Zahlen aus unserer Auswahl.

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

Nachdem das Array values initialisiert wurde, iterieren wir durch jede Zelle in unserer Auswahl.

Standardmäßig wird der Wert in jeder Zelle als String behandelt. Daher müssen wir, bevor wir prüfen, ob ein Wert eine Zahl ist, zunächst die Methode parseFloat() verwenden, um ihn in eine Zahl umzuwandeln.

Wenn der String in der Zelle eine Zahl darstellt, wird er durch parseFloat in eine tatsächliche Zahl umgewandelt. Falls nicht, wird daraus ein NaN (Not a Number).

Die Funktion isNaN(…) prüft, ob der übergebene Wert keine Zahl ist. Wenn wir ein „!“ vor isNaN(…) setzen, überprüfen wir, ob der Wert eine gültige Zahl ist.

Wenn der Wert eine Zahl ist, initialisieren wir die Variable value und speichern die Zahl darin. Anschließend fügen wir diesen Wert dem values-Array hinzu.

Nach dem Durchlauf enthält das Array values alle Zahlenwerte aus den ausgewählten Zellen.

Der Grund, warum wir dieses Array brauchen, ist, dass wir damit den Minimal- und Maximalwert aus den ausgewählten Zellen ermitteln können – und zwar mit den JavaScript-Methoden Math.min() und Math.max().

Ermitteln des Minimal- und Maximalwerts

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

Anwenden von Farben

Jetzt, da wir alle notwendigen Informationen haben, können wir benutzerdefinierte Farben auf die Zellen mit Zahlen anwenden.

Da wir sowohl den Minimal- als auch den Maximalwert aus den ausgewählten Zellen haben, können wir erneut durch die Zellen iterieren.

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

Wenn wir eine Farbe im RGB-System erstellen, benötigen wir die Parameter Rot, Grün und Blau, um jede gewünschte Farbe zu erzeugen. In diesem Fall soll die Farbe von Grün zu Rot verlaufen – das bedeutet, dass der Blauwert auf 0 bleibt, während sich Rot- und Grünwerte je nach gewünschtem Farbton verändern.

Wenn der Zahlenwert klein ist – also näher am Minimalwert – wird die Farbe grüner (der Minimalwert entspricht reinem Grün). Umgekehrt, wenn der Wert groß ist, verschiebt sich die Farbe in Richtung Rot (der Maximalwert entspricht reinem Rot).

Um zu bestimmen, wie groß oder klein ein Wert ist, verwenden wir die Min-Max-Normalisierung, um ein Verhältnis („ratio“) zwischen 0 und 1 zu berechnen. Ein größerer Wert ergibt ein Verhältnis näher bei 1, ein kleinerer Wert ergibt ein Verhältnis näher bei 0. Dieses Verhältnis wird mithilfe der Variablen minValue und maxValue berechnet.

Anschließend verwenden wir dieses Verhältnis, um die Werte für Rot und Grün zu bestimmen. Im Gegensatz zum Verhältnis, das zwischen 0 und 1 liegt, reichen die RGB-Werte von 0 bis 255. Daher multiplizieren wir das Verhältnis entsprechend.

Da Rot bei größeren Zahlen dominieren soll, berechnen wir den Rotwert, indem wir das Verhältnis mit 255 multiplizieren und auf die nächste ganze Zahl runden.

Der Grünwert wird anders berechnet: Er soll bei kleineren Zahlen dominieren, daher verwenden wir 1 − Verhältnis, das wir ebenfalls mit 255 multiplizieren. Je größer der Zahlenwert, desto näher ist das Verhältnis bei 1, wodurch 1 − Verhältnis kleiner wird – und somit auch der Grünwert.

Sobald wir die Werte für Rot und Grün haben, verwenden wir Api.CreateColorFromRGB(r, g, b), um die Farbe zu erstellen, und wenden sie mit .SetFillColor(color) auf die Zelle an.

Der vollständige Makrocode

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

Lassen Sie uns das Makro ausführen und sehen, wie es funktioniert!

Bevor wir das Makro ausführen:

Nachdem wir das Makro ausgeführt haben:

Jetzt können Sie die Datenverteilung in Ihrer Tabelle ganz einfach visualisieren – die Analyse wird dadurch deutlich intuitiver. Egal ob Sie mit der Desktop- oder der Webversion von ONLYOFFICE arbeiten – dieses Makro lässt sich nahtlos in Ihren Arbeitsablauf integrieren.

Verpassen Sie nicht die Gelegenheit, das volle Potenzial der ONLYOFFICE API zu entdecken. Mit einer Vielzahl an verfügbaren Methoden können Sie Ihre Automatisierungsideen Wirklichkeit werden lassen. Wenn Sie Fragen oder innovative Konzepte haben, zögern Sie nicht, sie mit uns zu teilen. Wir schätzen Ihr Feedback sehr und freuen uns auf die Zusammenarbeit mit Ihnen. Viel Erfolg bei Ihren Entdeckungen!

Über den Autor