Kreirajte toplotnu mapu podataka u tabeli koristeći ONLYOFFICE makro

U analizi podataka, prepoznavanje varijacija u numeričkim vrednostima na prvi pogled je ključno za donošenje odluka. Ručno formatiranje svake ćelije može biti dugotrajno i podložno greškama. Ovaj ONLYOFFICE makro rešava taj problem dinamičkim podešavanjem boja ćelija, olakšavajući efikasnije tumačenje složenih podataka.

Izrada makroa

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

Za početak, inicijalizujemo aktivni list, selekciju i opseg unutar varijabli sheet, range i data, redom.

Rukovanje greškom “Nisu odabrani podaci”

Odmah nakon toga proveravamo da li su neki podaci zaista selektovani. Ako nije selektovano ništa, zaustavljamo izvršavanje makro funkcije uz odgovarajuću poruku: “Nisu odabrani podaci.”

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

Ekstrahovanje indeksa redova i kolona iz selekcije

Ako podaci postoje, nastavljamo sa pribavljanjem njihovih parametara—indeksi početne kolone, krajnje kolone, početnog reda i krajnjeg reda.

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

Postoji mnogo načina na koje možemo da dobijemo indekse, ali jedan od njih je prikazan u isečku koda iznad:

• range.GetCells() – pristupamo ćelijama selekcije, i dodavanjem .Row() ili .Col() dobijamo indeks prvog reda i prve kolone.
• Kada to imamo, možemo koristiti data.length, što će nam dati broj redova u podacima. Zatim dodajemo ovu vrednost varijabli firstRowIndex kako bismo dobili indeks poslednjeg reda. Istu stvar radimo za kolone, koristeći data[0].length da dobijemo broj kolona, a zatim dodajemo vrednost firstColIndex.

Zašto data[0]?

Pošto ne znamo koliko redova imamo, a svaki data[0], data[1], itd. predstavlja jedan red, jedina stvar u koju možemo biti sigurni jeste da naši podaci uvek imaju bar jedan red. To znači da će data[0] uvek biti validan ako promenljiva data sadrži bilo kakve vrednosti.

Prikupljanje brojeva iz selektovanih ćelija tabele

Zatim kreiramo niz values i popunjavamo ga brojevima iz naše selekcije.

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

 

Nakon inicijalizacije niza values, prolazimo kroz svaku ćeliju u selekciji.

Podrazumevano, vrednost u svakoj ćeliji se tretira kao string, tako da kada proveravamo da li je vrednost broj, prvo moramo koristiti metodu parseFloat() kako bismo je konvertovali u broj.

Ako string unutar ćelije predstavlja broj, parseFloat će ga pretvoriti u broj. Ako ne, pretvoriće ga u NaN (Not a Number).

Funkcija isNaN(…) proverava da li vrednost unutar zagrada nije broj. Ako dodamo znak “!” ispred isNaN(…), proveravamo da li jeste broj.

Ako je vrednost broj, inicijalizujemo promenljivu value i u nju smeštamo broj. Zatim tu vrednost dodajemo u niz values.

Nakon iteracije, dobijamo niz values, koji sadrži sve brojeve iz selektovanih ćelija.

Razlog zbog kojeg nam treba ovaj niz je da bismo pronašli minimalnu i maksimalnu vrednost iz selektovanih ćelija koristeći JavaScript metode Math.min() i Math.max().

Pronalaženje minimalne i maksimalne vrednosti

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

Primena boja

Sada kada imamo sve potrebne informacije, možemo primeniti prilagođene boje na ćelije koje sadrže brojeve.

Imamo i minimalnu i maksimalnu vrednost iz selektovanih ćelija, pa možemo još jednom proći kroz ćelije.

<br style="font-weight: 400;" /><br style="font-weight: 400;" />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
      }
    }
  }

Kada pravimo boju koristeći RGB sistem, potrebni su nam parametri crvene, zelene i plave kako bismo generisali željenu boju. U ovom slučaju, boja treba da ide od zelene ka crvenoj, što znači da parametar plave ostaje 0, dok se crvena i zelena menjaju u zavisnosti od nijanse.

Ako je broj mali—bliži minimalnoj vrednosti—boja će biti zelenija (minimalna vrednost odgovara čistoj zelenoj). Suprotno tome, ako je broj veliki, boja prelazi ka crvenoj (maksimalna vrednost odgovara čistoj crvenoj).

Da bismo utvrdili koliko je broj veliki ili mali, koristimo metodu Min-Max normalizacije da dobijemo “odnos” između 0 i 1. Veći broj rezultira odnosom bližim 1, dok manji broj daje odnos bliži 0. Ovaj odnos se računa pomoću minValue i maxValue promenljivih.

Zatim možemo koristiti ovaj odnos za određivanje crvenog i zelenog parametra. Za razliku od odnosa, koji se kreće između 0 i 1, vrednosti crvene i zelene idu od 0 do 255, pa ih množimo u skladu s tim.

Pošto crvena treba da bude dominantnija kod većih brojeva, računamo je množenjem odnosa sa 225 i zaokružujemo na najbliži ceo broj.

Za zelenu, računica je drugačija. Zelena treba da bude dominantnija kod manjih brojeva, pa koristimo 1−ratio, množimo ga sa 225. Kada je broj veći, odnos se približava 1, što čini 1−ratio manjim, a samim tim smanjuje zelenu vrednost.

Kada dobijemo crveni i zeleni parametar, koristimo Api.CreateColorFromRGB(r, g, b) da kreiramo boju i .SetFillColor(color) da je primenimo na ćeliju.

Kompletan kod makroa

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

Pokrenimo makro i pogledajmo kako funkcioniše!

Pre pokretanja makroa:

Nakon pokretanja makroa:

 

Sada možete lako da vizualizujete raspodelu podataka u svojoj tabeli, čime analiza postaje intuitivnija. Bilo da radite u desktop ili veb verziji ONLYOFFICE-a, ovaj makro se besprekorno integriše u vaš tok rada.

Ne propustite priliku da istražite puni potencijal ONLYOFFICE API-ja. Sa širokim spektrom metoda, možete realizovati sve svoje ideje o automatizaciji. Ako imate pitanja ili inovativne koncepte, slobodno ih podelite sa nama. Cenimo vaš doprinos i radujemo se saradnji sa vama. Srećno u istraživanju!

O autoru