Crie um mapa de calor de dados de planilha usando uma macro do ONLYOFFICE

Na análise de dados, reconhecer variações em valores numéricos rapidamente é crucial para a tomada de decisões. Formatar cada célula manualmente pode ser demorado e propenso a erros. Esta macro do ONLYOFFICE resolve esse problema ajustando dinamicamente as cores das células, facilitando a interpretação eficiente de dados complexos.

Construindo a macro

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

Para começar, inicializamos a planilha ativa, a seleção e o intervalo dentro das variáveis ​​planilha, intervalo e dados, respectivamente.

Tratamento do erro “Nenhum dado selecionado”

Imediatamente após isso, verificamos se algum dado foi realmente selecionado. Caso nenhum dado seja selecionado, encerramos a função macro com a mensagem apropriada: “Nenhum dado selecionado“.

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

Extraindo índices de linha e coluna de uma seleção

Se houver dados presentes, prosseguimos recuperando seus parâmetros: índices de início de coluna, fim de coluna, início de linha e fim de linha.

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

Há muitas maneiras de obter os índices, mas uma delas é mostrada no trecho de código acima:

• range.GetCells() – acessamos as células da nossa seleção e, anexando .Row() ou .Col(), obtemos o índice da primeira linha e da primeira coluna.
• Com isso em mãos, podemos usar data.length, que nos dará o número de linhas em nossos dados. Em seguida, adicionamos isso à variável firstRowIndex para obter o índice da última linha. Fazemos o mesmo para colunas, usando data[0].length para obter o número de colunas e, em seguida, adicionando a variável firstColIndex.

Porque data[0]?

Como não sabemos quantas linhas temos, e cada um dos campos data[0], data[1], etc. representa uma linha, a única certeza que podemos ter é que nossos dados sempre terão pelo menos uma linha. Isso significa que data[0] sempre será válido se a variável data contiver algum valor.

Coletando números de células selecionadas da planilha

Em seguida, criamos a matriz em values e a preenchemos com números da nossa seleção.

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

Após inicializar o array de valores, iteramos por cada célula da nossa seleção.

Por padrão, o valor em cada célula é tratado como uma string, portanto, quando verificamos se o valor é um número, primeiro precisamos usar o método parseFloat() para convertê-lo em um número.

Se a string dentro da célula representar um número, parseFloat a transformará em um número. Caso contrário, a transformará em um NaN (Não é um Número).

A função isNaN(…) verifica se o valor dentro dos parênteses não é um número. Se adicionarmos um “!” antes de isNaN(…), estamos verificando se o valor é um número.

Se o valor for um número, inicializamos a variável value e armazenamos o número nela. Em seguida, anexamos esse valor ao array values.

Após a iteração, obtemos o array values, que contém todos os números das células selecionadas.

Precisamos desse array para encontrar os números mínimo e máximo das células selecionadas usando os métodos JavaScript Math.min() e Math.max().

Encontrando o valor mínimo e máximo

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

Aplicando cores

Agora que temos todas as informações necessárias, podemos aplicar cores personalizadas às células que contêm números.

Temos os números mínimo e máximo das células selecionadas, então podemos iterar pelas células mais uma vez.

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

Ao criar uma cor usando o sistema RGB, precisamos dos parâmetros vermelho, verde e azul para gerar a cor desejada. Nesse caso, a cor deve variar de verde a vermelho, ou seja, o parâmetro azul permanece em 0, enquanto os parâmetros vermelho e verde variam dependendo da tonalidade desejada.

Se o número for pequeno — mais próximo do valor mínimo — a cor será mais verde (o valor mínimo corresponde ao verde puro). Por outro lado, se o número for grande, a cor se deslocará para o vermelho (o valor máximo corresponde ao vermelho puro).

Para determinar o quão grande ou pequeno é o número, usamos o método de Normalização Mín-Máx para obter uma “razão” entre 0 e 1. Um número maior resulta em uma razão mais próxima de 1, enquanto um número menor produz uma razão mais próxima de 0. Essa razão é calculada usando as variáveis ​​minValue e maxValue.

Podemos então usar essa proporção para determinar os parâmetros vermelho e verde. Ao contrário da proporção, que varia de 0 a 1, os valores vermelho e verde variam de 0 a 255, então multiplicamos a proporção de acordo.

Como o vermelho deve ser mais dominante para números maiores, calculamos a proporção multiplicando a proporção por 225 e arredondando para o número inteiro mais próximo.

Para o verde, o cálculo é diferente. O verde deve ser mais dominante para números menores, então usamos a razão 1, multiplicando-a por 225. Quando o número é maior, a razão se aproxima de 1, tornando a razão 1 menor, o que, por sua vez, reduz o valor do verde.

Uma vez obtidos os parâmetros vermelho e verde, usamos Api.CreateColorFromRGB(r, g, b) para criar a cor e .SetFillColor(color) para aplicá-lo à célula.

O código macro completo

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

Vamos executar a macro e ver como ela funciona!

Antes de executar a macro:

Depois de executarmos a macro:

Agora você pode visualizar facilmente a distribuição de dados em sua planilha, tornando a análise mais intuitiva. Seja na versão desktop ou web do ONLYOFFICE, esta macro se integra perfeitamente ao seu fluxo de trabalho.

Não perca a oportunidade de explorar todo o potencial da API do ONLYOFFICE. Com uma ampla gama de métodos, você pode dar vida às suas ideias de automação. Se tiver alguma dúvida ou tiver ideias inovadoras, sinta-se à vontade para entrar em contato e compartilhe-os conosco. Valorizamos sua contribuição e estamos ansiosos para colaborar com você. Boa sorte em seus empreendimentos exploratórios!

Sobre o autor